If PostgreSQL count(*) is always slow how to paginate complex queries?

Question

If PostgreSQL's count(*) is always slow how to paginate complex queries?

Making triggers doesn't seem to be a good solution as long as in this case we have a lot of pages (for example different categories, filters, etc).

What to do if VACUUM/VACUUM ANALYZE/ANALYZE/VACUUM FULL doesn't help? What are the best practices to use count(*) with postgresql?

Answer 1

Did you read the heading on that article?

Note that the following article only applies to versions of PostgreSQL prior to 9.2. Index-only scans are now implemented.

Use 9.2 and you'll generally find you get much better results. Read the index-only scans wiki page for details.

That said, on older versions using LIMIT and OFFSET generally works fine. You can estimate rowcounts (and therefore pagecounts) using the table statistics if you don't mind a bit of variation. See "Estimating row count" in the article you already linked to.

Paginating using LIMIT and OFFSET is, IMO, an anti-pattern anyway. A lot of the time you can rephrase your pagination code so it uses sort_column > 'last_seen_value' LIMIT 100, i.e. it avoids the offset. This can sometimes result in very large performance gains.