4 ms·
Indexing all columns and then reading the statistics about which indexes are being used is an interesting approach, but it is only able to give you simple singl
by lobster_johnson 13y ago
Indexing all columns and then reading the statistics about which indexes are being used is an interesting approach, but it is only able to give you simple single-column indexes. Postgres has multi-column (composite) indexes and can be very clever about using them; additionally, there are the functional and partial indexes.
A better approach, I think, would be a tool that takes an SQL log and figures out the optimal indexes based on those SQL statements. It could run "explain" statements on the recorded queries to determine which columns need indexes, including ones that are queried together, which would benefit from multi-column indexes. The "explain" output would also give some information about common filter values which could be used to generate partial indexes.
Another way would be to use genetic algorithms to create random indexes, run the queries on a dummy database, and then read the index usage stats to determine the most efficient combinations.
What would be lovely is a tool that you could run autonomously every week or month, and which would simply add/remove indexes as it sees fit.
The ultimate solution would be for Postgres itself to figure out and report potential plan improvements. After all, the planner knows best.
- craigkerstiens 13y agoOriginal author here, the adding an index on every column is very much a joke. A better solution would be taking advantage of pg_stat_statements in Postgres 9.2. It normalizes all queries which are run and tracks total time spent in your DB on those queries, this would be a clear way to determine where indexes can likely improve your performance. There's actually an earlier post I did which talks a bit more about this - http://www.craigkerstiens.com/2013/01/10/more-on-postgres-performance/ http://www.craigkerstiens.com/2013/01/10/more-on-postgres-pe...
- ajtaylor 13y agoFeatures like pg_stat_statements are a major reason why I love Postgres. The dev team makes sure the foundations are rock solid, and then start pouring on the features. If only $work used Postgres...
- rosser 13y agoI think the thing you're looking for (or at least the first part of it) is called auto_explain. http://www.postgresql.org/docs/current/static/auto-explain.html http://www.postgresql.org/docs/current/static/auto-explain.h...
- lobster_johnson 13y agoNot really, that just does an explain automatically if the query exceeds a certain time. It may happen that the schema is optimal, in spite of the long execution time. What I am talking about would be "auto_improve", ie. where the planner has actually detected a subpar schema resulting in a slow query that can be improved by adding indexes.
- jacques_chester 13y ago> Another way would be to use genetic algorithms to create random indexes, run the queries on a dummy database, and then read the index usage stats to determine the most efficient combinations. I like GAs as much as the next bloke, but they're not suitable for every problem. It's a relatively small search space, but GAs impose a lot of overhead. I reckon a simple heuristic hill climber would probably thrash a GA on this task.
- lobster_johnson 13y agoYou're probably right, I was just throwing it out there; some kind of heuristic search algorithm, anyway. Postgres actually uses GAs for complex plans [1]. Because it's slower than the normal cost-based planner, it only kicks in when a query involves 12 or more tables. [1] http://www.postgresql.org/docs/9.1/static/geqo.html http://www.postgresql.org/docs/9.1/static/geqo.html
- jacques_chester 13y agoFascinating. Is it actually enabled by default or is a module that needs to be activated? The docco is a bit unclear. Nature-inspired computing does tend to begin shwoing up when very large search spaces appear.
- lobster_johnson 13y agoIt's enabled by default. You can set the threshold to something other than 12 if you want to: http://www.postgresql.org/docs/9.1/static/runtime-config-query.html#RUNTIME-CONFIG-QUERY-GEQO http://www.postgresql.org/docs/9.1/static/runtime-config-que...
- jacques_chester 13y agoThanks. The coolest thing I've been wrong about in ages.