Feeds page is slow - Githubissues

lemon24 / reader

A Python feed reader library.

https://reader.readthedocs.io

BSD 3-Clause "New" or "Revised" License

438 stars 36 forks source link

Feeds page is slow #251

Closed lemon24 closed 3 years ago

lemon24 commented 3 years ago

In two ways (all timings on my deployment):

It takes .2s to get all the feeds, about as much as it takes to get just as many entries (~150).
- Possible fix: Create indexes on feeds on the feed sort keys.
With counts, it takes 1.6s.
- Possible fix: Create an index on entries on the feed (may enable faster counting).
- Possible fix: Pre-compute/cache counts for get_entry_counts(feed=...) as feed metadata after feed updates, and return that instead.

lemon24 commented 3 years ago

Used the following indices, all confirmed to work for the relevant query with explain query plan:

create index feeds_by_kinda_title on feeds(
    lower(coalesce(user_title, title, '')) ASC,
    url ASC
);
create index feeds_by_added on feeds(
    added desc,
    url desc
);
create index entries_by_feed on entries(
    feed
);

The ones on feeds didn't really help (likely because there are so few feeds in the first place).

The one on entries made the with-counts page load time 2-3x faster.

lemon24 commented 3 years ago

OK, after adding entries_by_feed, /feeds?counts=yes on my deployment dropped down to .4s (4x faster).