Skip site navigation (1) Skip section navigation (2)

AND OR combination: index not being used

From: David Teran <david(dot)teran(at)cluster9(dot)com>
To: PgSQL Performance ML <pgsql-performance(at)postgresql(dot)org>
Subject: AND OR combination: index not being used
Date: 2005-05-12 09:07:41
Message-ID: 2aeb891e99fa6313e9e3a20d48c099aa@cluster9.com (view raw or flat)
Thread:
Lists: pgsql-performance
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


Responses

pgsql-performance by date

Next:From: PFCDate: 2005-05-12 10:02:56
Subject: Re: BLOB's bypassing the OS Filesystem for better Image loading speed?
Previous:From: Bruce MomjianDate: 2005-05-12 02:26:28
Subject: Re: Intel SRCS16 SATA raid?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group