I have a query that is being ran on PGSQL, and when queried at a fast rate
for large data sets, it is taking a long time to run because it isn't
making use of the available indexes. I found that changing the filter from
multiple OR's to an IN clause causes the right index to be used. Is there a
way I can force the index to be used even when using OR's?
Query with Disjunction:
SELECT field1, field2,..., fieldN
> FROM table1 WHERE
> ((((field9='val1' OR field9='val2') OR field9='val3') OR field9='val4')
> AND (field6='val5'));
Query Plan:
"Bitmap Heap Scan on table1 (cost=18.85..19.88 rows=1 width=395) (actual
> time=0.017..0.017 rows=0 loops=1)"
> " Recheck Cond: (((field6)::text = 'val5'::text) AND (((field9)::text =
> 'val1'::text) OR ((field9)::text = 'val2'::text) OR ((field9)::text =
> 'val3'::text) OR ((field9)::text = 'val4'::text)))"
> " -> BitmapAnd (cost=18.85..18.85 rows=1 width=0) (actual
> time=0.016..0.016 rows=0 loops=1)"
> " -> Bitmap Index Scan on idx_field6_field9 (cost=0.00..9.01
> rows=611 width=0) (actual time=0.015..0.015 rows=0 loops=1)"
> " Index Cond: ((field6)::text = 'val5'::text)"
> " -> BitmapOr (cost=9.59..9.59 rows=516 width=0) (never executed)"
> " -> Bitmap Index Scan on idx_id_field9 (cost=0.00..2.40
> rows=129 width=0) (never executed)"
> " Index Cond: ((field9)::text = 'val1'::text)"
> " -> Bitmap Index Scan on idx_id_field9 (cost=0.00..2.40
> rows=129 width=0) (never executed)"
> " Index Cond: ((field9)::text = 'val2'::text)"
> " -> Bitmap Index Scan on idx_id_field9 (cost=0.00..2.40
> rows=129 width=0) (never executed)"
> " Index Cond: ((field9)::text = 'val3'::text)"
> " -> Bitmap Index Scan on idx_id_field9 (cost=0.00..2.40
> rows=129 width=0) (never executed)"
> " Index Cond: ((field9)::text = 'val4'::text)"
> "Planning time: 0.177 ms"
> "Execution time: 0.061 ms"
Query with IN
SELECT field1, field2,..., fieldN
> FROM table1
> WHERE
> ((field9 IN ('val1', 'val2', 'val3', 'val4'))
> AND (field6='val5'));
Query Plan:
"Index Scan using idx_field6_field9 on table1 (cost=0.43..6.77 rows=1
> width=395) (actual time=0.032..0.032 rows=0 loops=1)"
> " Index Cond: (((field6)::text = 'val5'::text) AND ((field9)::text = ANY
> ('{val1,val2,val3,val4}'::text[])))"
> "Planning time: 0.145 ms"
> "Execution time: 0.055 ms"
There is an index on field 6 and field 9 which the second query uses as
expected, which the first one also should. Field9 is also kind of like a
state field, so its cardinality is extremely low - there's only like 9
different values across the whole table. Unfortunately, it isn't
straightforward to change the query to use an IN clause, so getting PG to
use the right plan would be ideal, even if it means using a different type
of index.
Thank you
