Re: An Idea for planner hints
От | Jim C. Nasby |
---|---|
Тема | Re: An Idea for planner hints |
Дата | |
Msg-id | 20060816230230.GW21363@pervasive.com обсуждение исходный текст |
Ответ на | Re: An Idea for planner hints (Gregory Stark <gsstark@mit.edu>) |
Список | pgsql-hackers |
On Wed, Aug 16, 2006 at 06:48:09PM -0400, Gregory Stark wrote: > "Jim C. Nasby" <jnasby@pervasive.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@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
В списке pgsql-hackers по дате отправления: