Re: Using left joins instead of inner joins as an optimization

On Fri, Sep 6, 2024 at 7:05 AM Xavier Solomon <xavier(dot)solomon515(at)gmail(dot)com>
wrote:

> > explain select b_id from b natural left join a;
> results in a `Seq Scan on b`. Whereas the query
> > explain select b_id from b natural join a;
> results in a join with sequential scans on both a and b.
>

I think your example is a little too contrived. Try explaining
select * from b natural left join a;
and you should see the plans become equivalent again.

I would expect a query that left joins but only pulls data from one table
to be not feasible in real life. Yes, in an ideal world the non-left join
would be smart enough to not even do the scan on a, but it's kind of a moot
point outside of odd select clauses.

- Is it a bad idea to use left joins to optimize this even if semantically
> an inner join would be correct?
>

Not at all - if it works for you, go ahead. But I'm dubious you will gain
much for queries that actually make use of the left join, at least for
relatively simply selects.

Cheers,
Greg