1

Describe the problem you’re having:
Loading articles in the special ‘all articles’ feed is quite slow, as in takes seconds. (Other queries/actions are quite fast, as in take tens of milliseconds.)

tt-rss version (including git commit id):
d7973fe1b

Platform (i.e. Linux distro, PHP, PostgreSQL, etc) versions:
Arch Linux, PHP 7.4.12, PostgreSQL 12.5

PostgreSQL has an effective_cache_size of 3 GB, shared_buffers of 1 GB, work_mem of 32 MB.

select count(*) from ttrss_entries;
 count
--------
 247063

The responsible query is this:

Summary 
EXPLAIN ANALYZE SELECT DISTINCT date_entered, to_char(date_entered, 'IYYY-IW') AS yyiw, guid, ttrss_entries.id,ttrss_entries.title, updated, label_cache, tag_cache, always_display_enclosures, site_url, note, num_comments, comments, int_id, uuid, lang, hide_images, unread,feed_id,marked,published,link,last_read,orig_feed_id, last_marked, last_published, ttrss_feeds.title AS feed_title,favicon_avg_color, content, author,score
FROM
  ttrss_entries LEFT JOIN ttrss_user_entries ON (ref_id = ttrss_entries.id)
  LEFT JOIN ttrss_feeds ON (feed_id = ttrss_feeds.id)
WHERE
  ttrss_user_entries.owner_uid = '2' AND
  true ORDER BY updated DESC
LIMIT 30 OFFSET 0;

And the analysis:

Summary 
 Limit  (cost=184978.79..184981.19 rows=30 width=819) (actual time=2500.231..2500.277 rows=30 loops=1)
   ->  Unique  (cost=184978.79..204332.55 rows=241922 width=819) (actual time=2500.229..2500.272 rows=30 loops=1)
         ->  Sort  (cost=184978.79..185583.60 rows=241922 width=819) (actual time=2500.226..2500.241 rows=30 loops=1)
               Sort Key: ttrss_entries.updated DESC, ttrss_entries.date_entered, (to_char(ttrss_entries.date_entered, 'IYYY-IW'::text)), ttrss_entries.guid, ttrss_entries.id, ttrss_entries.title, ttrss_user_entries.label_cache, ttrss_user_entries.tag_cache, ttrss_feeds.always_display_enclos...
               Sort Method: external merge  Disk: 181736kB
               ->  Hash Left Join  (cost=11.46..75695.41 rows=241922 width=819) (actual time=0.360..1277.200 rows=241777 loops=1)
                     Hash Cond: (ttrss_user_entries.feed_id = ttrss_feeds.id)
                     ->  Merge Join  (cost=1.48..74414.99 rows=241922 width=731) (actual time=0.095..994.593 rows=241777 loops=1)
                           Merge Cond: (ttrss_entries.id = ttrss_user_entries.ref_id)
                           ->  Index Scan using ttrss_entries_pkey on ttrss_entries  (cost=0.42..57357.95 rows=247221 width=615) (actual time=0.050..430.654 rows=247064 loops=1)
                           ->  Index Scan using ttrss_user_entries_ref_id_index on ttrss_user_entries  (cost=0.42..13426.46 rows=241922 width=120) (actual time=0.036..229.041 rows=241777 loops=1)
                                 Filter: (owner_uid = 2)
                                 Rows Removed by Filter: 5287
                     ->  Hash  (cost=8.88..8.88 rows=88 width=60) (actual time=0.158..0.159 rows=88 loops=1)
                           Buckets: 1024  Batches: 1  Memory Usage: 17kB
                           ->  Seq Scan on ttrss_feeds  (cost=0.00..8.88 rows=88 width=60) (actual time=0.025..0.091 rows=88 loops=1)
 Planning Time: 2.134 ms
 Execution Time: 2549.373 ms

Any options how to improve this? Tuning to PostgreSQL config, to tt-rss’ schema, to the query itself? Or will only faster hardware speed this up?

2

big performance killer in this query is selecting DISTINCT entries, removing it makes everything a lot simpler.

you can try removing this keyword from relevant queries (see below):

