Re: Hash grouping, aggregates

So one of the items on the TODO list is "Add hash for evaluating GROUP BY
aggregates (Tom)"

I'm finding this would benefit a lot of my queries. Most of the time seems to
be going into sorts for group by clauses. I don't know how long it would take
to build a hash of course, but I suspect it would be less than the sort.

Is this something a beginner could figure out? I'm thinking I need a normal
Hash node that builds exactly the same kind of hash as a join, then a HashScan
node that picks all the rows out of the hash.

The neat thing is that hash aggregates would allow grouping on data types that
have = operators but no useful < operator.

(Incidentally, I'm fond of "nested loop", I remember when I was a beginner SQL
programmer looking at plans and it was intuitively obvious what it meant. I
suspect for a beginner looking at "nestloop" it might not be quite so
obvious.)

--
greg

Bruno Wolff III <bruno(at)wolff(dot)to> writes:

> This is already in 7.4. You could try it out by building from CVS.
> From the HISTORY file:
> System can use either hash- or sort-based strategy for grouped
> aggregation

Ooh, doing that now. Thanks.

--
greg

On Tue, Feb 11, 2003 at 09:48:11 -0500,
Greg Stark <gsstark(at)mit(dot)edu> wrote:
>
> So one of the items on the TODO list is "Add hash for evaluating GROUP BY
> aggregates (Tom)"
>
> I'm finding this would benefit a lot of my queries. Most of the time seems to
> be going into sorts for group by clauses. I don't know how long it would take
> to build a hash of course, but I suspect it would be less than the sort.
>
> Is this something a beginner could figure out? I'm thinking I need a normal
> Hash node that builds exactly the same kind of hash as a join, then a HashScan
> node that picks all the rows out of the hash.

This is already in 7.4. You could try it out by building from CVS.
>From the HISTORY file:
System can use either hash- or sort-based strategy for grouped
aggregation

Greg Stark <gsstark(at)mit(dot)edu> writes:
> So one of the items on the TODO list is "Add hash for evaluating GROUP BY
> aggregates (Tom)"

It's done in CVS tip ... give it a try.

> The neat thing is that hash aggregates would allow grouping on data types that
> have = operators but no useful < operator.

Hm. Right now I think that would barf on you, because the parser wants
to find the '<' operator to label the grouping column with, even if the
planner later decides not to use it. It'd take some redesign of the
query data structure (specifically SortClause/GroupClause) to avoid that.

regards, tom lane

Bruno Wolff III <bruno(at)wolff(dot)to> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Greg Stark <gsstark(at)mit(dot)edu> writes:
>>> The neat thing is that hash aggregates would allow grouping on data types that
>>> have = operators but no useful < operator.
>>
>> Hm. Right now I think that would barf on you, because the parser wants
>> to find the '<' operator to label the grouping column with, even if the
>> planner later decides not to use it. It'd take some redesign of the
>> query data structure (specifically SortClause/GroupClause) to avoid that.

> I think another issue is that for some = operators you still might not
> be able to use a hash. I would expect the discussion for hash joins in
> http://developer.postgresql.org/docs/postgres/xoper-optimization.html
> would to hash aggregates as well.

Right, the = operator must be hashable or you're out of luck. But we
could imagine tweaking the parser to allow GROUP BY if it finds a
hashable = operator and no sort operator. The only objection I can see
to this is that it means the planner *must* use hash aggregation, which
might be a bad move if there are too many distinct groups.

regards, tom lane

On Tue, Feb 11, 2003 at 10:41:53 -0500,
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Greg Stark <gsstark(at)mit(dot)edu> writes:
> > So one of the items on the TODO list is "Add hash for evaluating GROUP BY
> > aggregates (Tom)"
>
> It's done in CVS tip ... give it a try.
>
> > The neat thing is that hash aggregates would allow grouping on data types that
> > have = operators but no useful < operator.
>
> Hm. Right now I think that would barf on you, because the parser wants
> to find the '<' operator to label the grouping column with, even if the
> planner later decides not to use it. It'd take some redesign of the
> query data structure (specifically SortClause/GroupClause) to avoid that.

I think another issue is that for some = operators you still might not
be able to use a hash. I would expect the discussion for hash joins in
http://developer.postgresql.org/docs/postgres/xoper-optimization.html
would to hash aggregates as well.

Tom Lane kirjutas T, 11.02.2003 kell 18:39:
> Bruno Wolff III <bruno(at)wolff(dot)to> writes:
> > Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> Greg Stark <gsstark(at)mit(dot)edu> writes:
> >>> The neat thing is that hash aggregates would allow grouping on data types that
> >>> have = operators but no useful < operator.
> >>
> >> Hm. Right now I think that would barf on you, because the parser wants
> >> to find the '<' operator to label the grouping column with, even if the
> >> planner later decides not to use it. It'd take some redesign of the
> >> query data structure (specifically SortClause/GroupClause) to avoid that.
>
> > I think another issue is that for some = operators you still might not
> > be able to use a hash. I would expect the discussion for hash joins in
> > http://developer.postgresql.org/docs/postgres/xoper-optimization.html
> > would to hash aggregates as well.
>
> Right, the = operator must be hashable or you're out of luck. But we
> could imagine tweaking the parser to allow GROUP BY if it finds a
> hashable = operator and no sort operator. The only objection I can see
> to this is that it means the planner *must* use hash aggregation, which
> might be a bad move if there are too many distinct groups.

If we run out of sort memory, we can always bail out later, preferrably
with a descriptive error message. It is not as elegant as erring out at
parse (or even plan/optimise) time, but the result is /almost/ the same.

Relying on hash aggregation will become essential if we are ever going
to implement the "other" groupings (CUBE, ROLLUP, (), ...), so it would
be nice if hash aggregation could also overflow to disk - I suspect that
this will still be faster that running an independent scan for each
GROUP BY grouping and merging the results.

-----
Hannu

Hannu Krosing <hannu(at)tm(dot)ee> writes:
> Relying on hash aggregation will become essential if we are ever going
> to implement the "other" groupings (CUBE, ROLLUP, (), ...), so it would
> be nice if hash aggregation could also overflow to disk

I did not make this happen, but it sounds like Joe and Natassa are
about to send their students out to do it ... in a month or so, we
can review the homework assignments and decide who gets an A ;-)

regards, tom lane