Re: An Idea for planner hints

On Tue, Aug 15, 2006 at 07:55:28PM +0200, Peter Eisentraut wrote:
> 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.

Well, the output would really need to go into some machine-readable
format, since you certainly aren't going to read it. That would also
make it trivial to identify plans that diverged greatly from reality.

> > 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.

Well, I guess there's actually two kinds of stats that are
interesting...

groups of columns that are often refered to as a group, ie:
WHERE a='blah' and b='bleh' and c='blech'

columns that are joined to other columns (perhaps in a group)

> 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.

Sure, but the idea is to make it easier to identify what those pairs
might be. If the grouping info was alwas in a deterministic order, then
simply doing

SELECT columns, count(*) ... GROUP BY columns ORDER BY count(*) DESC
LIMIT 10;

would be very useful. And given the data, if someone wanted to do a more
complex analysis they could.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461