A Collection of PostgreSQL Indexing Tips
craigkerstiens.com
craigkerstiens.com
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.
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.
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.
Nature-inspired computing does tend to begin shwoing up when very large search spaces appear.
http://www.postgresql.org/docs/current/static/auto-explain.h...
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.
- Indexes over expressions (e.g. upper(value)) are very helpful.
- You can add a WHERE clause to an index to index only part of the table, which is fantastically useful.
- on a non-C locale, LIKE won't (ever) use indexes. varchar_pattern_ops can help you with this to an extent, but it's not perfect.
You can also add pg_trgm as an extension and then create a GIN index. Not perfect at all but better than nothing.
Also indexes over expressions can be used to precalculate interesting sets if you have expensive routines in the database (the typical hypothetical example is image recognition).
That's the case for the C locale, but (as I understand it) not for other locales.
One solution I found after many struggles was to require all event tables to have a timestamp with timezone column, index its date() and require all queries attacking these tables to be selective on the date.
Edit: I ask because it's not included in the article's "Costs of Indexing".