From: David Teran <david.teran@cluster9.com>
To: PgSQL Performance ML <pgsql-performance@postgresql.org>
Subject: AND OR combination: index not being used
Date: Thu, 12 May 2005 11:07:41 +0200
Message-ID: <2aeb891e99fa6313e9e3a20d48c099aa@cluster9.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: david.teran@cluster9.com
Subject: Re: AND OR combination: index not being used
In-Reply-To: <2aeb891e99fa6313e9e3a20d48c099aa@cluster9.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox