Re: [HACKERS] Removing LEFT JOINs in more cases

On 4 March 2018 at 18:35, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> drop table if exists t1;
>
> create table t1 (a int);
> insert into t1 values(1);
>
> create or replace function notice(pn int) returns int as $$
> begin
> raise notice '%', pn;
> return pn;
> end;
> $$ volatile language plpgsql;
>
> create unique index t1_a_uidx on t1(a);
>
> explain (costs off, analyze, timing off, summary off)
> select t1.a from t1 left join t1 t2 on t1.a = t2.a and notice(t2.a) = t1.a;
> QUERY PLAN
> ----------------------------------------
> Seq Scan on t1 (actual rows=1 loops=1)
> (1 row)
>
> drop index t1_a_uidx; -- drop the index to disallow left join removal.
>
> explain (costs off, analyze, timing off, summary off)
> select t1.a from t1 left join t1 t2 on t1.a = t2.a and notice(t2.a) = t1.a;
> NOTICE: 1
> QUERY PLAN
> ----------------------------------------------------------
> Nested Loop Left Join (actual rows=1 loops=1)
> Join Filter: ((t1.a = t2.a) AND (notice(t2.a) = t1.a))
> -> Seq Scan on t1 (actual rows=1 loops=1)
> -> Seq Scan on t1 t2 (actual rows=1 loops=1)
> (4 rows)
>
> Should this be fixed? or is this case somehow not worth worrying about?

Please find attached two patches. The first of which is intended to
resolve the issue mentioned above with consideration that it may need
to be back-patched to where LEFT JOIN removals where introduced.

Patch two is intended to implement LEFT JOIN removal for cases that
any duplicates rows that the join causes would be subsequently removed
again via a GROUP BY or DISTINCT clause.

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