TODO Item: IN(long list ...)

Josh Berkus wrote:

Bruce, all:

This is a longstanding performance issue which just came up again on IRC,
and I can't find a TODO item for it. So I'd like it added to TODO.
Suggested phrasing:

-- Improve performance of queries with IN() clauses containing hundreds or
more literal values, possibly by re-writing it as a join to a virtual
table.

Hmm, wasn't there some work on this regard in 8.2?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.com> writes:

Josh Berkus wrote:

-- Improve performance of queries with IN() clauses containing hundreds or
more literal values, possibly by re-writing it as a join to a virtual
table.

Hmm, wasn't there some work on this regard in 8.2?

I think it's pretty much done. Try WHERE foo IN (VALUES (1),(2),...)
if you have so many values that a non-nestloop join seems indicated.
The plain non-VALUES list form is also significantly faster than it
was, but I think it will only result in a bitmap indexscan plan type.

regards, tom lane

Tom,

I think it's pretty much done. Try WHERE foo IN (VALUES (1),(2),...)
if you have so many values that a non-nestloop join seems indicated.

Hmmm. Was there a reason not to automate this? Thread link is fine if
you can remember the subject line ... I can't find it on archives.

The plain non-VALUES list form is also significantly faster than it
was, but I think it will only result in a bitmap indexscan plan type.

Yeah, even bitmapscans break down at 1000 values ...

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

Alvaro Herrera wrote:

Josh Berkus wrote:

Bruce, all:

This is a longstanding performance issue which just came up again on IRC,
and I can't find a TODO item for it. So I'd like it added to TODO.
Suggested phrasing:

-- Improve performance of queries with IN() clauses containing hundreds or
more literal values, possibly by re-writing it as a join to a virtual
table.

Hmm, wasn't there some work on this regard in 8.2?

Yes. It was fixed by Joe Conway when it was discovered, so never made
it on the TODO list.

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +