even mention materialized views...
-
Robert Bedell
---(end of broadcast)---
TIP 8: explain analyze is your friend
More specifically I would like to add grouping sets, and the CUBE and
ROLLUP
operators, into postgresql. Since modifying such the GROUP BY operation
would necessitate changing the query structure, wouldn't that affect the
query rewrites and genetic optimizer?
I don't think either the
I guess that by adding hash aggregates Tom solved most problems of
adding ROLLUP, CUBE and GROUPING SETS.
OTOH, I'm not sure if hash aggregates can already spill to disk if not
enough memory is available for keeping them all. If not, then adding
this capability would be great push towards
it seems that the longest GROUPING SET and all its left-continuous
subsets could be collected from the sorted scan and the rest from hash
aggregates.
GROUPING SET () will always need a hash ;)
To optimise any further would require use of statistics data, and is
probably not a good idea
For pure ROLLUP one could shortcut the split-into-groups and
put-together-again process, as ROLLUP is already doable from single
sorted scan.
Actually as long as the grouping sets are all left-continuous of the
longest
grouping set it's doable from a single sorted scan. If done with
Hannu Krosing [EMAIL PROTECTED] writes:
OTOH, I'm not sure if hash aggregates can already spill to disk if not
enough memory is available for keeping them all.
They do not, which is something it'd be good to fix, since if the
planner drastically underestimates the number of groups, you
Thanks for the pointers!
The documentation points to the
'sort_mem' parameter for this, but the code doesn't look to actually
implement that yet.
Well, yeah, that's sort of exactly the point ... it's considered during
planning but the executor code has no fallback if the planner
That's the right area to be looking at, but I don't think you can expect
to do a decent job with localized hacking in LookupTupleHashEntry. That
function's API is predicated on the assumption that you have random
access to any entry in the hash table --- which stops being true as soon
as you