On Wed, May 10, 2006 at 11:00:14PM +1000, chris smith wrote: > On 5/10/06, Alban Hertroys <alban@xxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
