PostgreSQL Count(*) Performance Improvements
cybertec-postgresql.com
PostgreSQL Count(*) Performance Improvements
1–10 of 35 posts
Re: PostgreSQL Count(*) Performance Improvements
#2I appreciate approaches that acknowledge when “close” is good enough. It would be interesting to graph how far off those numbers are to put a real metric to it.
Re: PostgreSQL Count(*) Performance Improvements
#3The headline makes it sound like they did work to improve the performance of count(*) in the postgres code, when that's not at all what the article is about.
This post from Citus is far more informative:
https://www.citusdata.com/blog/2016/10/12/count-performance/
Re: PostgreSQL Count(*) Performance Improvements
#4Mariadb
Re: PostgreSQL Count(*) Performance Improvements
#5Nothing to see here, move along.
Been using this since 2009 https://wiki.postgresql.org/wiki/Count_estimate
Re: PostgreSQL Count(*) Performance Improvements
#6Given the title, I expected this to be about how PG improved their counting. This is not what it was about.
I remember working over half a billion records and having problems when I needed a count. I used count(id) but that was mainly from internet mantra. I did not see an improvement. Using Citus gave me a significant improvement from 7 minutes to 1. And that was just a single coordinator, two workers on the same host. It could become much much better.
If the data is very stagnant and writes are very low the triggers are great. Usually the "close enough" with pages is good if you have over 100k since paging - please correct me if I'm wrong - is sometimes 1k off.
My preference is Citus as a catch all, but a trigger, a Redis cache managed at the app level, or using page counts are all . really useful for stickier situations.
Re: PostgreSQL Count(*) Performance Improvements
#7| A note about count(1) vs count(* ). One might think that count(1) would be faster because count(* ) appears to consult the data for a whole row. However the opposite is true.
Which is the opposite decision of other databases. So sometimes Postgres does make bad decisions...
Re: PostgreSQL Count(*) Performance Improvements
#8The headline makes it sound like they did work to improve the performance of count(*) in the postgres code, when that's not at all what the article is about. This post from Citus is far more informative: https://www.citusdata.com/blog/2016/10/12/count-performance/
+1. Citusdata article is much better.
Re: PostgreSQL Count(*) Performance Improvements
#9Sorry for being a bit off topic, but anyone out there who is using PostgreSQL in production, how do you manage tables with lots of updates? Is auto-vacuum doing good enough job for you or do you have to run “vacuum full” regularly?
Re: PostgreSQL Count(*) Performance Improvements
#10| A note about count(1) vs count(* ). One might think that count(1) would be faster because count(* ) appears to consult the data for a whole row. However the opposite is true. Which is the opposite decision of other databases. So sometimes Postgres does make bad decisions...
I think the "the opposite is true" is in reference to the assertion that "count(⧆) appears to consult the data for a whole row", not to the proposition that "one might think that count(1) would be faster [than count(⧆)]".
count(⧆) just counts the tuples themselves, which is fast; it's like counting heap-allocated data structures by counting their pointers (which you're already walking), without dereferencing those pointers.
count(1) counts the result of evaluating the SQL expression "1" upon landing on each row, but still walks the same pointers to do so.
So, in terms of time complexity, they're roughly equivalent. Both data items (the tuple and the SQL constant expression) are already on the stack, ready to be directly computed upon.
Postgres's count(1) isn't slower than the one in any other DBMS. It's just their count(⧆)—at least the expression-evaluation part of it—which is more optimized than the one in other DBMSes. Nothing wrong with that, IMHO.