mockup_bug15577_v2.sql

drop view if exists table_b_active;
drop table if exists table_a;
drop table if exists table_b;
drop table if exists table_c;
drop table if exists table_d;
drop type if exists "group";
drop type if exists "group_type";


create table table_a (
  id int primary key,
  table_c_id int,
  date date
);

insert into table_a select x,x,'2018-08-31' from generate_series(1,20000) x;

create type group_type AS ENUM ('A', 'B');
create type "group" AS ENUM ('A', 'B');

create table table_b (
  id int primary key,
  target_id int,
  group_type group_type,
  source_id int,
  date date,
  t_active bool
);

insert into table_b select x,x,case when x%26=0 then 'A'::group_type else 'B'::group_type end,x,'2018-01-01'::date + ((x % 365)::text || ' days')::interval,'t' from generate_series(1,200000) x;


create table table_c (
  id int,-- primary key,
  table_d_id int,
  table_e_id int,
  padding varchar(1024)
);

insert into table_c select x,x%16+1,x % 29,repeat('a',1024) from generate_Series(1,(49117+1751)*2) x;

create table table_d (
  id int primary key,
  "group" "group"
);


insert into table_d select x,'A' from generate_series(1,8)x;
insert into table_d select x,'B' from generate_series(9,8+55)x;

create index table_b_idx_target_id on table_b (target_id) where (t_active IS TRUE);

create unique index table_a_uq_001 on table_a (table_c_id, date);

create view table_b_active as SELECT * FROM table_b WHERE (table_b.t_active IS TRUE);

analyze;
set parallel_setup_cost=0;
set parallel_tuple_cost=0;
alter table table_c set (parallel_workers=1);
drop view if exists table_b_active;
drop table if exists table_a;
drop table if exists table_b;
drop table if exists table_c;
drop table if exists table_d;
drop type if exists "group";
drop type if exists "group_type";

create type group_type AS ENUM ('A', 'B');
create type "group" AS ENUM ('A', 'B');

create table table_a (
  id int primary key,
  table_c_id int,
  date date
);

insert into table_a select x,x,'2018-08-31' from generate_series(1,20000) x;

create table table_b (
  id int primary key,
  target_id int,
  group_type group_type,
  source_id int,
  date date,
  t_active bool,
  padding varchar(1024)
);

insert into table_b select x,200000-x,case when x%26=0 then 'B'::group_type else 'A'::group_type end,x,'2018-01-01'::date + ((x % 365)::text || ' days')::interval,'t',repeat('a',112) from generate_series(1,200000) x;


create table table_c (
  id int,
  table_d_id int,
  table_e_id int,
  padding varchar(1024)
);

insert into table_c select x,x%15+1,x % 29,repeat('a',124) from generate_Series(1,(49117+1751)*2) x;
insert into table_c select * from table_c;

insert into table_b
select generate_series(1000000, 1000010), 100112, 'A', 99888,
'2018-09-01', true,
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';

create table table_d (
  id int primary key,
  "group" "group"
);


insert into table_d select x,'A' from generate_series(1,8)x;
insert into table_d select x,'B' from generate_series(9,8+55)x;

create index table_b_idx_target_id on table_b (target_id) where (t_active IS TRUE);

create unique index table_a_uq_001 on table_a (table_c_id, date);

analyze;

-- Broken query
explain (costs off, analyze)
  SELECT tba.ctid, tba.target_id, ta.id AS table_a_id, 
         tc.id as table_c_id,
         tba.id AS table_b_id
  FROM table_b tba
         INNER JOIN table_c tc ON tba.target_id = tc.id
         INNER JOIN table_d td ON tc.table_d_id = td.id
         LEFT JOIN table_a ta ON ta.table_c_id = tc.id AND ta.date = '2018-08-31'::DATE
  WHERE tba.date BETWEEN '2018-08-10' :: DATE AND '2018-09-01' :: DATE
    AND td.group = 'A' and (tba.t_active IS TRUE)
    AND tc.table_e_id = 4
   
    AND (
      (tba.target_id = tc.id AND tba.group_type = 'A')
      OR tba.source_id = tc.id
    );

    
-- Working query. (redundant where clause item removed)
explain (costs off, analyze)
  SELECT tba.ctid, tba.target_id, ta.id AS table_a_id, 
         tc.id as table_c_id,
         tba.id AS table_b_id
  FROM table_b tba
         INNER JOIN table_c tc ON tba.target_id = tc.id
         INNER JOIN table_d td ON tc.table_d_id = td.id
         LEFT JOIN table_a ta ON ta.table_c_id = tc.id AND ta.date = '2018-08-31'::DATE
  WHERE tba.date BETWEEN '2018-08-10' :: DATE AND '2018-09-01' :: DATE
    AND td.group = 'A' and (tba.t_active IS TRUE)
    AND tc.table_e_id = 4
   
    AND (
      (tba.group_type = 'A')
      OR tba.source_id = tc.id
    );