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,
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
