On Wed, Aug 16, 2006 at 06:48:09PM -0400, Gregory Stark wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> 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
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 [EMAIL PROTECTED]
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not