Hi,
postgres 8.0.1, mac os x 10.3.9
i have a select with multiple OR's combined with one AND:
explain analyze SELECT t0.ATTRIBUTE_TYPE FROM ATTRIBUTE_VALUE t0 WHERE
(((t0.ATTRIBUTE_TYPE = 'pb'::varchar(10) OR t0.ATTRIBUTE_TYPE =
'po'::varchar(10) OR t0.ATTRIBUTE_TYPE = 'pn'::varchar(10) OR
t0.ATTRIBUTE_TYPE = 'ps'::varchar(10))) AND t0.ID_ATTRIBUTE =
17::int8);
The result is the following. It shows that postgres does not use an
index which makes the select pretty slow.
Seq Scan on attribute_value t0 (cost=0.00..529.13 rows=208 width=5)
(actual time=66.591..66.591 rows=0 loops=1)
Filter: ((((attribute_type)::text = 'pb'::text) OR
((attribute_type)::text = 'po'::text) OR ((attribute_type)::text =
'pn'::text) OR ((attribute_type)::text = 'ps'::text)) AND (id_attribute
= 17::bigint))
Total runtime: 66.664 ms
(3 rows)
When i remove one OR qualifier one can see that now an index is used.
explain analyze SELECT t0.ATTRIBUTE_TYPE FROM ATTRIBUTE_VALUE t0 WHERE
(((t0.ATTRIBUTE_TYPE = 'pb'::varchar(10) OR t0.ATTRIBUTE_TYPE =
'po'::varchar(10) OR t0.ATTRIBUTE_TYPE = 'pn'::varchar(10))) AND
t0.ID_ATTRIBUTE = 17::int8);
Index Scan using attribute_value__attribute_type__id_attribute,
attribute_value__attribute_type__id_attribute,
attribute_value__attribute_type__id_attribute on attribute_value t0
(cost=0.00..451.82 rows=137 width=5) (actual time=0.301..0.301 rows=0
loops=1)
Index Cond: ((((attribute_type)::text = 'pb'::text) AND
(id_attribute = 17::bigint)) OR (((attribute_type)::text = 'po'::text)
AND (id_attribute = 17::bigint)) OR (((attribute_type)::text =
'pn'::text) AND (id_attribute = 17::bigint)))
Filter: ((((attribute_type)::text = 'pb'::text) OR
((attribute_type)::text = 'po'::text) OR ((attribute_type)::text =
'pn'::text)) AND (id_attribute = 17::bigint))
Total runtime: 0.414 ms
(4 rows)
When i do 'set enable_seqscan=no' the index is used of course.
Unfortunately the sql is generated on the fly and its not easy, more or
less impossible to selectively enable / disable seqscan. Any hint how
to force postgres to use the index even with more OR parts?
regards, David
