On Fri, Sep 6, 2024 at 7:05 AM Xavier Solomon <xavier.solomon515@xxxxxxxxx> 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
