PostgreSQL Count(*) Performance Improvements • Hacker News Board

Re: PostgreSQL Count(*) Performance Improvements

#11

Post by davidgould » Thu, Apr 11, 2019, 2:20 AM UTC

j16sdiz wrote: post #5

Nothing to see here, move along. Been using this since 2009 https://wiki.postgresql.org/wiki/Count_estimate

Be careful with this if your postgresql release is not up to date. There was a bug in the way ANALYZE updated pg_class.reltuples that could cause the value in reltuples to grow incorrectly for large tables. If the table had a lot of updates the pg_class.reltuples value would tend to increase a bit each time it got updated.

This was fixed in March 2018 and was backpatched so any binary release since then should be ok, eg 9.x.latest 10.x.latest, 11.x.

See [0] for details of the bug.

[0] https://www.postgresql.org/message-id/flat/20180117164916.3f...

Re: PostgreSQL Count(*) Performance Improvements

#12

Post by davidgould » Thu, Apr 11, 2019, 2:23 AM UTC

brightball wrote: post #2

I 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.

If you are using a recent postgreql release and have just run ANALYZE, the pg_class.reltuples percentage error relative to the true row count will almost always be less than 1.0/DEFAULT_STATISTICS_TARGET, and usually substantially better than that.

Re: PostgreSQL Count(*) Performance Improvements

#13

Post by sitharus » Thu, Apr 11, 2019, 2:51 AM UTC

hinkley wrote: post #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...

I'm not sure I understand your statement. On databases without an intrinsic row count property the only way to find the row count is to go to every page and count the rows there. The query planner will use the same plan for both `count(*)` and `count(1)`.

Re: PostgreSQL Count(*) Performance Improvements

#14

Post by znep » Thu, Apr 11, 2019, 3:04 AM UTC

sitharus wrote: post #13

hinkley wrote: post #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...

I'm not sure I understand your statement. On databases without an intrinsic row count property the only way to find the row count is to go to every page and count the rows there. The query planner will use the same plan for both `count(*)` and `count(1)`.

An interesting postgres oddity I ran into a few years ago that isn't about counting but is in a similar arena is how under very specific circumstances, "SELECT *" is significantly faster than "SELECT one_column" despite the conventional and normally well advised advice that you should only select the columns you need.

See https://www.postgresql.org/message-id/CAN1FPGN1ynBj3m1DMszc9... and Tom Lane's followup for details.

Re: PostgreSQL Count(*) Performance Improvements

#15

Post by zeroimpl » Thu, Apr 11, 2019, 3:41 AM UTC

Note the trigger approach mentioned in the article would be terrible for performance in a concurrent environment, since only one transaction could modify the whole table at a time.

Re: PostgreSQL Count(*) Performance Improvements

#16

Post by pageald » Thu, Apr 11, 2019, 3:56 AM UTC

noncoml wrote: post #9

Sorry 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?

For our use case, we found the best approach was to clone all of the data to a temporary table with indices and constraints disabled, perform the updates, re-enable indices and constraints, and then replace the production table with the temporary table. This only works if you are able to update your data in bulk, and if some lag time in your updates is OK. This also has the benefit of never locking your production table.

In situations where real-time updates are important, the key is to minimize your indices as much as possible. Read up on heap only tuples (HOT). If that all isn't enough, maybe consider sharding your database.

Never run VACUUM FULL; it locks too aggressively. Let autovacuum do the job.

Re: PostgreSQL Count(*) Performance Improvements

#17

Post by gnulinux » Thu, Apr 11, 2019, 4:07 AM UTC

noncoml wrote: post #9

Sorry 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?

vacuum full locks everything, never use that. We do vacuum analyze table_name depending on tuple count.

Re: PostgreSQL Count(*) Performance Improvements

#18

Post by simonw » Thu, Apr 11, 2019, 4:18 AM UTC

zeroimpl wrote: post #15

Note the trigger approach mentioned in the article would be terrible for performance in a concurrent environment, since only one transaction could modify the whole table at a time.

I wonder if there's a way to "shard" these counters to avoid this problem.

If you had 10 different counters (maybe in ten different tables) and a mechanism for round-robin or randomly selecting which counter gets incremented/decremented would that allow ten concurrent transactions at once?

The query to return the total count would then need to sum the 10 individual counters, which should be extremely fast.

Or is the concurrency limitation here caused by the trigger on the counted table itself, not the writes performed by the trigger?

Re: PostgreSQL Count(*) Performance Improvements

#19

Post by ht85 » Thu, Apr 11, 2019, 5:10 AM UTC

simonw wrote: post #18

zeroimpl wrote: post #15

Note the trigger approach mentioned in the article would be terrible for performance in a concurrent environment, since only one transaction could modify the whole table at a time.

I wonder if there's a way to "shard" these counters to avoid this problem. If you had 10 different counters (maybe in ten different tables) and a mechanism for round-robin or randomly selecting which counter gets incremented/decremented would that allow ten concurrent transactions at once? The query to return the total count would then need to sum the 10 individual counters, which should be extremely fast. Or is the…

Even though only 1 / 10 counters would be locked, you'd still have to read all 10 to get the count, which would be blocked until the concurrent transaction ended.

Re: PostgreSQL Count(*) Performance Improvements

#20

Post by hinkley » Thu, Apr 11, 2019, 5:39 AM UTC

sitharus wrote: post #13

hinkley wrote: post #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...

I'm not sure I understand your statement. On databases without an intrinsic row count property the only way to find the row count is to go to every page and count the rows there. The query planner will use the same plan for both `count(*)` and `count(1)`.

Not according to that blurb. There have been other databases where count(1) was much faster. Here they are saying that in Postgres, count(1) is slower because star is treated as no arguments, and count(1) is more complex.