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

Nope, no difference how I express the joins:
create view v1 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 join  a on (c.a_id=a.id) join  b b1 on (c.b1_id=b1.id) join b b2 on (c.b2_id=b2.id) join b b3 on
(c.b3_id=b3.id);

# explain select id, b1_name from v1;
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Nested Loop  (cost=1.02..5.47 rows=1 width=7)
   Join Filter: (c.b3_id = b3.id)
   ->  Nested Loop  (cost=1.02..4.34 rows=1 width=11)
         Join Filter: (c.a_id = a.id)
         ->  Nested Loop  (cost=1.02..3.25 rows=1 width=15)
               Join Filter: (c.b2_id = b2.id)
               ->  Hash Join  (cost=1.02..2.12 rows=1 width=19)
                     Hash Cond: (b1.id = c.b1_id)
                     ->  Seq Scan on b b1  (cost=0.00..1.06 rows=6 width=7)
                     ->  Hash  (cost=1.01..1.01 rows=1 width=20)
                           ->  Seq Scan on c  (cost=0.00..1.01 rows=1 width=20)
               ->  Seq Scan on b b2  (cost=0.00..1.06 rows=6 width=4)
         ->  Seq Scan on a  (cost=0.00..1.04 rows=4 width=4)
   ->  Seq Scan on b b3  (cost=0.00..1.06 rows=6 width=4)
(14 rows)

Igor

PS: Here are the updated table definitions:
create table a (id int not null primary key, name varchar(128));
create table b (id int not null primary key, name varchar(128));
create table c (id int not null primary key, a_id int not null references a(id), b1_id int not null references b(id),
b2_idint not null references b(id), b3_id int not null references b(id));
 

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] 
Sent: Friday, July 01, 2016 1:38 PM
To: Sfiligoi, Igor <Igor.Sfiligoi@ga.com>; Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-general@postgresql.org
Subject: Re: --EXTERNAL--Re: [GENERAL] PSQL does not remove obvious useless joins

On 07/01/2016 01:28 PM, Sfiligoi, Igor wrote:
> Sorry... the example was incomplete.
>
> All the fields are defined as not-null.
> So it is guaranteed to always match the join.
>
> And PostgreSQL release notes claim that PGSQL can do at least partial join removal:
> https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.0#Join_Rem
> oval

Those examples use explicit joins, so you might try that in your view definition.

>
> I was hoping this use case would fit in.
>
> Any suggestions?
>
> Igor
>
> -----Original Message-----
> From: Merlin Moncure [mailto:mmoncure@gmail.com]
> Sent: Friday, July 01, 2016 12:42 PM
> To: Sfiligoi, Igor <Igor.Sfiligoi@ga.com>
> Cc: pgsql-general@postgresql.org
> Subject: --EXTERNAL--Re: [GENERAL] PSQL does not remove obvious 
> useless joins
>
> On Fri, Jul 1, 2016 at 12:17 PM, Sfiligoi, Igor <Igor.Sfiligoi@ga.com> wrote:
>> Hello.
>>
>> We have a view that is very generic, and we noticed that PostgreSQL 
>> is not very good at removing useless joins, which makes our queries very slow.
>>
>> We could change our code to avoid the view and write ad-hoc queries 
>> to the underlying tables, but would prefer not to, if there is a way around it.
>>
>> (BTW: We are currently using psql 9.4)
>>
>> Here is a simplified implementation:
>>
>> # 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 references a(id), 
>> b1_id int references b(id), b2_id int references b(id), b3_id int 
>> 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,  a, b b1, b b2, b b3 where c.a_id=a.id and c.b1_id=b1.id and 
>> c.b2_id=b2.id and c.b3_id=b3.id;
>>
>> When I try to get just info from tables c and b1:
>>
>> # select id, b1_name from v
>>
>> it still does all the joins (see below).
>>
>> I would expect just one join (due to the request of columns from the 
>> two tables),
>>
>> since all joins are on foreign constrains referencing primary keys,
>>
>> there are no filters on the other tables, so it is guaranteed that 
>> the useless joins will always return exactly one answer.
>
> I think what you're asking for is a lot more complex than it sounds, and incorrect.  The precise state of the data
influenceshow many records come back (in this case, either 1 or 0), for example if b3_id is null you get zero rows.
Moreto the point, you *instructed* the server to make the join.  There are strategies to make joins 'optional' at run
timewith respect to a query, but they are more complicated than simply withdrawing columns from the select list.
 
>
> Stepping back a bit, the query needs to be planned before peeking at the data in the tables.  The planner is able to
makeassumptions against a statistical picture of the data but shouldn't be expected to actually inspect precise result
datain order to generate a better plan.
 
>
> merlin
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com