Re: understanding explain data

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).

--
Postgresql & php tutorials
http://www.designmagick.com/