Combined indexes

In this recipe, we will be discussing how PostgreSQL combines multiple indexes to produce desired results.

Getting ready

When the query has a predicate with multiple columns and each column has associated with an index then the optimizer combines all the index results to fetch the desired results.

How to do it...

For demonstration, let's query the database as follows:

benchmarksql=# EXPLAIN SELECT * FROM bmsql_item WHERE i_data = 'Item Name' OR i_price = 3; 
                                     QUERY PLAN                                      
------------------------------------------------------------------------------------ 
 Bitmap Heap Scan on bmsql_item  (cost=8.82..60.67 rows=14 width=71) 
   Recheck Cond: (((i_data)::text = 'Item Name'::text) OR (i_price = '3'::numeric)) 
   ->  BitmapOr  (cost=8.82..8.82 rows=14 width=0) 
   ->  Bitmap Index Scan on data_idx  (cost=0.00..4.45 rows=4 width=0) 
     Index Cond: ((i_data)::text ...