EXPLAIN with index suggestions

Question

Is there any (preferably open source) software than can analyze a PostgreSQL EXPLAIN, and recommend the necessary indices that would speed up the query?

Answer 1

I literally just found this a couple of minutes ago: http://explain.depesz.com/. You paste in the results of your EXPLAIN ANALYZE and it shows you where there may be problems (it's even color coded).

From the help section...

explain.depesz.com is tool for finding real cause for slow queries. Generally, one would use EXPLAIN ANALYZE query; and read the output. The problem is that not all parts of the output are easily understandable by anybody, and it's not always obvious whether node that executes in 17.3ms is faster or slower than the one that runs in 100ms - given the fact that the first one is executed 7 times. To use the site, simply go to its first page and paste there explain analyze output from your psql. This output could look like this. After uploading you will be directed to page which shows parsed, and nicely (well, at least nice for me :) colorized to put emphasis on important parts. This could look like this. Side note: the url for colorized output is persistent, so you can simply use it to show it to others - for example - for those nice guys on irc channel #postgresql on freenode. This graph uses 4 colours to mark important things: white background - everything is fine yellow background - given node is worrying brown background - given node is more worrying red background - given node is very worrying Which color is used, is choosen based on which mode you will use: "Exclusive", "Inclusive" or "Rows X".

Answer 2

I'm not aware of any tool for Postgres that does this algorithmically, and in my opinion the human brain (and often a bit of experimentation in a dev environment) is really the only appropriate tool here. There are a lot of factors involved, including whether or not the query planner will even think your index is worth using -- something which is determined by the way your installation has tuned the query planner settings and the size/statistics on the involved table(s).

The best recommendation I can make is to do an EXPLAIN ANALYZE (the ANALYZE is important -- it will give you query & subplan run times), look at the results yourself & attack the biggest number you see first. You could probably write a parser to break up the EXPLAIN output (especially in 9.0 with JSON output), but I don't know of anyone who has tackled this yet (this is basically what optimizers for MS-SQL do...)

Answer 3

Nothing production grade, but for the curious, there is/was a research project to implement something like that. Search for "PostgreSQL index advisor".