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

21–30 of 34 posts

Re: Postgres Count Performance

#21

Post by sbuttgereit »

throwawayReply wrote: post #14
sb8244 wrote: post #12

Earlier quoted context omitted.

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.

This is historically correct, but I believe later versions of mssql server now default to their mvcc implementation. I think this switch was circa 2005; not 100% sure.

I managed an enterprise applications group that primarily used mssql for data in 2007. I can't recall which mvcc implementation they use. Our servers were MSSQL 2000 and I remember being a bit more than surprised when the DBAs told me the root of the performance problems we had were due to lock escalation; having come from the Oracle and PostgreSQL worlds, I was naive enough to have thought that lock escalation implementations like this were historical curiosities rather than something I'd actually run into... live and learn I guess.

Re: Postgres Count Performance

#22

Post by honkhonkpants »

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…

Problems of this kind are the reason I always wanted a dbms to offer a direct plan API instead of a sql parser. It's dumb to fiddle with the statement until you get the desired plan. You should be able to just call for that plan.

Re: Postgres Count Performance

#23

Post by pgaddict »

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…

Right, I was going to point out that one of the main issues with the per-table tally (trigger-based) is limited concurrency, as all inserts/deletes have to modify the same row. Deadlocks (due to updating counters for different tables) are an extreme case of this. The solution (or rather a mitigation reducing the chance of a deadlocks) is having multiple counters for each table (say, 32), and updating only one of them (e.g. based on backed PID). The trouble is that this increases the size of row_counts table.

Another issue with the global tally is that the row_counts table bloats quite a bit, because the trigger is executed per-row and the update creates a copy of the row (so the next invocation has to walk all the previous MVCC copies, causing the long INSERT time). I wonder whether statement-level triggers might be used here, somehow (I don't think so).

Re: Postgres Count Performance

#24

Post by pgaddict »

A few comments regarding the count_distinct extension (author here):

> Note that custom extensions written in C like count_distinct are not bound by the value of work_mem. The array constructed in this extension can exceed your memory expectations.

This is slightly inaccurate, as this is not specific to custom aggregates. Custom aggregates are estimated just like any other (built-in) aggregates - number of expected groups times memory per group. The trouble is that (a) for aggregates with variable-length per-group state, we don't have a good size estimate, and (b) if the planner decides to use HashAggregate, we're unable to do anything when reaching work_mem. But this has nothing to do with the aggregate being custom - array_agg() and string_agg() have the same issue, for example.

FWIW, the extension was written quite a long time ago - before the various sort optimizations made by Peter Geoghegan. I wonder whether that made count_distinct obsolete.

Re: Postgres Count Performance

#25

Post by ozgune »

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…

(Ozgun from Citus Data)

We find that the primary motivation for using cstore is reducing disk I/O / storage footprint. cstore_fdw keeps a columnar layout on disk in compressed form and reads only relevant columns. For example, it's commonly used for data archival purposes.

That said, cstore_fdw doesn't yet make optimizations related to query planning and execution. We made experiments in that direction (https://news.ycombinator.com/item?id=8423825), but making those changes production ready is no small effort.

Since all benchmarks in this blog post are for in-memory data, I don't know how much they would benefit from cstore. If I have the time, I'll give it a try and update this comment with the results.

Re: Postgres Count Performance

#26

Post by mey »

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…

The middle ground to this sounds like a skip-list, but I haven't seen that implemented inside a SQL system. Maybe a trigger on inserts to the count row to randomly insert the skip list count. Problem would be, this would essentially require ordering and a transaction, so maybe could be calculated offline in an async way in some way.

Re: Postgres Count Performance

#28

Post by life_is_short »

pgaddict wrote: post #24

A few comments regarding the count_distinct extension (author here): > Note that custom extensions written in C like count_distinct are not bound by the value of work_mem. The array constructed in this extension can exceed your memory expectations. This is slightly inaccurate, as this is not specific to custom aggregates. Custom aggregates are estimated just like any other (built-in) aggregates - number of expected g…

> for aggregates with variable-length per-group state, we don't have a good size estimate

Can you say a bit more why?

Re: Postgres Count Performance

#29

Post by pgaddict »

life_is_short wrote: post #28
pgaddict wrote: post #24

A few comments regarding the count_distinct extension (author here): > Note that custom extensions written in C like count_distinct are not bound by the value of work_mem. The array constructed in this extension can exceed your memory expectations. This is slightly inaccurate, as this is not specific to custom aggregates. Custom aggregates are estimated just like any other (built-in) aggregates - number of expected g…

> for aggregates with variable-length per-group state, we don't have a good size estimate Can you say a bit more why?

No one implemented a better solution ;-)

Jokes aside, it seems simple but is fairly tricky, as it depends both on input data and various other parameters. For example for array_agg() or string_agg() it might be estimated from number of entries / average length. For hll it also depends on the accuracy and expected number of values to track, etc. I was considering adding another method to the API, providing a better estimate, but never got to that.

So the current code simply assumes 1KB (IIRC) per group in those cases, or something like that.

Of course, the memory estimate also depends on the number of groups, but that's an orthogonal issue.

Post reply on HN