High Performance PostgreSQL for Rails

Tricks for Fast COUNT() Queries

Bare COUNT(*) queries are known to be slow, although optimizations have been made in newer versions of PostgreSQL. To learn more, check out the post, “Faster PostgreSQL Counting.”^[181]

In the last chapter, you learned how they can also be avoided using application-level caches with tactics like a counter cache column and the .size method in Active Record that reads from that column.

While caches are nice, what if you’d like to directly improve the performance for SQL COUNT() queries? How could you do that?

Count queries can exist as two types: an unfiltered count of all rows for a table or a filtered count, for a portion of the rows in a table. The filtered type can be very slow but can be made fast using some ...