Menu
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?
852
So to make SELECT COUNT(*) queries fast, here's what to do:Get on any version that supports batch mode on columnstore indexes, and put a columnstore index on the table – although your experiences are going to vary dramatically depending on the kind of query you have.
Some of the tricks we used to speed up SELECT-s in PostgreSQL: LEFT JOIN with redundant conditions, VALUES, extended statistics, primary key type conversion, CLUSTER, pg_hint_plan + bonus.
A common need when working with large amounts of data is how to fetch the data and display it in a reasonable amount of time. The solution to this problem is pagination. Pagination can be done on the front-end, back-end, or both. Note: This technique works well in PostgreSQL and MySQL.
Typically discovered through slow response or extended increases in database CPU, the pg_stat_activity view can help to find out what query is causing issues. The pg_stat_activity view contains details of all currently running queries, including user, connection, and timing details.
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.
165
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
