Hacker News Reader

One trick with counts is that you very rarely need a perfectly accurate count for that exact moment in time; doing an explain on an appropriate 'SELECT' and capturing the estimated number of rows returned by that is usually good enough in 98% of the cases.

When you do need an accurate count, phrasing the query so the results can be pulled exclusively from the table index is also usually good enough.

Never thought of using EXPLAIN to get an estimated count... nice trick!

How about this: https://www.periscopedata.com/blog/use-subqueries-to-count-d...

Triggers on writes to update a counter? Basically you spread the cost of COUNT on INSERTs and UPDATEs. It might be a good tradeoff or a horrible one depending on your workloads.

I suspect one physical order index-only scans are supported this should be a lot faster.

I do not think anyone is working on this (I cannot recall seeing any discussion on the mailing list at least the last 2-3 years) and this is only true assuming the primary key is significantly narrower than the table, otherwise a sequential scan is the fastest way to implement count(*).