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


---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings

Reply via email to