postgres not use index, IN statement

From: "Anibal David Acosta" <aa(at)devshock(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: postgres not use index, IN statement
Date: 2011-05-23 21:30:34
Message-ID: 006801cc1990$a8b34e10$fa19ea30$@devshock.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I have a strange situation.
I have a table of detail with millones of rows and a table of items with
thousands of rows

When I do..

select count(*) from wiz_application_response where application_item_id in
(select id from wiz_application_item where application_id=110)

This query NOT use the index on column application_item_id, instead is doing
a sequential scan

BUT, when I add the range of min and max id of the subquery, the postgres
uses the INDEX
This is the second query...

select count(*) from wiz_application_response where application_item_id
between 908 and 1030 and application_item_id in(select id from
wiz_application_item where application_id=110)

908 and 1030 are limits (lower and upper) of the subquery, the subquery
returns 100 elements aprox.

So, this is some bug?

Thanks!

Anibal

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Josh Berkus 2011-05-23 21:31:12 Re: Pushing LIMIT into sub-queries of a UNION ALL?
Previous Message Robert Klemme 2011-05-23 19:47:18 Re: Pushing LIMIT into sub-queries of a UNION ALL?