Re: understanding explain data

[Sim Zacks <sim@xxxxxxxxxxxxxx>]

I agree with you that an index isn't always the answer, that was more of 
an example. I was thinking more along the lines of an intelligent part 
of the database that has access to the statistics and would be able to 
spit out recommendations for the query.

Such as, I type in a monster query and say optimize and in would be able 
to spit out 4 smaller views that use each other and generate the same 
result in 10% of the time. Or to say this query is optimized, but you 
need an index on these columns.

I disagree with you that a human brain would be better then a machine 
for optimizing purposes. If the system is programmed to optimize 
correctly, then it will when to stick data into a temp table and update 
columns instead of doing a select because x number of joins are too much 
for the select. Humans may not know the optimal number of joins before 
the query becomes inefficent.

Alban Hertroys 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.
>
> Regards,