*** /var/lib/postgresql/postgres/src/test/regress/expected/inherit.out 2018-12-12 19:09:43.877432423 +0100 --- /var/lib/postgresql/postgres/src/test/regress/results/inherit.out 2018-12-25 01:22:57.876139321 +0100 *************** *** 1297,1315 **** drop index patest2i; explain (costs off) select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; ! QUERY PLAN ! -------------------------------------------------- ! Nested Loop ! -> Limit ! -> Seq Scan on int4_tbl -> Append ! -> Index Scan using patest0i on patest0 ! Index Cond: (id = int4_tbl.f1) ! -> Index Scan using patest1i on patest1 ! Index Cond: (id = int4_tbl.f1) -> Seq Scan on patest2 ! Filter: (int4_tbl.f1 = id) ! (10 rows) select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; id | x | f1 --- 1297,1314 ---- drop index patest2i; explain (costs off) select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; ! QUERY PLAN ! ----------------------------------------- ! Hash Join ! Hash Cond: (patest0.id = int4_tbl.f1) -> Append ! -> Seq Scan on patest0 ! -> Seq Scan on patest1 -> Seq Scan on patest2 ! -> Hash ! -> Limit ! -> Seq Scan on int4_tbl ! (9 rows) select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; id | x | f1 ====================================================================== *** /var/lib/postgresql/postgres/src/test/regress/expected/equivclass.out 2018-12-12 19:09:38.838432379 +0100 --- /var/lib/postgresql/postgres/src/test/regress/results/equivclass.out 2018-12-25 01:23:02.109139043 +0100 *************** *** 260,286 **** union all select ff + 4 as x from ec1) as ss2 where ss1.x = ec1.f1 and ss1.x = ss2.x and ec1.ff = 42::int8; ! QUERY PLAN ! --------------------------------------------------------------------- Nested Loop ! -> Nested Loop ! -> Index Scan using ec1_pkey on ec1 ! Index Cond: (ff = '42'::bigint) ! -> Append ! -> Index Scan using ec1_expr2 on ec1 ec1_1 ! Index Cond: (((ff + 2) + 1) = ec1.f1) ! -> Index Scan using ec1_expr3 on ec1 ec1_2 ! Index Cond: (((ff + 3) + 1) = ec1.f1) ! -> Index Scan using ec1_expr4 on ec1 ec1_3 ! Index Cond: ((ff + 4) = ec1.f1) -> Append ! -> Index Scan using ec1_expr2 on ec1 ec1_4 ! Index Cond: (((ff + 2) + 1) = (((ec1_1.ff + 2) + 1))) ! -> Index Scan using ec1_expr3 on ec1 ec1_5 ! Index Cond: (((ff + 3) + 1) = (((ec1_1.ff + 2) + 1))) ! -> Index Scan using ec1_expr4 on ec1 ec1_6 ! Index Cond: ((ff + 4) = (((ec1_1.ff + 2) + 1))) ! (18 rows) -- let's try that as a mergejoin set enable_mergejoin = on; --- 260,285 ---- union all select ff + 4 as x from ec1) as ss2 where ss1.x = ec1.f1 and ss1.x = ss2.x and ec1.ff = 42::int8; ! QUERY PLAN ! ------------------------------------------------------------------ Nested Loop ! Join Filter: ((((ec1_1.ff + 2) + 1)) = (((ec1_4.ff + 2) + 1))) -> Append ! -> Seq Scan on ec1 ec1_4 ! -> Seq Scan on ec1 ec1_5 ! -> Seq Scan on ec1 ec1_6 ! -> Materialize ! -> Nested Loop ! -> Index Scan using ec1_pkey on ec1 ! Index Cond: (ff = '42'::bigint) ! -> Append ! -> Index Scan using ec1_expr2 on ec1 ec1_1 ! Index Cond: (((ff + 2) + 1) = ec1.f1) ! -> Index Scan using ec1_expr3 on ec1 ec1_2 ! Index Cond: (((ff + 3) + 1) = ec1.f1) ! -> Index Scan using ec1_expr4 on ec1 ec1_3 ! Index Cond: ((ff + 4) = ec1.f1) ! (17 rows) -- let's try that as a mergejoin set enable_mergejoin = on; *************** *** 345,354 **** -> Index Scan using ec1_expr2 on ec1 ec1_1 Index Cond: (((ff + 2) + 1) = ec1.f1) -> Seq Scan on ec1 ec1_2 - Filter: (((ff + 3) + 1) = ec1.f1) -> Index Scan using ec1_expr4 on ec1 ec1_3 Index Cond: ((ff + 4) = ec1.f1) ! (10 rows) -- let's try that as a mergejoin set enable_mergejoin = on; --- 344,352 ---- -> Index Scan using ec1_expr2 on ec1 ec1_1 Index Cond: (((ff + 2) + 1) = ec1.f1) -> Seq Scan on ec1 ec1_2 -> Index Scan using ec1_expr4 on ec1 ec1_3 Index Cond: ((ff + 4) = ec1.f1) ! (9 rows) -- let's try that as a mergejoin set enable_mergejoin = on; ====================================================================== *** /var/lib/postgresql/postgres/src/test/regress/expected/partition_join.out 2018-12-12 19:09:43.880432423 +0100 --- /var/lib/postgresql/postgres/src/test/regress/results/partition_join.out 2018-12-25 01:23:06.002138788 +0100 *************** *** 535,560 **** Sort Sort Key: t1.a -> Append ! -> Nested Loop ! Join Filter: (t1.a = ((t3.a + t3.b) / 2)) ! -> Hash Join ! Hash Cond: (t2.b = t1.a) ! -> Seq Scan on prt2_p1 t2 ! -> Hash ! -> Seq Scan on prt1_p1 t1 ! Filter: (b = 0) ! -> Index Scan using iprt1_e_p1_ab2 on prt1_e_p1 t3 ! Index Cond: (((a + b) / 2) = t2.b) ! -> Nested Loop ! Join Filter: (t1_1.a = ((t3_1.a + t3_1.b) / 2)) ! -> Hash Join ! Hash Cond: (t2_1.b = t1_1.a) ! -> Seq Scan on prt2_p2 t2_1 ! -> Hash ! -> Seq Scan on prt1_p2 t1_1 ! Filter: (b = 0) ! -> Index Scan using iprt1_e_p2_ab2 on prt1_e_p2 t3_1 ! Index Cond: (((a + b) / 2) = t2_1.b) -> Nested Loop Join Filter: (t1_2.a = ((t3_2.a + t3_2.b) / 2)) -> Hash Join --- 535,560 ---- Sort Sort Key: t1.a -> Append ! -> Hash Join ! Hash Cond: (((t3.a + t3.b) / 2) = t1.a) ! -> Seq Scan on prt1_e_p1 t3 ! -> Hash ! -> Hash Join ! Hash Cond: (t2.b = t1.a) ! -> Seq Scan on prt2_p1 t2 ! -> Hash ! -> Seq Scan on prt1_p1 t1 ! Filter: (b = 0) ! -> Hash Join ! Hash Cond: (((t3_1.a + t3_1.b) / 2) = t1_1.a) ! -> Seq Scan on prt1_e_p2 t3_1 ! -> Hash ! -> Hash Join ! Hash Cond: (t2_1.b = t1_1.a) ! -> Seq Scan on prt2_p2 t2_1 ! -> Hash ! -> Seq Scan on prt1_p2 t1_1 ! Filter: (b = 0) -> Nested Loop Join Filter: (t1_2.a = ((t3_2.a + t3_2.b) / 2)) -> Hash Join *************** *** 745,752 **** -- Semi-join EXPLAIN (COSTS OFF) SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1, prt1_e t2 WHERE t1.a = 0 AND t1.b = (t2.a + t2.b)/2) AND t1.b = 0 ORDER BY t1.a; ! QUERY PLAN ! --------------------------------------------------------------------------------- Sort Sort Key: t1.a -> Append --- 745,752 ---- -- Semi-join EXPLAIN (COSTS OFF) SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1, prt1_e t2 WHERE t1.a = 0 AND t1.b = (t2.a + t2.b)/2) AND t1.b = 0 ORDER BY t1.a; ! QUERY PLAN ! ------------------------------------------------------------------------- Sort Sort Key: t1.a -> Append *************** *** 780,794 **** Join Filter: (t1_2.a = t1_5.b) -> HashAggregate Group Key: t1_5.b ! -> Nested Loop ! -> Seq Scan on prt2_p3 t1_5 ! Filter: (a = 0) ! -> Index Scan using iprt1_e_p3_ab2 on prt1_e_p3 t2_2 ! Index Cond: (((a + b) / 2) = t1_5.b) -> Index Scan using iprt1_p3_a on prt1_p3 t1_2 Index Cond: (a = ((t2_2.a + t2_2.b) / 2)) Filter: (b = 0) ! (41 rows) SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1, prt1_e t2 WHERE t1.a = 0 AND t1.b = (t2.a + t2.b)/2) AND t1.b = 0 ORDER BY t1.a; a | b | c --- 780,795 ---- Join Filter: (t1_2.a = t1_5.b) -> HashAggregate Group Key: t1_5.b ! -> Hash Join ! Hash Cond: (((t2_2.a + t2_2.b) / 2) = t1_5.b) ! -> Seq Scan on prt1_e_p3 t2_2 ! -> Hash ! -> Seq Scan on prt2_p3 t1_5 ! Filter: (a = 0) -> Index Scan using iprt1_p3_a on prt1_p3 t1_2 Index Cond: (a = ((t2_2.a + t2_2.b) / 2)) Filter: (b = 0) ! (42 rows) SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1, prt1_e t2 WHERE t1.a = 0 AND t1.b = (t2.a + t2.b)/2) AND t1.b = 0 ORDER BY t1.a; a | b | c ====================================================================== *** /var/lib/postgresql/postgres/src/test/regress/expected/partition_prune.out 2018-12-12 19:09:43.881432423 +0100 --- /var/lib/postgresql/postgres/src/test/regress/results/partition_prune.out 2018-12-25 01:23:06.283138769 +0100 *************** *** 2650,2673 **** explain (analyze, costs off, summary off, timing off) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; ! QUERY PLAN ! -------------------------------------------------------------------------- ! Nested Loop (actual rows=2 loops=1) ! -> Seq Scan on tbl1 (actual rows=2 loops=1) ! -> Append (actual rows=1 loops=2) ! -> Index Scan using tprt1_idx on tprt_1 (never executed) ! Index Cond: (col1 = tbl1.col1) ! -> Index Scan using tprt2_idx on tprt_2 (actual rows=1 loops=2) ! Index Cond: (col1 = tbl1.col1) ! -> Index Scan using tprt3_idx on tprt_3 (never executed) ! Index Cond: (col1 = tbl1.col1) ! -> Index Scan using tprt4_idx on tprt_4 (never executed) ! Index Cond: (col1 = tbl1.col1) ! -> Index Scan using tprt5_idx on tprt_5 (never executed) ! Index Cond: (col1 = tbl1.col1) ! -> Index Scan using tprt6_idx on tprt_6 (never executed) ! Index Cond: (col1 = tbl1.col1) ! (15 rows) select tbl1.col1, tprt.col1 from tbl1 inner join tprt on tbl1.col1 > tprt.col1 --- 2650,2672 ---- explain (analyze, costs off, summary off, timing off) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; ! QUERY PLAN ! ----------------------------------------------------------------------- ! Gather (actual rows=2 loops=1) ! Workers Planned: 2 ! Workers Launched: 2 ! -> Nested Loop (actual rows=1 loops=3) ! Join Filter: (tbl1.col1 = tprt_1.col1) ! Rows Removed by Join Filter: 4 ! -> Parallel Append (actual rows=2 loops=3) ! -> Parallel Seq Scan on tprt_1 (actual rows=2 loops=1) ! -> Parallel Seq Scan on tprt_2 (actual rows=3 loops=1) ! -> Parallel Seq Scan on tprt_3 (actual rows=1 loops=1) ! -> Parallel Seq Scan on tprt_4 (actual rows=0 loops=1) ! -> Parallel Seq Scan on tprt_5 (actual rows=0 loops=1) ! -> Parallel Seq Scan on tprt_6 (actual rows=1 loops=1) ! -> Seq Scan on tbl1 (actual rows=2 loops=7) ! (14 rows) select tbl1.col1, tprt.col1 from tbl1 inner join tprt on tbl1.col1 > tprt.col1 *************** *** 2716,2739 **** explain (analyze, costs off, summary off, timing off) select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1; ! QUERY PLAN ! -------------------------------------------------------------------------- ! Nested Loop (actual rows=3 loops=1) ! -> Seq Scan on tbl1 (actual rows=5 loops=1) ! -> Append (actual rows=1 loops=5) ! -> Index Scan using tprt1_idx on tprt_1 (never executed) ! Index Cond: (col1 = tbl1.col1) ! -> Index Scan using tprt2_idx on tprt_2 (actual rows=1 loops=2) ! Index Cond: (col1 = tbl1.col1) ! -> Index Scan using tprt3_idx on tprt_3 (actual rows=0 loops=3) ! Index Cond: (col1 = tbl1.col1) ! -> Index Scan using tprt4_idx on tprt_4 (never executed) ! Index Cond: (col1 = tbl1.col1) ! -> Index Scan using tprt5_idx on tprt_5 (never executed) ! Index Cond: (col1 = tbl1.col1) ! -> Index Scan using tprt6_idx on tprt_6 (never executed) ! Index Cond: (col1 = tbl1.col1) ! (15 rows) select tbl1.col1, tprt.col1 from tbl1 inner join tprt on tbl1.col1 > tprt.col1 --- 2715,2737 ---- explain (analyze, costs off, summary off, timing off) select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1; ! QUERY PLAN ! ----------------------------------------------------------------------- ! Gather (actual rows=3 loops=1) ! Workers Planned: 2 ! Workers Launched: 2 ! -> Nested Loop (actual rows=1 loops=3) ! Join Filter: (tbl1.col1 = tprt_1.col1) ! Rows Removed by Join Filter: 11 ! -> Parallel Append (actual rows=2 loops=3) ! -> Parallel Seq Scan on tprt_1 (actual rows=2 loops=1) ! -> Parallel Seq Scan on tprt_2 (actual rows=3 loops=1) ! -> Parallel Seq Scan on tprt_3 (actual rows=1 loops=1) ! -> Parallel Seq Scan on tprt_4 (actual rows=0 loops=1) ! -> Parallel Seq Scan on tprt_5 (actual rows=0 loops=1) ! -> Parallel Seq Scan on tprt_6 (actual rows=1 loops=1) ! -> Seq Scan on tbl1 (actual rows=5 loops=7) ! (14 rows) select tbl1.col1, tprt.col1 from tbl1 inner join tprt on tbl1.col1 > tprt.col1 *************** *** 2812,2835 **** insert into tbl1 values (10000); explain (analyze, costs off, summary off, timing off) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; ! QUERY PLAN ! ------------------------------------------------------------------- ! Nested Loop (actual rows=0 loops=1) ! -> Seq Scan on tbl1 (actual rows=1 loops=1) ! -> Append (actual rows=0 loops=1) ! -> Index Scan using tprt1_idx on tprt_1 (never executed) ! Index Cond: (col1 = tbl1.col1) ! -> Index Scan using tprt2_idx on tprt_2 (never executed) ! Index Cond: (col1 = tbl1.col1) ! -> Index Scan using tprt3_idx on tprt_3 (never executed) ! Index Cond: (col1 = tbl1.col1) ! -> Index Scan using tprt4_idx on tprt_4 (never executed) ! Index Cond: (col1 = tbl1.col1) ! -> Index Scan using tprt5_idx on tprt_5 (never executed) ! Index Cond: (col1 = tbl1.col1) ! -> Index Scan using tprt6_idx on tprt_6 (never executed) ! Index Cond: (col1 = tbl1.col1) ! (15 rows) select tbl1.col1, tprt.col1 from tbl1 inner join tprt on tbl1.col1 = tprt.col1 --- 2810,2832 ---- insert into tbl1 values (10000); explain (analyze, costs off, summary off, timing off) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; ! QUERY PLAN ! ----------------------------------------------------------------------- ! Gather (actual rows=0 loops=1) ! Workers Planned: 2 ! Workers Launched: 2 ! -> Nested Loop (actual rows=0 loops=3) ! Join Filter: (tbl1.col1 = tprt_1.col1) ! Rows Removed by Join Filter: 2 ! -> Parallel Append (actual rows=2 loops=3) ! -> Parallel Seq Scan on tprt_1 (actual rows=2 loops=1) ! -> Parallel Seq Scan on tprt_2 (actual rows=3 loops=1) ! -> Parallel Seq Scan on tprt_3 (actual rows=1 loops=1) ! -> Parallel Seq Scan on tprt_4 (actual rows=0 loops=1) ! -> Parallel Seq Scan on tprt_5 (actual rows=0 loops=1) ! -> Parallel Seq Scan on tprt_6 (actual rows=1 loops=1) ! -> Seq Scan on tbl1 (actual rows=1 loops=7) ! (14 rows) select tbl1.col1, tprt.col1 from tbl1 inner join tprt on tbl1.col1 = tprt.col1 ======================================================================