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 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 [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 match
