Re: [HACKERS] longer-term optimizer musings - Mailing list pgsql-hackers

From Erik Riedel
Subject Re: [HACKERS] longer-term optimizer musings
Date
Msg-id oqyI3k200gNt8mTNoS@andrew.cmu.edu
Whole thread Raw
In response to Re: [HACKERS] longer-term optimizer musings  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
> > Also note that none of this actually speeds up even my query, it only
> > makes the optimizer estimate much closer to the actual query cost
> > (which is what I care about for the work I am doing).
>  
> Well, that could result in a more intelligently chosen plan further up
> the tree, so it *could* lead to a faster query.  However this would
> only be true if there were important choices to be made at higher tree
> levels.  I suppose you would have to be looking at a subselect involving
> GROUP BY for this to really make much difference in practice.
>  
Right, if there are still choices higher up.  In particular, the case
that I was looking at was the possible combination of Aggregation and
Sort nodes that I'd mentioned before.  Having the proper estimate at
that point would tell you if it were worthwhile doing the aggregation
(or duplicate elimination) while sorting.  Which could save lots of
memory and writing/reading of run files for out-of-core mergesort.

While I'm at it, I should note that this combination of aggr and sort is
not my invention by a long shot.  The paper:

"Fast Algorithms for Universal Quantification in Large Databases"

referenced at:

http://www.cse.ogi.edu/DISC/projects/ereq/papers/graefe-papers.html

claims this as the "obvious optimization" and provides pointers to the
(by now ancient) papers that discuss both this and combination of
aggregation and hashing, which should be even cheaper in general. 
Section 2.2, page 10.

I guess those things just never found their way into Stonebraker's
version of the code.  Maybe they are of interest in the future.

Erik



pgsql-hackers by date:

Previous
From: "Jackson, DeJuan"
Date:
Subject: RE: [HACKERS] backend unstable, \d broken, groups broken was CVS 3-22-99 \d broken?
Next
From: RHS Linux User
Date:
Subject: Re: [HACKERS] Really slow query on 6.4.2