PostgreSQL – Understanding Explain Plan on Composite Index

postgresql

On PostgreSQL 12.1, given:

postgres=> create table abc(a int not null, b text not null, c boolean not null);
CREATE TABLE

postgres=> create index on abc (a, b, c);
CREATE INDEX

I then prepared a query that only filters on a, i.e. the first element in the index.

postgres=> prepare only_a(int) as select 42 from abc where a = 1;
PREPARE

postgres=> explain execute only_a(100);
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Bitmap Heap Scan on abc  (cost=4.20..13.67 rows=6 width=4)
   Recheck Cond: (a = 1)
   ->  Bitmap Index Scan on abc_a_b_c_idx  (cost=0.00..4.20 rows=6 width=0)
         Index Cond: (a = 1)
(4 rows)

It's able to use the index in order to filter on a, per the above.

Then, I prepared a query that filters on a and c, i.e. does not use b.

postgres=> prepare a_and_c(int) as select 42 from abc where a = 1 and c = true;
PREPARE

Then, I ran explain execute, 

postgres=> explain execute a_and_c(100);
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Bitmap Heap Scan on abc  (cost=4.21..11.32 rows=3 width=4)
   Recheck Cond: (a = 1)
   Filter: c
   ->  Bitmap Index Scan on abc_a_b_c_idx  (cost=0.00..4.21 rows=3 width=0)
         Index Cond: ((a = 1) AND (c = true))
(5 rows)

My understanding is that Index Cond: ((a = 1) AND (c = true)) means that abc_a_b_c_idx was able to use both a and c values, despite b not being involved in the where.

If so, how can c be used since b is not used?

Best Answer

"c" is just being used as an in-index filter. It jumps to the part of the index where "a"=1 and scans it until "a" becomes >1. For all of those rows it filters out anything where "c" is not true, based on the value of "c" found in the index.

It is unfortunate that the EXPLAIN syntax doesn't distinguish a column used for jumping to a specific part of the index (or stopping in index scan at the end of the relevant values), from one just used for filtering within the index. But EXPLAIN doesn't understand the internals of indexes, it just knows that "c" is being used, it doesn't know how efficiently it is being used.

Related Question