Skip site navigation (1) Skip section navigation (2)

Re: Automatic optimization of IN clauses via INNER JOIN

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Thomas Hamilton <thomashamilton76(at)yahoo(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Automatic optimization of IN clauses via INNER JOIN
Date: 2009-12-18 14:18:14
Message-ID: 603c8f070912180618y1063edc6x37ae870a24f99acd@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, Dec 17, 2009 at 9:20 PM, Craig Ringer
<craig(at)postnewspapers(dot)com(dot)au> wrote:
> On 17/12/2009 11:57 PM, Tom Lane wrote:
>>
>> Thomas Hamilton<thomashamilton76(at)yahoo(dot)com>  writes:
>>>
>>> But in our testing under the same optimization and conditions INNER JOIN
>>> is significantly outperforming IN.
>>
>> [ shrug... ]  You haven't provided any details, so it's impossible to
>> offer any useful advice.
>
> In other words: can we discuss this with reference to a specific case?
> Please provide your queries, your EXPLAIN ANALYZE output, and other relevant
> details as per:
>
>  http://wiki.postgresql.org/wiki/SlowQueryQuestions
>
> I'd be interested in knowing whether the planner can perform such
> transformations and if so why it doesn't myself. I have the vague feeling
> there may be semantic differences in the handling of NULL but I can't
> currently seem to puzzle them out.

NOT IN is the only that really kills you as far as optimization is
concerned.  IN can be transformed to a join.  NOT IN forces a NOT
(subplan)-type plan, which bites - hard.

...Robert

In response to

Responses

pgsql-performance by date

Next:From: Grzegorz JaśkiewiczDate: 2009-12-18 14:24:00
Subject: Re: Automatic optimization of IN clauses via INNER JOIN
Previous:From: Sigurgeir GunnarssonDate: 2009-12-18 12:46:37
Subject: Re: Issues with \copy from file

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group