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

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 (view raw or flat)
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

pgsql-performance by date

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

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