Hi, all.
I got some mystic behaviour of PostgreSQL, perhaps this is a bug.
Tables is a model of a simple graph where parents and children related as «many to many». And there is a field «valid» to validate for current moment.
set search_path = 'left_join';
begin;
drop schema if exists left_join cascade;
create schema left_join;
create table node (
node_id integer primary key generated always as identity,
valid daterange not null
);
create table link (
parent integer not null references node(node_id),
child integer not null references node(node_id),
valid daterange not null,
primary key (parent,child)
);
insert into node (node_id,valid) values (default,daterange('2019-01-01','2020-01-01'));
commit;
Now I want to get a one node and all children, if they exists:
=> explain select * from node as parent left join link on parent.node_id=link.parent left join node as child on link.child=child.node_id where parent.node_id=1;
QUERY PLAN
------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=4.50..26.76 rows=1 width=112)
-> Nested Loop Left Join (cost=4.35..21.91 rows=1 width=76)
Join Filter: (parent.node_id = link.parent)
-> Index Scan using node_pkey on node parent (cost=0.15..8.17 rows=1 width=36)
Index Cond: (node_id = 1)
-> Bitmap Heap Scan on link (cost=4.20..13.67 rows=6 width=40)
Recheck Cond: (parent = 1)
-> Bitmap Index Scan on link_pkey (cost=0.00..4.20 rows=6 width=0)
Index Cond: (parent = 1)
-> Index Scan using node_pkey on node child (cost=0.15..4.84 rows=1 width=36)
Index Cond: (link.child = node_id)
(11 rows)
All fine, there is «Left Join» in the planner. And the result is exactly what I want:
=> select * from node as parent left join link on parent.node_id=link.parent left join node as child on link.child=child.node_id where parent.node_id=1;
node_id | valid | parent | child | valid | node_id | valid
---------+-------------------------+--------+-------+-------+---------+-------
1 | [2019-01-01,2020-01-01) | | | | |
(1 row)
But things begin be strange if I add validation by time.
=> explain select * from node as parent left join link on parent.node_id=link.parent left join node as child on link.child=child.node_id where parent.node_id=1 and current_date <@ parent.valid and current_date <@ link.valid and current_date <@ child.valid;
QUERY PLAN
------------------------------------------------------------------------------------------
Nested Loop (cost=4.50..32.35 rows=1 width=112)
-> Nested Loop (cost=4.35..21.88 rows=1 width=76)
-> Index Scan using node_pkey on node parent (cost=0.15..8.18 rows=1 width=36)
Index Cond: (node_id = 1)
Filter: (CURRENT_DATE <@ valid)
-> Bitmap Heap Scan on link (cost=4.20..13.70 rows=1 width=40)
Recheck Cond: (parent = 1)
Filter: (CURRENT_DATE <@ valid)
-> Bitmap Index Scan on link_pkey (cost=0.00..4.20 rows=6 width=0)
Index Cond: (parent = 1)
-> Index Scan using node_pkey on node child (cost=0.15..8.18 rows=1 width=36)
Index Cond: (node_id = link.child)
Filter: (CURRENT_DATE <@ valid)
(13 rows)
«Left Join»’s are lost. And in the result too:
=> select * from node as parent left join link on parent.node_id=link.parent left join node as child on link.child=child.node_id where parent.node_id=1 and current_date <@ parent.valid and current_date <@ link.valid and current_date <@ child.valid;
node_id | valid | parent | child | valid | node_id | valid
---------+-------+--------+-------+-------+---------+-------
(0 rows)
