Re: Speeding up Aggregates

Greg Stark writes:
> Call it a wishlist bug. The problem is it would be a hard feature to
> implement properly. And none of the people paid to work on postgres
> by various companies seem to have this on their to-do lists. So
> don't expect it in the near future.

We are using Postgres heavily, and we should be able to find some time
and/or funding to help.

We're becoming more and more frustrated with the discontinuous
behaviour of the planner.  It seems every complex query we have these
days needs some "hint" like "ORDER BY foo DESC LIMIT 1" to make it run
on the order of seconds, not minutes.  We usually figure out a way to
write the query so the planner does the right thing, and pushes
the discontinuity out far enough that the user doesn't see it.
However, it takes a lot of work, and it seems to me that work would be
put to better use improving the planner than improving our knowledge
of how to get the planner to do the right thing by coding the SQL in
some unusual way.

Please allow me to go out on a limb here.  I know that Tom is
philosophically opposed to planner hints.  However, we do have a
problem that the planner is never going to be smart enough.  This
leaves the SQL coder the only option of collecting a bag of
(non-portable) SQL tricks.  I saw what the best SQL coders did at
Tandem, and frankly, it's scary.  Being at Tandem, the SQL coders also
had the option (if they could argue their case strong enough) of
adding a new rule to the optimizer.  This doesn't solve the problem
for outsiders no matter how good they are at SQL.

Would it be possible to extend the planner with a pattern matching
language?  It would formalize what it is doing already, and would
allow outsiders to teach the planner about idiosyncrasies without
changing the SQL.  It would be like a style sheet in Latex (or Scribe :-)
if you are familiar with these typesetting languages.

Comments?

Rob