diff 
diff --git a/classes/feeds.php b/classes/feeds.php
index c1b7e8022..da524e972 100755
--- a/classes/feeds.php
+++ b/classes/feeds.php
@@ -1729,7 +1729,7 @@ class Feeds extends Handler_Protected {

                        if (!$search && !$skip_first_id_check) {
                                // if previous topmost article id changed that means our current pagination is no longer valid
-                               $query = "SELECT DISTINCT
+                               $query = "SELECT
                                                        ttrss_feeds.title,
                                                        date_entered,
                             $yyiw_qpart,
@@ -1769,7 +1769,7 @@ class Feeds extends Handler_Protected {
                                }
                        }

-                       $query = "SELECT DISTINCT
+                       $query = "SELECT
                                                date_entered,
                         $yyiw_qpart,
                                                guid,
@@ -1810,7 +1810,7 @@ class Feeds extends Handler_Protected {
                } else {
                        // browsing by tag

-                       $query = "SELECT DISTINCT
+                       $query = "SELECT
                                                        date_entered,
                                                        guid,
                                                        note,

this might have various unexpected consequences though. duplicate headlines, maybe.

i’ve added the following optional hidden tunable which disables DISTINCT if defined:

https://git.tt-rss.org/fox/tt-rss/commit/e9673eb13d0cd549868db5b77adf81eb03aa5b8a

everyone is welcome to test this and report results here. both performance-wise and related to any changes in output rows. duplicates, broken lazy load, whatever else.

3

Indeed, without the distinct it’s much faster.
Why is the distinct there in the first place? Doesn’t it try to eliminate duplicate rows that include all columns including title, guid, uuid, id, author and even content … of which there should never and possibly cannot ever be duplicates?!

4

classes/feeds.php contains several other queries that look like this:
SELECT DISTINCT id FROM ttrss_entries, ttrss_user_entries WHERE ref_id = id

but ttrss_entries.id is the primary key, therefore unique, so these queries return distinct results by design, no?

5

some view perspectives could include duplicates (before?), although i don’t remember the details.

it wasn’t added for no reason, however the reason might no longer be applicable.

previously multiple user_entries could link to a single row in ttrss_entries. this is no longer the case though.

we don’t do deduplication anymore, not for years now, and we don’t allow one user to have duplicate entries, for example, in multiple feeds (that used to be an option). therefore, a bunch of those DISTINCTs is likely no longer needed.

also, frontend filters duplicate article IDs anyway, just in case there was some overlap with lazy loading.

e: those DISTINCTs in feeds.php should’ve been removed after the overhaul which canned deduplication but i suppose i never thought about it. still, i would like someone else to run this for a few days, and look for oddities.

6

Ok, so these queries could be improved as well, although change in performance will probably be negligible to none for DISTINCT id queries.

But for that “all articles” queries the DISTINCT across all those columns really killed performance. Execution time drops from ~2000ms to 1ms for LIMIT 30 and to a few ms for LIMIT 1000.

7

But for that “all articles” queries the DISTINCT across all those columns really killed performance. Execution time drops from ~2000ms to 1ms for LIMIT 30 and to a few ms for LIMIT 1000.

yeah it makes total sense but well i guess nobody noticed.

8

after some extensive reading of many boring articles i’m not really seeing any issues with DISTINCT disabled so i’m going to push its removal to trunk.

i think in this particular case performance gained is worth the change regardless of possible issues which are unlikely, i think, given that user entries and entries tables are in a 1-to-1 relationship now. if something is discovered we’ll see how to work around it.

i’m not going to change anything with catchup_feed() because of what is said above and because catching up is not really a big deal performance-wise.

9

I have seen dupes when viewing labels. I added “test” to 2 articles, each in different categories. Then I clicked the “test” link to view all articles w/ the label.

I added the label to articles:

  • Is Microsoft’s ‘Find Your Joy’ Holiday Ad Sad? - Slashdot; and
  • Tommy Kraft - Visual Effect Artist

The “Friday January 31, 2014” Lio comic already had a “test” label from its rss feed. You can see in the screenshot below that Tommy Kraft’s video is duplicated. I have other labels and I see some dupes there too; this is how I reproduced it.

test-label-dupes
test-label-dupes1366×211 38.6 KB

10

labels don’t come from the feed itself, unless you have the auto assign plugin enabled. maybe you mean tags?

also, i’m not sure if its possible to label something twice, at least i couldn’t. :slight_smile:

i’d like to replicate this but at the moment i’m not sure how.

btw there’s definitely duplicates now in the feed which has all labels combined, for obvious reasons.

11

I started noticing duplicate headlines appearing and this morning went and searched and found this. My seeing duplicates started when this change was made. This isn’t on “all articles” just on a category. Here is an example and you ca see the duplicates:

image
image1099×856 116 KB

12

yeah, i might have been a bit too hasty in removing DISTINCT:

  1. filtering strictly on frontend quickly desyncs LIMIT/OFFSET calculation thus breaking lazy load

  2. there’s a SELECT DISTINCT ON (columns) syntax (that mysql naturally doesn’t support) which might be somewhat faster because there’s less fields to process but i haven’t really noticed:

distinct on (...) query 
EXPLAIN ANALYZE SELECT DISTINCT ON (id, yyiw, ttrss_feeds.title, score , date_entered , updated )
						ttrss_entries.id AS id,
						date_entered,
						to_char(date_entered, 'IYYY-IW') AS yyiw,
						guid,
						ttrss_entries.title,
						updated,
						label_cache,
						tag_cache,
						always_display_enclosures,
						site_url,
						note,
						num_comments,
						comments,
						int_id,
						uuid,
						lang,
						hide_images,
						unread,feed_id,marked,published,link,last_read,orig_feed_id,
						last_marked, last_published,
						ttrss_feeds.title AS feed_title,favicon_avg_color,
						content, 
						author,score
					FROM
						ttrss_entries LEFT JOIN ttrss_user_entries ON (ref_id = ttrss_entries.id)
						LEFT JOIN ttrss_feeds ON (feed_id = ttrss_feeds.id)
					WHERE
					ttrss_user_entries.owner_uid = '2' AND
					true ORDER BY yyiw desc,  ttrss_feeds.title, score DESC, date_entered DESC, updated DESC
					LIMIT 30 OFFSET 0;

i guess it’s either duplicates or using the above with fallback DISTINCT for mysql.

e: i’m honestly not sure which one of left joins or w/e else causes these duplicates, i sadly couldn’t find any on my tt-rss database. this might be one of those situations where i’m too dumb to figure something out. :man_shrugging:

13

https://git.tt-rss.org/fox/tt-rss/commit/d06cc8267bb7dfadd3693260af8788aa990a304e

a shameful changeset, i guess.

e: from people affected i would appreciate a database dump (postgres).

14

You’re correct, I did mean tags. I manually added “test” to a couple articles and when I cliked the “test” anchor in the article the resulting list had dupes.

15

This PR doesn’t fix the duplicate articles when viewing the “test” tag. I am running on gentoo, pgsql 12.4, apache 2.4.46, and php 7.4.11.

e. I’ll try to create a minimal reproducible database and dump it when I get some free time.

16

Got time quicker than I thought. The dump has this discuss and soylentNews subscribed. I added “test” tags to 2 articles (this one “Slow ‘all articles’ queries, options to improve?” and one in soylent “Laptop Vendors Will Pair AMD’s Cezanne (Zen 3) with High-End GPUs”). The dupes show up when I click the “test” anchor. It shows 3 articles 1 for this discuss and the soylent article is duped. HTH.

BTW, this is dumped from my dev machine which is Win10, psql 12.1, apache 2.4.39, and php 7.3.7; but it shows the same behavior as my gentoo server.

Attacheddb.tt-rss.20201207.dump.gz (156.2 KB)

e. the articles I tagged are read; mark all unread to see the 2 tagged articles.

17

I tagged test to some more articles and removed it from the soylent article and the duplication disappeared. Uhoh, I thought it might be a plugin or something; so I disabled all plugins. I added test tags to more articles and randomly on some it caused dupes and others it did not, it might take adding test to 10+ articles b/f I saw the dupes again. This is a wily one : /

e. I don’t think the plugins effect this though.

18

this could be a tag-related bug somewhere which surfaced because of above changes.

browsing by tag is a separate query and, to me, a less interesting one, so don’t worry about the dumps and stuff.

it could as well use a full DISTINCT like it did before which would probably deal with this oddity, honestly i would prefer to not dig into tag-related tables and stuff unless absolutely necessary. :slight_smile:

19

The dupes don’t bother me. Sometimes removing/replacing them fixes it. The ones I do use don’t vary that much. This is a very low priority “feature”, IMO.

20

The dupes come from the ttrss_tags table, see screenshot. Somehow the tag/post_int_id is getting duplicated via the webapp.

ttrss-dupe-sql..PNG