Re: understanding explain data

On Wed, May 10, 2006 at 11:00:14PM +1000, chris smith wrote:
> On 5/10/06, Alban Hertroys <alban(at)magproductions(dot)nl> wrote:
> >Sim Zacks wrote:
> >> Something such as: with this explain data, adding an index on table tbl
> >> column A would drastically improve the efficiency. Or at least an
> >> application that would say, the least efficient part of your query is on
> >> this part of the code so that you could more easily figure out what to
> >> do about it.
> >
> >The latter part is the most useful IMO, optimizing usually needs a
> >(human) brain to put things into the right perspective. Adding an index
> >can speed up your queries only so much, a more optimal data presentation
> > (like moving calculations to insert/update instead of select) can do a
> >lot more sometimes.
> >
> >It looks like something like that shouldn't be too hard to write...
> >Maybe it even does exist already. Personally I'd prefer a command line
> >tool ;)
> >It would help if you can pipe the output of explain analyze to an
> >external tool from within psql.
>
> I've thought about writing a similar tool.. I'm about 30% of the way :)
>
> It's written in python and can grab the queries out of the db logs..
> but the harder part is working out the explain output.. also taking in
> to consideration an index might be available but not the best option
> for the query.
>
> I guess the easiest way to check is to have the script turn seq scans
> off when it runs explain and go from there.
>
> If anyone's interested in helping it go further contact me off list
> (can put it on pgfoundry.org and go from there if need be).

There's been some discussion about allowing EXPLAIN to produce
machine-readable output, probably in XML. I agree that it would be a lot
easier if there was some way you could take explain output and plug it
into some tool that would present an easier to understand format. I tend
to shy away from helping people that post EXPLAIN just because it's such
a PITA to read (especially if their email program word-wrapped the
output).
--
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