3 ms·
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
by pgaddict 10y ago
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.
- life_is_short 10y ago> for aggregates with variable-length per-group state, we don't have a good size estimate Can you say a bit more why?
- pgaddict 10y agoNo 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.