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

Postgres Count Performance

citusdata.com

Post reply on HN
Print view View original

11–20 of 34 posts

Re: Postgres Count Performance

#11

Post by buremba »

andrea_s wrote: post #3

It's a bit odd there's no mention of the PG columnar store in this article ( https://www.citusdata.com/blog/2014/04/03/columnar-store-for... ) - especially since it's from the same company. It would be interesting to see how much the performances improve once you use cstore_fdw (especially since 1M records is quite small when talking about OLAP workloads). disclaimer: I've never used cstore_fdw, but I have evaluated…

I think cstore_fdw is not popular enough among Citus users. Only a few of their customers use it since it's not trivial to use cstore_fdw in real-time workloads. Given than its use-case is mainly analytics, it seems a bit odd though.

Re: Postgres Count Performance

#12

Post by sb8244 »

throwawayReply wrote: post #10

The counting seems really slow compared to MSSQL? I'm not familiar with the reference hardware used here, but just running a similar test on my desktop with sql server it can count distinct a million strings in under a second (with parallelisation, around 2.5s cpu time). Or am I missing the fact that these benchmarks are run on a reference spec which is comparatively old?

I'm not familiar with mssql, does it use mvcc? That is the reason PG counting is slow (really slow)

Re: Postgres Count Performance

#13

Post by sb8244 »

throwawayReply wrote: post #10

The counting seems really slow compared to MSSQL? I'm not familiar with the reference hardware used here, but just running a similar test on my desktop with sql server it can count distinct a million strings in under a second (with parallelisation, around 2.5s cpu time). Or am I missing the fact that these benchmarks are run on a reference spec which is comparatively old?

I'm not familiar with mssql, does it use mvcc? That is the reason PG counting is slow (really slow)

Re: Postgres Count Performance

#14

Post by throwawayReply »

sb8244 wrote: post #12
throwawayReply wrote: post #10

The counting seems really slow compared to MSSQL? I'm not familiar with the reference hardware used here, but just running a similar test on my desktop with sql server it can count distinct a million strings in under a second (with parallelisation, around 2.5s cpu time). Or am I missing the fact that these benchmarks are run on a reference spec which is comparatively old?

I'm not familiar with mssql, does it use mvcc? That is the reason PG counting is slow (really slow)

I'm not an expert, this is my layman's understands.

It has different isolation levels, some involving snapshots and some not. I think most concurrency issues are (by default) dealt with by locks, which start at row level and can escalate to page and table level (with significant slowdown seen when lock escalation happens in contentious places).

But that only has an effect if it's under write.

Re: Postgres Count Performance

#15

Post by eveningcoffee »

rowanseymour wrote: post #7

Even if counts could be made faster, at scale you'd probably still want to avoid counting anything that can be pre-calculated. We use something similar to the trigger-based method they describe, tho have found that a lot of updates to count table inevitably ends with deadlocks. So instead of updating a count value, we always insert a new count of 1 or -1, and use summing to calculate the total count as needed. A back…

This is very elegant solution.

Re: Postgres Count Performance

#16

Post by oelmekki »

rowanseymour wrote: post #7

Even if counts could be made faster, at scale you'd probably still want to avoid counting anything that can be pre-calculated. We use something similar to the trigger-based method they describe, tho have found that a lot of updates to count table inevitably ends with deadlocks. So instead of updating a count value, we always insert a new count of 1 or -1, and use summing to calculate the total count as needed. A back…

Great solution, thanks for sharing.

At this point, I wonder: with a huge dataset, wouldn't you have better time leaving the count field out of postgres altogether and use something like redis with its INCR/DECR instructions instead? This would prevent having deadlocks as well.

EDIT: that is, if you don't need to use the count field in other queries.

Re: Postgres Count Performance

#17

Post by brightball »

rowanseymour wrote: post #7

Even if counts could be made faster, at scale you'd probably still want to avoid counting anything that can be pre-calculated. We use something similar to the trigger-based method they describe, tho have found that a lot of updates to count table inevitably ends with deadlocks. So instead of updating a count value, we always insert a new count of 1 or -1, and use summing to calculate the total count as needed. A back…

Solid solution. I've done similar by just writing counts to Memcached and then periodically persisting them to the database. If I you don't have queries that actually need the counts the persist time can be stretched out to much longer windows too.

Re: Postgres Count Performance

#18

Post by rowanseymour »

oelmekki wrote: post #16
rowanseymour wrote: post #7

Even if counts could be made faster, at scale you'd probably still want to avoid counting anything that can be pre-calculated. We use something similar to the trigger-based method they describe, tho have found that a lot of updates to count table inevitably ends with deadlocks. So instead of updating a count value, we always insert a new count of 1 or -1, and use summing to calculate the total count as needed. A back…

Great solution, thanks for sharing. At this point, I wonder: with a huge dataset, wouldn't you have better time leaving the count field out of postgres altogether and use something like redis with its INCR/DECR instructions instead? This would prevent having deadlocks as well. EDIT: that is, if you don't need to use the count field in other queries.

We've found that Redis works great for counting some things, but when you need a count which is guaranteed to match what is in a db table, it's preferable to manage counts at a db level. Often we're making counts based on the state of a record, and it's easy for the application level view of things to end up stale.

Re: Postgres Count Performance

#19

Post by pavlik_enemy »

barrkel wrote: post #6
oelmekki wrote: post #4

Super in-depth analysis, thanks to the author for writing it. Past the recommendation of counting based on an indexed column, I wonder if this should really be user's concern. This paragraph especially triggers a "this should be fixed upstream" feeling in me: > A word of warning. When work_mem is high enough to hold the whole relation PostgreSQL will choose HashAggregate even when an index exists. Paradoxically, givi…

If you think "needs upstream fix", then no database is suitable for use, because they all choose poor plans in many edge cases. This is why most databases have extensions that let you hint or force index use (not Postgres, however; a mistake with mitigation), or support parenthesising your joins to force an evaluation order (this is an indirect way of forcing index use or join order, a mitigation), etc. Query plannin…

You can disable certain scan types in Postgres and that's something I had to do when no matter what it reported incorrect stats.

Re: Postgres Count Performance

#20

Post by pavlik_enemy »

oelmekki wrote: post #8
barrkel wrote: post #6

Earlier quoted context omitted.

If you think "needs upstream fix", then no database is suitable for use, because they all choose poor plans in many edge cases. This is why most databases have extensions that let you hint or force index use (not Postgres, however; a mistake with mitigation), or support parenthesising your joins to force an evaluation order (this is an indirect way of forcing index use or join order, a mitigation), etc. Query plannin…

Optimizing queries manually is fine by me, what troubled me was more the fact that it was recommended to change a configuration setting on the fly, which sounded like asking for problems regarding concurrent requests. But actually, I've just made a test, and it appears changing this setting only impacts the current connection, so provided it's toggled back after the request, this should not be a problem.

Though I wonder how it would work with pg-bouncer.
Post reply on HN