Re: inner join removal

On Thu, Jul 8, 2010 at 3:05 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Thu, Jul 8, 2010 at 2:48 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> I think it might work out better if you ask "what
>>> additional conditions are needed in order to prove that this inner join
>>> can commute with this left join", and then work at being able to prove
>>> that.  (It's entirely likely that the planner isn't currently gathering
>>> the right information for solving that problem.)
>
>> We have to avoid putting much of anything into the critical path where
>> we're trying out different join orders - we want to figure it out
>> earlier and, if possible, by examining each relation just once.
>
> Right, but look for example at the logic involved with the current outer
> join transformation identity #3, which can't be applied unless the join
> predicate is strict for the left-hand side.  We avoid doing the dogwork
> to test that more than once.

<admits ignorance>

Uh... where exactly is that logic? I've been looking for it for 2
years and haven't found it yet.

> I'm hoping that something similar will
> work for this problem.  But it's way premature to say much about that
> without a mathematical characterization of the extra conditions needed
> to make the joins commutable.

I wrote a previous email on this topic which might be a start in that direction.

http://archives.postgresql.org/pgsql-hackers/2009-10/msg01012.php

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company