Today I Learned

A Hashrocket project

Ready to join Hashrocket? Find Openings here and apply today.

Explicitly Use Index For EXPLAIN in PostgreSQL

Sometimes when checking the EXPLAIN of a query results in a sequential scan where you expected an index to be used. This is expected as an optimization of PostgreSQL to decide which is the best method to use.

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Seq Scan on widgets  (cost=0.00..70.92 rows=1038 width=402)
   Filter: ((deleted_at IS NULL) AND ((widget_type)::text = 'foo'::text))

But what if you WANT to see how the index will be used? Well you can force sequential scanning to be turned off.

SET enable_seqscan TO off;

Now if we view the query plan again we can see the index in use.

                                    QUERY PLAN
-----------------------------------------------------------------------------------------------
 Bitmap Heap Scan on widgets  (cost=32.87..100.76 rows=1038 width=402)
   Recheck Cond: ((widget_type)::text = 'foo'::text)
   Filter: (deleted_at IS NULL)
   ->  Bitmap Index Scan on index_widgets_on_widget_type  (cost=0.00..32.61 rows=1111 width=0)
         Index Cond: ((widget_type)::text = 'foo'::text)

Hooray!

Now just don’t forget to re-enable the previous functionality

SET enable_seqscan TO on;
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, our series of free PostgreSQL screencasts.