Re: An Idea for planner hints

Jim C. Nasby wrote:
> > Meet EXPLAIN ANALYZE.
>
> Which does no good for apps that you don't control the code on. Even
> if you do control the code, you have to find a way to stick EXPLAIN
> ANALYZE in front of every query, and figure out how to deal with
> what's comming back.

It would not be hard to create an "auto explain analyze" mode that
implicitly runs EXPLAIN ANALYZE along with every query and logs the
result. On its face, it sounds like an obviously great idea. I just
don't see how you would put that to actual use, unless you want to read
server logs all day long. Grepping for query duration and using the
statistics views are much more manageable tuning methods. In my view
anyway.

> Going back to the original discussion though, there's no reason this
> needs to involve EXPLAIN ANALYZE. All we want to know is what columns
> the planner is dealing with as a set rather than individually.

This would log a whole bunch of column groups, since every moderately
interesting query uses a column in combination with some other column,
but you still won't know which ones you want the planner to optimize.

To get that piece of information, you'd need to do something like
principal component analysis over the column groups thus identified.
Which might be a fun thing to do. But for the moment I think it's
better to stick to declaring the interesting pairs/groups manually.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/