Table A and B are populated with data like this: opt=# SELECT setseed(0.12345); setseed --------- (1 row) opt=# INSERT INTO a (a1, a2, a3, a4) SELECT CASE WHEN random() < 0.1 THEN NULL ELSE floor(random() * 10000)::int END, CASE WHEN random() < 0.04 THEN NULL ELSE floor(random() * 10000)::int END, CASE WHEN random() < 0.021 THEN NULL ELSE floor(random() * 10000)::int END, CASE WHEN random() < 0.01 THEN NULL ELSE floor(random() * 10000)::int END FROM generate_series(1, 10000); INSERT 0 10000 opt=# INSERT INTO b (b1, b2, b3, b4) SELECT CASE WHEN random() < 0.1 THEN NULL ELSE floor(random() * 10000)::int END, CASE WHEN random() < 0.04 THEN NULL ELSE floor(random() * 10000)::int END, CASE WHEN random() < 0.021 THEN NULL ELSE floor(random() * 10000)::int END, CASE WHEN random() < 0.01 THEN NULL ELSE floor(random() * 10000)::int END FROM generate_series(1, 30000); INSERT 0 30000 With current join_clause_is_movable_to() implementation, run the following queries, storing their results in materialized view MV1 and MV2,respectively: opt=# explain analyze create materialized view MV1 as select * from a, lateral(select sum(b1) x,sum(b2) y,b3,b4 from b where b3+b4a3-999 and a4=18) or (x-y Seq Scan on a (cost=0.00..154.00 rows=10000 width=16) (actual time=0.010..9.215 rows=10000.00 loops=1) Buffers: shared hit=54 -> Subquery Scan on i (cost=787.00..1112.76 rows=12 width=24) (actual time=35.603..35.611 rows=5.09 loops=10000) Filter: (((i.b3 < a.a3) AND ((a.a4 = 32) OR (a.a4 = 33))) OR ((i.b3 > (a.a3 - 999)) AND (a.a4 = 18)) OR (((i.x - i.y) < (a.a3 + a.a4)) AND ((a.a1 - a.a2) = a. a4))) Rows Removed by Filter: 12487 Buffers: shared hit=1620000 -> HashAggregate (cost=787.00..855.58 rows=6858 width=24) (actual time=21.721..28.422 rows=12492.00 loops=10000) Group Key: b.b3, b.b4 Batches: 1 Memory Usage: 2841kB Buffers: shared hit=1620000 -> Seq Scan on b (cost=0.00..687.00 rows=10000 width=16) (actual time=1.161..10.169 rows=12493.69 loops=10000) Filter: ((b3 + b4) < (a.a1 + a.a2)) Rows Removed by Filter: 17506 Buffers: shared hit=1620000 Planning Time: 0.251 ms Execution Time: 356292.545 ms (18 rows) opt=# explain analyze create materialized view MV2 as select * from a left join lateral(select sum(b1) x,sum(b2) y,b3,b4 from b group by b3,b4 having b3+b4<>a3+a4) on a1+a3>x+b3 where (a1=coalesce(x,30) and a2-a4>a1+a3) or (a2=coalesce(y-b3,41) and a2+5=a4+a3); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- Nested Loop Left Join (cost=985.50..13494629.00 rows=1617 width=40) (actual time=11034.488..631277.881 rows=213.00 loops=1) Filter: (((a.a1 = COALESCE(unnamed_subquery.x, '30'::bigint)) AND ((a.a2 - a.a4) > (a.a1 + a.a3))) OR ((a.a2 = COALESCE((unnamed_subquery.y - unnamed_subquery.b3), '41'::bigint)) AND ((a.a2 + 5) = (a.a4 + a.a3)))) Rows Removed by Filter: 113918508 Buffers: shared hit=1619838 read=216 -> Seq Scan on a (cost=0.00..154.00 rows=10000 width=16) (actual time=0.116..13.882 rows=10000.00 loops=1) Buffers: shared read=54 -> Subquery Scan on unnamed_subquery (cost=985.50..1246.59 rows=3165 width=24) (actual time=40.443..59.584 rows=11391.74 loops=10000) Filter: ((a.a1 + a.a3) > (unnamed_subquery.x + unnamed_subquery.b3)) Rows Removed by Filter: 16659 Buffers: shared hit=1619838 read=162 -> HashAggregate (cost=985.50..1080.44 rows=9494 width=24) (actual time=38.196..52.896 rows=28050.80 loops=10000) Group Key: b.b3, b.b4 Batches: 1 Memory Usage: 2841kB Buffers: shared hit=1619838 read=162 -> Seq Scan on b (cost=0.00..687.00 rows=29850 width=16) (actual time=0.301..11.329 rows=28053.70 loops=10000) Filter: ((b3 + b4) <> (a.a3 + a.a4)) Rows Removed by Filter: 1946 Buffers: shared hit=1619838 read=162 Planning: Buffers: shared hit=106 read=3 Planning Time: 0.892 ms Execution Time: 631284.853 ms (22 rows) After relaxation of join_clause_is_movable_to(), run the queries again, storing their results in materialized view MV3 and MV4, respectively: opt=# explain analyze create materialized view MV3 as select * from a, lateral(select sum(b1) x,sum(b2) y,b3,b4 from b where b3+b4a3-999 and a4=18) or (x-y Seq Scan on a (cost=0.00..279.00 rows=55 width=16) (actual time=0.214..5.725 rows=7.00 loops=1) Filter: ((a4 = 32) OR (a4 = 33) OR (a4 = 18) OR ((a1 - a2) = a4)) Rows Removed by Filter: 9993 Buffers: shared read=54 -> Subquery Scan on i (cost=787.00..1112.76 rows=2287 width=24) (actual time=22.745..33.245 rows=7274.71 loops=7) Filter: (((i.b3 < a.a3) AND ((a.a4 = 32) OR (a.a4 = 33))) OR ((i.b3 > (a.a3 - 999)) AND (a.a4 = 18)) OR (((i.x - i.y) < (a.a3 + a.a4)) AND ((a.a1 - a.a2) = a. a4))) Rows Removed by Filter: 4294 Buffers: shared hit=972 read=162 -> HashAggregate (cost=787.00..855.58 rows=6858 width=24) (actual time=21.746..28.365 rows=11568.29 loops=7) Group Key: b.b3, b.b4 Batches: 1 Memory Usage: 1817kB Buffers: shared hit=972 read=162 -> Seq Scan on b (cost=0.00..687.00 rows=10000 width=16) (actual time=0.016..10.581 rows=11570.14 loops=7) Filter: ((b3 + b4) < (a.a1 + a.a2)) Rows Removed by Filter: 18430 Buffers: shared hit=972 read=162 Planning Time: 0.331 ms Execution Time: 369.580 ms (20 rows) opt=# explain analyze create materialized view MV4 as select * from a left join lateral(select sum(b1) x,sum(b2) y,b3,b4 from b group by b3,b4 having b3+b4<>a3+a4) on a1+a3>x+b3 where (a1=coalesce(x,30) and a2-a4>a1+a3) or (a2=coalesce(y-b3,41) and a2+5=a4+a3); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- Nested Loop Left Join (cost=985.50..4543893.73 rows=544 width=40) (actual time=544.179..22428.814 rows=213.00 loops=1) Filter: (((a.a1 = COALESCE(unnamed_subquery.x, '30'::bigint)) AND ((a.a2 - a.a4) > (a.a1 + a.a3))) OR ((a.a2 = COALESCE((unnamed_subquery.y - unnamed_subquery.b3), '41'::bigint)) AND ((a.a2 + 5) = (a.a4 + a.a3)))) Rows Removed by Filter: 954660 Buffers: shared hit=59994 -> Seq Scan on a (cost=0.00..304.00 rows=3367 width=16) (actual time=0.018..4.985 rows=370.00 loops=1) Filter: (((a2 - a4) > (a1 + a3)) OR ((a2 + 5) = (a4 + a3))) Rows Removed by Filter: 9630 Buffers: shared hit=54 -> Subquery Scan on unnamed_subquery (cost=985.50..1246.59 rows=3165 width=24) (actual time=38.590..59.817 rows=2580.74 loops=370) Filter: ((a.a1 + a.a3) > (unnamed_subquery.x + unnamed_subquery.b3)) Rows Removed by Filter: 26431 Buffers: shared hit=59940 -> HashAggregate (cost=985.50..1080.44 rows=9494 width=24) (actual time=38.264..53.415 rows=29011.91 loops=370) Group Key: b.b3, b.b4 Batches: 1 Memory Usage: 2841kB Buffers: shared hit=59940 -> Seq Scan on b (cost=0.00..687.00 rows=29850 width=16) (actual time=0.007..11.280 rows=29014.90 loops=370) Filter: ((b3 + b4) <> (a.a3 + a.a4)) Rows Removed by Filter: 985 Buffers: shared hit=59940 Planning: Buffers: shared hit=74 Planning Time: 0.616 ms Execution Time: 22444.571 ms (24 rows) MV1 and MV3 shall have the same cardinalities: opt=# select count(1) from mv1; count ------- 50923 (1 row) opt=# select count(1) from mv3; count ------- 50923 (1 row) MV1 and MV3 shall be identical: opt=# (SELECT * FROM MV1 EXCEPT ALL SELECT * FROM MV3) UNION ALL (SELECT * FROM MV3 EXCEPT ALL SELECT * FROM MV1); a1 | a2 | a3 | a4 | x | y | b3 | b4 ----+----+----+----+---+---+----+---- (0 rows) MV2 and MV4 shall have the same cardinalities: opt=# select count(1) from mv2; count ------- 213 (1 row) opt=# select count(1) from mv4; count ------- 213 (1 row) MV2 and MV4 shall be identical: opt=# (SELECT * FROM MV2 EXCEPT ALL SELECT * FROM MV4) UNION ALL (SELECT * FROM MV4 EXCEPT ALL SELECT * FROM MV2); a1 | a2 | a3 | a4 | x | y | b3 | b4 ----+----+----+----+---+---+----+---- (0 rows)