Re: --EXTERNAL--Re: PSQL does not remove obvious useless joins

On Fri, Jul 1, 2016 at 7:15 PM, Sfiligoi, Igor <Igor.Sfiligoi@ga.com> wrote:
> OK.  Will change our query generation code to not use the view.
> (I have tried the LEFT JOIN approach, but it just does not seem to perform.)

> PS: Here are the numbers for the real production query (will not provide details):
> Original query:                          300s
> Query on a manually optimized view:        1ms
> Using left joins:                        200s

Please show a self-contained case (i.e., one that can be run
against an empty database to demonstrate the problem).  You might
start from this one and modify it until you see the problem that
you describe:

create table a (id int primary key, name varchar(128));
create table b (id int primary key, name varchar(128));
create table c (id int primary key,
                a_id int not null references a(id),
                b1_id int not null references b(id),
                b2_id int not null references b(id),
                b3_id int not null references b(id));

create view v as
  select
      c.id, c.a_id, c.b1_id, c.b2_id, c.b3_id, a.name a_name,
      b1.name b1_name, b2.name b2_name, b3.name b3_name
  from c
  left join a    on a.id = c.a_id
  left join b b1 on b1.id = c.b1_id
  left join b b2 on b2.id = c.b2_id
  left join b b3 on b3.id = c.b3_id;

insert into a values (1, 'a1');
insert into b values (1, 'b1'), (2, 'b2'), (3, 'b3');
insert into c values (1, 1, 1, 2, 3);

vacuum analyze a;
vacuum analyze b;
vacuum analyze c;

select id, b1_name from v;
explain (analyze, buffers, verbose) select id, b1_name from v;

I'm seeing the unreferenced tables pruned from the plan, and a 1ms
execution time for the select from the view.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company