Craig Kerstiens: Will Postgres Use My Index? Hypothetical Indexing for Postgres

Postgres is a great database with a ton of features including really rich indexing. Postgres itself maintains all sorts of data under the covers about things like cache hits and misses, when indexes are and aren't used, and more. If you're staring at a complex explain plan you may think some well targeted indexes may help, but how can you be sure? Enter HypoPG, a Postgres extension for adding hypothetical indexes to Postgres so you can do index planning. HypoPG supports hypothetical indexes: b-tree brin hash but not gin or gist indexes First load up some sample data. We're going to leverage the same dataset from our Postgres tutorial on indexing with b-trees. And take a look at our first explain plan: EXPLAIN ANALYZE SELECT * FROM weather WHERE event_type = 'Winter Storm'; QUERY PLAN ----------------------------------------------------------------------------- Seq Scan on weather (cost=0.00..115.00 rows=11 width=622) (actual time=0.007..0.295 rows=11 loops=1) Filter: ((event_type)::text = 'Winter Storm'::text) Rows Removed by Filter: 1189 Planning Time: 0.332 ms Execution Time: 0.423 ms (5 rows) Here we can see it's using a sequential scan. One option is to go ahead and create an index, but how do we know it'll work? How do we know Postgres will actually use the index? HypoPG will show whether the hypothetical index would be used or not. Create the extension for HypoPG CREATE EXTENSION hypopg; Create a select with the create index statement inside the parenthesis. SELECT hypopg_create_index('CREATE INDEX idx_weather_type ON weather(event_type);'); We can see that our hypothetical index is now used <13731>btree_weather_event_type. hypopg_create_index ----------------------------------------- (13732,<13732>btree_weather_event_type) (1 row) Time: 91.903 ms Now we run EXPLAIN to see if it will use the hypothetical index EXPLAIN SELECT * FROM weather WHERE event_type = 'Winter Storm'; 🎉 [...]