From: Mike Winter <mike.winter@frontlogic.com>
To: pgsql-sql@postgresql.org
Subject: Efficiency Issue with "SELECT IN (..)" and "SELECT .. OR .."
Date: Thu, 15 May 2003 15:44:29 -0600 (CST)
Message-ID: <Pine.LNX.4.33L2.0305151524590.6984-100000@frontlogic.com> (raw)
Hi, when doing queries of the type:
SELECT id FROM foo WHERE id IN (1, 4, 3, 2, 10, 11, 14) .., I get
terrible performance on tables of any resonable size. I see the
same behaviour when doing queries of the form "SELECT id FROM
foo WHERE id = 5 OR id = 6 OR ..."
When doing an "EXPLAIN" on the query, I get output like the
following:
Index Scan using foo_idx, foo_idx, foo_idx, foo_idx, foo_idx,
foo_idx on foo (cost=0.00..18.16 rows=6 width=4)
If the "IN (1, 2, 3, 6, ..., n)" clause is big enough, the
database will actually throw an error saying "Recursive Depth
Exceeded" or something similar and not complete the query.
It looks to me like the query parser is recursively calling
an index scan for each row in the 'IN' clause rather than just
doing one index scan that it seems it should be.
My question is, does anyone have any alternate ideas for how I
can do a query like this and have it perform well? The tables I
am working with are big enough that a sequential scan is not
helpful. Is this a bug I am encountering or an error in my
query? Is this a known issue?
I have seen this beahaviour on 7.2.1 and 7.3.2 on both Solaris
and Linux platforms.
Thanks for any input.
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: mike.winter@frontlogic.com
Subject: Re: Efficiency Issue with "SELECT IN (..)" and "SELECT .. OR .."
In-Reply-To: <Pine.LNX.4.33L2.0305151524590.6984-100000@frontlogic.com>
References: <Pine.LNX.4.33L2.0305151524590.6984-100000@frontlogic.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