Hi hackers,
I happened to notice $subject and not sure if it's an issue or not. When
we're trying to remove a LEFT JOIN, one of the requirements is the inner
side needs to be a single baserel. If there is a join qual that is a
sublink and can be converted to a semi join with the inner side rel, the
inner side would no longer be a single baserel and as a result the LEFT
JOIN can no longer be removed.
Here is an example to illustrate this behavior:
create table a(i int, j int);
create table b(i int UNIQUE, j int);
create table c(i int, j int);
# explain (costs off) select a.i from a left join b on a.i = b.i and
b.j in (select j from c where b.i = c.i);
QUERY PLAN
---------------
Seq Scan on a
(1 row)
For the query above, we do not pull up the sublink and the LEFT JOIN is
removed.
# explain (costs off) select a.i from a left join b on a.i = b.i and
b.j in (select j from c);
QUERY PLAN
---------------------------------------
Hash Left Join
Hash Cond: (a.i = b.i)
-> Seq Scan on a
-> Hash
-> Hash Semi Join
Hash Cond: (b.j = c.j)
-> Seq Scan on b
-> Hash
-> Seq Scan on c
(9 rows)
Now for this above query, the sublink is pulled up to be a semi-join
with inner side rel 'b', which makes the inner side no longer a single
baserel. That causes the LEFT JOIN failing to be removed.
That is to say, pulling up sublink sometimes breaks join-removal logic.
Is this an issue that bothers you too?
Thanks
Richard
