Re: Automatic optimization of IN clauses via INNER JOIN

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
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 00:32:42
Message-ID: 14755.1261182762@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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.

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

regards, tom lane

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Robert Haas 2009-12-19 03:33:26 Re: Automatic optimization of IN clauses via INNER JOIN
Previous Message Robert Haas 2009-12-19 00:24:31 Re: Issues with \copy from file