Postgres Count Performance
citusdata.com
Postgres Count Performance
1–10 of 34 posts
Re: Postgres Count Performance
#2No mention in the article of the new parallel query support in 9.6 that can speed up COUNT(*)?
https://www.postgresql.org/docs/current/static/parallel-plan...
Re: Postgres Count Performance
#3It'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 a number of columnar databases in the past.
Re: Postgres Count Performance
#4Super 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, giving the database more memory resources can lead to a worse plan. You can force the index-only scan by setting SET enable_hashagg=false; but remember to set it true again afterward or other query plans will get messed up.
But worst case scenario, this article will be useful until this is fixed, so thanks again :)
Re: Postgres Count Performance
#5> On the other hand count(1) takes an argument and PostgreSQL has to check at every row to see that ts argument, 1, is indeed still not NULL.
I expect that's the same for all function calls like this? Surely pg has a concept of constants and doesn't needlessly re-check parameters?
Re: Postgres Count Performance
#6Super 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 planning is something where a poor choice can have serious performance ramifications, because n is usually much larger than in most programs. Analyzing the algorithmic complexity of a piece of SQL takes some experience and experimentation, and with different table stats the query planner may make different decisions. It can be worthwhile limiting the planner's discretion to get more predictable performance.
(I work on a product where many of the features can be expressed in terms of relational algebra. Often, both the best performing and quickest to write and test implementation logic is a bunch of SQL, and not the kind of CRUD that is easily wrapped with an ORM. What would make my life easier is a SQL linter that, given a model of costs, would prevent people writing queries that scale only linearly with specific table sizes. I've accumulated sufficient intuition that I could do this for MySQL at this point.)
Re: Postgres Count Performance
#7Even 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 background task is responsible for continually squashing the count values.
Re: Postgres Count Performance
#8Super 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…
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.
Re: Postgres Count Performance
#9No mention in the article of the new parallel query support in 9.6 that can speed up COUNT(*)? https://www.postgresql.org/docs/current/static/parallel-plan...
We were doing some benchmarks recently on different databases. Most of the queries were heavy aggregations (>1billion of rows) and, to be honest, we were a bit disappointed with the new parallel query support in pg, we were expecting much better performance.
While doing the benchmarks, we could see that citus was always taking full advantage of all the cores in the cluster, while postgres parallelization was not.
Disclaimer: I'm not a db expert and I don't have any relationship with either citus or postgres.
Re: Postgres Count Performance
#10The 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?