Re: An Idea for planner hints

On Wed, Aug 16, 2006 at 06:48:09PM -0400, Gregory Stark wrote:
> "Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
>
> > On Tue, Aug 15, 2006 at 07:55:28PM +0200, Peter Eisentraut wrote:
> >
> > > 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.
>
> Oracle's EXPLAIN had a peculiar design feature that always seemed bizarre from
> a user's point of view. But here's where it begins to become clear what they
> were thinking.
>
> It stuffs the EXPLAIN output into a table. It means you can then use SQL to
> format the data for display, to generate aggregate reports of plans, or to
> search for plans or plan nodes that meet certain criteria. They don't even
> have to be plans generated by your session. You can have an application run
> explain on its queries and then go and peek at the plans from a separate
> session. And it doesn't interfere with the query outputting its normal output.
>
> I'm not sure it's worth throwing out the more user-friendly interface we have
> now but I think it's clear that a table is the obvious "machine-readable
> format" if you're already sitting in an SQL database... :)

Actually, I had another idea, though I'm not sure how useful it will
ultimately be...

There's now a program to analyze generic PostgreSQL logs, someone else
just posted that they're working on an analyzer for VACUUM, and there's
a desire for machine-readable EXPLAIN output. What about providing a
secondary logging mechanism that produces machine-readable output for
different operations? The three I just mentioned are obvious choices,
but there could be more.

> Also, incidentally you guys are still thinking of applications that don't use
> prepared queries and parameters extensively. If they do they won't have reams
> of plans since there'll only be one ream of plans with one plan for each query
> on a session start not one for each execution.

That behavior could presumably be changed if we added the ability to
analyze every statement a particular session was running.
--
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