Re: Allowing join removals for more join types

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> David Rowley <dgrowleyml(at)gmail(dot)com> writes:
> > I'm not quite there with inner joins yet. I'm still getting my head around
> > just where the join quals are actually stored.
>
> TBH I think that trying to do anything at all for inner joins is probably
> a bad idea. The cases where the optimization could succeed are so narrow
> that it's unlikely to be worth adding cycles to every query to check.

I agree that we don't want to add too many cycles to trivial queries but
I don't think it's at all fair to say that FK-check joins are a narrow
use-case and avoiding that join could be a very nice win.

> The planning cost of all this is likely to be a concern anyway; but
> if you can show that you don't add anything unless there are some outer
> joins in the query, you can at least overcome objections about possibly
> adding significant overhead to trivial queries.

I'm not quite buying this. We're already beyond really trivial queries
since we're talking about joins and then considering how expensive joins
can be, putting in a bit of effort to eliminate one would be time well
worth spending, imv.

In any case, I'd certainly suggest David continue to develop this and
then we can look at measuring the cost on cases where it was time wasted
and on cases where it helps. We may also be able to come up with ways
to short-circuit the test and thereby minimize the cost in cases where
it won't help.

Thanks,

Stephen