Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL Count(*) Performance Improvements

cybertec-postgresql.com

Post reply on HN
Print view View original

21–30 of 35 posts

Re: PostgreSQL Count(*) Performance Improvements

#21

Post by masklinn »

derefr wrote: post #10
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 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 po…

I'm surprised count() is not special-cased in pretty much every database, and count(*) as well. There's no reason either would do anything further counting the number of records / tuples.

Re: PostgreSQL Count(*) Performance Improvements

#22

Post by ants_a »

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…

Sharding the counters would help, but with MVCC the typical solution is a delta table containing +1 and -1 records that is periodically compacted into the main count. With some cleverness about how to perform the compaction it's possible to make that very efficient.

Re: PostgreSQL Count(*) Performance Improvements

#23

Post by ants_a »

ht85 wrote: post #19
simonw wrote: post #18

Earlier quoted context omitted.

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.

With MVCC writers don't block readers.

Re: PostgreSQL Count(*) Performance Improvements

#24

Post by stubish »

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…

Instead of maintaining a single row in a table for the count, you maintain a table containing several rows. Instead of updating the single row, you insert a new row containing a 1 or -1. To get the count, you sum() the table. And you have a process to rollup the rows occasionally. This way you avoid the locks, except for the rollup.

Re: PostgreSQL Count(*) Performance Improvements

#25

Post by groestl »

BenMorganIO wrote: post #6

Given 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 ho…

As an alternative to page counts, we used HLLs to estimate (unique) cardinality, and were quite happy with it. There is a postgres extension (postgresql-hll) and also a version for the JVM using the same algorithm/data format.

Re: PostgreSQL Count(*) Performance Improvements

#27

Post by rejschaap »

derefr wrote: post #10
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 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 po…

> Postgres's count(1) isn't slower than the one in any other DBMS.

count( * ) is faster on Postgres than count(1). But both are fundementally slow because of MVCC. And count( * ) on postgres (the optimized one on Postgres) is much slower than count(1) on other databases (the optimzed one on other databases). So practically speaking, counting rows is slower on Postgres than on other databases.

That said, I love Postgres. I use it every day. There is some room for improvement and it does improve all the time. It is an amazing open source project. And I wouldn't care at all if they never optimize count(1)

Re: PostgreSQL Count(*) Performance Improvements

#28

Post by freekh »

It is not count() that is slow, it is iterating through the rows that is / can be slow :)

For me, the trick to basic understanding of perf in PG was exactly this: it is all about limiting the amount of rows you have to iterate over. It is true for count() but also for every other operation you do.

PG is surprisingly non-magical (at least in my experience) in that you won't get much perf for free, but on the other hand you can reason about perf & optimize pretty reliably once you come to terms with this.

Re: PostgreSQL Count(*) Performance Improvements

#29

Post by yardstick »

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?

If you are updating lots of rows, specify a low fill factor, both indexes and tables. It will avoid table bloat from dead pages by reusing existing pages for updates rather than appending the latest row version to a new page (and avoids the associated index update)

Eg

Create table (...) with (fillfactor=30)

https://www.postgresql.org/docs/current/sql-createtable.html

Re: PostgreSQL Count(*) Performance Improvements

#30

Post by Erwin »

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?

If you end up needing full vacuuming to reduce bloat, there's also the pg_repack extension (it's even supported on RDS).

The extension essentially creates a new table without the block and replaces the original one, keeping track of changes to the table using trigger; so the exclusive lock of VACUUM FULL is not needed for quite the long time.

Post reply on HN