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';

🎉 You can see in the plan below it did pick up the new index.

                                           QUERY PLAN
---------------------------------------------------------------------------
 Bitmap Heap Scan on weather  (cost=4.04..11.21 rows=2 width=2210)
   Recheck Cond: ((event_type)::text = 'Winter Storm'::text)
   ->  Bitmap Index Scan on "<13731>btree_weather_event_type"  (cost=0.00..4.04 rows=2 width=0)
         Index Cond: ((event_type)::text = 'Winter Storm'::text)
(4 rows)

Next we may want to create our index, or we may want to do a bit more planning first. For example, how large is our index going to be after creation? HypoPG will let us query to see the index size:

SELECT indexname,pg_size_pretty(hypopg_relation_size(indexrelid))
FROM hypopg();

            indexname            | pg_size_pretty
---------------------------------+----------------
 <13731>btree_weather_event_type | 40 kB
(1 row)

EXPLAIN ANALYZE:

Hypothetical indexes can't be used for EXPLAIN ANALYZE because that tool plans AND executes the statement. Given that the index doesn't actually exist, it can't leverage a non-existent index.

Testing and Reset:

To see everything you’re testing:

select * from hypopg();

To drop your hypothetical indexes and start over:

select * from hypopg_reset();

HypoPG for your Query Improvement Process

HypoPG fits really well into a query improvement workflow like this:

• Use pg_stat_statements to find your slowest queries that would benefit from indexing.

• Test create indexes with HypoPG.

• Confirm the index is used with EXPLAIN.

• Implement the index.

• Rinse and repeat for continual performance improvement.

HypoPG is built into Crunchy Bridge. Try it out today.