bad_merge.sql

application/octet-stream

Download

drop table if exists table1, table2;
create table table1 (id int, first int, last int, fullname text);
create table table2 (fullname text, first int);
create or replace function gibberish(int) returns text language SQL as $_$ select left(string_agg(left(encode(sha256(random()::text::bytea),$$base64$$),42),$$$$),$1) from generate_series(0,$1/42) $_$;
insert into table2 select gibberish(3), random()*1000000 from generate_series(1,90000);
insert into table1 (id, last,fullname) select random()*1000000, random()*1000000,gibberish(3) from generate_series(1,5000000);
vacuum analyze table1 , table2 ;
explain (analyze, settings) SELECT DISTINCT z.id, z.last
            FROM table1 z 
            INNER JOIN (
                SELECT DISTINCT a.id, b.first
                FROM table1 a
                INNER JOIN (SELECT DISTINCT fullname, first FROM table2) b
                ON (a.fullname = b.fullname)) x
            ON z.id = x.id
            WHERE z.last = x.first;
set enable_hashjoin TO off ;
explain (analyze, settings) SELECT DISTINCT z.id, z.last
            FROM table1 z 
            INNER JOIN (
                SELECT DISTINCT a.id, b.first
                FROM table1 a
                INNER JOIN (SELECT DISTINCT fullname, first FROM table2) b
                ON (a.fullname = b.fullname)) x
            ON z.id = x.id
            WHERE z.last = x.first;