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: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>, Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>, 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-19 03:33:26
Message-ID: 603c8f070912181933n5ed9eae9s3dc6ad97ad83ed44@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Fri, Dec 18, 2009 at 7:32 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> If at least one column in the subselect is strict, you can rewrite it
>> that way yourself, but the optimizer won't do it. I wish it did, but I
>> don't wish it badly enough to have written the code myself, and
>> apparently neither does anyone else.
>
> I was thinking about this earlier today.  It's a bit of a PITA because
> we need the information very early in the planner, before it's done much
> analysis.  So for example we might find ourselves duplicating the work
> that will happen later to determine which tables are nullable by outer
> joins.  I think this would be all right as long as we ensure that it's
> only done when there's a chance for a win (ie, no extra cycles if
> there's not actually a NOT IN present).  It could still be an
> unpleasantly large amount of new code though.

I haven't looked at the code (I'm not even sure where you're thinking
this would need to happen) but is there any way that we can do this
and usefully hold onto the results for future use?

> Wouldn't we need to enforce that *all* columns of the subselect are
> non-null, rather than *any*?

[ thinks about it ]

Yes.

...Robert

In response to

pgsql-performance by date

Next:From: Robert HaasDate: 2009-12-20 02:11:55
Subject: Re: Idea how to get rid of Bitmap Heap Scan
Previous:From: Tom LaneDate: 2009-12-19 00:32:42
Subject: Re: Automatic optimization of IN clauses via INNER JOIN

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