I have a table called orders_table in postgres and I am creating composite index using two columns order_id and customer_id.
Query 1:
-- Create a new index on order_id and customer_id column
CREATE INDEX idx_orders_table_order_id_customer_id ON
orders_table (order_id, customer_id);
When I analyze this query, postgres is running a Seq Scan on column customer_id
EXPLAIN ANALYZE
SELECT
*
FROM
orders_table
WHERE
customer_id = 'VINET'
Output:
Seq Scan on orders_table (cost=0.00..24.38 rows=5 width=90) (actual time=0.018..0.159 rows=5 loops=1)
Filter: (customer_id = 'VINET'::bpchar)
Rows Removed by Filter: 825
Planning Time: 1.160 ms
Execution Time: 0.179 ms
Query 2:
But when I reverse the order of columns in the index such as :
CREATE INDEX idx_orders_table_customer_id_order_id ON
orders_table (customer_id, order_id);
and analyze the query:
EXPLAIN ANALYZE
SELECT
*
FROM
orders_table
WHERE
customer_id = 'VINET'
Bitmap Heap Scan on orders_table (cost=4.31..15.41 rows=5 width=90) (actual time=0.040..0.042 rows=5 loops=1)
Recheck Cond: (customer_id = 'VINET'::bpchar)
Heap Blocks: exact=2
-> Bitmap Index Scan on idx_orders_table_customer_id_order_id (cost=0.00..4.31 rows=5 width=0) (actual time=0.036..0.036 rows=5 loops=1)
Index Cond: (customer_id = 'VINET'::bpchar)
Planning Time: 1.269 ms
Execution Time: 0.066 ms
This time it is performing bit Bitmap Index Scan and not sequential scan. I understand that due to low cardinality of the column, it is favoring Bitmap Index Scan. But why did it not perform Bitmap Index Scan in the first query as well? Can someone help me understand this behavior?