diff options
Diffstat (limited to 'contrib/postgres_fdw/expected/postgres_fdw.out')
| -rw-r--r-- | contrib/postgres_fdw/expected/postgres_fdw.out | 296 |
1 files changed, 148 insertions, 148 deletions
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index d2765456384..97b24f3e38e 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -6260,10 +6260,10 @@ UPDATE rw_view SET b = b + 5; QUERY PLAN ---------------------------------------------------------------------------------------- Update on public.parent_tbl - Foreign Update on public.foreign_tbl + Foreign Update on public.foreign_tbl parent_tbl_1 Remote SQL: UPDATE public.child_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b - -> Foreign Scan on public.foreign_tbl - Output: foreign_tbl.a, (foreign_tbl.b + 5), foreign_tbl.ctid + -> Foreign Scan on public.foreign_tbl parent_tbl_1 + Output: parent_tbl_1.a, (parent_tbl_1.b + 5), parent_tbl_1.ctid Remote SQL: SELECT a, b, ctid FROM public.child_tbl WHERE ((a < b)) FOR UPDATE (6 rows) @@ -6275,10 +6275,10 @@ UPDATE rw_view SET b = b + 15; QUERY PLAN ---------------------------------------------------------------------------------------- Update on public.parent_tbl - Foreign Update on public.foreign_tbl + Foreign Update on public.foreign_tbl parent_tbl_1 Remote SQL: UPDATE public.child_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b - -> Foreign Scan on public.foreign_tbl - Output: foreign_tbl.a, (foreign_tbl.b + 15), foreign_tbl.ctid + -> Foreign Scan on public.foreign_tbl parent_tbl_1 + Output: parent_tbl_1.a, (parent_tbl_1.b + 15), parent_tbl_1.ctid Remote SQL: SELECT a, b, ctid FROM public.child_tbl WHERE ((a < b)) FOR UPDATE (6 rows) @@ -7066,8 +7066,8 @@ select * from bar where f1 in (select f1 from foo) for update; -> Append -> Seq Scan on public.bar Output: bar.f1, bar.f2, bar.ctid, bar.*, bar.tableoid - -> Foreign Scan on public.bar2 - Output: bar2.f1, bar2.f2, bar2.ctid, bar2.*, bar2.tableoid + -> Foreign Scan on public.bar2 bar_1 + Output: bar_1.f1, bar_1.f2, bar_1.ctid, bar_1.*, bar_1.tableoid Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE -> Hash Output: foo.ctid, foo.f1, foo.*, foo.tableoid @@ -7077,8 +7077,8 @@ select * from bar where f1 in (select f1 from foo) for update; -> Append -> Seq Scan on public.foo Output: foo.ctid, foo.f1, foo.*, foo.tableoid - -> Foreign Scan on public.foo2 - Output: foo2.ctid, foo2.f1, foo2.*, foo2.tableoid + -> Foreign Scan on public.foo2 foo_1 + Output: foo_1.ctid, foo_1.f1, foo_1.*, foo_1.tableoid Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1 (23 rows) @@ -7104,8 +7104,8 @@ select * from bar where f1 in (select f1 from foo) for share; -> Append -> Seq Scan on public.bar Output: bar.f1, bar.f2, bar.ctid, bar.*, bar.tableoid - -> Foreign Scan on public.bar2 - Output: bar2.f1, bar2.f2, bar2.ctid, bar2.*, bar2.tableoid + -> Foreign Scan on public.bar2 bar_1 + Output: bar_1.f1, bar_1.f2, bar_1.ctid, bar_1.*, bar_1.tableoid Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR SHARE -> Hash Output: foo.ctid, foo.f1, foo.*, foo.tableoid @@ -7115,8 +7115,8 @@ select * from bar where f1 in (select f1 from foo) for share; -> Append -> Seq Scan on public.foo Output: foo.ctid, foo.f1, foo.*, foo.tableoid - -> Foreign Scan on public.foo2 - Output: foo2.ctid, foo2.f1, foo2.*, foo2.tableoid + -> Foreign Scan on public.foo2 foo_1 + Output: foo_1.ctid, foo_1.f1, foo_1.*, foo_1.tableoid Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1 (23 rows) @@ -7132,11 +7132,11 @@ select * from bar where f1 in (select f1 from foo) for share; -- Check UPDATE with inherited target and an inherited source table explain (verbose, costs off) update bar set f2 = f2 + 100 where f1 in (select f1 from foo); - QUERY PLAN ---------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------- Update on public.bar Update on public.bar - Foreign Update on public.bar2 + Foreign Update on public.bar2 bar_1 Remote SQL: UPDATE public.loct2 SET f2 = $2 WHERE ctid = $1 -> Hash Join Output: bar.f1, (bar.f2 + 100), bar.ctid, foo.ctid, foo.*, foo.tableoid @@ -7152,15 +7152,15 @@ update bar set f2 = f2 + 100 where f1 in (select f1 from foo); -> Append -> Seq Scan on public.foo Output: foo.ctid, foo.f1, foo.*, foo.tableoid - -> Foreign Scan on public.foo2 - Output: foo2.ctid, foo2.f1, foo2.*, foo2.tableoid + -> Foreign Scan on public.foo2 foo_1 + Output: foo_1.ctid, foo_1.f1, foo_1.*, foo_1.tableoid Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1 -> Hash Join - Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, foo.ctid, foo.*, foo.tableoid + Output: bar_1.f1, (bar_1.f2 + 100), bar_1.f3, bar_1.ctid, foo.ctid, foo.*, foo.tableoid Inner Unique: true - Hash Cond: (bar2.f1 = foo.f1) - -> Foreign Scan on public.bar2 - Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid + Hash Cond: (bar_1.f1 = foo.f1) + -> Foreign Scan on public.bar2 bar_1 + Output: bar_1.f1, bar_1.f2, bar_1.f3, bar_1.ctid Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE -> Hash Output: foo.ctid, foo.f1, foo.*, foo.tableoid @@ -7170,8 +7170,8 @@ update bar set f2 = f2 + 100 where f1 in (select f1 from foo); -> Append -> Seq Scan on public.foo Output: foo.ctid, foo.f1, foo.*, foo.tableoid - -> Foreign Scan on public.foo2 - Output: foo2.ctid, foo2.f1, foo2.*, foo2.tableoid + -> Foreign Scan on public.foo2 foo_1 + Output: foo_1.ctid, foo_1.f1, foo_1.*, foo_1.tableoid Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1 (39 rows) @@ -7197,7 +7197,7 @@ where bar.f1 = ss.f1; -------------------------------------------------------------------------------------- Update on public.bar Update on public.bar - Foreign Update on public.bar2 + Foreign Update on public.bar2 bar_1 Remote SQL: UPDATE public.loct2 SET f2 = $2 WHERE ctid = $1 -> Hash Join Output: bar.f1, (bar.f2 + 100), bar.ctid, (ROW(foo.f1)) @@ -7205,26 +7205,26 @@ where bar.f1 = ss.f1; -> Append -> Seq Scan on public.foo Output: ROW(foo.f1), foo.f1 - -> Foreign Scan on public.foo2 - Output: ROW(foo2.f1), foo2.f1 + -> Foreign Scan on public.foo2 foo_1 + Output: ROW(foo_1.f1), foo_1.f1 Remote SQL: SELECT f1 FROM public.loct1 - -> Seq Scan on public.foo foo_1 - Output: ROW((foo_1.f1 + 3)), (foo_1.f1 + 3) - -> Foreign Scan on public.foo2 foo2_1 - Output: ROW((foo2_1.f1 + 3)), (foo2_1.f1 + 3) + -> Seq Scan on public.foo foo_2 + Output: ROW((foo_2.f1 + 3)), (foo_2.f1 + 3) + -> Foreign Scan on public.foo2 foo_3 + Output: ROW((foo_3.f1 + 3)), (foo_3.f1 + 3) Remote SQL: SELECT f1 FROM public.loct1 -> Hash Output: bar.f1, bar.f2, bar.ctid -> Seq Scan on public.bar Output: bar.f1, bar.f2, bar.ctid -> Merge Join - Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, (ROW(foo.f1)) - Merge Cond: (bar2.f1 = foo.f1) + Output: bar_1.f1, (bar_1.f2 + 100), bar_1.f3, bar_1.ctid, (ROW(foo.f1)) + Merge Cond: (bar_1.f1 = foo.f1) -> Sort - Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid - Sort Key: bar2.f1 - -> Foreign Scan on public.bar2 - Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid + Output: bar_1.f1, bar_1.f2, bar_1.f3, bar_1.ctid + Sort Key: bar_1.f1 + -> Foreign Scan on public.bar2 bar_1 + Output: bar_1.f1, bar_1.f2, bar_1.f3, bar_1.ctid Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE -> Sort Output: (ROW(foo.f1)), foo.f1 @@ -7232,13 +7232,13 @@ where bar.f1 = ss.f1; -> Append -> Seq Scan on public.foo Output: ROW(foo.f1), foo.f1 - -> Foreign Scan on public.foo2 - Output: ROW(foo2.f1), foo2.f1 + -> Foreign Scan on public.foo2 foo_1 + Output: ROW(foo_1.f1), foo_1.f1 Remote SQL: SELECT f1 FROM public.loct1 - -> Seq Scan on public.foo foo_1 - Output: ROW((foo_1.f1 + 3)), (foo_1.f1 + 3) - -> Foreign Scan on public.foo2 foo2_1 - Output: ROW((foo2_1.f1 + 3)), (foo2_1.f1 + 3) + -> Seq Scan on public.foo foo_2 + Output: ROW((foo_2.f1 + 3)), (foo_2.f1 + 3) + -> Foreign Scan on public.foo2 foo_3 + Output: ROW((foo_3.f1 + 3)), (foo_3.f1 + 3) Remote SQL: SELECT f1 FROM public.loct1 (45 rows) @@ -7288,8 +7288,8 @@ explain (verbose, costs off) Sort Key: foo.f1 -> Index Scan using i_foo_f1 on public.foo Output: foo.f1, foo.f2 - -> Foreign Scan on public.foo2 - Output: foo2.f1, foo2.f2 + -> Foreign Scan on public.foo2 foo_1 + Output: foo_1.f1, foo_1.f2 Remote SQL: SELECT f1, f2 FROM public.loct1 ORDER BY f1 ASC NULLS LAST -> Index Only Scan using i_loct1_f1 on public.loct1 Output: loct1.f1 @@ -7328,8 +7328,8 @@ explain (verbose, costs off) Sort Key: foo.f1 -> Index Scan using i_foo_f1 on public.foo Output: foo.f1, foo.f2 - -> Foreign Scan on public.foo2 - Output: foo2.f1, foo2.f2 + -> Foreign Scan on public.foo2 foo_1 + Output: foo_1.f1, foo_1.f2 Remote SQL: SELECT f1, f2 FROM public.loct1 ORDER BY f1 ASC NULLS LAST -> Index Only Scan using i_loct1_f1 on public.loct1 Output: loct1.f1 @@ -7371,11 +7371,11 @@ delete from foo where f1 < 5 returning *; Delete on public.foo Output: foo.f1, foo.f2 Delete on public.foo - Foreign Delete on public.foo2 + Foreign Delete on public.foo2 foo_1 -> Index Scan using i_foo_f1 on public.foo Output: foo.ctid Index Cond: (foo.f1 < 5) - -> Foreign Delete on public.foo2 + -> Foreign Delete on public.foo2 foo_1 Remote SQL: DELETE FROM public.loct1 WHERE ((f1 < 5)) RETURNING f1, f2 (9 rows) @@ -7396,10 +7396,10 @@ update bar set f2 = f2 + 100 returning *; Update on public.bar Output: bar.f1, bar.f2 Update on public.bar - Foreign Update on public.bar2 + Foreign Update on public.bar2 bar_1 -> Seq Scan on public.bar Output: bar.f1, (bar.f2 + 100), bar.ctid - -> Foreign Update on public.bar2 + -> Foreign Update on public.bar2 bar_1 Remote SQL: UPDATE public.loct2 SET f2 = (f2 + 100) RETURNING f1, f2 (8 rows) @@ -7427,12 +7427,12 @@ update bar set f2 = f2 + 100; -------------------------------------------------------------------------------------------------------- Update on public.bar Update on public.bar - Foreign Update on public.bar2 + Foreign Update on public.bar2 bar_1 Remote SQL: UPDATE public.loct2 SET f1 = $2, f2 = $3, f3 = $4 WHERE ctid = $1 RETURNING f1, f2, f3 -> Seq Scan on public.bar Output: bar.f1, (bar.f2 + 100), bar.ctid - -> Foreign Scan on public.bar2 - Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, bar2.* + -> Foreign Scan on public.bar2 bar_1 + Output: bar_1.f1, (bar_1.f2 + 100), bar_1.f3, bar_1.ctid, bar_1.* Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE (9 rows) @@ -7455,13 +7455,13 @@ delete from bar where f2 < 400; --------------------------------------------------------------------------------------------- Delete on public.bar Delete on public.bar - Foreign Delete on public.bar2 + Foreign Delete on public.bar2 bar_1 Remote SQL: DELETE FROM public.loct2 WHERE ctid = $1 RETURNING f1, f2, f3 -> Seq Scan on public.bar Output: bar.ctid Filter: (bar.f2 < 400) - -> Foreign Scan on public.bar2 - Output: bar2.ctid, bar2.* + -> Foreign Scan on public.bar2 bar_1 + Output: bar_1.ctid, bar_1.* Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 WHERE ((f2 < 400)) FOR UPDATE (10 rows) @@ -7499,7 +7499,7 @@ update parent set b = parent.b || remt2.b from remt2 where parent.a = remt2.a re Update on public.parent Output: parent.a, parent.b, remt2.a, remt2.b Update on public.parent - Foreign Update on public.remt1 + Foreign Update on public.remt1 parent_1 -> Nested Loop Output: parent.a, (parent.b || remt2.b), parent.ctid, remt2.*, remt2.a, remt2.b Join Filter: (parent.a = remt2.a) @@ -7526,7 +7526,7 @@ delete from parent using remt2 where parent.a = remt2.a returning parent; Delete on public.parent Output: parent.* Delete on public.parent - Foreign Delete on public.remt1 + Foreign Delete on public.remt1 parent_1 -> Nested Loop Output: parent.ctid, remt2.* Join Filter: (parent.a = remt2.a) @@ -7753,14 +7753,14 @@ update utrtest set a = 1 where a = 1 or a = 2 returning *; QUERY PLAN ---------------------------------------------------------------------------------------------- Update on public.utrtest - Output: remp.a, remp.b - Foreign Update on public.remp - Update on public.locp - -> Foreign Update on public.remp + Output: utrtest_1.a, utrtest_1.b + Foreign Update on public.remp utrtest_1 + Update on public.locp utrtest_2 + -> Foreign Update on public.remp utrtest_1 Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a = 2))) RETURNING a, b - -> Seq Scan on public.locp - Output: 1, locp.b, locp.ctid - Filter: ((locp.a = 1) OR (locp.a = 2)) + -> Seq Scan on public.locp utrtest_2 + Output: 1, utrtest_2.b, utrtest_2.ctid + Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2)) (9 rows) -- The new values are concatenated with ' triggered !' @@ -7775,14 +7775,14 @@ insert into utrtest values (2, 'qux'); -- Check case where the foreign partition isn't a subplan target rel explain (verbose, costs off) update utrtest set a = 1 where a = 2 returning *; - QUERY PLAN --------------------------------------- + QUERY PLAN +------------------------------------------------ Update on public.utrtest - Output: locp.a, locp.b - Update on public.locp - -> Seq Scan on public.locp - Output: 1, locp.b, locp.ctid - Filter: (locp.a = 2) + Output: utrtest_1.a, utrtest_1.b + Update on public.locp utrtest_1 + -> Seq Scan on public.locp utrtest_1 + Output: 1, utrtest_1.b, utrtest_1.ctid + Filter: (utrtest_1.a = 2) (6 rows) -- The new values are concatenated with ' triggered !' @@ -7805,13 +7805,13 @@ update utrtest set a = 1 returning *; QUERY PLAN ----------------------------------------------------------------- Update on public.utrtest - Output: remp.a, remp.b - Foreign Update on public.remp - Update on public.locp - -> Foreign Update on public.remp + Output: utrtest_1.a, utrtest_1.b + Foreign Update on public.remp utrtest_1 + Update on public.locp utrtest_2 + -> Foreign Update on public.remp utrtest_1 Remote SQL: UPDATE public.loct SET a = 1 RETURNING a, b - -> Seq Scan on public.locp - Output: 1, locp.b, locp.ctid + -> Seq Scan on public.locp utrtest_2 + Output: 1, utrtest_2.b, utrtest_2.ctid (8 rows) update utrtest set a = 1 returning *; @@ -7827,28 +7827,28 @@ insert into utrtest values (2, 'qux'); -- with a non-direct modification plan explain (verbose, costs off) update utrtest set a = 1 from (values (1), (2)) s(x) where a = s.x returning *; - QUERY PLAN ------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------- Update on public.utrtest - Output: remp.a, remp.b, "*VALUES*".column1 - Foreign Update on public.remp + Output: utrtest_1.a, utrtest_1.b, "*VALUES*".column1 + Foreign Update on public.remp utrtest_1 Remote SQL: UPDATE public.loct SET a = $2 WHERE ctid = $1 RETURNING a, b - Update on public.locp + Update on public.locp utrtest_2 -> Hash Join - Output: 1, remp.b, remp.ctid, "*VALUES*".*, "*VALUES*".column1 - Hash Cond: (remp.a = "*VALUES*".column1) - -> Foreign Scan on public.remp - Output: remp.b, remp.ctid, remp.a + Output: 1, utrtest_1.b, utrtest_1.ctid, "*VALUES*".*, "*VALUES*".column1 + Hash Cond: (utrtest_1.a = "*VALUES*".column1) + -> Foreign Scan on public.remp utrtest_1 + Output: utrtest_1.b, utrtest_1.ctid, utrtest_1.a Remote SQL: SELECT a, b, ctid FROM public.loct FOR UPDATE -> Hash Output: "*VALUES*".*, "*VALUES*".column1 -> Values Scan on "*VALUES*" Output: "*VALUES*".*, "*VALUES*".column1 -> Hash Join - Output: 1, locp.b, locp.ctid, "*VALUES*".*, "*VALUES*".column1 - Hash Cond: (locp.a = "*VALUES*".column1) - -> Seq Scan on public.locp - Output: locp.b, locp.ctid, locp.a + Output: 1, utrtest_2.b, utrtest_2.ctid, "*VALUES*".*, "*VALUES*".column1 + Hash Cond: (utrtest_2.a = "*VALUES*".column1) + -> Seq Scan on public.locp utrtest_2 + Output: utrtest_2.b, utrtest_2.ctid, utrtest_2.a -> Hash Output: "*VALUES*".*, "*VALUES*".column1 -> Values Scan on "*VALUES*" @@ -7880,12 +7880,12 @@ update utrtest set a = 3 returning *; QUERY PLAN ----------------------------------------------------------------- Update on public.utrtest - Output: locp.a, locp.b - Update on public.locp - Foreign Update on public.remp - -> Seq Scan on public.locp - Output: 3, locp.b, locp.ctid - -> Foreign Update on public.remp + Output: utrtest_1.a, utrtest_1.b + Update on public.locp utrtest_1 + Foreign Update on public.remp utrtest_2 + -> Seq Scan on public.locp utrtest_1 + Output: 3, utrtest_1.b, utrtest_1.ctid + -> Foreign Update on public.remp utrtest_2 Remote SQL: UPDATE public.loct SET a = 3 RETURNING a, b (8 rows) @@ -7894,27 +7894,27 @@ ERROR: cannot route tuples into foreign table to be updated "remp" -- with a non-direct modification plan explain (verbose, costs off) update utrtest set a = 3 from (values (2), (3)) s(x) where a = s.x returning *; - QUERY PLAN ------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------- Update on public.utrtest - Output: locp.a, locp.b, "*VALUES*".column1 - Update on public.locp - Foreign Update on public.remp + Output: utrtest_1.a, utrtest_1.b, "*VALUES*".column1 + Update on public.locp utrtest_1 + Foreign Update on public.remp utrtest_2 Remote SQL: UPDATE public.loct SET a = $2 WHERE ctid = $1 RETURNING a, b -> Hash Join - Output: 3, locp.b, locp.ctid, "*VALUES*".*, "*VALUES*".column1 - Hash Cond: (locp.a = "*VALUES*".column1) - -> Seq Scan on public.locp - Output: locp.b, locp.ctid, locp.a + Output: 3, utrtest_1.b, utrtest_1.ctid, "*VALUES*".*, "*VALUES*".column1 + Hash Cond: (utrtest_1.a = "*VALUES*".column1) + -> Seq Scan on public.locp utrtest_1 + Output: utrtest_1.b, utrtest_1.ctid, utrtest_1.a -> Hash Output: "*VALUES*".*, "*VALUES*".column1 -> Values Scan on "*VALUES*" Output: "*VALUES*".*, "*VALUES*".column1 -> Hash Join - Output: 3, remp.b, remp.ctid, "*VALUES*".*, "*VALUES*".column1 - Hash Cond: (remp.a = "*VALUES*".column1) - -> Foreign Scan on public.remp - Output: remp.b, remp.ctid, remp.a + Output: 3, utrtest_2.b, utrtest_2.ctid, "*VALUES*".*, "*VALUES*".column1 + Hash Cond: (utrtest_2.a = "*VALUES*".column1) + -> Foreign Scan on public.remp utrtest_2 + Output: utrtest_2.b, utrtest_2.ctid, utrtest_2.a Remote SQL: SELECT a, b, ctid FROM public.loct FOR UPDATE -> Hash Output: "*VALUES*".*, "*VALUES*".column1 @@ -8506,8 +8506,8 @@ SELECT t1.a,t2.b,t2.c FROM fprt1 t1 LEFT JOIN (SELECT * FROM fprt2 WHERE a < 10) QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan - Output: t1.a, ftprt2_p1.b, ftprt2_p1.c - Relations: (public.ftprt1_p1 t1) LEFT JOIN (public.ftprt2_p1) + Output: t1.a, fprt2.b, fprt2.c + Relations: (public.ftprt1_p1 t1) LEFT JOIN (public.ftprt2_p1 fprt2) Remote SQL: SELECT r5.a, r6.b, r6.c FROM (public.fprt1_p1 r5 LEFT JOIN public.fprt2_p1 r6 ON (((r5.a = r6.b)) AND ((r5.b = r6.a)) AND ((r6.a < 10)))) WHERE ((r5.a < 10)) ORDER BY r5.a ASC NULLS LAST, r6.b ASC NULLS LAST, r6.c ASC NULLS LAST (4 rows) @@ -8584,21 +8584,21 @@ SELECT t1.a,t1.b FROM fprt1 t1, LATERAL (SELECT t2.a, t2.b FROM fprt2 t2 WHERE t -- with PHVs, partitionwise join selected but no join pushdown EXPLAIN (COSTS OFF) SELECT t1.a, t1.phv, t2.b, t2.phv FROM (SELECT 't1_phv' phv, * FROM fprt1 WHERE a % 25 = 0) t1 FULL JOIN (SELECT 't2_phv' phv, * FROM fprt2 WHERE b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY t1.a, t2.b; - QUERY PLAN ------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------- Sort - Sort Key: ftprt1_p1.a, ftprt2_p1.b + Sort Key: fprt1.a, fprt2.b -> Append -> Hash Full Join - Hash Cond: (ftprt1_p1.a = ftprt2_p1.b) - -> Foreign Scan on ftprt1_p1 + Hash Cond: (fprt1.a = fprt2.b) + -> Foreign Scan on ftprt1_p1 fprt1 -> Hash - -> Foreign Scan on ftprt2_p1 + -> Foreign Scan on ftprt2_p1 fprt2 -> Hash Full Join - Hash Cond: (ftprt1_p2.a = ftprt2_p2.b) - -> Foreign Scan on ftprt1_p2 + Hash Cond: (fprt1_1.a = fprt2_1.b) + -> Foreign Scan on ftprt1_p2 fprt1_1 -> Hash - -> Foreign Scan on ftprt2_p2 + -> Foreign Scan on ftprt2_p2 fprt2_1 (13 rows) SELECT t1.a, t1.phv, t2.b, t2.phv FROM (SELECT 't1_phv' phv, * FROM fprt1 WHERE a % 25 = 0) t1 FULL JOIN (SELECT 't2_phv' phv, * FROM fprt2 WHERE b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY t1.a, t2.b; @@ -8672,34 +8672,34 @@ ANALYZE fpagg_tab_p3; SET enable_partitionwise_aggregate TO false; EXPLAIN (COSTS OFF) SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1; - QUERY PLAN -------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------- Sort - Sort Key: fpagg_tab_p1.a + Sort Key: pagg_tab.a -> HashAggregate - Group Key: fpagg_tab_p1.a - Filter: (avg(fpagg_tab_p1.b) < '22'::numeric) + Group Key: pagg_tab.a + Filter: (avg(pagg_tab.b) < '22'::numeric) -> Append - -> Foreign Scan on fpagg_tab_p1 - -> Foreign Scan on fpagg_tab_p2 - -> Foreign Scan on fpagg_tab_p3 + -> Foreign Scan on fpagg_tab_p1 pagg_tab + -> Foreign Scan on fpagg_tab_p2 pagg_tab_1 + -> Foreign Scan on fpagg_tab_p3 pagg_tab_2 (9 rows) -- Plan with partitionwise aggregates is enabled SET enable_partitionwise_aggregate TO true; EXPLAIN (COSTS OFF) SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1; - QUERY PLAN -------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------ Sort - Sort Key: fpagg_tab_p1.a + Sort Key: pagg_tab.a -> Append -> Foreign Scan - Relations: Aggregate on (public.fpagg_tab_p1) + Relations: Aggregate on (public.fpagg_tab_p1 pagg_tab) -> Foreign Scan - Relations: Aggregate on (public.fpagg_tab_p2) + Relations: Aggregate on (public.fpagg_tab_p2 pagg_tab_1) -> Foreign Scan - Relations: Aggregate on (public.fpagg_tab_p3) + Relations: Aggregate on (public.fpagg_tab_p3 pagg_tab_2) (9 rows) SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1; @@ -8760,23 +8760,23 @@ SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1; -- When GROUP BY clause does not match with PARTITION KEY. EXPLAIN (COSTS OFF) SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700 ORDER BY 1; - QUERY PLAN ------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------- Sort - Sort Key: fpagg_tab_p1.b + Sort Key: pagg_tab.b -> Finalize HashAggregate - Group Key: fpagg_tab_p1.b - Filter: (sum(fpagg_tab_p1.a) < 700) + Group Key: pagg_tab.b + Filter: (sum(pagg_tab.a) < 700) -> Append -> Partial HashAggregate - Group Key: fpagg_tab_p1.b - -> Foreign Scan on fpagg_tab_p1 + Group Key: pagg_tab.b + -> Foreign Scan on fpagg_tab_p1 pagg_tab -> Partial HashAggregate - Group Key: fpagg_tab_p2.b - -> Foreign Scan on fpagg_tab_p2 + Group Key: pagg_tab_1.b + -> Foreign Scan on fpagg_tab_p2 pagg_tab_1 -> Partial HashAggregate - Group Key: fpagg_tab_p3.b - -> Foreign Scan on fpagg_tab_p3 + Group Key: pagg_tab_2.b + -> Foreign Scan on fpagg_tab_p3 pagg_tab_2 (15 rows) -- Clean-up |