pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

postgres not use index, IN statement
2+ messages / 2 participants
[nested] [flat]

* postgres not use index, IN statement
@ 2011-05-23 21:30  Anibal David Acosta <aa@devshock.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Anibal David Acosta @ 2011-05-23 21:30 UTC (permalink / raw)
  To: 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





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: postgres not use index, IN statement
@ 2011-05-23 23:26  Craig Ringer <craig@postnewspapers.com.au>
  parent: Anibal David Acosta <aa@devshock.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Craig Ringer @ 2011-05-23 23:26 UTC (permalink / raw)
  To: Anibal David Acosta <aa@devshock.com>; +Cc: pgsql-performance

On 24/05/2011 5:30 AM, Anibal David Acosta wrote:

> So, this is some bug?

Hard to know with the information provided. Please post EXPLAIN ANALYZE 
output.

http://wiki.postgresql.org/wiki/SlowQueryQuestions

-- 
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2011-05-23 23:26 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-05-23 21:30 postgres not use index, IN statement Anibal David Acosta <aa@devshock.com>
2011-05-23 23:26 ` Craig Ringer <craig@postnewspapers.com.au>

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