pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAND OR combination: index not being used
3+ messages / 2 participants
[nested] [flat]
* AND OR combination: index not being used
@ 2005-05-12 09:07 David Teran <david.teran@cluster9.com>
2005-05-12 14:15 ` Re: AND OR combination: index not being used Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: David Teran @ 2005-05-12 09:07 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: AND OR combination: index not being used
2005-05-12 09:07 AND OR combination: index not being used David Teran <david.teran@cluster9.com>
@ 2005-05-12 14:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-05-12 22:38 ` Re: AND OR combination: index not being used David Teran <david.teran@cluster9.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2005-05-12 14:15 UTC (permalink / raw)
To: David Teran <david.teran@cluster9.com>; +Cc: pgsql-performance
David Teran <david.teran@cluster9.com> writes:
> Any hint how
> to force postgres to use the index even with more OR parts?
More up-to-date statistics would evidently help; the thing is estimating
hundreds of rows returned and actually finding none.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: AND OR combination: index not being used
2005-05-12 09:07 AND OR combination: index not being used David Teran <david.teran@cluster9.com>
2005-05-12 14:15 ` Re: AND OR combination: index not being used Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-05-12 22:38 ` David Teran <david.teran@cluster9.com>
0 siblings, 0 replies; 3+ messages in thread
From: David Teran @ 2005-05-12 22:38 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On 12.05.2005, at 16:15, Tom Lane wrote:
> David Teran <david.teran@cluster9.com> writes:
>> Any hint how
>> to force postgres to use the index even with more OR parts?
>
> More up-to-date statistics would evidently help; the thing is
> estimating
> hundreds of rows returned and actually finding none.
>
I always do a 'vacuum analyze' if something does not work as expected.
But this did not help. Any other tip?
regards, David
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-05-12 22:38 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-12 09:07 AND OR combination: index not being used David Teran <david.teran@cluster9.com>
2005-05-12 14:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-05-12 22:38 ` David Teran <david.teran@cluster9.com>
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