Re: [HACKERS] Removing LEFT JOINs in more cases

On 10 January 2018 at 08:44, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> select distinct nextval('foo') from a left join b ...
>
> The presence of the DISTINCT again doesn't excuse changing how often
> nextval() gets called.
>
> I kinda doubt this list of counterexamples is exhaustive, either;
> it's just what occurred to me in five or ten minutes thought.
> So maybe you can make this idea work, but you need to think much
> harder about what the counterexamples are.

While working on the cases where the join removal should be disallowed
I discovered that the existing code is not too careful about this
either:

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?

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