På onsdag 16. januar 2019 kl. 11:54:21, skrev Олег Самойлов <spl...@ya.ru <mailto:spl...@ya.ru>>: Hi, all.
I got some mystic behaviour of PostgreSQL, perhaps this is a bug. [snip] 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) The moment you involve columns on "left joined" relations this way in the WHERE-clause, it effectively becomes a right join. -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>
