Re: Patch to support SEMI and ANTI join removal

On 2014-10-09 00:21:44 +1300, David Rowley wrote:
> Ok, so I've been hacking away at this for a couple of evenings and I think
> I have a working prototype finally!

Cool!

> So it seems it's not quite as efficient as join removal at planning time,
> but still a big win when it's possible to perform the join skipping.

Have you checked where the overhead is? Is it really just the additional
node that the tuples are passed through?

Have you measured the overhead of the plan/execution time checks over
master?

> One thing that we've lost out of this execution time join removal checks
> method is the ability to still remove joins where the join column is
> NULLable. The previous patch added IS NOT NULL to ensure the query was
> equivalent when a join was removed, of course this meant that any
> subsequent joins may later not have been removed due to the IS NOT NULL
> quals existing (which could restrict the rows and remove the ability that
> the foreign key could guarantee the existence of exactly 1 row matching the
> join condition). I've not yet come up with a nice way to reimplement these
> null checks at execution time, so I've thought that perhaps I won't bother,
> at least not for this patch. I'd just disable join skipping at planning
> time if any of the join columns can have NULLs.

Sounds fair enough for the first round.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services