Postgres Pro
Downloads
Home   >   mailing lists

Thread: TODO Item: IN(long list ...)

TODO Item: IN(long list ...)

From
Josh Berkus
Date:
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.

Corrections?

-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

Re: TODO Item: IN(long list ...)

From
Alvaro Herrera
Date:
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.

Re: TODO Item: IN(long list ...)

From
Tom Lane
Date:
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

Re: TODO Item: IN(long list ...)

From
Josh Berkus
Date:
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

Re: TODO Item: IN(long list ...)

From
Bruce Momjian
Date:
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. +