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;
Output:
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?