3 ms·
I love Postgres, but the one thing I think sucks is it's COUNT() performance. I've read all sorts of hacks but I would love for someone to solve this for me!
by anko 10y ago
I love Postgres, but the one thing I think sucks is it's COUNT() performance.
I've read all sorts of hacks but I would love for someone to solve this for me!
- bottled_poe 10y agoHow about this: https://www.periscopedata.com/blog/use-subqueries-to-count-distinct-50x-faster.html https://www.periscopedata.com/blog/use-subqueries-to-count-d...
- einhverfr 10y agoI suspect one physical order index-only scans are supported this should be a lot faster.
- jeltz 10y agoI 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(*).
- falcolas 10y agoOne 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.
- ishi 10y agoNever thought of using EXPLAIN to get an estimated count... nice trick!
- pmontra 10y agoTriggers 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.