Before you start
Building an index on a big table takes time and disk, and you only learn afterwards whether the planner uses it. HypoPG lets you create a hypothetical index: it exists only in your session's memory, holds no data, and takes no lock. The planner still takes it into account when it plans, so you can see what the plan would be.
You need PostgreSQL with the hypopg extension available, and the right to create extensions. Try it on a test copy first.
Try an index
- Install the extension.
CREATE EXTENSION IF NOT EXISTS hypopg; - Create the hypothetical index.
SELECT * FROM hypopg_create_index( 'CREATE INDEX ON orders (customer_id, created_at)'); - Run plain EXPLAIN on your query.
EXPLAIN SELECT id, total FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20; - Look for the index in the plan. If the planner would use it, the plan shows an Index Scan that names it. The name looks like
"<13543>btree_orders_customer_id_created_at", where the number in angle brackets is made up by HypoPG. If the plan still shows a Seq Scan, the planner would ignore this index, so do not build it. - Clean up.
Closing the session also removes every hypothetical index.SELECT hypopg_reset();
What it can and cannot tell you
- It works with plain
EXPLAINonly. A hypothetical index does not exist on disk, soEXPLAIN ANALYZEcannot use it. - It shows whether the planner would use the index and the estimated cost, not how fast the query would really run.
- The estimate depends on the table statistics. If they are stale, run
ANALYZEon the table first. - Only your session sees the index, so other connections are unaffected.
Index advice in dbtempo
dbtempo proposes one index set for each PostgreSQL table from your real workload. See PostgreSQL index advisor for the advisor.