Postgres not using multicolumn index for queries on leftmost columns?

Posted on

Question :

I’m working in Postgres 9.5.15 and have a table called project:

 id                             | integer                     |           | not null | nextval('project_id_seq'::regclass)
 user_id                        | integer                     |           | not null |
 is_deleted                     | boolean                     |           | not null | false
 is_listed                      | boolean                     |           | not null | false

And it has this multicolumn index:

"project_is_deleted_is_listed_user_id_ix" btree (is_deleted, is_listed, user_id)

But if I try to query the two left columns, the table is being sequentially scanned rather than using the index:

LIVE !!! => analyze;
LIVE !!! => explain analyze select * from project where is_listed and not is_deleted;


    QUERY PLAN                                                        
 Seq Scan on project  (cost=0.00..36846.95 rows=460760 width=508) (actual time=0.006..281.478 rows=394615 loops=1)
   Filter: (is_listed AND (NOT is_deleted))
   Rows Removed by Filter: 491652
 Planning time: 0.091 ms
 Execution time: 356.221 ms
(5 rows)

I don’t understand why this is – 356ms execution time seems quite slow, surely it’d be faster to use the index? And given that the two left-most columns are being queried, surely the index is usable?

Answer :

Your query is returning half of the table. There is little point in using an index in such a case. Why do you think it would be faster to read both half the index and the table, rather than reading just the table?

Leave a Reply

Your email address will not be published. Required fields are marked *