diff options
Diffstat (limited to 'src/test/regress/expected')
36 files changed, 2945 insertions, 876 deletions
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index 035f9a78206..bc83a6e188e 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -782,9 +782,9 @@ select array(select sum(x+y) s QUERY PLAN ------------------------------------------------------------------- Function Scan on pg_catalog.generate_series x - Output: ARRAY(SubPlan 1) + Output: ARRAY(SubPlan array_1) Function Call: generate_series(1, 3) - SubPlan 1 + SubPlan array_1 -> Sort Output: (sum((x.x + y.y))), y.y Sort Key: (sum((x.x + y.y))) @@ -960,7 +960,7 @@ explain (costs off) ------------------------------------------------------------ Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Index Only Scan using tenk1_unique1 on tenk1 Index Cond: (unique1 IS NOT NULL) @@ -978,7 +978,7 @@ explain (costs off) --------------------------------------------------------------------- Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Index Only Scan Backward using tenk1_unique1 on tenk1 Index Cond: (unique1 IS NOT NULL) @@ -996,7 +996,7 @@ explain (costs off) ------------------------------------------------------------------------ Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Index Only Scan Backward using tenk1_unique1 on tenk1 Index Cond: ((unique1 IS NOT NULL) AND (unique1 < 42)) @@ -1014,7 +1014,7 @@ explain (costs off) ------------------------------------------------------------------------ Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Index Only Scan Backward using tenk1_unique1 on tenk1 Index Cond: ((unique1 IS NOT NULL) AND (unique1 > 42)) @@ -1038,7 +1038,7 @@ explain (costs off) --------------------------------------------------------------------------- Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Index Only Scan Backward using tenk1_unique1 on tenk1 Index Cond: ((unique1 IS NOT NULL) AND (unique1 > 42000)) @@ -1058,7 +1058,7 @@ explain (costs off) ---------------------------------------------------------------------------- Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Index Only Scan Backward using tenk1_thous_tenthous on tenk1 Index Cond: ((thousand = 33) AND (tenthous IS NOT NULL)) @@ -1076,7 +1076,7 @@ explain (costs off) -------------------------------------------------------------------------- Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Index Only Scan using tenk1_thous_tenthous on tenk1 Index Cond: ((thousand = 33) AND (tenthous IS NOT NULL)) @@ -1095,10 +1095,10 @@ explain (costs off) QUERY PLAN ----------------------------------------------------------------------------------------- Seq Scan on int4_tbl - SubPlan 2 + SubPlan expr_1 -> Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Index Only Scan using tenk1_unique1 on tenk1 Index Cond: ((unique1 IS NOT NULL) AND (unique1 > int4_tbl.f1)) @@ -1121,8 +1121,8 @@ explain (costs off) QUERY PLAN --------------------------------------------------------------------- HashAggregate - Group Key: (InitPlan 1).col1 - InitPlan 1 + Group Key: (InitPlan minmax_1).col1 + InitPlan minmax_1 -> Limit -> Index Only Scan Backward using tenk1_unique2 on tenk1 Index Cond: (unique2 IS NOT NULL) @@ -1141,8 +1141,8 @@ explain (costs off) QUERY PLAN --------------------------------------------------------------------- Sort - Sort Key: ((InitPlan 1).col1) - InitPlan 1 + Sort Key: ((InitPlan minmax_1).col1) + InitPlan minmax_1 -> Limit -> Index Only Scan Backward using tenk1_unique2 on tenk1 Index Cond: (unique2 IS NOT NULL) @@ -1161,8 +1161,8 @@ explain (costs off) QUERY PLAN --------------------------------------------------------------------- Sort - Sort Key: ((InitPlan 1).col1) - InitPlan 1 + Sort Key: ((InitPlan minmax_1).col1) + InitPlan minmax_1 -> Limit -> Index Only Scan Backward using tenk1_unique2 on tenk1 Index Cond: (unique2 IS NOT NULL) @@ -1181,8 +1181,8 @@ explain (costs off) QUERY PLAN --------------------------------------------------------------------- Sort - Sort Key: (((InitPlan 1).col1 + 1)) - InitPlan 1 + Sort Key: (((InitPlan minmax_1).col1 + 1)) + InitPlan minmax_1 -> Limit -> Index Only Scan Backward using tenk1_unique2 on tenk1 Index Cond: (unique2 IS NOT NULL) @@ -1202,7 +1202,7 @@ explain (costs off) --------------------------------------------------------------------- Sort Sort Key: (generate_series(1, 3)) DESC - InitPlan 1 + InitPlan minmax_1 -> Limit -> Index Only Scan Backward using tenk1_unique2 on tenk1 Index Cond: (unique2 IS NOT NULL) @@ -1226,7 +1226,7 @@ explain (costs off) ---------------------------------------------------- Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Result One-Time Filter: (100 IS NOT NULL) @@ -1258,7 +1258,7 @@ explain (costs off) --------------------------------------------------------------------------------------------- Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Merge Append Sort Key: minmaxtest.f1 @@ -1269,7 +1269,7 @@ explain (costs off) -> Index Only Scan Backward using minmaxtest2i on minmaxtest2 minmaxtest_3 Index Cond: (f1 IS NOT NULL) -> Index Only Scan using minmaxtest3i on minmaxtest3 minmaxtest_4 - InitPlan 2 + InitPlan minmax_2 -> Limit -> Merge Append Sort Key: minmaxtest_5.f1 DESC @@ -1294,7 +1294,7 @@ explain (costs off) QUERY PLAN --------------------------------------------------------------------------------------------- Unique - InitPlan 1 + InitPlan minmax_1 -> Limit -> Merge Append Sort Key: minmaxtest.f1 @@ -1305,7 +1305,7 @@ explain (costs off) -> Index Only Scan Backward using minmaxtest2i on minmaxtest2 minmaxtest_3 Index Cond: (f1 IS NOT NULL) -> Index Only Scan using minmaxtest3i on minmaxtest3 minmaxtest_4 - InitPlan 2 + InitPlan minmax_2 -> Limit -> Merge Append Sort Key: minmaxtest_5.f1 DESC @@ -1317,7 +1317,7 @@ explain (costs off) Index Cond: (f1 IS NOT NULL) -> Index Only Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest_9 -> Sort - Sort Key: ((InitPlan 1).col1), ((InitPlan 2).col1) + Sort Key: ((InitPlan minmax_1).col1), ((InitPlan minmax_2).col1) -> Result Replaces: MinMaxAggregate (27 rows) @@ -1342,10 +1342,10 @@ explain (costs off) QUERY PLAN --------------------------------------------------------------------- Seq Scan on int4_tbl t0 - SubPlan 2 + SubPlan expr_1 -> HashAggregate - Group Key: (InitPlan 1).col1 - InitPlan 1 + Group Key: (InitPlan minmax_1).col1 + InitPlan minmax_1 -> Limit -> Seq Scan on int4_tbl t1 Filter: ((f1 IS NOT NULL) AND (f1 = t0.f1)) diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out index 69805d4b9ec..05d8b3b369e 100644 --- a/src/test/regress/expected/collate.icu.utf8.out +++ b/src/test/regress/expected/collate.icu.utf8.out @@ -2437,11 +2437,11 @@ SELECT c collate "C", count(c) FROM pagg_tab3 GROUP BY c collate "C" ORDER BY 1; SET enable_partitionwise_join TO false; EXPLAIN (COSTS OFF) SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROUP BY 1 ORDER BY t1.c COLLATE "C"; - QUERY PLAN -------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------- Sort Sort Key: t1.c COLLATE "C" - -> HashAggregate + -> Finalize HashAggregate Group Key: t1.c -> Hash Join Hash Cond: (t1.c = t2.c) @@ -2449,10 +2449,12 @@ SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROU -> Seq Scan on pagg_tab3_p2 t1_1 -> Seq Scan on pagg_tab3_p1 t1_2 -> Hash - -> Append - -> Seq Scan on pagg_tab3_p2 t2_1 - -> Seq Scan on pagg_tab3_p1 t2_2 -(13 rows) + -> Partial HashAggregate + Group Key: t2.c + -> Append + -> Seq Scan on pagg_tab3_p2 t2_1 + -> Seq Scan on pagg_tab3_p1 t2_2 +(15 rows) SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROUP BY 1 ORDER BY t1.c COLLATE "C"; c | count @@ -2464,11 +2466,11 @@ SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROU SET enable_partitionwise_join TO true; EXPLAIN (COSTS OFF) SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROUP BY 1 ORDER BY t1.c COLLATE "C"; - QUERY PLAN -------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------- Sort Sort Key: t1.c COLLATE "C" - -> HashAggregate + -> Finalize HashAggregate Group Key: t1.c -> Hash Join Hash Cond: (t1.c = t2.c) @@ -2476,10 +2478,12 @@ SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROU -> Seq Scan on pagg_tab3_p2 t1_1 -> Seq Scan on pagg_tab3_p1 t1_2 -> Hash - -> Append - -> Seq Scan on pagg_tab3_p2 t2_1 - -> Seq Scan on pagg_tab3_p1 t2_2 -(13 rows) + -> Partial HashAggregate + Group Key: t2.c + -> Append + -> Seq Scan on pagg_tab3_p2 t2_1 + -> Seq Scan on pagg_tab3_p1 t2_2 +(15 rows) SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROUP BY 1 ORDER BY t1.c COLLATE "C"; c | count @@ -2690,6 +2694,55 @@ SELECT * FROM t5 ORDER BY c ASC, a ASC; 3 | d1 | d1 (3 rows) +-- Check that DEFAULT expressions in SQL/JSON functions use the same collation +-- as the RETURNING type. Mismatched collations should raise an error. +CREATE DOMAIN d1 AS text COLLATE case_insensitive; +CREATE DOMAIN d2 AS text COLLATE "C"; +SELECT JSON_VALUE('{"a": "A"}', '$.a' RETURNING d1 DEFAULT ('C' COLLATE "C") COLLATE case_insensitive ON EMPTY) = 'a'; -- true + ?column? +---------- + t +(1 row) + +SELECT JSON_VALUE('{"a": "A"}', '$.a' RETURNING d1 DEFAULT 'C' ON EMPTY) = 'a'; -- true + ?column? +---------- + t +(1 row) + +SELECT JSON_VALUE('{"a": "A"}', '$.a' RETURNING d1 DEFAULT 'C'::d2 ON EMPTY) = 'a'; -- error +ERROR: the collation of DEFAULT expression conflicts with RETURNING clause +LINE 1: ...ON_VALUE('{"a": "A"}', '$.a' RETURNING d1 DEFAULT 'C'::d2 ON... + ^ +DETAIL: "C" versus "case_insensitive" +SELECT JSON_VALUE('{"a": "A"}', '$.a' RETURNING d1 DEFAULT 'C' COLLATE "C" ON EMPTY) = 'a'; -- error +ERROR: the collation of DEFAULT expression conflicts with RETURNING clause +LINE 1: ...ON_VALUE('{"a": "A"}', '$.a' RETURNING d1 DEFAULT 'C' COLLAT... + ^ +DETAIL: "C" versus "case_insensitive" +SELECT JSON_VALUE('{"a": "A"}', '$.c' RETURNING d1 DEFAULT 'A' ON EMPTY) = 'a'; -- true + ?column? +---------- + t +(1 row) + +SELECT JSON_VALUE('{"a": "A"}', '$.c' RETURNING d1 DEFAULT 'A' COLLATE case_insensitive ON EMPTY) = 'a'; -- true + ?column? +---------- + t +(1 row) + +SELECT JSON_VALUE('{"a": "A"}', '$.c' RETURNING d1 DEFAULT 'A'::d2 ON EMPTY) = 'a'; -- error +ERROR: the collation of DEFAULT expression conflicts with RETURNING clause +LINE 1: ...ON_VALUE('{"a": "A"}', '$.c' RETURNING d1 DEFAULT 'A'::d2 ON... + ^ +DETAIL: "C" versus "case_insensitive" +SELECT JSON_VALUE('{"a": "A"}', '$.c' RETURNING d1 DEFAULT 'A' COLLATE "C" ON EMPTY) = 'a'; -- error +ERROR: the collation of DEFAULT expression conflicts with RETURNING clause +LINE 1: ...ON_VALUE('{"a": "A"}', '$.c' RETURNING d1 DEFAULT 'A' COLLAT... + ^ +DETAIL: "C" versus "case_insensitive" +DROP DOMAIN d1, d2; -- cleanup RESET search_path; SET client_min_messages TO warning; diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index 98e68e972be..c743fc769cb 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -593,7 +593,7 @@ SELECT point(x,x), (SELECT f1 FROM gpolygon_tbl ORDER BY f1 <-> point(x,x) LIMIT QUERY PLAN -------------------------------------------------------------------------------------------- Function Scan on generate_series x - SubPlan 1 + SubPlan expr_1 -> Limit -> Index Scan using ggpolygonind on gpolygon_tbl Order By: (f1 <-> point((x.x)::double precision, (x.x)::double precision)) @@ -1908,11 +1908,11 @@ SELECT * FROM tenk1 EXPLAIN (COSTS OFF) SELECT * FROM tenk1 WHERE thousand = 42 AND (tenthous = 1 OR tenthous = (SELECT 1 + 2) OR tenthous = 42); - QUERY PLAN ----------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------- Index Scan using tenk1_thous_tenthous on tenk1 - Index Cond: ((thousand = 42) AND (tenthous = ANY (ARRAY[1, (InitPlan 1).col1, 42]))) - InitPlan 1 + Index Cond: ((thousand = 42) AND (tenthous = ANY (ARRAY[1, (InitPlan expr_1).col1, 42]))) + InitPlan expr_1 -> Result (4 rows) @@ -2043,8 +2043,8 @@ SELECT count(*) FROM tenk1 t1 ---------------------------------------------------------------------------- Aggregate -> Index Only Scan using tenk1_thous_tenthous on tenk1 t1 - Filter: ((thousand = 42) OR (thousand = (SubPlan 1))) - SubPlan 1 + Filter: ((thousand = 42) OR (thousand = (SubPlan expr_1))) + SubPlan expr_1 -> Limit -> Index Only Scan using tenk1_thous_tenthous on tenk1 t2 Index Cond: (thousand = (t1.tenthous + 1)) diff --git a/src/test/regress/expected/eager_aggregate.out b/src/test/regress/expected/eager_aggregate.out new file mode 100644 index 00000000000..5ac966186f7 --- /dev/null +++ b/src/test/regress/expected/eager_aggregate.out @@ -0,0 +1,1712 @@ +-- +-- EAGER AGGREGATION +-- Test we can push aggregation down below join +-- +CREATE TABLE eager_agg_t1 (a int, b int, c double precision); +CREATE TABLE eager_agg_t2 (a int, b int, c double precision); +CREATE TABLE eager_agg_t3 (a int, b int, c double precision); +INSERT INTO eager_agg_t1 SELECT i, i, i FROM generate_series(1, 1000) i; +INSERT INTO eager_agg_t2 SELECT i, i%10, i FROM generate_series(1, 1000) i; +INSERT INTO eager_agg_t3 SELECT i%10, i%10, i FROM generate_series(1, 1000) i; +ANALYZE eager_agg_t1; +ANALYZE eager_agg_t2; +ANALYZE eager_agg_t3; +-- +-- Test eager aggregation over base rel +-- +-- Perform scan of a table, aggregate the result, join it to the other table +-- and finalize the aggregation. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + QUERY PLAN +------------------------------------------------------------------ + Finalize GroupAggregate + Output: t1.a, avg(t2.c) + Group Key: t1.a + -> Sort + Output: t1.a, (PARTIAL avg(t2.c)) + Sort Key: t1.a + -> Hash Join + Output: t1.a, (PARTIAL avg(t2.c)) + Hash Cond: (t1.b = t2.b) + -> Seq Scan on public.eager_agg_t1 t1 + Output: t1.a, t1.b, t1.c + -> Hash + Output: t2.b, (PARTIAL avg(t2.c)) + -> Partial HashAggregate + Output: t2.b, PARTIAL avg(t2.c) + Group Key: t2.b + -> Seq Scan on public.eager_agg_t2 t2 + Output: t2.a, t2.b, t2.c +(18 rows) + +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + a | avg +---+----- + 1 | 496 + 2 | 497 + 3 | 498 + 4 | 499 + 5 | 500 + 6 | 501 + 7 | 502 + 8 | 503 + 9 | 504 +(9 rows) + +-- Produce results with sorting aggregation +SET enable_hashagg TO off; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + QUERY PLAN +------------------------------------------------------------------------ + Finalize GroupAggregate + Output: t1.a, avg(t2.c) + Group Key: t1.a + -> Sort + Output: t1.a, (PARTIAL avg(t2.c)) + Sort Key: t1.a + -> Hash Join + Output: t1.a, (PARTIAL avg(t2.c)) + Hash Cond: (t1.b = t2.b) + -> Seq Scan on public.eager_agg_t1 t1 + Output: t1.a, t1.b, t1.c + -> Hash + Output: t2.b, (PARTIAL avg(t2.c)) + -> Partial GroupAggregate + Output: t2.b, PARTIAL avg(t2.c) + Group Key: t2.b + -> Sort + Output: t2.c, t2.b + Sort Key: t2.b + -> Seq Scan on public.eager_agg_t2 t2 + Output: t2.c, t2.b +(21 rows) + +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + a | avg +---+----- + 1 | 496 + 2 | 497 + 3 | 498 + 4 | 499 + 5 | 500 + 6 | 501 + 7 | 502 + 8 | 503 + 9 | 504 +(9 rows) + +RESET enable_hashagg; +-- +-- Test eager aggregation over join rel +-- +-- Perform join of tables, aggregate the result, join it to the other table +-- and finalize the aggregation. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.a, avg(t2.c + t3.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b + JOIN eager_agg_t3 t3 ON t2.a = t3.a +GROUP BY t1.a ORDER BY t1.a; + QUERY PLAN +------------------------------------------------------------------------------ + Finalize GroupAggregate + Output: t1.a, avg((t2.c + t3.c)) + Group Key: t1.a + -> Sort + Output: t1.a, (PARTIAL avg((t2.c + t3.c))) + Sort Key: t1.a + -> Hash Join + Output: t1.a, (PARTIAL avg((t2.c + t3.c))) + Hash Cond: (t1.b = t2.b) + -> Seq Scan on public.eager_agg_t1 t1 + Output: t1.a, t1.b, t1.c + -> Hash + Output: t2.b, (PARTIAL avg((t2.c + t3.c))) + -> Partial HashAggregate + Output: t2.b, PARTIAL avg((t2.c + t3.c)) + Group Key: t2.b + -> Hash Join + Output: t2.c, t2.b, t3.c + Hash Cond: (t3.a = t2.a) + -> Seq Scan on public.eager_agg_t3 t3 + Output: t3.a, t3.b, t3.c + -> Hash + Output: t2.c, t2.b, t2.a + -> Seq Scan on public.eager_agg_t2 t2 + Output: t2.c, t2.b, t2.a +(25 rows) + +SELECT t1.a, avg(t2.c + t3.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b + JOIN eager_agg_t3 t3 ON t2.a = t3.a +GROUP BY t1.a ORDER BY t1.a; + a | avg +---+----- + 1 | 497 + 2 | 499 + 3 | 501 + 4 | 503 + 5 | 505 + 6 | 507 + 7 | 509 + 8 | 511 + 9 | 513 +(9 rows) + +-- Produce results with sorting aggregation +SET enable_hashagg TO off; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.a, avg(t2.c + t3.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b + JOIN eager_agg_t3 t3 ON t2.a = t3.a +GROUP BY t1.a ORDER BY t1.a; + QUERY PLAN +------------------------------------------------------------------------------------ + Finalize GroupAggregate + Output: t1.a, avg((t2.c + t3.c)) + Group Key: t1.a + -> Sort + Output: t1.a, (PARTIAL avg((t2.c + t3.c))) + Sort Key: t1.a + -> Hash Join + Output: t1.a, (PARTIAL avg((t2.c + t3.c))) + Hash Cond: (t1.b = t2.b) + -> Seq Scan on public.eager_agg_t1 t1 + Output: t1.a, t1.b, t1.c + -> Hash + Output: t2.b, (PARTIAL avg((t2.c + t3.c))) + -> Partial GroupAggregate + Output: t2.b, PARTIAL avg((t2.c + t3.c)) + Group Key: t2.b + -> Sort + Output: t2.c, t2.b, t3.c + Sort Key: t2.b + -> Hash Join + Output: t2.c, t2.b, t3.c + Hash Cond: (t3.a = t2.a) + -> Seq Scan on public.eager_agg_t3 t3 + Output: t3.a, t3.b, t3.c + -> Hash + Output: t2.c, t2.b, t2.a + -> Seq Scan on public.eager_agg_t2 t2 + Output: t2.c, t2.b, t2.a +(28 rows) + +SELECT t1.a, avg(t2.c + t3.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b + JOIN eager_agg_t3 t3 ON t2.a = t3.a +GROUP BY t1.a ORDER BY t1.a; + a | avg +---+----- + 1 | 497 + 2 | 499 + 3 | 501 + 4 | 503 + 5 | 505 + 6 | 507 + 7 | 509 + 8 | 511 + 9 | 513 +(9 rows) + +RESET enable_hashagg; +-- +-- Test that eager aggregation works for outer join +-- +-- Ensure aggregation can be pushed down to the non-nullable side +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + RIGHT JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + QUERY PLAN +------------------------------------------------------------------ + Finalize GroupAggregate + Output: t1.a, avg(t2.c) + Group Key: t1.a + -> Sort + Output: t1.a, (PARTIAL avg(t2.c)) + Sort Key: t1.a + -> Hash Right Join + Output: t1.a, (PARTIAL avg(t2.c)) + Hash Cond: (t1.b = t2.b) + -> Seq Scan on public.eager_agg_t1 t1 + Output: t1.a, t1.b, t1.c + -> Hash + Output: t2.b, (PARTIAL avg(t2.c)) + -> Partial HashAggregate + Output: t2.b, PARTIAL avg(t2.c) + Group Key: t2.b + -> Seq Scan on public.eager_agg_t2 t2 + Output: t2.a, t2.b, t2.c +(18 rows) + +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + RIGHT JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + a | avg +---+----- + 1 | 496 + 2 | 497 + 3 | 498 + 4 | 499 + 5 | 500 + 6 | 501 + 7 | 502 + 8 | 503 + 9 | 504 + | 505 +(10 rows) + +-- Ensure aggregation cannot be pushed down to the nullable side +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t2.b, avg(t2.c) + FROM eager_agg_t1 t1 + LEFT JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t2.b ORDER BY t2.b; + QUERY PLAN +------------------------------------------------------------ + Sort + Output: t2.b, (avg(t2.c)) + Sort Key: t2.b + -> HashAggregate + Output: t2.b, avg(t2.c) + Group Key: t2.b + -> Hash Right Join + Output: t2.b, t2.c + Hash Cond: (t2.b = t1.b) + -> Seq Scan on public.eager_agg_t2 t2 + Output: t2.a, t2.b, t2.c + -> Hash + Output: t1.b + -> Seq Scan on public.eager_agg_t1 t1 + Output: t1.b +(15 rows) + +SELECT t2.b, avg(t2.c) + FROM eager_agg_t1 t1 + LEFT JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t2.b ORDER BY t2.b; + b | avg +---+----- + 1 | 496 + 2 | 497 + 3 | 498 + 4 | 499 + 5 | 500 + 6 | 501 + 7 | 502 + 8 | 503 + 9 | 504 + | +(10 rows) + +-- +-- Test that eager aggregation works for parallel plans +-- +SET parallel_setup_cost=0; +SET parallel_tuple_cost=0; +SET min_parallel_table_scan_size=0; +SET max_parallel_workers_per_gather=4; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + QUERY PLAN +--------------------------------------------------------------------------------- + Finalize GroupAggregate + Output: t1.a, avg(t2.c) + Group Key: t1.a + -> Gather Merge + Output: t1.a, (PARTIAL avg(t2.c)) + Workers Planned: 2 + -> Sort + Output: t1.a, (PARTIAL avg(t2.c)) + Sort Key: t1.a + -> Parallel Hash Join + Output: t1.a, (PARTIAL avg(t2.c)) + Hash Cond: (t1.b = t2.b) + -> Parallel Seq Scan on public.eager_agg_t1 t1 + Output: t1.a, t1.b, t1.c + -> Parallel Hash + Output: t2.b, (PARTIAL avg(t2.c)) + -> Partial HashAggregate + Output: t2.b, PARTIAL avg(t2.c) + Group Key: t2.b + -> Parallel Seq Scan on public.eager_agg_t2 t2 + Output: t2.a, t2.b, t2.c +(21 rows) + +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + a | avg +---+----- + 1 | 496 + 2 | 497 + 3 | 498 + 4 | 499 + 5 | 500 + 6 | 501 + 7 | 502 + 8 | 503 + 9 | 504 +(9 rows) + +RESET parallel_setup_cost; +RESET parallel_tuple_cost; +RESET min_parallel_table_scan_size; +RESET max_parallel_workers_per_gather; +-- +-- Test eager aggregation with GEQO +-- +SET geqo = on; +SET geqo_threshold = 2; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + QUERY PLAN +------------------------------------------------------------------ + Finalize GroupAggregate + Output: t1.a, avg(t2.c) + Group Key: t1.a + -> Sort + Output: t1.a, (PARTIAL avg(t2.c)) + Sort Key: t1.a + -> Hash Join + Output: t1.a, (PARTIAL avg(t2.c)) + Hash Cond: (t1.b = t2.b) + -> Seq Scan on public.eager_agg_t1 t1 + Output: t1.a, t1.b, t1.c + -> Hash + Output: t2.b, (PARTIAL avg(t2.c)) + -> Partial HashAggregate + Output: t2.b, PARTIAL avg(t2.c) + Group Key: t2.b + -> Seq Scan on public.eager_agg_t2 t2 + Output: t2.a, t2.b, t2.c +(18 rows) + +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a ORDER BY t1.a; + a | avg +---+----- + 1 | 496 + 2 | 497 + 3 | 498 + 4 | 499 + 5 | 500 + 6 | 501 + 7 | 502 + 8 | 503 + 9 | 504 +(9 rows) + +RESET geqo; +RESET geqo_threshold; +DROP TABLE eager_agg_t1; +DROP TABLE eager_agg_t2; +DROP TABLE eager_agg_t3; +-- +-- Test eager aggregation for partitionwise join +-- +-- Enable partitionwise aggregate, which by default is disabled. +SET enable_partitionwise_aggregate TO true; +-- Enable partitionwise join, which by default is disabled. +SET enable_partitionwise_join TO true; +CREATE TABLE eager_agg_tab1(x int, y int) PARTITION BY RANGE(x); +CREATE TABLE eager_agg_tab1_p1 PARTITION OF eager_agg_tab1 FOR VALUES FROM (0) TO (5); +CREATE TABLE eager_agg_tab1_p2 PARTITION OF eager_agg_tab1 FOR VALUES FROM (5) TO (10); +CREATE TABLE eager_agg_tab1_p3 PARTITION OF eager_agg_tab1 FOR VALUES FROM (10) TO (15); +CREATE TABLE eager_agg_tab2(x int, y int) PARTITION BY RANGE(y); +CREATE TABLE eager_agg_tab2_p1 PARTITION OF eager_agg_tab2 FOR VALUES FROM (0) TO (5); +CREATE TABLE eager_agg_tab2_p2 PARTITION OF eager_agg_tab2 FOR VALUES FROM (5) TO (10); +CREATE TABLE eager_agg_tab2_p3 PARTITION OF eager_agg_tab2 FOR VALUES FROM (10) TO (15); +INSERT INTO eager_agg_tab1 SELECT i % 15, i % 10 FROM generate_series(1, 1000) i; +INSERT INTO eager_agg_tab2 SELECT i % 10, i % 15 FROM generate_series(1, 1000) i; +ANALYZE eager_agg_tab1; +ANALYZE eager_agg_tab2; +-- When GROUP BY clause matches; full aggregation is performed for each +-- partition. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.x, sum(t1.y), count(*) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab2 t2 ON t1.x = t2.y +GROUP BY t1.x ORDER BY t1.x; + QUERY PLAN +--------------------------------------------------------------------------------------- + Sort + Output: t1.x, (sum(t1.y)), (count(*)) + Sort Key: t1.x + -> Append + -> Finalize HashAggregate + Output: t1.x, sum(t1.y), count(*) + Group Key: t1.x + -> Hash Join + Output: t1.x, (PARTIAL sum(t1.y)), (PARTIAL count(*)) + Hash Cond: (t2.y = t1.x) + -> Seq Scan on public.eager_agg_tab2_p1 t2 + Output: t2.y + -> Hash + Output: t1.x, (PARTIAL sum(t1.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t1.x, PARTIAL sum(t1.y), PARTIAL count(*) + Group Key: t1.x + -> Seq Scan on public.eager_agg_tab1_p1 t1 + Output: t1.x, t1.y + -> Finalize HashAggregate + Output: t1_1.x, sum(t1_1.y), count(*) + Group Key: t1_1.x + -> Hash Join + Output: t1_1.x, (PARTIAL sum(t1_1.y)), (PARTIAL count(*)) + Hash Cond: (t2_1.y = t1_1.x) + -> Seq Scan on public.eager_agg_tab2_p2 t2_1 + Output: t2_1.y + -> Hash + Output: t1_1.x, (PARTIAL sum(t1_1.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t1_1.x, PARTIAL sum(t1_1.y), PARTIAL count(*) + Group Key: t1_1.x + -> Seq Scan on public.eager_agg_tab1_p2 t1_1 + Output: t1_1.x, t1_1.y + -> Finalize HashAggregate + Output: t1_2.x, sum(t1_2.y), count(*) + Group Key: t1_2.x + -> Hash Join + Output: t1_2.x, (PARTIAL sum(t1_2.y)), (PARTIAL count(*)) + Hash Cond: (t2_2.y = t1_2.x) + -> Seq Scan on public.eager_agg_tab2_p3 t2_2 + Output: t2_2.y + -> Hash + Output: t1_2.x, (PARTIAL sum(t1_2.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t1_2.x, PARTIAL sum(t1_2.y), PARTIAL count(*) + Group Key: t1_2.x + -> Seq Scan on public.eager_agg_tab1_p3 t1_2 + Output: t1_2.x, t1_2.y +(49 rows) + +SELECT t1.x, sum(t1.y), count(*) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab2 t2 ON t1.x = t2.y +GROUP BY t1.x ORDER BY t1.x; + x | sum | count +----+-------+------- + 0 | 10890 | 4356 + 1 | 15544 | 4489 + 2 | 20033 | 4489 + 3 | 24522 | 4489 + 4 | 29011 | 4489 + 5 | 11390 | 4489 + 6 | 15879 | 4489 + 7 | 20368 | 4489 + 8 | 24857 | 4489 + 9 | 29346 | 4489 + 10 | 11055 | 4489 + 11 | 15246 | 4356 + 12 | 19602 | 4356 + 13 | 23958 | 4356 + 14 | 28314 | 4356 +(15 rows) + +-- GROUP BY having other matching key +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t2.y, sum(t1.y), count(*) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab2 t2 ON t1.x = t2.y +GROUP BY t2.y ORDER BY t2.y; + QUERY PLAN +--------------------------------------------------------------------------------------- + Sort + Output: t2.y, (sum(t1.y)), (count(*)) + Sort Key: t2.y + -> Append + -> Finalize HashAggregate + Output: t2.y, sum(t1.y), count(*) + Group Key: t2.y + -> Hash Join + Output: t2.y, (PARTIAL sum(t1.y)), (PARTIAL count(*)) + Hash Cond: (t2.y = t1.x) + -> Seq Scan on public.eager_agg_tab2_p1 t2 + Output: t2.y + -> Hash + Output: t1.x, (PARTIAL sum(t1.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t1.x, PARTIAL sum(t1.y), PARTIAL count(*) + Group Key: t1.x + -> Seq Scan on public.eager_agg_tab1_p1 t1 + Output: t1.y, t1.x + -> Finalize HashAggregate + Output: t2_1.y, sum(t1_1.y), count(*) + Group Key: t2_1.y + -> Hash Join + Output: t2_1.y, (PARTIAL sum(t1_1.y)), (PARTIAL count(*)) + Hash Cond: (t2_1.y = t1_1.x) + -> Seq Scan on public.eager_agg_tab2_p2 t2_1 + Output: t2_1.y + -> Hash + Output: t1_1.x, (PARTIAL sum(t1_1.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t1_1.x, PARTIAL sum(t1_1.y), PARTIAL count(*) + Group Key: t1_1.x + -> Seq Scan on public.eager_agg_tab1_p2 t1_1 + Output: t1_1.y, t1_1.x + -> Finalize HashAggregate + Output: t2_2.y, sum(t1_2.y), count(*) + Group Key: t2_2.y + -> Hash Join + Output: t2_2.y, (PARTIAL sum(t1_2.y)), (PARTIAL count(*)) + Hash Cond: (t2_2.y = t1_2.x) + -> Seq Scan on public.eager_agg_tab2_p3 t2_2 + Output: t2_2.y + -> Hash + Output: t1_2.x, (PARTIAL sum(t1_2.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t1_2.x, PARTIAL sum(t1_2.y), PARTIAL count(*) + Group Key: t1_2.x + -> Seq Scan on public.eager_agg_tab1_p3 t1_2 + Output: t1_2.y, t1_2.x +(49 rows) + +SELECT t2.y, sum(t1.y), count(*) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab2 t2 ON t1.x = t2.y +GROUP BY t2.y ORDER BY t2.y; + y | sum | count +----+-------+------- + 0 | 10890 | 4356 + 1 | 15544 | 4489 + 2 | 20033 | 4489 + 3 | 24522 | 4489 + 4 | 29011 | 4489 + 5 | 11390 | 4489 + 6 | 15879 | 4489 + 7 | 20368 | 4489 + 8 | 24857 | 4489 + 9 | 29346 | 4489 + 10 | 11055 | 4489 + 11 | 15246 | 4356 + 12 | 19602 | 4356 + 13 | 23958 | 4356 + 14 | 28314 | 4356 +(15 rows) + +-- When GROUP BY clause does not match; partial aggregation is performed for +-- each partition. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t2.x, sum(t1.x), count(*) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab2 t2 ON t1.x = t2.y +GROUP BY t2.x HAVING avg(t1.x) > 5 ORDER BY t2.x; + QUERY PLAN +------------------------------------------------------------------------------------------------------------ + Sort + Output: t2.x, (sum(t1.x)), (count(*)) + Sort Key: t2.x + -> Finalize HashAggregate + Output: t2.x, sum(t1.x), count(*) + Group Key: t2.x + Filter: (avg(t1.x) > '5'::numeric) + -> Append + -> Hash Join + Output: t2.x, (PARTIAL sum(t1.x)), (PARTIAL count(*)), (PARTIAL avg(t1.x)) + Hash Cond: (t2.y = t1.x) + -> Seq Scan on public.eager_agg_tab2_p1 t2 + Output: t2.x, t2.y + -> Hash + Output: t1.x, (PARTIAL sum(t1.x)), (PARTIAL count(*)), (PARTIAL avg(t1.x)) + -> Partial HashAggregate + Output: t1.x, PARTIAL sum(t1.x), PARTIAL count(*), PARTIAL avg(t1.x) + Group Key: t1.x + -> Seq Scan on public.eager_agg_tab1_p1 t1 + Output: t1.x + -> Hash Join + Output: t2_1.x, (PARTIAL sum(t1_1.x)), (PARTIAL count(*)), (PARTIAL avg(t1_1.x)) + Hash Cond: (t2_1.y = t1_1.x) + -> Seq Scan on public.eager_agg_tab2_p2 t2_1 + Output: t2_1.x, t2_1.y + -> Hash + Output: t1_1.x, (PARTIAL sum(t1_1.x)), (PARTIAL count(*)), (PARTIAL avg(t1_1.x)) + -> Partial HashAggregate + Output: t1_1.x, PARTIAL sum(t1_1.x), PARTIAL count(*), PARTIAL avg(t1_1.x) + Group Key: t1_1.x + -> Seq Scan on public.eager_agg_tab1_p2 t1_1 + Output: t1_1.x + -> Hash Join + Output: t2_2.x, (PARTIAL sum(t1_2.x)), (PARTIAL count(*)), (PARTIAL avg(t1_2.x)) + Hash Cond: (t2_2.y = t1_2.x) + -> Seq Scan on public.eager_agg_tab2_p3 t2_2 + Output: t2_2.x, t2_2.y + -> Hash + Output: t1_2.x, (PARTIAL sum(t1_2.x)), (PARTIAL count(*)), (PARTIAL avg(t1_2.x)) + -> Partial HashAggregate + Output: t1_2.x, PARTIAL sum(t1_2.x), PARTIAL count(*), PARTIAL avg(t1_2.x) + Group Key: t1_2.x + -> Seq Scan on public.eager_agg_tab1_p3 t1_2 + Output: t1_2.x +(44 rows) + +SELECT t2.x, sum(t1.x), count(*) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab2 t2 ON t1.x = t2.y +GROUP BY t2.x HAVING avg(t1.x) > 5 ORDER BY t2.x; + x | sum | count +---+-------+------- + 0 | 33835 | 6667 + 1 | 39502 | 6667 + 2 | 46169 | 6667 + 3 | 52836 | 6667 + 4 | 59503 | 6667 + 5 | 33500 | 6667 + 6 | 39837 | 6667 + 7 | 46504 | 6667 + 8 | 53171 | 6667 + 9 | 59838 | 6667 +(10 rows) + +-- Check with eager aggregation over join rel +-- full aggregation +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.x, sum(t2.y + t3.y) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab1 t2 ON t1.x = t2.x + JOIN eager_agg_tab1 t3 ON t2.x = t3.x +GROUP BY t1.x ORDER BY t1.x; + QUERY PLAN +------------------------------------------------------------------------------------------- + Sort + Output: t1.x, (sum((t2.y + t3.y))) + Sort Key: t1.x + -> Append + -> Finalize HashAggregate + Output: t1.x, sum((t2.y + t3.y)) + Group Key: t1.x + -> Hash Join + Output: t1.x, (PARTIAL sum((t2.y + t3.y))) + Hash Cond: (t1.x = t2.x) + -> Seq Scan on public.eager_agg_tab1_p1 t1 + Output: t1.x + -> Hash + Output: t2.x, t3.x, (PARTIAL sum((t2.y + t3.y))) + -> Partial HashAggregate + Output: t2.x, t3.x, PARTIAL sum((t2.y + t3.y)) + Group Key: t2.x + -> Hash Join + Output: t2.y, t2.x, t3.y, t3.x + Hash Cond: (t2.x = t3.x) + -> Seq Scan on public.eager_agg_tab1_p1 t2 + Output: t2.y, t2.x + -> Hash + Output: t3.y, t3.x + -> Seq Scan on public.eager_agg_tab1_p1 t3 + Output: t3.y, t3.x + -> Finalize HashAggregate + Output: t1_1.x, sum((t2_1.y + t3_1.y)) + Group Key: t1_1.x + -> Hash Join + Output: t1_1.x, (PARTIAL sum((t2_1.y + t3_1.y))) + Hash Cond: (t1_1.x = t2_1.x) + -> Seq Scan on public.eager_agg_tab1_p2 t1_1 + Output: t1_1.x + -> Hash + Output: t2_1.x, t3_1.x, (PARTIAL sum((t2_1.y + t3_1.y))) + -> Partial HashAggregate + Output: t2_1.x, t3_1.x, PARTIAL sum((t2_1.y + t3_1.y)) + Group Key: t2_1.x + -> Hash Join + Output: t2_1.y, t2_1.x, t3_1.y, t3_1.x + Hash Cond: (t2_1.x = t3_1.x) + -> Seq Scan on public.eager_agg_tab1_p2 t2_1 + Output: t2_1.y, t2_1.x + -> Hash + Output: t3_1.y, t3_1.x + -> Seq Scan on public.eager_agg_tab1_p2 t3_1 + Output: t3_1.y, t3_1.x + -> Finalize HashAggregate + Output: t1_2.x, sum((t2_2.y + t3_2.y)) + Group Key: t1_2.x + -> Hash Join + Output: t1_2.x, (PARTIAL sum((t2_2.y + t3_2.y))) + Hash Cond: (t1_2.x = t2_2.x) + -> Seq Scan on public.eager_agg_tab1_p3 t1_2 + Output: t1_2.x + -> Hash + Output: t2_2.x, t3_2.x, (PARTIAL sum((t2_2.y + t3_2.y))) + -> Partial HashAggregate + Output: t2_2.x, t3_2.x, PARTIAL sum((t2_2.y + t3_2.y)) + Group Key: t2_2.x + -> Hash Join + Output: t2_2.y, t2_2.x, t3_2.y, t3_2.x + Hash Cond: (t2_2.x = t3_2.x) + -> Seq Scan on public.eager_agg_tab1_p3 t2_2 + Output: t2_2.y, t2_2.x + -> Hash + Output: t3_2.y, t3_2.x + -> Seq Scan on public.eager_agg_tab1_p3 t3_2 + Output: t3_2.y, t3_2.x +(70 rows) + +SELECT t1.x, sum(t2.y + t3.y) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab1 t2 ON t1.x = t2.x + JOIN eager_agg_tab1 t3 ON t2.x = t3.x +GROUP BY t1.x ORDER BY t1.x; + x | sum +----+--------- + 0 | 1437480 + 1 | 2082896 + 2 | 2684422 + 3 | 3285948 + 4 | 3887474 + 5 | 1526260 + 6 | 2127786 + 7 | 2729312 + 8 | 3330838 + 9 | 3932364 + 10 | 1481370 + 11 | 2012472 + 12 | 2587464 + 13 | 3162456 + 14 | 3737448 +(15 rows) + +-- partial aggregation +SET enable_hashagg TO off; +SET max_parallel_workers_per_gather TO 0; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t3.y, sum(t2.y + t3.y) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab1 t2 ON t1.x = t2.x + JOIN eager_agg_tab1 t3 ON t2.x = t3.x +GROUP BY t3.y ORDER BY t3.y; + QUERY PLAN +------------------------------------------------------------------------------------------- + Finalize GroupAggregate + Output: t3.y, sum((t2.y + t3.y)) + Group Key: t3.y + -> Sort + Output: t3.y, (PARTIAL sum((t2.y + t3.y))) + Sort Key: t3.y + -> Append + -> Hash Join + Output: t3.y, (PARTIAL sum((t2.y + t3.y))) + Hash Cond: (t2.x = t1.x) + -> Partial GroupAggregate + Output: t2.x, t3.y, t3.x, PARTIAL sum((t2.y + t3.y)) + Group Key: t2.x, t3.y, t3.x + -> Incremental Sort + Output: t2.y, t2.x, t3.y, t3.x + Sort Key: t2.x, t3.y + Presorted Key: t2.x + -> Merge Join + Output: t2.y, t2.x, t3.y, t3.x + Merge Cond: (t2.x = t3.x) + -> Sort + Output: t2.y, t2.x + Sort Key: t2.x + -> Seq Scan on public.eager_agg_tab1_p1 t2 + Output: t2.y, t2.x + -> Sort + Output: t3.y, t3.x + Sort Key: t3.x + -> Seq Scan on public.eager_agg_tab1_p1 t3 + Output: t3.y, t3.x + -> Hash + Output: t1.x + -> Seq Scan on public.eager_agg_tab1_p1 t1 + Output: t1.x + -> Hash Join + Output: t3_1.y, (PARTIAL sum((t2_1.y + t3_1.y))) + Hash Cond: (t2_1.x = t1_1.x) + -> Partial GroupAggregate + Output: t2_1.x, t3_1.y, t3_1.x, PARTIAL sum((t2_1.y + t3_1.y)) + Group Key: t2_1.x, t3_1.y, t3_1.x + -> Incremental Sort + Output: t2_1.y, t2_1.x, t3_1.y, t3_1.x + Sort Key: t2_1.x, t3_1.y + Presorted Key: t2_1.x + -> Merge Join + Output: t2_1.y, t2_1.x, t3_1.y, t3_1.x + Merge Cond: (t2_1.x = t3_1.x) + -> Sort + Output: t2_1.y, t2_1.x + Sort Key: t2_1.x + -> Seq Scan on public.eager_agg_tab1_p2 t2_1 + Output: t2_1.y, t2_1.x + -> Sort + Output: t3_1.y, t3_1.x + Sort Key: t3_1.x + -> Seq Scan on public.eager_agg_tab1_p2 t3_1 + Output: t3_1.y, t3_1.x + -> Hash + Output: t1_1.x + -> Seq Scan on public.eager_agg_tab1_p2 t1_1 + Output: t1_1.x + -> Hash Join + Output: t3_2.y, (PARTIAL sum((t2_2.y + t3_2.y))) + Hash Cond: (t2_2.x = t1_2.x) + -> Partial GroupAggregate + Output: t2_2.x, t3_2.y, t3_2.x, PARTIAL sum((t2_2.y + t3_2.y)) + Group Key: t2_2.x, t3_2.y, t3_2.x + -> Incremental Sort + Output: t2_2.y, t2_2.x, t3_2.y, t3_2.x + Sort Key: t2_2.x, t3_2.y + Presorted Key: t2_2.x + -> Merge Join + Output: t2_2.y, t2_2.x, t3_2.y, t3_2.x + Merge Cond: (t2_2.x = t3_2.x) + -> Sort + Output: t2_2.y, t2_2.x + Sort Key: t2_2.x + -> Seq Scan on public.eager_agg_tab1_p3 t2_2 + Output: t2_2.y, t2_2.x + -> Sort + Output: t3_2.y, t3_2.x + Sort Key: t3_2.x + -> Seq Scan on public.eager_agg_tab1_p3 t3_2 + Output: t3_2.y, t3_2.x + -> Hash + Output: t1_2.x + -> Seq Scan on public.eager_agg_tab1_p3 t1_2 + Output: t1_2.x +(88 rows) + +SELECT t3.y, sum(t2.y + t3.y) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab1 t2 ON t1.x = t2.x + JOIN eager_agg_tab1 t3 ON t2.x = t3.x +GROUP BY t3.y ORDER BY t3.y; + y | sum +---+--------- + 0 | 1111110 + 1 | 2000132 + 2 | 2889154 + 3 | 3778176 + 4 | 4667198 + 5 | 3334000 + 6 | 4223022 + 7 | 5112044 + 8 | 6001066 + 9 | 6890088 +(10 rows) + +RESET enable_hashagg; +RESET max_parallel_workers_per_gather; +-- try that with GEQO too +SET geqo = on; +SET geqo_threshold = 2; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.x, sum(t1.y), count(*) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab2 t2 ON t1.x = t2.y +GROUP BY t1.x ORDER BY t1.x; + QUERY PLAN +--------------------------------------------------------------------------------------- + Sort + Output: t1.x, (sum(t1.y)), (count(*)) + Sort Key: t1.x + -> Append + -> Finalize HashAggregate + Output: t1.x, sum(t1.y), count(*) + Group Key: t1.x + -> Hash Join + Output: t1.x, (PARTIAL sum(t1.y)), (PARTIAL count(*)) + Hash Cond: (t2.y = t1.x) + -> Seq Scan on public.eager_agg_tab2_p1 t2 + Output: t2.y + -> Hash + Output: t1.x, (PARTIAL sum(t1.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t1.x, PARTIAL sum(t1.y), PARTIAL count(*) + Group Key: t1.x + -> Seq Scan on public.eager_agg_tab1_p1 t1 + Output: t1.x, t1.y + -> Finalize HashAggregate + Output: t1_1.x, sum(t1_1.y), count(*) + Group Key: t1_1.x + -> Hash Join + Output: t1_1.x, (PARTIAL sum(t1_1.y)), (PARTIAL count(*)) + Hash Cond: (t2_1.y = t1_1.x) + -> Seq Scan on public.eager_agg_tab2_p2 t2_1 + Output: t2_1.y + -> Hash + Output: t1_1.x, (PARTIAL sum(t1_1.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t1_1.x, PARTIAL sum(t1_1.y), PARTIAL count(*) + Group Key: t1_1.x + -> Seq Scan on public.eager_agg_tab1_p2 t1_1 + Output: t1_1.x, t1_1.y + -> Finalize HashAggregate + Output: t1_2.x, sum(t1_2.y), count(*) + Group Key: t1_2.x + -> Hash Join + Output: t1_2.x, (PARTIAL sum(t1_2.y)), (PARTIAL count(*)) + Hash Cond: (t2_2.y = t1_2.x) + -> Seq Scan on public.eager_agg_tab2_p3 t2_2 + Output: t2_2.y + -> Hash + Output: t1_2.x, (PARTIAL sum(t1_2.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t1_2.x, PARTIAL sum(t1_2.y), PARTIAL count(*) + Group Key: t1_2.x + -> Seq Scan on public.eager_agg_tab1_p3 t1_2 + Output: t1_2.x, t1_2.y +(49 rows) + +SELECT t1.x, sum(t1.y), count(*) + FROM eager_agg_tab1 t1 + JOIN eager_agg_tab2 t2 ON t1.x = t2.y +GROUP BY t1.x ORDER BY t1.x; + x | sum | count +----+-------+------- + 0 | 10890 | 4356 + 1 | 15544 | 4489 + 2 | 20033 | 4489 + 3 | 24522 | 4489 + 4 | 29011 | 4489 + 5 | 11390 | 4489 + 6 | 15879 | 4489 + 7 | 20368 | 4489 + 8 | 24857 | 4489 + 9 | 29346 | 4489 + 10 | 11055 | 4489 + 11 | 15246 | 4356 + 12 | 19602 | 4356 + 13 | 23958 | 4356 + 14 | 28314 | 4356 +(15 rows) + +RESET geqo; +RESET geqo_threshold; +DROP TABLE eager_agg_tab1; +DROP TABLE eager_agg_tab2; +-- +-- Test with multi-level partitioning scheme +-- +CREATE TABLE eager_agg_tab_ml(x int, y int) PARTITION BY RANGE(x); +CREATE TABLE eager_agg_tab_ml_p1 PARTITION OF eager_agg_tab_ml FOR VALUES FROM (0) TO (10); +CREATE TABLE eager_agg_tab_ml_p2 PARTITION OF eager_agg_tab_ml FOR VALUES FROM (10) TO (20) PARTITION BY RANGE(x); +CREATE TABLE eager_agg_tab_ml_p2_s1 PARTITION OF eager_agg_tab_ml_p2 FOR VALUES FROM (10) TO (15); +CREATE TABLE eager_agg_tab_ml_p2_s2 PARTITION OF eager_agg_tab_ml_p2 FOR VALUES FROM (15) TO (20); +CREATE TABLE eager_agg_tab_ml_p3 PARTITION OF eager_agg_tab_ml FOR VALUES FROM (20) TO (30) PARTITION BY RANGE(x); +CREATE TABLE eager_agg_tab_ml_p3_s1 PARTITION OF eager_agg_tab_ml_p3 FOR VALUES FROM (20) TO (25); +CREATE TABLE eager_agg_tab_ml_p3_s2 PARTITION OF eager_agg_tab_ml_p3 FOR VALUES FROM (25) TO (30); +INSERT INTO eager_agg_tab_ml SELECT i % 30, i % 30 FROM generate_series(1, 1000) i; +ANALYZE eager_agg_tab_ml; +-- When GROUP BY clause matches; full aggregation is performed for each +-- partition. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.x, sum(t2.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x +GROUP BY t1.x ORDER BY t1.x; + QUERY PLAN +--------------------------------------------------------------------------------------- + Sort + Output: t1.x, (sum(t2.y)), (count(*)) + Sort Key: t1.x + -> Append + -> Finalize HashAggregate + Output: t1.x, sum(t2.y), count(*) + Group Key: t1.x + -> Hash Join + Output: t1.x, (PARTIAL sum(t2.y)), (PARTIAL count(*)) + Hash Cond: (t1.x = t2.x) + -> Seq Scan on public.eager_agg_tab_ml_p1 t1 + Output: t1.x + -> Hash + Output: t2.x, (PARTIAL sum(t2.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2.x, PARTIAL sum(t2.y), PARTIAL count(*) + Group Key: t2.x + -> Seq Scan on public.eager_agg_tab_ml_p1 t2 + Output: t2.y, t2.x + -> Finalize HashAggregate + Output: t1_1.x, sum(t2_1.y), count(*) + Group Key: t1_1.x + -> Hash Join + Output: t1_1.x, (PARTIAL sum(t2_1.y)), (PARTIAL count(*)) + Hash Cond: (t1_1.x = t2_1.x) + -> Seq Scan on public.eager_agg_tab_ml_p2_s1 t1_1 + Output: t1_1.x + -> Hash + Output: t2_1.x, (PARTIAL sum(t2_1.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_1.x, PARTIAL sum(t2_1.y), PARTIAL count(*) + Group Key: t2_1.x + -> Seq Scan on public.eager_agg_tab_ml_p2_s1 t2_1 + Output: t2_1.y, t2_1.x + -> Finalize HashAggregate + Output: t1_2.x, sum(t2_2.y), count(*) + Group Key: t1_2.x + -> Hash Join + Output: t1_2.x, (PARTIAL sum(t2_2.y)), (PARTIAL count(*)) + Hash Cond: (t1_2.x = t2_2.x) + -> Seq Scan on public.eager_agg_tab_ml_p2_s2 t1_2 + Output: t1_2.x + -> Hash + Output: t2_2.x, (PARTIAL sum(t2_2.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_2.x, PARTIAL sum(t2_2.y), PARTIAL count(*) + Group Key: t2_2.x + -> Seq Scan on public.eager_agg_tab_ml_p2_s2 t2_2 + Output: t2_2.y, t2_2.x + -> Finalize HashAggregate + Output: t1_3.x, sum(t2_3.y), count(*) + Group Key: t1_3.x + -> Hash Join + Output: t1_3.x, (PARTIAL sum(t2_3.y)), (PARTIAL count(*)) + Hash Cond: (t1_3.x = t2_3.x) + -> Seq Scan on public.eager_agg_tab_ml_p3_s1 t1_3 + Output: t1_3.x + -> Hash + Output: t2_3.x, (PARTIAL sum(t2_3.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_3.x, PARTIAL sum(t2_3.y), PARTIAL count(*) + Group Key: t2_3.x + -> Seq Scan on public.eager_agg_tab_ml_p3_s1 t2_3 + Output: t2_3.y, t2_3.x + -> Finalize HashAggregate + Output: t1_4.x, sum(t2_4.y), count(*) + Group Key: t1_4.x + -> Hash Join + Output: t1_4.x, (PARTIAL sum(t2_4.y)), (PARTIAL count(*)) + Hash Cond: (t1_4.x = t2_4.x) + -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t1_4 + Output: t1_4.x + -> Hash + Output: t2_4.x, (PARTIAL sum(t2_4.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_4.x, PARTIAL sum(t2_4.y), PARTIAL count(*) + Group Key: t2_4.x + -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t2_4 + Output: t2_4.y, t2_4.x +(79 rows) + +SELECT t1.x, sum(t2.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x +GROUP BY t1.x ORDER BY t1.x; + x | sum | count +----+-------+------- + 0 | 0 | 1089 + 1 | 1156 | 1156 + 2 | 2312 | 1156 + 3 | 3468 | 1156 + 4 | 4624 | 1156 + 5 | 5780 | 1156 + 6 | 6936 | 1156 + 7 | 8092 | 1156 + 8 | 9248 | 1156 + 9 | 10404 | 1156 + 10 | 11560 | 1156 + 11 | 11979 | 1089 + 12 | 13068 | 1089 + 13 | 14157 | 1089 + 14 | 15246 | 1089 + 15 | 16335 | 1089 + 16 | 17424 | 1089 + 17 | 18513 | 1089 + 18 | 19602 | 1089 + 19 | 20691 | 1089 + 20 | 21780 | 1089 + 21 | 22869 | 1089 + 22 | 23958 | 1089 + 23 | 25047 | 1089 + 24 | 26136 | 1089 + 25 | 27225 | 1089 + 26 | 28314 | 1089 + 27 | 29403 | 1089 + 28 | 30492 | 1089 + 29 | 31581 | 1089 +(30 rows) + +-- When GROUP BY clause does not match; partial aggregation is performed for +-- each partition. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.y, sum(t2.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x +GROUP BY t1.y ORDER BY t1.y; + QUERY PLAN +--------------------------------------------------------------------------------------- + Sort + Output: t1.y, (sum(t2.y)), (count(*)) + Sort Key: t1.y + -> Finalize HashAggregate + Output: t1.y, sum(t2.y), count(*) + Group Key: t1.y + -> Append + -> Hash Join + Output: t1.y, (PARTIAL sum(t2.y)), (PARTIAL count(*)) + Hash Cond: (t1.x = t2.x) + -> Seq Scan on public.eager_agg_tab_ml_p1 t1 + Output: t1.y, t1.x + -> Hash + Output: t2.x, (PARTIAL sum(t2.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2.x, PARTIAL sum(t2.y), PARTIAL count(*) + Group Key: t2.x + -> Seq Scan on public.eager_agg_tab_ml_p1 t2 + Output: t2.y, t2.x + -> Hash Join + Output: t1_1.y, (PARTIAL sum(t2_1.y)), (PARTIAL count(*)) + Hash Cond: (t1_1.x = t2_1.x) + -> Seq Scan on public.eager_agg_tab_ml_p2_s1 t1_1 + Output: t1_1.y, t1_1.x + -> Hash + Output: t2_1.x, (PARTIAL sum(t2_1.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_1.x, PARTIAL sum(t2_1.y), PARTIAL count(*) + Group Key: t2_1.x + -> Seq Scan on public.eager_agg_tab_ml_p2_s1 t2_1 + Output: t2_1.y, t2_1.x + -> Hash Join + Output: t1_2.y, (PARTIAL sum(t2_2.y)), (PARTIAL count(*)) + Hash Cond: (t1_2.x = t2_2.x) + -> Seq Scan on public.eager_agg_tab_ml_p2_s2 t1_2 + Output: t1_2.y, t1_2.x + -> Hash + Output: t2_2.x, (PARTIAL sum(t2_2.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_2.x, PARTIAL sum(t2_2.y), PARTIAL count(*) + Group Key: t2_2.x + -> Seq Scan on public.eager_agg_tab_ml_p2_s2 t2_2 + Output: t2_2.y, t2_2.x + -> Hash Join + Output: t1_3.y, (PARTIAL sum(t2_3.y)), (PARTIAL count(*)) + Hash Cond: (t1_3.x = t2_3.x) + -> Seq Scan on public.eager_agg_tab_ml_p3_s1 t1_3 + Output: t1_3.y, t1_3.x + -> Hash + Output: t2_3.x, (PARTIAL sum(t2_3.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_3.x, PARTIAL sum(t2_3.y), PARTIAL count(*) + Group Key: t2_3.x + -> Seq Scan on public.eager_agg_tab_ml_p3_s1 t2_3 + Output: t2_3.y, t2_3.x + -> Hash Join + Output: t1_4.y, (PARTIAL sum(t2_4.y)), (PARTIAL count(*)) + Hash Cond: (t1_4.x = t2_4.x) + -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t1_4 + Output: t1_4.y, t1_4.x + -> Hash + Output: t2_4.x, (PARTIAL sum(t2_4.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_4.x, PARTIAL sum(t2_4.y), PARTIAL count(*) + Group Key: t2_4.x + -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t2_4 + Output: t2_4.y, t2_4.x +(67 rows) + +SELECT t1.y, sum(t2.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x +GROUP BY t1.y ORDER BY t1.y; + y | sum | count +----+-------+------- + 0 | 0 | 1089 + 1 | 1156 | 1156 + 2 | 2312 | 1156 + 3 | 3468 | 1156 + 4 | 4624 | 1156 + 5 | 5780 | 1156 + 6 | 6936 | 1156 + 7 | 8092 | 1156 + 8 | 9248 | 1156 + 9 | 10404 | 1156 + 10 | 11560 | 1156 + 11 | 11979 | 1089 + 12 | 13068 | 1089 + 13 | 14157 | 1089 + 14 | 15246 | 1089 + 15 | 16335 | 1089 + 16 | 17424 | 1089 + 17 | 18513 | 1089 + 18 | 19602 | 1089 + 19 | 20691 | 1089 + 20 | 21780 | 1089 + 21 | 22869 | 1089 + 22 | 23958 | 1089 + 23 | 25047 | 1089 + 24 | 26136 | 1089 + 25 | 27225 | 1089 + 26 | 28314 | 1089 + 27 | 29403 | 1089 + 28 | 30492 | 1089 + 29 | 31581 | 1089 +(30 rows) + +-- Check with eager aggregation over join rel +-- full aggregation +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.x, sum(t2.y + t3.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x + JOIN eager_agg_tab_ml t3 ON t2.x = t3.x +GROUP BY t1.x ORDER BY t1.x; + QUERY PLAN +---------------------------------------------------------------------------------------------------------- + Sort + Output: t1.x, (sum((t2.y + t3.y))), (count(*)) + Sort Key: t1.x + -> Append + -> Finalize HashAggregate + Output: t1.x, sum((t2.y + t3.y)), count(*) + Group Key: t1.x + -> Hash Join + Output: t1.x, (PARTIAL sum((t2.y + t3.y))), (PARTIAL count(*)) + Hash Cond: (t1.x = t2.x) + -> Seq Scan on public.eager_agg_tab_ml_p1 t1 + Output: t1.x + -> Hash + Output: t2.x, t3.x, (PARTIAL sum((t2.y + t3.y))), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2.x, t3.x, PARTIAL sum((t2.y + t3.y)), PARTIAL count(*) + Group Key: t2.x + -> Hash Join + Output: t2.y, t2.x, t3.y, t3.x + Hash Cond: (t2.x = t3.x) + -> Seq Scan on public.eager_agg_tab_ml_p1 t2 + Output: t2.y, t2.x + -> Hash + Output: t3.y, t3.x + -> Seq Scan on public.eager_agg_tab_ml_p1 t3 + Output: t3.y, t3.x + -> Finalize HashAggregate + Output: t1_1.x, sum((t2_1.y + t3_1.y)), count(*) + Group Key: t1_1.x + -> Hash Join + Output: t1_1.x, (PARTIAL sum((t2_1.y + t3_1.y))), (PARTIAL count(*)) + Hash Cond: (t1_1.x = t2_1.x) + -> Seq Scan on public.eager_agg_tab_ml_p2_s1 t1_1 + Output: t1_1.x + -> Hash + Output: t2_1.x, t3_1.x, (PARTIAL sum((t2_1.y + t3_1.y))), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_1.x, t3_1.x, PARTIAL sum((t2_1.y + t3_1.y)), PARTIAL count(*) + Group Key: t2_1.x + -> Hash Join + Output: t2_1.y, t2_1.x, t3_1.y, t3_1.x + Hash Cond: (t2_1.x = t3_1.x) + -> Seq Scan on public.eager_agg_tab_ml_p2_s1 t2_1 + Output: t2_1.y, t2_1.x + -> Hash + Output: t3_1.y, t3_1.x + -> Seq Scan on public.eager_agg_tab_ml_p2_s1 t3_1 + Output: t3_1.y, t3_1.x + -> Finalize HashAggregate + Output: t1_2.x, sum((t2_2.y + t3_2.y)), count(*) + Group Key: t1_2.x + -> Hash Join + Output: t1_2.x, (PARTIAL sum((t2_2.y + t3_2.y))), (PARTIAL count(*)) + Hash Cond: (t1_2.x = t2_2.x) + -> Seq Scan on public.eager_agg_tab_ml_p2_s2 t1_2 + Output: t1_2.x + -> Hash + Output: t2_2.x, t3_2.x, (PARTIAL sum((t2_2.y + t3_2.y))), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_2.x, t3_2.x, PARTIAL sum((t2_2.y + t3_2.y)), PARTIAL count(*) + Group Key: t2_2.x + -> Hash Join + Output: t2_2.y, t2_2.x, t3_2.y, t3_2.x + Hash Cond: (t2_2.x = t3_2.x) + -> Seq Scan on public.eager_agg_tab_ml_p2_s2 t2_2 + Output: t2_2.y, t2_2.x + -> Hash + Output: t3_2.y, t3_2.x + -> Seq Scan on public.eager_agg_tab_ml_p2_s2 t3_2 + Output: t3_2.y, t3_2.x + -> Finalize HashAggregate + Output: t1_3.x, sum((t2_3.y + t3_3.y)), count(*) + Group Key: t1_3.x + -> Hash Join + Output: t1_3.x, (PARTIAL sum((t2_3.y + t3_3.y))), (PARTIAL count(*)) + Hash Cond: (t1_3.x = t2_3.x) + -> Seq Scan on public.eager_agg_tab_ml_p3_s1 t1_3 + Output: t1_3.x + -> Hash + Output: t2_3.x, t3_3.x, (PARTIAL sum((t2_3.y + t3_3.y))), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_3.x, t3_3.x, PARTIAL sum((t2_3.y + t3_3.y)), PARTIAL count(*) + Group Key: t2_3.x + -> Hash Join + Output: t2_3.y, t2_3.x, t3_3.y, t3_3.x + Hash Cond: (t2_3.x = t3_3.x) + -> Seq Scan on public.eager_agg_tab_ml_p3_s1 t2_3 + Output: t2_3.y, t2_3.x + -> Hash + Output: t3_3.y, t3_3.x + -> Seq Scan on public.eager_agg_tab_ml_p3_s1 t3_3 + Output: t3_3.y, t3_3.x + -> Finalize HashAggregate + Output: t1_4.x, sum((t2_4.y + t3_4.y)), count(*) + Group Key: t1_4.x + -> Hash Join + Output: t1_4.x, (PARTIAL sum((t2_4.y + t3_4.y))), (PARTIAL count(*)) + Hash Cond: (t1_4.x = t2_4.x) + -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t1_4 + Output: t1_4.x + -> Hash + Output: t2_4.x, t3_4.x, (PARTIAL sum((t2_4.y + t3_4.y))), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_4.x, t3_4.x, PARTIAL sum((t2_4.y + t3_4.y)), PARTIAL count(*) + Group Key: t2_4.x + -> Hash Join + Output: t2_4.y, t2_4.x, t3_4.y, t3_4.x + Hash Cond: (t2_4.x = t3_4.x) + -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t2_4 + Output: t2_4.y, t2_4.x + -> Hash + Output: t3_4.y, t3_4.x + -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t3_4 + Output: t3_4.y, t3_4.x +(114 rows) + +SELECT t1.x, sum(t2.y + t3.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x + JOIN eager_agg_tab_ml t3 ON t2.x = t3.x +GROUP BY t1.x ORDER BY t1.x; + x | sum | count +----+---------+------- + 0 | 0 | 35937 + 1 | 78608 | 39304 + 2 | 157216 | 39304 + 3 | 235824 | 39304 + 4 | 314432 | 39304 + 5 | 393040 | 39304 + 6 | 471648 | 39304 + 7 | 550256 | 39304 + 8 | 628864 | 39304 + 9 | 707472 | 39304 + 10 | 786080 | 39304 + 11 | 790614 | 35937 + 12 | 862488 | 35937 + 13 | 934362 | 35937 + 14 | 1006236 | 35937 + 15 | 1078110 | 35937 + 16 | 1149984 | 35937 + 17 | 1221858 | 35937 + 18 | 1293732 | 35937 + 19 | 1365606 | 35937 + 20 | 1437480 | 35937 + 21 | 1509354 | 35937 + 22 | 1581228 | 35937 + 23 | 1653102 | 35937 + 24 | 1724976 | 35937 + 25 | 1796850 | 35937 + 26 | 1868724 | 35937 + 27 | 1940598 | 35937 + 28 | 2012472 | 35937 + 29 | 2084346 | 35937 +(30 rows) + +-- partial aggregation +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t3.y, sum(t2.y + t3.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x + JOIN eager_agg_tab_ml t3 ON t2.x = t3.x +GROUP BY t3.y ORDER BY t3.y; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------ + Sort + Output: t3.y, (sum((t2.y + t3.y))), (count(*)) + Sort Key: t3.y + -> Finalize HashAggregate + Output: t3.y, sum((t2.y + t3.y)), count(*) + Group Key: t3.y + -> Append + -> Hash Join + Output: t3.y, (PARTIAL sum((t2.y + t3.y))), (PARTIAL count(*)) + Hash Cond: (t1.x = t2.x) + -> Seq Scan on public.eager_agg_tab_ml_p1 t1 + Output: t1.x + -> Hash + Output: t2.x, t3.y, t3.x, (PARTIAL sum((t2.y + t3.y))), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2.x, t3.y, t3.x, PARTIAL sum((t2.y + t3.y)), PARTIAL count(*) + Group Key: t2.x, t3.y, t3.x + -> Hash Join + Output: t2.y, t2.x, t3.y, t3.x + Hash Cond: (t2.x = t3.x) + -> Seq Scan on public.eager_agg_tab_ml_p1 t2 + Output: t2.y, t2.x + -> Hash + Output: t3.y, t3.x + -> Seq Scan on public.eager_agg_tab_ml_p1 t3 + Output: t3.y, t3.x + -> Hash Join + Output: t3_1.y, (PARTIAL sum((t2_1.y + t3_1.y))), (PARTIAL count(*)) + Hash Cond: (t1_1.x = t2_1.x) + -> Seq Scan on public.eager_agg_tab_ml_p2_s1 t1_1 + Output: t1_1.x + -> Hash + Output: t2_1.x, t3_1.y, t3_1.x, (PARTIAL sum((t2_1.y + t3_1.y))), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_1.x, t3_1.y, t3_1.x, PARTIAL sum((t2_1.y + t3_1.y)), PARTIAL count(*) + Group Key: t2_1.x, t3_1.y, t3_1.x + -> Hash Join + Output: t2_1.y, t2_1.x, t3_1.y, t3_1.x + Hash Cond: (t2_1.x = t3_1.x) + -> Seq Scan on public.eager_agg_tab_ml_p2_s1 t2_1 + Output: t2_1.y, t2_1.x + -> Hash + Output: t3_1.y, t3_1.x + -> Seq Scan on public.eager_agg_tab_ml_p2_s1 t3_1 + Output: t3_1.y, t3_1.x + -> Hash Join + Output: t3_2.y, (PARTIAL sum((t2_2.y + t3_2.y))), (PARTIAL count(*)) + Hash Cond: (t1_2.x = t2_2.x) + -> Seq Scan on public.eager_agg_tab_ml_p2_s2 t1_2 + Output: t1_2.x + -> Hash + Output: t2_2.x, t3_2.y, t3_2.x, (PARTIAL sum((t2_2.y + t3_2.y))), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_2.x, t3_2.y, t3_2.x, PARTIAL sum((t2_2.y + t3_2.y)), PARTIAL count(*) + Group Key: t2_2.x, t3_2.y, t3_2.x + -> Hash Join + Output: t2_2.y, t2_2.x, t3_2.y, t3_2.x + Hash Cond: (t2_2.x = t3_2.x) + -> Seq Scan on public.eager_agg_tab_ml_p2_s2 t2_2 + Output: t2_2.y, t2_2.x + -> Hash + Output: t3_2.y, t3_2.x + -> Seq Scan on public.eager_agg_tab_ml_p2_s2 t3_2 + Output: t3_2.y, t3_2.x + -> Hash Join + Output: t3_3.y, (PARTIAL sum((t2_3.y + t3_3.y))), (PARTIAL count(*)) + Hash Cond: (t1_3.x = t2_3.x) + -> Seq Scan on public.eager_agg_tab_ml_p3_s1 t1_3 + Output: t1_3.x + -> Hash + Output: t2_3.x, t3_3.y, t3_3.x, (PARTIAL sum((t2_3.y + t3_3.y))), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_3.x, t3_3.y, t3_3.x, PARTIAL sum((t2_3.y + t3_3.y)), PARTIAL count(*) + Group Key: t2_3.x, t3_3.y, t3_3.x + -> Hash Join + Output: t2_3.y, t2_3.x, t3_3.y, t3_3.x + Hash Cond: (t2_3.x = t3_3.x) + -> Seq Scan on public.eager_agg_tab_ml_p3_s1 t2_3 + Output: t2_3.y, t2_3.x + -> Hash + Output: t3_3.y, t3_3.x + -> Seq Scan on public.eager_agg_tab_ml_p3_s1 t3_3 + Output: t3_3.y, t3_3.x + -> Hash Join + Output: t3_4.y, (PARTIAL sum((t2_4.y + t3_4.y))), (PARTIAL count(*)) + Hash Cond: (t1_4.x = t2_4.x) + -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t1_4 + Output: t1_4.x + -> Hash + Output: t2_4.x, t3_4.y, t3_4.x, (PARTIAL sum((t2_4.y + t3_4.y))), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_4.x, t3_4.y, t3_4.x, PARTIAL sum((t2_4.y + t3_4.y)), PARTIAL count(*) + Group Key: t2_4.x, t3_4.y, t3_4.x + -> Hash Join + Output: t2_4.y, t2_4.x, t3_4.y, t3_4.x + Hash Cond: (t2_4.x = t3_4.x) + -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t2_4 + Output: t2_4.y, t2_4.x + -> Hash + Output: t3_4.y, t3_4.x + -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t3_4 + Output: t3_4.y, t3_4.x +(102 rows) + +SELECT t3.y, sum(t2.y + t3.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x + JOIN eager_agg_tab_ml t3 ON t2.x = t3.x +GROUP BY t3.y ORDER BY t3.y; + y | sum | count +----+---------+------- + 0 | 0 | 35937 + 1 | 78608 | 39304 + 2 | 157216 | 39304 + 3 | 235824 | 39304 + 4 | 314432 | 39304 + 5 | 393040 | 39304 + 6 | 471648 | 39304 + 7 | 550256 | 39304 + 8 | 628864 | 39304 + 9 | 707472 | 39304 + 10 | 786080 | 39304 + 11 | 790614 | 35937 + 12 | 862488 | 35937 + 13 | 934362 | 35937 + 14 | 1006236 | 35937 + 15 | 1078110 | 35937 + 16 | 1149984 | 35937 + 17 | 1221858 | 35937 + 18 | 1293732 | 35937 + 19 | 1365606 | 35937 + 20 | 1437480 | 35937 + 21 | 1509354 | 35937 + 22 | 1581228 | 35937 + 23 | 1653102 | 35937 + 24 | 1724976 | 35937 + 25 | 1796850 | 35937 + 26 | 1868724 | 35937 + 27 | 1940598 | 35937 + 28 | 2012472 | 35937 + 29 | 2084346 | 35937 +(30 rows) + +-- try that with GEQO too +SET geqo = on; +SET geqo_threshold = 2; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.x, sum(t2.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x +GROUP BY t1.x ORDER BY t1.x; + QUERY PLAN +--------------------------------------------------------------------------------------- + Sort + Output: t1.x, (sum(t2.y)), (count(*)) + Sort Key: t1.x + -> Append + -> Finalize HashAggregate + Output: t1.x, sum(t2.y), count(*) + Group Key: t1.x + -> Hash Join + Output: t1.x, (PARTIAL sum(t2.y)), (PARTIAL count(*)) + Hash Cond: (t1.x = t2.x) + -> Seq Scan on public.eager_agg_tab_ml_p1 t1 + Output: t1.x + -> Hash + Output: t2.x, (PARTIAL sum(t2.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2.x, PARTIAL sum(t2.y), PARTIAL count(*) + Group Key: t2.x + -> Seq Scan on public.eager_agg_tab_ml_p1 t2 + Output: t2.y, t2.x + -> Finalize HashAggregate + Output: t1_1.x, sum(t2_1.y), count(*) + Group Key: t1_1.x + -> Hash Join + Output: t1_1.x, (PARTIAL sum(t2_1.y)), (PARTIAL count(*)) + Hash Cond: (t1_1.x = t2_1.x) + -> Seq Scan on public.eager_agg_tab_ml_p2_s1 t1_1 + Output: t1_1.x + -> Hash + Output: t2_1.x, (PARTIAL sum(t2_1.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_1.x, PARTIAL sum(t2_1.y), PARTIAL count(*) + Group Key: t2_1.x + -> Seq Scan on public.eager_agg_tab_ml_p2_s1 t2_1 + Output: t2_1.y, t2_1.x + -> Finalize HashAggregate + Output: t1_2.x, sum(t2_2.y), count(*) + Group Key: t1_2.x + -> Hash Join + Output: t1_2.x, (PARTIAL sum(t2_2.y)), (PARTIAL count(*)) + Hash Cond: (t1_2.x = t2_2.x) + -> Seq Scan on public.eager_agg_tab_ml_p2_s2 t1_2 + Output: t1_2.x + -> Hash + Output: t2_2.x, (PARTIAL sum(t2_2.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_2.x, PARTIAL sum(t2_2.y), PARTIAL count(*) + Group Key: t2_2.x + -> Seq Scan on public.eager_agg_tab_ml_p2_s2 t2_2 + Output: t2_2.y, t2_2.x + -> Finalize HashAggregate + Output: t1_3.x, sum(t2_3.y), count(*) + Group Key: t1_3.x + -> Hash Join + Output: t1_3.x, (PARTIAL sum(t2_3.y)), (PARTIAL count(*)) + Hash Cond: (t1_3.x = t2_3.x) + -> Seq Scan on public.eager_agg_tab_ml_p3_s1 t1_3 + Output: t1_3.x + -> Hash + Output: t2_3.x, (PARTIAL sum(t2_3.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_3.x, PARTIAL sum(t2_3.y), PARTIAL count(*) + Group Key: t2_3.x + -> Seq Scan on public.eager_agg_tab_ml_p3_s1 t2_3 + Output: t2_3.y, t2_3.x + -> Finalize HashAggregate + Output: t1_4.x, sum(t2_4.y), count(*) + Group Key: t1_4.x + -> Hash Join + Output: t1_4.x, (PARTIAL sum(t2_4.y)), (PARTIAL count(*)) + Hash Cond: (t1_4.x = t2_4.x) + -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t1_4 + Output: t1_4.x + -> Hash + Output: t2_4.x, (PARTIAL sum(t2_4.y)), (PARTIAL count(*)) + -> Partial HashAggregate + Output: t2_4.x, PARTIAL sum(t2_4.y), PARTIAL count(*) + Group Key: t2_4.x + -> Seq Scan on public.eager_agg_tab_ml_p3_s2 t2_4 + Output: t2_4.y, t2_4.x +(79 rows) + +SELECT t1.x, sum(t2.y), count(*) + FROM eager_agg_tab_ml t1 + JOIN eager_agg_tab_ml t2 ON t1.x = t2.x +GROUP BY t1.x ORDER BY t1.x; + x | sum | count +----+-------+------- + 0 | 0 | 1089 + 1 | 1156 | 1156 + 2 | 2312 | 1156 + 3 | 3468 | 1156 + 4 | 4624 | 1156 + 5 | 5780 | 1156 + 6 | 6936 | 1156 + 7 | 8092 | 1156 + 8 | 9248 | 1156 + 9 | 10404 | 1156 + 10 | 11560 | 1156 + 11 | 11979 | 1089 + 12 | 13068 | 1089 + 13 | 14157 | 1089 + 14 | 15246 | 1089 + 15 | 16335 | 1089 + 16 | 17424 | 1089 + 17 | 18513 | 1089 + 18 | 19602 | 1089 + 19 | 20691 | 1089 + 20 | 21780 | 1089 + 21 | 22869 | 1089 + 22 | 23958 | 1089 + 23 | 25047 | 1089 + 24 | 26136 | 1089 + 25 | 27225 | 1089 + 26 | 28314 | 1089 + 27 | 29403 | 1089 + 28 | 30492 | 1089 + 29 | 31581 | 1089 +(30 rows) + +RESET geqo; +RESET geqo_threshold; +DROP TABLE eager_agg_tab_ml; diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out index 210bbe307a7..991121545c5 100644 --- a/src/test/regress/expected/groupingsets.out +++ b/src/test/regress/expected/groupingsets.out @@ -504,17 +504,17 @@ select grouping(ss.x) from int8_tbl i1 cross join lateral (select (select i1.q1) as x) ss group by ss.x; - QUERY PLAN ------------------------------------------------- + QUERY PLAN +---------------------------------------------------------- GroupAggregate - Output: GROUPING((SubPlan 1)), ((SubPlan 2)) - Group Key: ((SubPlan 2)) + Output: GROUPING((SubPlan expr_1)), ((SubPlan expr_2)) + Group Key: ((SubPlan expr_2)) -> Sort - Output: ((SubPlan 2)), i1.q1 - Sort Key: ((SubPlan 2)) + Output: ((SubPlan expr_2)), i1.q1 + Sort Key: ((SubPlan expr_2)) -> Seq Scan on public.int8_tbl i1 - Output: (SubPlan 2), i1.q1 - SubPlan 2 + Output: (SubPlan expr_2), i1.q1 + SubPlan expr_2 -> Result Output: i1.q1 (11 rows) @@ -534,22 +534,22 @@ select (select grouping(ss.x)) from int8_tbl i1 cross join lateral (select (select i1.q1) as x) ss group by ss.x; - QUERY PLAN --------------------------------------------- + QUERY PLAN +------------------------------------------------ GroupAggregate - Output: (SubPlan 2), ((SubPlan 3)) - Group Key: ((SubPlan 3)) + Output: (SubPlan expr_1), ((SubPlan expr_3)) + Group Key: ((SubPlan expr_3)) -> Sort - Output: ((SubPlan 3)), i1.q1 - Sort Key: ((SubPlan 3)) + Output: ((SubPlan expr_3)), i1.q1 + Sort Key: ((SubPlan expr_3)) -> Seq Scan on public.int8_tbl i1 - Output: (SubPlan 3), i1.q1 - SubPlan 3 + Output: (SubPlan expr_3), i1.q1 + SubPlan expr_3 -> Result Output: i1.q1 - SubPlan 2 + SubPlan expr_1 -> Result - Output: GROUPING((SubPlan 1)) + Output: GROUPING((SubPlan expr_2)) (14 rows) select (select grouping(ss.x)) @@ -592,7 +592,7 @@ explain (costs off) ------------------------------------------------------------ Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Index Only Scan using tenk1_unique1 on tenk1 Index Cond: (unique1 IS NOT NULL) @@ -881,7 +881,7 @@ explain (costs off) Sort Sort Key: "*VALUES*".column1 -> Values Scan on "*VALUES*" - SubPlan 1 + SubPlan expr_1 -> Aggregate Group Key: () Filter: "*VALUES*".column1 @@ -2169,17 +2169,17 @@ order by a, b, c; -- test handling of outer GroupingFunc within subqueries explain (costs off) select (select grouping(v1)) from (values ((select 1))) v(v1) group by cube(v1); - QUERY PLAN -------------------------------- + QUERY PLAN +------------------------------------ MixedAggregate - Hash Key: (InitPlan 3).col1 + Hash Key: (InitPlan expr_3).col1 Group Key: () - InitPlan 1 + InitPlan expr_2 -> Result - InitPlan 3 + InitPlan expr_3 -> Result -> Result - SubPlan 2 + SubPlan expr_1 -> Result (10 rows) @@ -2192,15 +2192,15 @@ select (select grouping(v1)) from (values ((select 1))) v(v1) group by cube(v1); explain (costs off) select (select grouping(v1)) from (values ((select 1))) v(v1) group by v1; - QUERY PLAN ----------------- + QUERY PLAN +------------------- GroupAggregate - InitPlan 1 + InitPlan expr_2 -> Result - InitPlan 3 + InitPlan expr_3 -> Result -> Result - SubPlan 2 + SubPlan expr_1 -> Result (8 rows) @@ -2222,18 +2222,18 @@ order by case when grouping((select t1.v from gstest5 t2 where id = t1.id)) = 0 then (select t1.v from gstest5 t2 where id = t1.id) else null end nulls first; - QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort - Output: (GROUPING((SubPlan 1))), ((SubPlan 3)), (CASE WHEN (GROUPING((SubPlan 2)) = 0) THEN ((SubPlan 3)) ELSE NULL::integer END), t1.v - Sort Key: (CASE WHEN (GROUPING((SubPlan 2)) = 0) THEN ((SubPlan 3)) ELSE NULL::integer END) NULLS FIRST + Output: (GROUPING((SubPlan expr_1))), ((SubPlan expr_3)), (CASE WHEN (GROUPING((SubPlan expr_2)) = 0) THEN ((SubPlan expr_3)) ELSE NULL::integer END), t1.v + Sort Key: (CASE WHEN (GROUPING((SubPlan expr_2)) = 0) THEN ((SubPlan expr_3)) ELSE NULL::integer END) NULLS FIRST -> HashAggregate - Output: GROUPING((SubPlan 1)), ((SubPlan 3)), CASE WHEN (GROUPING((SubPlan 2)) = 0) THEN ((SubPlan 3)) ELSE NULL::integer END, t1.v + Output: GROUPING((SubPlan expr_1)), ((SubPlan expr_3)), CASE WHEN (GROUPING((SubPlan expr_2)) = 0) THEN ((SubPlan expr_3)) ELSE NULL::integer END, t1.v Hash Key: t1.v - Hash Key: (SubPlan 3) + Hash Key: (SubPlan expr_3) -> Seq Scan on pg_temp.gstest5 t1 - Output: (SubPlan 3), t1.v, t1.id - SubPlan 3 + Output: (SubPlan expr_3), t1.v, t1.id + SubPlan expr_3 -> Bitmap Heap Scan on pg_temp.gstest5 t2 Output: t1.v Recheck Cond: (t2.id = t1.id) @@ -2272,18 +2272,18 @@ select grouping((select t1.v from gstest5 t2 where id = t1.id)), from gstest5 t1 group by grouping sets(v, s) order by o nulls first; - QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort - Output: (GROUPING((SubPlan 1))), ((SubPlan 3)), (CASE WHEN (GROUPING((SubPlan 2)) = 0) THEN ((SubPlan 3)) ELSE NULL::integer END), t1.v - Sort Key: (CASE WHEN (GROUPING((SubPlan 2)) = 0) THEN ((SubPlan 3)) ELSE NULL::integer END) NULLS FIRST + Output: (GROUPING((SubPlan expr_1))), ((SubPlan expr_3)), (CASE WHEN (GROUPING((SubPlan expr_2)) = 0) THEN ((SubPlan expr_3)) ELSE NULL::integer END), t1.v + Sort Key: (CASE WHEN (GROUPING((SubPlan expr_2)) = 0) THEN ((SubPlan expr_3)) ELSE NULL::integer END) NULLS FIRST -> HashAggregate - Output: GROUPING((SubPlan 1)), ((SubPlan 3)), CASE WHEN (GROUPING((SubPlan 2)) = 0) THEN ((SubPlan 3)) ELSE NULL::integer END, t1.v + Output: GROUPING((SubPlan expr_1)), ((SubPlan expr_3)), CASE WHEN (GROUPING((SubPlan expr_2)) = 0) THEN ((SubPlan expr_3)) ELSE NULL::integer END, t1.v Hash Key: t1.v - Hash Key: (SubPlan 3) + Hash Key: (SubPlan expr_3) -> Seq Scan on pg_temp.gstest5 t1 - Output: (SubPlan 3), t1.v, t1.id - SubPlan 3 + Output: (SubPlan expr_3), t1.v, t1.id + SubPlan expr_3 -> Bitmap Heap Scan on pg_temp.gstest5 t2 Output: t1.v Recheck Cond: (t2.id = t1.id) diff --git a/src/test/regress/expected/incremental_sort.out b/src/test/regress/expected/incremental_sort.out index 5a1dd9fc022..fdec5b9ba52 100644 --- a/src/test/regress/expected/incremental_sort.out +++ b/src/test/regress/expected/incremental_sort.out @@ -1609,13 +1609,13 @@ from tenk1 t, generate_series(1, 1000); --------------------------------------------------------------------------------- Unique -> Sort - Sort Key: t.unique1, ((SubPlan 1)) + Sort Key: t.unique1, ((SubPlan expr_1)) -> Gather Workers Planned: 2 -> Nested Loop -> Parallel Index Only Scan using tenk1_unique1 on tenk1 t -> Function Scan on generate_series - SubPlan 1 + SubPlan expr_1 -> Index Only Scan using tenk1_unique1 on tenk1 Index Cond: (unique1 = t.unique1) (11 rows) @@ -1628,13 +1628,13 @@ order by 1, 2; QUERY PLAN --------------------------------------------------------------------------- Sort - Sort Key: t.unique1, ((SubPlan 1)) + Sort Key: t.unique1, ((SubPlan expr_1)) -> Gather Workers Planned: 2 -> Nested Loop -> Parallel Index Only Scan using tenk1_unique1 on tenk1 t -> Function Scan on generate_series - SubPlan 1 + SubPlan expr_1 -> Index Only Scan using tenk1_unique1 on tenk1 Index Cond: (unique1 = t.unique1) (10 rows) diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 031dd87424a..0490a746555 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1758,9 +1758,9 @@ explain (verbose, costs off) select min(1-id) from matest0; QUERY PLAN --------------------------------------------------------------------------------- Result - Output: (InitPlan 1).col1 + Output: (InitPlan minmax_1).col1 Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit Output: ((1 - matest0.id)) -> Result @@ -1948,7 +1948,7 @@ SELECT min(x) FROM -------------------------------------------------------------------- Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Merge Append Sort Key: a.unique1 @@ -1967,7 +1967,7 @@ SELECT min(y) FROM -------------------------------------------------------------------- Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Merge Append Sort Key: a.unique1 @@ -2008,7 +2008,7 @@ FROM generate_series(1, 3) g(i); QUERY PLAN ---------------------------------------------------------------- Function Scan on generate_series g - SubPlan 1 + SubPlan array_1 -> Limit -> Merge Append Sort Key: ((d.d + g.i)) @@ -2048,19 +2048,19 @@ insert into inhpar select x, x::text from generate_series(1,5) x; insert into inhcld select x::text, x from generate_series(6,10) x; explain (verbose, costs off) update inhpar i set (f1, f2) = (select i.f1, i.f2 || '-' from int4_tbl limit 1); - QUERY PLAN --------------------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------- Update on public.inhpar i Update on public.inhpar i_1 Update on public.inhcld i_2 -> Result - Output: (SubPlan 1).col1, (SubPlan 1).col2, (rescan SubPlan 1), i.tableoid, i.ctid + Output: (SubPlan multiexpr_1).col1, (SubPlan multiexpr_1).col2, (rescan SubPlan multiexpr_1), i.tableoid, i.ctid -> Append -> Seq Scan on public.inhpar i_1 Output: i_1.f1, i_1.f2, i_1.tableoid, i_1.ctid -> Seq Scan on public.inhcld i_2 Output: i_2.f1, i_2.f2, i_2.tableoid, i_2.ctid - SubPlan 1 + SubPlan multiexpr_1 -> Limit Output: (i.f1), (((i.f2)::text || '-'::text)) -> Seq Scan on public.int4_tbl @@ -2096,21 +2096,21 @@ alter table inhpar attach partition inhcld2 for values from (5) to (100); insert into inhpar select x, x::text from generate_series(1,10) x; explain (verbose, costs off) update inhpar i set (f1, f2) = (select i.f1, i.f2 || '-' from int4_tbl limit 1); - QUERY PLAN ------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------ Update on public.inhpar i Update on public.inhcld1 i_1 Update on public.inhcld2 i_2 -> Append -> Seq Scan on public.inhcld1 i_1 - Output: (SubPlan 1).col1, (SubPlan 1).col2, (rescan SubPlan 1), i_1.tableoid, i_1.ctid - SubPlan 1 + Output: (SubPlan multiexpr_1).col1, (SubPlan multiexpr_1).col2, (rescan SubPlan multiexpr_1), i_1.tableoid, i_1.ctid + SubPlan multiexpr_1 -> Limit Output: (i_1.f1), (((i_1.f2)::text || '-'::text)) -> Seq Scan on public.int4_tbl Output: i_1.f1, ((i_1.f2)::text || '-'::text) -> Seq Scan on public.inhcld2 i_2 - Output: (SubPlan 1).col1, (SubPlan 1).col2, (rescan SubPlan 1), i_2.tableoid, i_2.ctid + Output: (SubPlan multiexpr_1).col1, (SubPlan multiexpr_1).col2, (rescan SubPlan multiexpr_1), i_2.tableoid, i_2.ctid (13 rows) update inhpar i set (f1, f2) = (select i.f1, i.f2 || '-' from int4_tbl limit 1); @@ -3260,11 +3260,11 @@ explain (costs off) select min(a), max(a) from parted_minmax where b = '12345'; ------------------------------------------------------------------------------------------------ Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Index Only Scan using parted_minmax1i on parted_minmax1 parted_minmax Index Cond: ((a IS NOT NULL) AND (b = '12345'::text)) - InitPlan 2 + InitPlan minmax_2 -> Limit -> Index Only Scan Backward using parted_minmax1i on parted_minmax1 parted_minmax_1 Index Cond: ((a IS NOT NULL) AND (b = '12345'::text)) diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out index fdd0f6c8f25..db668474684 100644 --- a/src/test/regress/expected/insert_conflict.out +++ b/src/test/regress/expected/insert_conflict.out @@ -61,9 +61,9 @@ explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on con Insert on insertconflicttest Conflict Resolution: UPDATE Conflict Arbiter Indexes: op_index_key, collation_index_key, both_index_key - Conflict Filter: EXISTS(SubPlan 1) + Conflict Filter: EXISTS(SubPlan exists_1) -> Result - SubPlan 1 + SubPlan exists_1 -> Index Only Scan using both_index_expr_key on insertconflicttest ii Index Cond: (key = excluded.key) (8 rows) diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index cd37f549b5a..d10095de70f 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2375,7 +2375,7 @@ order by t1.unique1; Sort Sort Key: t1.unique1 -> Hash Join - Hash Cond: ((t1.two = t2.two) AND (t1.unique1 = (SubPlan 2))) + Hash Cond: ((t1.two = t2.two) AND (t1.unique1 = (SubPlan expr_1))) -> Bitmap Heap Scan on tenk1 t1 Recheck Cond: (unique1 < 10) -> Bitmap Index Scan on tenk1_unique1 @@ -2385,10 +2385,10 @@ order by t1.unique1; Recheck Cond: (unique1 < 10) -> Bitmap Index Scan on tenk1_unique1 Index Cond: (unique1 < 10) - SubPlan 2 + SubPlan expr_1 -> Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Index Only Scan using tenk1_unique1 on tenk1 Index Cond: ((unique1 IS NOT NULL) AND (unique1 = t2.unique1)) @@ -2840,20 +2840,22 @@ select x.thousand, x.twothousand, count(*) from tenk1 x inner join tenk1 y on x.thousand = y.thousand group by x.thousand, x.twothousand order by x.thousand desc, x.twothousand; - QUERY PLAN ----------------------------------------------------------------------------------- - GroupAggregate + QUERY PLAN +---------------------------------------------------------------------------------------- + Finalize GroupAggregate Group Key: x.thousand, x.twothousand -> Incremental Sort Sort Key: x.thousand DESC, x.twothousand Presorted Key: x.thousand -> Merge Join Merge Cond: (y.thousand = x.thousand) - -> Index Only Scan Backward using tenk1_thous_tenthous on tenk1 y + -> Partial GroupAggregate + Group Key: y.thousand + -> Index Only Scan Backward using tenk1_thous_tenthous on tenk1 y -> Sort Sort Key: x.thousand DESC -> Seq Scan on tenk1 x -(11 rows) +(13 rows) reset enable_hashagg; reset enable_nestloop; @@ -3181,11 +3183,11 @@ where unique1 in (select unique2 from tenk1 b); explain (costs off) select a.* from tenk1 a where unique1 not in (select unique2 from tenk1 b); - QUERY PLAN ------------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------------- Seq Scan on tenk1 a - Filter: (NOT (ANY (unique1 = (hashed SubPlan 1).col1))) - SubPlan 1 + Filter: (NOT (ANY (unique1 = (hashed SubPlan any_1).col1))) + SubPlan any_1 -> Index Only Scan using tenk1_unique2 on tenk1 b (4 rows) @@ -3706,11 +3708,11 @@ order by 1,2; Sort Key: t1.q1, t1.q2 -> Hash Left Join Hash Cond: (t1.q2 = t2.q1) - Filter: (1 = (SubPlan 1)) + Filter: (1 = (SubPlan expr_1)) -> Seq Scan on int8_tbl t1 -> Hash -> Seq Scan on int8_tbl t2 - SubPlan 1 + SubPlan expr_1 -> Limit -> Result One-Time Filter: ((42) IS NOT NULL) @@ -4225,14 +4227,14 @@ from int8_tbl i8 right join (select false as z) ss3 on true, lateral (select i8.q2 as q2l where x limit 1) ss4 where i8.q2 = 123; - QUERY PLAN ----------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------- Nested Loop - Output: i8.q1, i8.q2, (InitPlan 1).col1, false, (i8.q2) - InitPlan 1 + Output: i8.q1, i8.q2, (InitPlan expr_1).col1, false, (i8.q2) + InitPlan expr_1 -> Result Output: true - InitPlan 2 + InitPlan expr_2 -> Result Output: true -> Seq Scan on public.int4_tbl i4 @@ -4241,7 +4243,7 @@ where i8.q2 = 123; -> Nested Loop Output: i8.q1, i8.q2, (i8.q2) -> Subquery Scan on ss1 - Output: ss1.y, (InitPlan 1).col1 + Output: ss1.y, (InitPlan expr_1).col1 -> Limit Output: NULL::integer -> Result @@ -4255,7 +4257,7 @@ where i8.q2 = 123; Output: (i8.q2) -> Result Output: i8.q2 - One-Time Filter: ((InitPlan 1).col1) + One-Time Filter: ((InitPlan expr_1).col1) (29 rows) explain (verbose, costs off) @@ -4268,14 +4270,14 @@ from int8_tbl i8 right join (select false as z) ss3 on true, lateral (select i8.q2 as q2l where x limit 1) ss4 where i8.q2 = 123; - QUERY PLAN ----------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------- Nested Loop - Output: i8.q1, i8.q2, (InitPlan 1).col1, false, (i8.q2) - InitPlan 1 + Output: i8.q1, i8.q2, (InitPlan expr_1).col1, false, (i8.q2) + InitPlan expr_1 -> Result Output: true - InitPlan 2 + InitPlan expr_2 -> Result Output: true -> Limit @@ -4285,7 +4287,7 @@ where i8.q2 = 123; -> Nested Loop Output: i8.q1, i8.q2, (i8.q2) -> Seq Scan on public.int4_tbl i4 - Output: i4.f1, (InitPlan 1).col1 + Output: i4.f1, (InitPlan expr_1).col1 Filter: (i4.f1 = 0) -> Nested Loop Output: i8.q1, i8.q2, (i8.q2) @@ -4296,7 +4298,7 @@ where i8.q2 = 123; Output: (i8.q2) -> Result Output: i8.q2 - One-Time Filter: ((InitPlan 1).col1) + One-Time Filter: ((InitPlan expr_1).col1) (27 rows) -- Test proper handling of appendrel PHVs during useless-RTE removal @@ -5757,13 +5759,13 @@ explain (costs off) select a.unique1, b.unique2 from onek a left join onek b on a.unique1 = b.unique2 where (b.unique2, random() > 0) = any (select q1, random() > 0 from int8_tbl c where c.q1 < b.unique1); - QUERY PLAN ------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------- Hash Join Hash Cond: (b.unique2 = a.unique1) -> Seq Scan on onek b - Filter: (ANY ((unique2 = (SubPlan 1).col1) AND ((random() > '0'::double precision) = (SubPlan 1).col2))) - SubPlan 1 + Filter: (ANY ((unique2 = (SubPlan any_1).col1) AND ((random() > '0'::double precision) = (SubPlan any_1).col2))) + SubPlan any_1 -> Seq Scan on int8_tbl c Filter: (q1 < b.unique1) -> Hash @@ -6105,7 +6107,7 @@ select exists( QUERY PLAN --------------------------------------------------------------------- Seq Scan on int4_tbl x0 - SubPlan 1 + SubPlan exists_1 -> Nested Loop Left Join Join Filter: (t2.q2 = t4.q2) -> Nested Loop Left Join @@ -6956,7 +6958,7 @@ where t1.a = t2.a; ------------------------------------------ Seq Scan on sj t2 Filter: (a IS NOT NULL) - SubPlan 1 + SubPlan expr_1 -> Result One-Time Filter: (t2.a = t2.a) -> Seq Scan on sj @@ -8983,8 +8985,8 @@ lateral (select * from int8_tbl t1, where q2 = (select greatest(t1.q1,t2.q2)) and (select v.id=0)) offset 0) ss2) ss where t1.q1 = ss.q2) ss0; - QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------ Nested Loop Output: "*VALUES*".column1, t1.q1, t1.q2, ss2.q1, ss2.q2 -> Seq Scan on public.int8_tbl t1 @@ -8998,20 +9000,20 @@ lateral (select * from int8_tbl t1, Filter: (t1.q1 = ss2.q2) -> Seq Scan on public.int8_tbl t2 Output: t2.q1, t2.q2 - Filter: (ANY ((t2.q1 = (SubPlan 3).col1) AND ((random() > '0'::double precision) = (SubPlan 3).col2))) - SubPlan 3 + Filter: (ANY ((t2.q1 = (SubPlan any_1).col1) AND ((random() > '0'::double precision) = (SubPlan any_1).col2))) + SubPlan any_1 -> Result Output: t3.q2, (random() > '0'::double precision) - One-Time Filter: (InitPlan 2).col1 - InitPlan 1 + One-Time Filter: (InitPlan expr_2).col1 + InitPlan expr_1 -> Result Output: GREATEST(t1.q1, t2.q2) - InitPlan 2 + InitPlan expr_2 -> Result Output: ("*VALUES*".column1 = 0) -> Seq Scan on public.int8_tbl t3 Output: t3.q1, t3.q2 - Filter: (t3.q2 = (InitPlan 1).col1) + Filter: (t3.q2 = (InitPlan expr_1).col1) (27 rows) select * from (values (0), (1)) v(id), @@ -9723,13 +9725,13 @@ SELECT * FROM rescan_bhs t1 LEFT JOIN rescan_bhs t2 ON t1.a IN QUERY PLAN ----------------------------------------------------------- Nested Loop Left Join - Join Filter: (ANY (t1.a = (SubPlan 1).col1)) + Join Filter: (ANY (t1.a = (SubPlan any_1).col1)) -> Bitmap Heap Scan on rescan_bhs t1 -> Bitmap Index Scan on rescan_bhs_a_idx -> Materialize -> Bitmap Heap Scan on rescan_bhs t2 -> Bitmap Index Scan on rescan_bhs_a_idx - SubPlan 1 + SubPlan any_1 -> Result One-Time Filter: (t2.a > 1) -> Bitmap Heap Scan on rescan_bhs t3 diff --git a/src/test/regress/expected/join_hash.out b/src/test/regress/expected/join_hash.out index 4fc34a0e72a..a45e1450040 100644 --- a/src/test/regress/expected/join_hash.out +++ b/src/test/regress/expected/join_hash.out @@ -1031,30 +1031,30 @@ WHERE ------------------------------------------------------------------------------------------------ Hash Join Output: hjtest_1.a, hjtest_2.a, (hjtest_1.tableoid)::regclass, (hjtest_2.tableoid)::regclass - Hash Cond: ((hjtest_1.id = (SubPlan 1)) AND ((SubPlan 2) = (SubPlan 3))) + Hash Cond: ((hjtest_1.id = (SubPlan expr_1)) AND ((SubPlan expr_2) = (SubPlan expr_3))) Join Filter: (hjtest_1.a <> hjtest_2.b) -> Seq Scan on public.hjtest_1 Output: hjtest_1.a, hjtest_1.tableoid, hjtest_1.id, hjtest_1.b - Filter: ((SubPlan 4) < 50) - SubPlan 4 + Filter: ((SubPlan expr_4) < 50) + SubPlan expr_4 -> Result Output: (hjtest_1.b * 5) -> Hash Output: hjtest_2.a, hjtest_2.tableoid, hjtest_2.id, hjtest_2.c, hjtest_2.b -> Seq Scan on public.hjtest_2 Output: hjtest_2.a, hjtest_2.tableoid, hjtest_2.id, hjtest_2.c, hjtest_2.b - Filter: ((SubPlan 5) < 55) - SubPlan 5 + Filter: ((SubPlan expr_5) < 55) + SubPlan expr_5 -> Result Output: (hjtest_2.c * 5) - SubPlan 1 + SubPlan expr_1 -> Result Output: 1 One-Time Filter: (hjtest_2.id = 1) - SubPlan 3 + SubPlan expr_3 -> Result Output: (hjtest_2.c * 5) - SubPlan 2 + SubPlan expr_2 -> Result Output: (hjtest_1.b * 5) (28 rows) @@ -1085,30 +1085,30 @@ WHERE ------------------------------------------------------------------------------------------------ Hash Join Output: hjtest_1.a, hjtest_2.a, (hjtest_1.tableoid)::regclass, (hjtest_2.tableoid)::regclass - Hash Cond: (((SubPlan 1) = hjtest_1.id) AND ((SubPlan 3) = (SubPlan 2))) + Hash Cond: (((SubPlan expr_1) = hjtest_1.id) AND ((SubPlan expr_3) = (SubPlan expr_2))) Join Filter: (hjtest_1.a <> hjtest_2.b) -> Seq Scan on public.hjtest_2 Output: hjtest_2.a, hjtest_2.tableoid, hjtest_2.id, hjtest_2.c, hjtest_2.b - Filter: ((SubPlan 5) < 55) - SubPlan 5 + Filter: ((SubPlan expr_5) < 55) + SubPlan expr_5 -> Result Output: (hjtest_2.c * 5) -> Hash Output: hjtest_1.a, hjtest_1.tableoid, hjtest_1.id, hjtest_1.b -> Seq Scan on public.hjtest_1 Output: hjtest_1.a, hjtest_1.tableoid, hjtest_1.id, hjtest_1.b - Filter: ((SubPlan 4) < 50) - SubPlan 4 + Filter: ((SubPlan expr_4) < 50) + SubPlan expr_4 -> Result Output: (hjtest_1.b * 5) - SubPlan 2 + SubPlan expr_2 -> Result Output: (hjtest_1.b * 5) - SubPlan 1 + SubPlan expr_1 -> Result Output: 1 One-Time Filter: (hjtest_2.id = 1) - SubPlan 3 + SubPlan expr_3 -> Result Output: (hjtest_2.c * 5) (28 rows) diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out index fbcaf113266..00c30b91459 100644 --- a/src/test/regress/expected/memoize.out +++ b/src/test/regress/expected/memoize.out @@ -429,8 +429,8 @@ WHERE unique1 < 3 ---------------------------------------------------------------- Index Scan using tenk1_unique1 on tenk1 t0 Index Cond: (unique1 < 3) - Filter: EXISTS(SubPlan 1) - SubPlan 1 + Filter: EXISTS(SubPlan exists_1) + SubPlan exists_1 -> Nested Loop -> Index Scan using tenk1_hundred on tenk1 t2 Filter: (t0.two <> four) diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out index 44df626c40c..9cb1d87066a 100644 --- a/src/test/regress/expected/merge.out +++ b/src/test/regress/expected/merge.out @@ -1828,29 +1828,29 @@ WHEN MATCHED AND t.c > s.cnt THEN -> Hash Join Output: t.ctid, s.a, s.b, s.c, s.d, s.ctid Hash Cond: (t.a = s.a) - Join Filter: (t.b < (SubPlan 1)) + Join Filter: (t.b < (SubPlan expr_1)) -> Seq Scan on public.tgt t Output: t.ctid, t.a, t.b -> Hash Output: s.a, s.b, s.c, s.d, s.ctid -> Seq Scan on public.src s Output: s.a, s.b, s.c, s.d, s.ctid - SubPlan 1 + SubPlan expr_1 -> Aggregate Output: count(*) -> Seq Scan on public.ref r Output: r.ab, r.cd Filter: ((r.ab = (s.a + s.b)) AND (r.cd = (s.c - s.d))) - SubPlan 4 + SubPlan expr_3 -> Aggregate Output: count(*) -> Seq Scan on public.ref r_2 Output: r_2.ab, r_2.cd Filter: ((r_2.ab = (s.a + s.b)) AND (r_2.cd = (s.c - s.d))) - SubPlan 3 + SubPlan multiexpr_1 -> Result - Output: s.b, (InitPlan 2).col1 - InitPlan 2 + Output: s.b, (InitPlan expr_2).col1 + InitPlan expr_2 -> Aggregate Output: count(*) -> Seq Scan on public.ref r_1 diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out index 20bf9ea9cdf..a357e1d0c0e 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -1470,7 +1470,7 @@ WHERE aggfnoid = 0 OR aggtransfn = 0 OR (aggkind = 'n' AND aggnumdirectargs > 0) OR aggfinalmodify NOT IN ('r', 's', 'w') OR aggmfinalmodify NOT IN ('r', 's', 'w') OR - aggtranstype = 0 OR aggtransspace < 0 OR aggmtransspace < 0; + aggtranstype = 0 OR aggmtransspace < 0; ctid | aggfnoid ------+---------- (0 rows) diff --git a/src/test/regress/expected/partition_aggregate.out b/src/test/regress/expected/partition_aggregate.out index cb12bf53719..fc84929a002 100644 --- a/src/test/regress/expected/partition_aggregate.out +++ b/src/test/regress/expected/partition_aggregate.out @@ -13,6 +13,8 @@ SET enable_partitionwise_join TO true; SET max_parallel_workers_per_gather TO 0; -- Disable incremental sort, which can influence selected plans due to fuzz factor. SET enable_incremental_sort TO off; +-- Disable eager aggregation, which can interfere with the generation of partitionwise aggregation. +SET enable_eager_aggregate TO off; -- -- Tests for list partitioned tables. -- diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index 7499cdb2cdf..deacdd75807 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -1915,21 +1915,21 @@ select * from from int4_tbl touter) ss, asptab where asptab.id > ss.b::int; - QUERY PLAN --------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------- Nested Loop -> Seq Scan on int4_tbl touter -> Append -> Index Only Scan using asptab0_pkey on asptab0 asptab_1 - Index Cond: (id > (EXISTS(SubPlan 3))::integer) - SubPlan 4 + Index Cond: (id > (EXISTS(SubPlan exists_3))::integer) + SubPlan exists_4 -> Seq Scan on int4_tbl tinner_2 -> Index Only Scan using asptab1_pkey on asptab1 asptab_2 - Index Cond: (id > (EXISTS(SubPlan 3))::integer) - SubPlan 3 + Index Cond: (id > (EXISTS(SubPlan exists_3))::integer) + SubPlan exists_3 -> Seq Scan on int4_tbl tinner_1 Filter: (f1 = touter.f1) - SubPlan 2 + SubPlan exists_2 -> Seq Scan on int4_tbl tinner (14 rows) @@ -2236,36 +2236,36 @@ explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 prepare ab_q2 (int, int) as select a from ab where a between $1 and $2 and b < (select 3); explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q2 (2, 2); - QUERY PLAN ------------------------------------------------------------------------ + QUERY PLAN +---------------------------------------------------------------------------- Append (actual rows=0.00 loops=1) Subplans Removed: 6 - InitPlan 1 + InitPlan expr_1 -> Result (actual rows=1.00 loops=1) -> Seq Scan on ab_a2_b1 ab_1 (actual rows=0.00 loops=1) - Filter: ((a >= $1) AND (a <= $2) AND (b < (InitPlan 1).col1)) + Filter: ((a >= $1) AND (a <= $2) AND (b < (InitPlan expr_1).col1)) -> Seq Scan on ab_a2_b2 ab_2 (actual rows=0.00 loops=1) - Filter: ((a >= $1) AND (a <= $2) AND (b < (InitPlan 1).col1)) + Filter: ((a >= $1) AND (a <= $2) AND (b < (InitPlan expr_1).col1)) -> Seq Scan on ab_a2_b3 ab_3 (never executed) - Filter: ((a >= $1) AND (a <= $2) AND (b < (InitPlan 1).col1)) + Filter: ((a >= $1) AND (a <= $2) AND (b < (InitPlan expr_1).col1)) (10 rows) -- As above, but swap the PARAM_EXEC Param to the first partition level prepare ab_q3 (int, int) as select a from ab where b between $1 and $2 and a < (select 3); explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q3 (2, 2); - QUERY PLAN ------------------------------------------------------------------------ + QUERY PLAN +---------------------------------------------------------------------------- Append (actual rows=0.00 loops=1) Subplans Removed: 6 - InitPlan 1 + InitPlan expr_1 -> Result (actual rows=1.00 loops=1) -> Seq Scan on ab_a1_b2 ab_1 (actual rows=0.00 loops=1) - Filter: ((b >= $1) AND (b <= $2) AND (a < (InitPlan 1).col1)) + Filter: ((b >= $1) AND (b <= $2) AND (a < (InitPlan expr_1).col1)) -> Seq Scan on ab_a2_b2 ab_2 (actual rows=0.00 loops=1) - Filter: ((b >= $1) AND (b <= $2) AND (a < (InitPlan 1).col1)) + Filter: ((b >= $1) AND (b <= $2) AND (a < (InitPlan expr_1).col1)) -> Seq Scan on ab_a3_b2 ab_3 (never executed) - Filter: ((b >= $1) AND (b <= $2) AND (a < (InitPlan 1).col1)) + Filter: ((b >= $1) AND (b <= $2) AND (a < (InitPlan expr_1).col1)) (10 rows) -- @@ -2475,23 +2475,23 @@ select explain_parallel_append('execute ab_q5 (33, 44, 55)'); -- Test Parallel Append with PARAM_EXEC Params select explain_parallel_append('select count(*) from ab where (a = (select 1) or a = (select 3)) and b = 2'); - explain_parallel_append ------------------------------------------------------------------------------------------------- + explain_parallel_append +---------------------------------------------------------------------------------------------------------- Aggregate (actual rows=N loops=N) - InitPlan 1 + InitPlan expr_1 -> Result (actual rows=N loops=N) - InitPlan 2 + InitPlan expr_2 -> Result (actual rows=N loops=N) -> Gather (actual rows=N loops=N) Workers Planned: 2 Workers Launched: N -> Parallel Append (actual rows=N loops=N) -> Parallel Seq Scan on ab_a1_b2 ab_1 (actual rows=N loops=N) - Filter: ((b = 2) AND ((a = (InitPlan 1).col1) OR (a = (InitPlan 2).col1))) + Filter: ((b = 2) AND ((a = (InitPlan expr_1).col1) OR (a = (InitPlan expr_2).col1))) -> Parallel Seq Scan on ab_a2_b2 ab_2 (never executed) - Filter: ((b = 2) AND ((a = (InitPlan 1).col1) OR (a = (InitPlan 2).col1))) + Filter: ((b = 2) AND ((a = (InitPlan expr_1).col1) OR (a = (InitPlan expr_2).col1))) -> Parallel Seq Scan on ab_a3_b2 ab_3 (actual rows=N loops=N) - Filter: ((b = 2) AND ((a = (InitPlan 1).col1) OR (a = (InitPlan 2).col1))) + Filter: ((b = 2) AND ((a = (InitPlan expr_1).col1) OR (a = (InitPlan expr_2).col1))) (15 rows) -- Test pruning during parallel nested loop query @@ -2692,65 +2692,65 @@ select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 QUERY PLAN ---------------------------------------------------------------------------- Append (actual rows=0.00 loops=1) - InitPlan 1 + InitPlan expr_1 -> Aggregate (actual rows=1.00 loops=1) -> Seq Scan on lprt_a (actual rows=102.00 loops=1) - InitPlan 2 + InitPlan expr_2 -> Aggregate (actual rows=1.00 loops=1) -> Seq Scan on lprt_a lprt_a_1 (actual rows=102.00 loops=1) -> Bitmap Heap Scan on ab_a1_b1 ab_1 (never executed) - Recheck Cond: (a = (InitPlan 1).col1) - Filter: (b = (InitPlan 2).col1) + Recheck Cond: (a = (InitPlan expr_1).col1) + Filter: (b = (InitPlan expr_2).col1) -> Bitmap Index Scan on ab_a1_b1_a_idx (never executed) - Index Cond: (a = (InitPlan 1).col1) + Index Cond: (a = (InitPlan expr_1).col1) Index Searches: 0 -> Bitmap Heap Scan on ab_a1_b2 ab_2 (never executed) - Recheck Cond: (a = (InitPlan 1).col1) - Filter: (b = (InitPlan 2).col1) + Recheck Cond: (a = (InitPlan expr_1).col1) + Filter: (b = (InitPlan expr_2).col1) -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed) - Index Cond: (a = (InitPlan 1).col1) + Index Cond: (a = (InitPlan expr_1).col1) Index Searches: 0 -> Bitmap Heap Scan on ab_a1_b3 ab_3 (never executed) - Recheck Cond: (a = (InitPlan 1).col1) - Filter: (b = (InitPlan 2).col1) + Recheck Cond: (a = (InitPlan expr_1).col1) + Filter: (b = (InitPlan expr_2).col1) -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed) - Index Cond: (a = (InitPlan 1).col1) + Index Cond: (a = (InitPlan expr_1).col1) Index Searches: 0 -> Bitmap Heap Scan on ab_a2_b1 ab_4 (never executed) - Recheck Cond: (a = (InitPlan 1).col1) - Filter: (b = (InitPlan 2).col1) + Recheck Cond: (a = (InitPlan expr_1).col1) + Filter: (b = (InitPlan expr_2).col1) -> Bitmap Index Scan on ab_a2_b1_a_idx (never executed) - Index Cond: (a = (InitPlan 1).col1) + Index Cond: (a = (InitPlan expr_1).col1) Index Searches: 0 -> Bitmap Heap Scan on ab_a2_b2 ab_5 (never executed) - Recheck Cond: (a = (InitPlan 1).col1) - Filter: (b = (InitPlan 2).col1) + Recheck Cond: (a = (InitPlan expr_1).col1) + Filter: (b = (InitPlan expr_2).col1) -> Bitmap Index Scan on ab_a2_b2_a_idx (never executed) - Index Cond: (a = (InitPlan 1).col1) + Index Cond: (a = (InitPlan expr_1).col1) Index Searches: 0 -> Bitmap Heap Scan on ab_a2_b3 ab_6 (never executed) - Recheck Cond: (a = (InitPlan 1).col1) - Filter: (b = (InitPlan 2).col1) + Recheck Cond: (a = (InitPlan expr_1).col1) + Filter: (b = (InitPlan expr_2).col1) -> Bitmap Index Scan on ab_a2_b3_a_idx (never executed) - Index Cond: (a = (InitPlan 1).col1) + Index Cond: (a = (InitPlan expr_1).col1) Index Searches: 0 -> Bitmap Heap Scan on ab_a3_b1 ab_7 (never executed) - Recheck Cond: (a = (InitPlan 1).col1) - Filter: (b = (InitPlan 2).col1) + Recheck Cond: (a = (InitPlan expr_1).col1) + Filter: (b = (InitPlan expr_2).col1) -> Bitmap Index Scan on ab_a3_b1_a_idx (never executed) - Index Cond: (a = (InitPlan 1).col1) + Index Cond: (a = (InitPlan expr_1).col1) Index Searches: 0 -> Bitmap Heap Scan on ab_a3_b2 ab_8 (actual rows=0.00 loops=1) - Recheck Cond: (a = (InitPlan 1).col1) - Filter: (b = (InitPlan 2).col1) + Recheck Cond: (a = (InitPlan expr_1).col1) + Filter: (b = (InitPlan expr_2).col1) -> Bitmap Index Scan on ab_a3_b2_a_idx (actual rows=0.00 loops=1) - Index Cond: (a = (InitPlan 1).col1) + Index Cond: (a = (InitPlan expr_1).col1) Index Searches: 1 -> Bitmap Heap Scan on ab_a3_b3 ab_9 (never executed) - Recheck Cond: (a = (InitPlan 1).col1) - Filter: (b = (InitPlan 2).col1) + Recheck Cond: (a = (InitPlan expr_1).col1) + Filter: (b = (InitPlan expr_2).col1) -> Bitmap Index Scan on ab_a3_b3_a_idx (never executed) - Index Cond: (a = (InitPlan 1).col1) + Index Cond: (a = (InitPlan expr_1).col1) Index Searches: 0 (61 rows) @@ -2760,45 +2760,45 @@ select * from (select * from ab where a = 1 union all select * from ab) ab where QUERY PLAN ---------------------------------------------------------------------------------- Append (actual rows=0.00 loops=1) - InitPlan 1 + InitPlan expr_1 -> Result (actual rows=1.00 loops=1) -> Append (actual rows=0.00 loops=1) -> Bitmap Heap Scan on ab_a1_b1 ab_11 (actual rows=0.00 loops=1) Recheck Cond: (a = 1) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0.00 loops=1) Index Cond: (a = 1) Index Searches: 1 -> Bitmap Heap Scan on ab_a1_b2 ab_12 (never executed) Recheck Cond: (a = 1) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed) Index Cond: (a = 1) Index Searches: 0 -> Bitmap Heap Scan on ab_a1_b3 ab_13 (never executed) Recheck Cond: (a = 1) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed) Index Cond: (a = 1) Index Searches: 0 -> Seq Scan on ab_a1_b1 ab_1 (actual rows=0.00 loops=1) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a1_b2 ab_2 (never executed) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a1_b3 ab_3 (never executed) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a2_b1 ab_4 (actual rows=0.00 loops=1) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a2_b2 ab_5 (never executed) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a2_b3 ab_6 (never executed) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a3_b1 ab_7 (actual rows=0.00 loops=1) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a3_b2 ab_8 (never executed) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a3_b3 ab_9 (never executed) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) (40 rows) -- A case containing a UNION ALL with a non-partitioned child. @@ -2807,47 +2807,47 @@ select * from (select * from ab where a = 1 union all (values(10,5)) union all s QUERY PLAN ---------------------------------------------------------------------------------- Append (actual rows=0.00 loops=1) - InitPlan 1 + InitPlan expr_1 -> Result (actual rows=1.00 loops=1) -> Append (actual rows=0.00 loops=1) -> Bitmap Heap Scan on ab_a1_b1 ab_11 (actual rows=0.00 loops=1) Recheck Cond: (a = 1) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0.00 loops=1) Index Cond: (a = 1) Index Searches: 1 -> Bitmap Heap Scan on ab_a1_b2 ab_12 (never executed) Recheck Cond: (a = 1) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed) Index Cond: (a = 1) Index Searches: 0 -> Bitmap Heap Scan on ab_a1_b3 ab_13 (never executed) Recheck Cond: (a = 1) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed) Index Cond: (a = 1) Index Searches: 0 -> Result (actual rows=0.00 loops=1) - One-Time Filter: (5 = (InitPlan 1).col1) + One-Time Filter: (5 = (InitPlan expr_1).col1) -> Seq Scan on ab_a1_b1 ab_1 (actual rows=0.00 loops=1) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a1_b2 ab_2 (never executed) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a1_b3 ab_3 (never executed) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a2_b1 ab_4 (actual rows=0.00 loops=1) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a2_b2 ab_5 (never executed) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a2_b3 ab_6 (never executed) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a3_b1 ab_7 (actual rows=0.00 loops=1) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a3_b2 ab_8 (never executed) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a3_b3 ab_9 (never executed) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) (42 rows) -- Another UNION ALL test, but containing a mix of exec init and exec run-time pruning. @@ -2865,27 +2865,27 @@ union all ) ab where a = $1 and b = (select -10); -- Ensure the xy_1 subplan is not pruned. explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q6(1); - QUERY PLAN --------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------- Append (actual rows=0.00 loops=1) Subplans Removed: 12 - InitPlan 1 + InitPlan expr_1 -> Result (actual rows=1.00 loops=1) -> Seq Scan on ab_a1_b1 ab_1 (never executed) - Filter: ((a = $1) AND (b = (InitPlan 1).col1)) + Filter: ((a = $1) AND (b = (InitPlan expr_1).col1)) -> Seq Scan on ab_a1_b2 ab_2 (never executed) - Filter: ((a = $1) AND (b = (InitPlan 1).col1)) + Filter: ((a = $1) AND (b = (InitPlan expr_1).col1)) -> Seq Scan on ab_a1_b3 ab_3 (never executed) - Filter: ((a = $1) AND (b = (InitPlan 1).col1)) + Filter: ((a = $1) AND (b = (InitPlan expr_1).col1)) -> Seq Scan on xy_1 (actual rows=0.00 loops=1) - Filter: ((x = $1) AND (y = (InitPlan 1).col1)) + Filter: ((x = $1) AND (y = (InitPlan expr_1).col1)) Rows Removed by Filter: 1 -> Seq Scan on ab_a1_b1 ab_4 (never executed) - Filter: ((a = $1) AND (b = (InitPlan 1).col1)) + Filter: ((a = $1) AND (b = (InitPlan expr_1).col1)) -> Seq Scan on ab_a1_b2 ab_5 (never executed) - Filter: ((a = $1) AND (b = (InitPlan 1).col1)) + Filter: ((a = $1) AND (b = (InitPlan expr_1).col1)) -> Seq Scan on ab_a1_b3 ab_6 (never executed) - Filter: ((a = $1) AND (b = (InitPlan 1).col1)) + Filter: ((a = $1) AND (b = (InitPlan expr_1).col1)) (19 rows) -- Ensure we see just the xy_1 row. @@ -2971,7 +2971,7 @@ update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (select 1);'); Update on ab_a1_b1 ab_a1_1 Update on ab_a1_b2 ab_a1_2 Update on ab_a1_b3 ab_a1_3 - InitPlan 1 + InitPlan expr_1 -> Result (actual rows=1.00 loops=1) -> Nested Loop (actual rows=3.00 loops=1) -> Append (actual rows=3.00 loops=1) @@ -2982,11 +2982,11 @@ update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (select 1);'); Storage: Memory Maximum Storage: NkB -> Append (actual rows=1.00 loops=1) -> Seq Scan on ab_a2_b1 ab_a2_1 (actual rows=1.00 loops=1) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a2_b2 ab_a2_2 (never executed) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) -> Seq Scan on ab_a2_b3 ab_a2_3 (never executed) - Filter: (b = (InitPlan 1).col1) + Filter: (b = (InitPlan expr_1).col1) (20 rows) select tableoid::regclass, * from ab; @@ -3356,12 +3356,12 @@ select * from listp where a = (select null::int); QUERY PLAN ------------------------------------------------------ Append (actual rows=0.00 loops=1) - InitPlan 1 + InitPlan expr_1 -> Result (actual rows=1.00 loops=1) -> Seq Scan on listp_1_1 listp_1 (never executed) - Filter: (a = (InitPlan 1).col1) + Filter: (a = (InitPlan expr_1).col1) -> Seq Scan on listp_2_1 listp_2 (never executed) - Filter: (a = (InitPlan 1).col1) + Filter: (a = (InitPlan expr_1).col1) (7 rows) drop table listp; @@ -3500,14 +3500,14 @@ prepare ps1 as select * from mc3p where a = $1 and abs(b) < (select 3); explain (analyze, costs off, summary off, timing off, buffers off) execute ps1(1); - QUERY PLAN -------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------ Append (actual rows=1.00 loops=1) Subplans Removed: 2 - InitPlan 1 + InitPlan expr_1 -> Result (actual rows=1.00 loops=1) -> Seq Scan on mc3p1 mc3p_1 (actual rows=1.00 loops=1) - Filter: ((a = $1) AND (abs(b) < (InitPlan 1).col1)) + Filter: ((a = $1) AND (abs(b) < (InitPlan expr_1).col1)) (6 rows) deallocate ps1; @@ -3515,16 +3515,16 @@ prepare ps2 as select * from mc3p where a <= $1 and abs(b) < (select 3); explain (analyze, costs off, summary off, timing off, buffers off) execute ps2(1); - QUERY PLAN --------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------- Append (actual rows=2.00 loops=1) Subplans Removed: 1 - InitPlan 1 + InitPlan expr_1 -> Result (actual rows=1.00 loops=1) -> Seq Scan on mc3p0 mc3p_1 (actual rows=1.00 loops=1) - Filter: ((a <= $1) AND (abs(b) < (InitPlan 1).col1)) + Filter: ((a <= $1) AND (abs(b) < (InitPlan expr_1).col1)) -> Seq Scan on mc3p1 mc3p_2 (actual rows=1.00 loops=1) - Filter: ((a <= $1) AND (abs(b) < (InitPlan 1).col1)) + Filter: ((a <= $1) AND (abs(b) < (InitPlan expr_1).col1)) (8 rows) deallocate ps2; @@ -3540,14 +3540,14 @@ select * from boolp where a = (select value from boolvalues where value); QUERY PLAN -------------------------------------------------------------- Append (actual rows=0.00 loops=1) - InitPlan 1 + InitPlan expr_1 -> Seq Scan on boolvalues (actual rows=1.00 loops=1) Filter: value Rows Removed by Filter: 1 -> Seq Scan on boolp_f boolp_1 (never executed) - Filter: (a = (InitPlan 1).col1) + Filter: (a = (InitPlan expr_1).col1) -> Seq Scan on boolp_t boolp_2 (actual rows=0.00 loops=1) - Filter: (a = (InitPlan 1).col1) + Filter: (a = (InitPlan expr_1).col1) (9 rows) explain (analyze, costs off, summary off, timing off, buffers off) @@ -3555,14 +3555,14 @@ select * from boolp where a = (select value from boolvalues where not value); QUERY PLAN -------------------------------------------------------------- Append (actual rows=0.00 loops=1) - InitPlan 1 + InitPlan expr_1 -> Seq Scan on boolvalues (actual rows=1.00 loops=1) Filter: (NOT value) Rows Removed by Filter: 1 -> Seq Scan on boolp_f boolp_1 (actual rows=0.00 loops=1) - Filter: (a = (InitPlan 1).col1) + Filter: (a = (InitPlan expr_1).col1) -> Seq Scan on boolp_t boolp_2 (never executed) - Filter: (a = (InitPlan 1).col1) + Filter: (a = (InitPlan expr_1).col1) (9 rows) drop table boolp; @@ -3654,22 +3654,22 @@ explain (analyze, costs off, summary off, timing off, buffers off) select * from -------------------------------------------------------------------------------------------------- Merge Append (actual rows=20.00 loops=1) Sort Key: ma_test.b - InitPlan 2 + InitPlan expr_1 -> Result (actual rows=1.00 loops=1) Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit (actual rows=1.00 loops=1) -> Index Scan using ma_test_p2_b_idx on ma_test_p2 (actual rows=1.00 loops=1) Index Cond: (b IS NOT NULL) Index Searches: 1 -> Index Scan using ma_test_p1_b_idx on ma_test_p1 ma_test_1 (never executed) - Filter: (a >= (InitPlan 2).col1) + Filter: (a >= (InitPlan expr_1).col1) Index Searches: 0 -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_2 (actual rows=10.00 loops=1) - Filter: (a >= (InitPlan 2).col1) + Filter: (a >= (InitPlan expr_1).col1) Index Searches: 1 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_3 (actual rows=10.00 loops=1) - Filter: (a >= (InitPlan 2).col1) + Filter: (a >= (InitPlan expr_1).col1) Index Searches: 1 (19 rows) @@ -4043,17 +4043,17 @@ from ( select 1, 1, 1 ) s(a, b, c) where s.a = 1 and s.b = 1 and s.c = (select 1); - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------ Append - InitPlan 1 + InitPlan expr_1 -> Result -> Seq Scan on p1 p - Filter: ((a = 1) AND (b = 1) AND (c = (InitPlan 1).col1)) + Filter: ((a = 1) AND (b = 1) AND (c = (InitPlan expr_1).col1)) -> Seq Scan on q111 q1 - Filter: ((a = 1) AND (b = 1) AND (c = (InitPlan 1).col1)) + Filter: ((a = 1) AND (b = 1) AND (c = (InitPlan expr_1).col1)) -> Result - One-Time Filter: (1 = (InitPlan 1).col1) + One-Time Filter: (1 = (InitPlan expr_1).col1) (9 rows) select * @@ -4081,18 +4081,18 @@ from ( ) s(a, b, c) where s.a = $1 and s.b = $2 and s.c = (select 1); explain (costs off) execute q (1, 1); - QUERY PLAN ------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------- Append Subplans Removed: 1 - InitPlan 1 + InitPlan expr_1 -> Result -> Seq Scan on p1 p - Filter: ((a = $1) AND (b = $2) AND (c = (InitPlan 1).col1)) + Filter: ((a = $1) AND (b = $2) AND (c = (InitPlan expr_1).col1)) -> Seq Scan on q111 q1 - Filter: ((a = $1) AND (b = $2) AND (c = (InitPlan 1).col1)) + Filter: ((a = $1) AND (b = $2) AND (c = (InitPlan expr_1).col1)) -> Result - One-Time Filter: ((1 = $1) AND (1 = $2) AND (1 = (InitPlan 1).col1)) + One-Time Filter: ((1 = $1) AND (1 = $2) AND (1 = (InitPlan expr_1).col1)) (10 rows) execute q (1, 1); @@ -4110,11 +4110,11 @@ create table listp2 partition of listp for values in(2) partition by list(b); create table listp2_10 partition of listp2 for values in (10); explain (analyze, costs off, summary off, timing off, buffers off) select * from listp where a = (select 2) and b <> 10; - QUERY PLAN ------------------------------------------------------ + QUERY PLAN +-------------------------------------------------------- Seq Scan on listp1 listp (actual rows=0.00 loops=1) - Filter: ((b <> 10) AND (a = (InitPlan 1).col1)) - InitPlan 1 + Filter: ((b <> 10) AND (a = (InitPlan expr_1).col1)) + InitPlan expr_1 -> Result (never executed) (4 rows) @@ -4182,13 +4182,13 @@ select explain_parallel_append('select * from listp where a = (select 1);'); Gather (actual rows=N loops=N) Workers Planned: 2 Workers Launched: N - InitPlan 1 + InitPlan expr_1 -> Result (actual rows=N loops=N) -> Parallel Append (actual rows=N loops=N) -> Seq Scan on listp_12_1 listp_1 (actual rows=N loops=N) - Filter: (a = (InitPlan 1).col1) + Filter: (a = (InitPlan expr_1).col1) -> Parallel Seq Scan on listp_12_2 listp_2 (never executed) - Filter: (a = (InitPlan 1).col1) + Filter: (a = (InitPlan expr_1).col1) (10 rows) -- Like the above but throw some more complexity at the planner by adding @@ -4205,19 +4205,19 @@ select * from listp where a = (select 2);'); Workers Launched: N -> Parallel Append (actual rows=N loops=N) -> Parallel Append (actual rows=N loops=N) - InitPlan 2 + InitPlan expr_2 -> Result (actual rows=N loops=N) -> Seq Scan on listp_12_1 listp_1 (never executed) - Filter: (a = (InitPlan 2).col1) + Filter: (a = (InitPlan expr_2).col1) -> Parallel Seq Scan on listp_12_2 listp_2 (actual rows=N loops=N) - Filter: (a = (InitPlan 2).col1) + Filter: (a = (InitPlan expr_2).col1) -> Parallel Append (actual rows=N loops=N) - InitPlan 1 + InitPlan expr_1 -> Result (actual rows=N loops=N) -> Seq Scan on listp_12_1 listp_4 (actual rows=N loops=N) - Filter: (a = (InitPlan 1).col1) + Filter: (a = (InitPlan expr_1).col1) -> Parallel Seq Scan on listp_12_2 listp_5 (never executed) - Filter: (a = (InitPlan 1).col1) + Filter: (a = (InitPlan expr_1).col1) (18 rows) drop table listp; @@ -4240,23 +4240,23 @@ select * from rangep where b IN((select 1),(select 2)) order by a; QUERY PLAN --------------------------------------------------------------------------------------------------------------- Append (actual rows=0.00 loops=1) - InitPlan 1 + InitPlan expr_1 -> Result (actual rows=1.00 loops=1) - InitPlan 2 + InitPlan expr_2 -> Result (actual rows=1.00 loops=1) -> Merge Append (actual rows=0.00 loops=1) Sort Key: rangep_2.a -> Index Scan using rangep_0_to_100_1_a_idx on rangep_0_to_100_1 rangep_2 (actual rows=0.00 loops=1) - Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1])) + Filter: (b = ANY (ARRAY[(InitPlan expr_1).col1, (InitPlan expr_2).col1])) Index Searches: 1 -> Index Scan using rangep_0_to_100_2_a_idx on rangep_0_to_100_2 rangep_3 (actual rows=0.00 loops=1) - Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1])) + Filter: (b = ANY (ARRAY[(InitPlan expr_1).col1, (InitPlan expr_2).col1])) Index Searches: 1 -> Index Scan using rangep_0_to_100_3_a_idx on rangep_0_to_100_3 rangep_4 (never executed) - Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1])) + Filter: (b = ANY (ARRAY[(InitPlan expr_1).col1, (InitPlan expr_2).col1])) Index Searches: 0 -> Index Scan using rangep_100_to_200_a_idx on rangep_100_to_200 rangep_5 (actual rows=0.00 loops=1) - Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1])) + Filter: (b = ANY (ARRAY[(InitPlan expr_1).col1, (InitPlan expr_2).col1])) Index Searches: 1 (19 rows) diff --git a/src/test/regress/expected/portals.out b/src/test/regress/expected/portals.out index 06726ed4ab7..31f77abc446 100644 --- a/src/test/regress/expected/portals.out +++ b/src/test/regress/expected/portals.out @@ -1472,18 +1472,18 @@ rollback; -- Check handling of non-backwards-scan-capable plans with scroll cursors begin; explain (costs off) declare c1 cursor for select (select 42) as x; - QUERY PLAN ----------------- + QUERY PLAN +------------------- Result - InitPlan 1 + InitPlan expr_1 -> Result (3 rows) explain (costs off) declare c1 scroll cursor for select (select 42) as x; - QUERY PLAN ----------------- + QUERY PLAN +------------------- Materialize - InitPlan 1 + InitPlan expr_1 -> Result -> Result (4 rows) diff --git a/src/test/regress/expected/predicate.out b/src/test/regress/expected/predicate.out index 304b6868b90..66fb0854b88 100644 --- a/src/test/regress/expected/predicate.out +++ b/src/test/regress/expected/predicate.out @@ -247,11 +247,11 @@ SELECT * FROM pred_tab t1 QUERY PLAN --------------------------------------------------------- Nested Loop Left Join - Join Filter: EXISTS(SubPlan 1) + Join Filter: EXISTS(SubPlan exists_1) -> Seq Scan on pred_tab t1 -> Materialize -> Seq Scan on pred_tab t2 - SubPlan 1 + SubPlan exists_1 -> Nested Loop -> Nested Loop -> Nested Loop @@ -274,8 +274,8 @@ SELECT * FROM pred_tab t1 QUERY PLAN -------------------------------------------- Nested Loop Left Join - Join Filter: (InitPlan 1).col1 - InitPlan 1 + Join Filter: (InitPlan exists_1).col1 + InitPlan exists_1 -> Result Replaces: Join on t3, t4, t5, t6 One-Time Filter: false diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out index a79325e8a2f..fa8984ffe0d 100644 --- a/src/test/regress/expected/psql.out +++ b/src/test/regress/expected/psql.out @@ -6445,9 +6445,9 @@ List of schemas (0 rows) \dRp "no.such.publication" - List of publications - Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root -------+-------+------------+---------+---------+---------+-----------+-------------------+---------- + List of publications + Name | Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +------+-------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- (0 rows) \dRs "no.such.subscription" diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out index 895ca87a0df..e72d1308967 100644 --- a/src/test/regress/expected/publication.out +++ b/src/test/regress/expected/publication.out @@ -40,20 +40,20 @@ CREATE PUBLICATION testpub_xxx WITH (publish_generated_columns); ERROR: invalid value for publication parameter "publish_generated_columns": "" DETAIL: Valid values are "none" and "stored". \dRp - List of publications - Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------+--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - testpub_default | regress_publication_user | f | f | t | f | f | none | f - testpub_ins_trunct | regress_publication_user | f | t | f | f | f | none | f + List of publications + Name | Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + testpub_default | regress_publication_user | f | f | f | t | f | f | none | f + testpub_ins_trunct | regress_publication_user | f | f | t | f | f | f | none | f (2 rows) ALTER PUBLICATION testpub_default SET (publish = 'insert, update, delete'); \dRp - List of publications - Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------+--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - testpub_default | regress_publication_user | f | t | t | t | f | none | f - testpub_ins_trunct | regress_publication_user | f | t | f | f | f | none | f + List of publications + Name | Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + testpub_default | regress_publication_user | f | f | t | t | t | f | none | f + testpub_ins_trunct | regress_publication_user | f | f | t | f | f | f | none | f (2 rows) --- adding tables @@ -70,15 +70,15 @@ CREATE TABLE testpub_tbl2 (id serial primary key, data text); -- fail - can't add to for all tables publication ALTER PUBLICATION testpub_foralltables ADD TABLE testpub_tbl2; ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES -DETAIL: Tables cannot be added to or dropped from FOR ALL TABLES publications. +DETAIL: Tables or sequences cannot be added to or dropped from FOR ALL TABLES publications. -- fail - can't drop from all tables publication ALTER PUBLICATION testpub_foralltables DROP TABLE testpub_tbl2; ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES -DETAIL: Tables cannot be added to or dropped from FOR ALL TABLES publications. +DETAIL: Tables or sequences cannot be added to or dropped from FOR ALL TABLES publications. -- fail - can't add to for all tables publication ALTER PUBLICATION testpub_foralltables SET TABLE pub_test.testpub_nopk; ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES -DETAIL: Tables cannot be added to or dropped from FOR ALL TABLES publications. +DETAIL: Tables or sequences cannot be added to or dropped from FOR ALL TABLES publications. -- fail - can't add schema to 'FOR ALL TABLES' publication ALTER PUBLICATION testpub_foralltables ADD TABLES IN SCHEMA pub_test; ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES @@ -97,10 +97,10 @@ RESET client_min_messages; -- should be able to add schema to 'FOR TABLE' publication ALTER PUBLICATION testpub_fortable ADD TABLES IN SCHEMA pub_test; \dRp+ testpub_fortable - Publication testpub_fortable - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub_fortable + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "public.testpub_tbl1" Tables from schemas: @@ -109,20 +109,20 @@ Tables from schemas: -- should be able to drop schema from 'FOR TABLE' publication ALTER PUBLICATION testpub_fortable DROP TABLES IN SCHEMA pub_test; \dRp+ testpub_fortable - Publication testpub_fortable - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub_fortable + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "public.testpub_tbl1" -- should be able to set schema to 'FOR TABLE' publication ALTER PUBLICATION testpub_fortable SET TABLES IN SCHEMA pub_test; \dRp+ testpub_fortable - Publication testpub_fortable - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub_fortable + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test" @@ -133,10 +133,10 @@ CREATE PUBLICATION testpub_forschema FOR TABLES IN SCHEMA pub_test; CREATE PUBLICATION testpub_for_tbl_schema FOR TABLES IN SCHEMA pub_test, TABLE pub_test.testpub_nopk; RESET client_min_messages; \dRp+ testpub_for_tbl_schema - Publication testpub_for_tbl_schema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub_for_tbl_schema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "pub_test.testpub_nopk" Tables from schemas: @@ -154,10 +154,10 @@ LINE 1: ...CATION testpub_parsertst FOR TABLES IN SCHEMA foo, test.foo; -- should be able to add a table of the same schema to the schema publication ALTER PUBLICATION testpub_forschema ADD TABLE pub_test.testpub_nopk; \dRp+ testpub_forschema - Publication testpub_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "pub_test.testpub_nopk" Tables from schemas: @@ -166,10 +166,10 @@ Tables from schemas: -- should be able to drop the table ALTER PUBLICATION testpub_forschema DROP TABLE pub_test.testpub_nopk; \dRp+ testpub_forschema - Publication testpub_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test" @@ -180,10 +180,10 @@ ERROR: relation "testpub_nopk" is not part of the publication -- should be able to set table to schema publication ALTER PUBLICATION testpub_forschema SET TABLE pub_test.testpub_nopk; \dRp+ testpub_forschema - Publication testpub_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "pub_test.testpub_nopk" @@ -207,10 +207,10 @@ Not-null constraints: "testpub_tbl2_id_not_null" NOT NULL "id" \dRp+ testpub_foralltables - Publication testpub_foralltables - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | t | t | t | f | f | none | f + Publication testpub_foralltables + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | t | f | t | t | f | f | none | f (1 row) DROP TABLE testpub_tbl2; @@ -222,24 +222,110 @@ CREATE PUBLICATION testpub3 FOR TABLE testpub_tbl3; CREATE PUBLICATION testpub4 FOR TABLE ONLY testpub_tbl3; RESET client_min_messages; \dRp+ testpub3 - Publication testpub3 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub3 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "public.testpub_tbl3" "public.testpub_tbl3a" \dRp+ testpub4 - Publication testpub4 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub4 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "public.testpub_tbl3" DROP TABLE testpub_tbl3, testpub_tbl3a; DROP PUBLICATION testpub3, testpub4; +--- Tests for publications with SEQUENCES +CREATE SEQUENCE regress_pub_seq0; +CREATE SEQUENCE pub_test.regress_pub_seq1; +-- FOR ALL SEQUENCES +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION regress_pub_forallsequences1 FOR ALL SEQUENCES; +RESET client_min_messages; +SELECT pubname, puballtables, puballsequences FROM pg_publication WHERE pubname = 'regress_pub_forallsequences1'; + pubname | puballtables | puballsequences +------------------------------+--------------+----------------- + regress_pub_forallsequences1 | f | t +(1 row) + +\d+ regress_pub_seq0 + Sequence "public.regress_pub_seq0" + Type | Start | Minimum | Maximum | Increment | Cycles? | Cache +--------+-------+---------+---------------------+-----------+---------+------- + bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1 +Publications: + "regress_pub_forallsequences1" + +\dRp+ regress_pub_forallsequences1 + Publication regress_pub_forallsequences1 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | t | t | t | t | t | none | f +(1 row) + +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION regress_pub_forallsequences2 FOR ALL SEQUENCES; +RESET client_min_messages; +-- check that describe sequence lists both publications the sequence belongs to +\d+ pub_test.regress_pub_seq1 + Sequence "pub_test.regress_pub_seq1" + Type | Start | Minimum | Maximum | Increment | Cycles? | Cache +--------+-------+---------+---------------------+-----------+---------+------- + bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1 +Publications: + "regress_pub_forallsequences1" + "regress_pub_forallsequences2" + +--- Specifying both ALL TABLES and ALL SEQUENCES +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION regress_pub_for_allsequences_alltables FOR ALL SEQUENCES, ALL TABLES; +-- Specifying WITH clause in an ALL SEQUENCES publication will emit a NOTICE. +SET client_min_messages = 'NOTICE'; +CREATE PUBLICATION regress_pub_for_allsequences_alltables_withclause FOR ALL SEQUENCES, ALL TABLES WITH (publish = 'insert'); +NOTICE: publication parameters are not applicable to sequence synchronization and will be ignored for sequences +WARNING: "wal_level" is insufficient to publish logical changes +HINT: Set "wal_level" to "logical" before creating subscriptions. +CREATE PUBLICATION regress_pub_for_allsequences_withclause FOR ALL SEQUENCES WITH (publish_generated_columns = 'stored'); +NOTICE: publication parameters are not applicable to sequence synchronization and will be ignored for sequences +WARNING: "wal_level" is insufficient to publish logical changes +HINT: Set "wal_level" to "logical" before creating subscriptions. +RESET client_min_messages; +SELECT pubname, puballtables, puballsequences FROM pg_publication WHERE pubname = 'regress_pub_for_allsequences_alltables'; + pubname | puballtables | puballsequences +----------------------------------------+--------------+----------------- + regress_pub_for_allsequences_alltables | t | t +(1 row) + +\dRp+ regress_pub_for_allsequences_alltables + Publication regress_pub_for_allsequences_alltables + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | t | t | t | t | t | t | none | f +(1 row) + +DROP SEQUENCE regress_pub_seq0, pub_test.regress_pub_seq1; +DROP PUBLICATION regress_pub_forallsequences1; +DROP PUBLICATION regress_pub_forallsequences2; +DROP PUBLICATION regress_pub_for_allsequences_alltables; +DROP PUBLICATION regress_pub_for_allsequences_alltables_withclause; +DROP PUBLICATION regress_pub_for_allsequences_withclause; +-- fail - Specifying ALL TABLES more than once +CREATE PUBLICATION regress_pub_for_allsequences_alltables FOR ALL SEQUENCES, ALL TABLES, ALL TABLES; +ERROR: invalid publication object list +LINE 1: ...equences_alltables FOR ALL SEQUENCES, ALL TABLES, ALL TABLES... + ^ +DETAIL: ALL TABLES can be specified only once. +-- fail - Specifying ALL SEQUENCES more than once +CREATE PUBLICATION regress_pub_for_allsequences_alltables FOR ALL SEQUENCES, ALL TABLES, ALL SEQUENCES; +ERROR: invalid publication object list +LINE 1: ...equences_alltables FOR ALL SEQUENCES, ALL TABLES, ALL SEQUEN... + ^ +DETAIL: ALL SEQUENCES can be specified only once. -- Tests for partitioned tables SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub_forparted; @@ -255,10 +341,10 @@ UPDATE testpub_parted1 SET a = 1; -- only parent is listed as being in publication, not the partition ALTER PUBLICATION testpub_forparted ADD TABLE testpub_parted; \dRp+ testpub_forparted - Publication testpub_forparted - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub_forparted + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "public.testpub_parted" @@ -273,10 +359,10 @@ ALTER TABLE testpub_parted DETACH PARTITION testpub_parted1; UPDATE testpub_parted1 SET a = 1; ALTER PUBLICATION testpub_forparted SET (publish_via_partition_root = true); \dRp+ testpub_forparted - Publication testpub_forparted - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | t + Publication testpub_forparted + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | t Tables: "public.testpub_parted" @@ -305,10 +391,10 @@ SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub5 FOR TABLE testpub_rf_tbl1, testpub_rf_tbl2 WHERE (c <> 'test' AND d < 5) WITH (publish = 'insert'); RESET client_min_messages; \dRp+ testpub5 - Publication testpub5 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | f | f | f | none | f + Publication testpub5 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | f | f | f | none | f Tables: "public.testpub_rf_tbl1" "public.testpub_rf_tbl2" WHERE ((c <> 'test'::text) AND (d < 5)) @@ -321,10 +407,10 @@ Tables: ALTER PUBLICATION testpub5 ADD TABLE testpub_rf_tbl3 WHERE (e > 1000 AND e < 2000); \dRp+ testpub5 - Publication testpub5 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | f | f | f | none | f + Publication testpub5 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | f | f | f | none | f Tables: "public.testpub_rf_tbl1" "public.testpub_rf_tbl2" WHERE ((c <> 'test'::text) AND (d < 5)) @@ -340,10 +426,10 @@ Publications: ALTER PUBLICATION testpub5 DROP TABLE testpub_rf_tbl2; \dRp+ testpub5 - Publication testpub5 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | f | f | f | none | f + Publication testpub5 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | f | f | f | none | f Tables: "public.testpub_rf_tbl1" "public.testpub_rf_tbl3" WHERE ((e > 1000) AND (e < 2000)) @@ -351,10 +437,10 @@ Tables: -- remove testpub_rf_tbl1 and add testpub_rf_tbl3 again (another WHERE expression) ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl3 WHERE (e > 300 AND e < 500); \dRp+ testpub5 - Publication testpub5 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | f | f | f | none | f + Publication testpub5 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | f | f | f | none | f Tables: "public.testpub_rf_tbl3" WHERE ((e > 300) AND (e < 500)) @@ -387,10 +473,10 @@ SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub_syntax1 FOR TABLE testpub_rf_tbl1, ONLY testpub_rf_tbl3 WHERE (e < 999) WITH (publish = 'insert'); RESET client_min_messages; \dRp+ testpub_syntax1 - Publication testpub_syntax1 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | f | f | f | none | f + Publication testpub_syntax1 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | f | f | f | none | f Tables: "public.testpub_rf_tbl1" "public.testpub_rf_tbl3" WHERE (e < 999) @@ -400,10 +486,10 @@ SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub_syntax2 FOR TABLE testpub_rf_tbl1, testpub_rf_schema1.testpub_rf_tbl5 WHERE (h < 999) WITH (publish = 'insert'); RESET client_min_messages; \dRp+ testpub_syntax2 - Publication testpub_syntax2 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | f | f | f | none | f + Publication testpub_syntax2 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | f | f | f | none | f Tables: "public.testpub_rf_tbl1" "testpub_rf_schema1.testpub_rf_tbl5" WHERE (h < 999) @@ -518,10 +604,10 @@ CREATE PUBLICATION testpub6 FOR TABLES IN SCHEMA testpub_rf_schema2; ALTER PUBLICATION testpub6 SET TABLES IN SCHEMA testpub_rf_schema2, TABLE testpub_rf_schema2.testpub_rf_tbl6 WHERE (i < 99); RESET client_min_messages; \dRp+ testpub6 - Publication testpub6 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub6 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "testpub_rf_schema2.testpub_rf_tbl6" WHERE (i < 99) Tables from schemas: @@ -813,10 +899,10 @@ CREATE PUBLICATION testpub_table_ins WITH (publish = 'insert, truncate'); RESET client_min_messages; ALTER PUBLICATION testpub_table_ins ADD TABLE testpub_tbl5 (a); -- ok \dRp+ testpub_table_ins - Publication testpub_table_ins - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | f | f | t | none | f + Publication testpub_table_ins + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | f | f | t | none | f Tables: "public.testpub_tbl5" (a) @@ -1006,10 +1092,10 @@ CREATE TABLE testpub_tbl_both_filters (a int, b int, c int, PRIMARY KEY (a,c)); ALTER TABLE testpub_tbl_both_filters REPLICA IDENTITY USING INDEX testpub_tbl_both_filters_pkey; ALTER PUBLICATION testpub_both_filters ADD TABLE testpub_tbl_both_filters (a,c) WHERE (c != 1); \dRp+ testpub_both_filters - Publication testpub_both_filters - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub_both_filters + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "public.testpub_tbl_both_filters" (a, c) WHERE (c <> 1) @@ -1217,10 +1303,10 @@ ERROR: relation "testpub_tbl1" is already member of publication "testpub_fortbl CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1; ERROR: publication "testpub_fortbl" already exists \dRp+ testpub_fortbl - Publication testpub_fortbl - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub_fortbl + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "pub_test.testpub_nopk" "public.testpub_tbl1" @@ -1260,10 +1346,10 @@ Not-null constraints: "testpub_tbl1_id_not_null" NOT NULL "id" \dRp+ testpub_default - Publication testpub_default - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | f | none | f + Publication testpub_default + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | f | none | f Tables: "pub_test.testpub_nopk" "public.testpub_tbl1" @@ -1334,7 +1420,7 @@ SET ROLE regress_publication_user3; -- fail - new owner must be superuser ALTER PUBLICATION testpub4 owner to regress_publication_user2; -- fail ERROR: permission denied to change owner of publication "testpub4" -HINT: The owner of a FOR TABLES IN SCHEMA publication must be a superuser. +HINT: The owner of a FOR ALL TABLES or ALL SEQUENCES or TABLES IN SCHEMA publication must be a superuser. ALTER PUBLICATION testpub4 owner to regress_publication_user; -- ok SET ROLE regress_publication_user; DROP PUBLICATION testpub4; @@ -1343,10 +1429,10 @@ REVOKE CREATE ON DATABASE regression FROM regress_publication_user2; DROP TABLE testpub_parted; DROP TABLE testpub_tbl1; \dRp+ testpub_default - Publication testpub_default - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | f | none | f + Publication testpub_default + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | f | none | f (1 row) -- fail - must be owner of publication @@ -1356,20 +1442,20 @@ ERROR: must be owner of publication testpub_default RESET ROLE; ALTER PUBLICATION testpub_default RENAME TO testpub_foo; \dRp testpub_foo - List of publications - Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root --------------+--------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - testpub_foo | regress_publication_user | f | t | t | t | f | none | f + List of publications + Name | Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +-------------+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + testpub_foo | regress_publication_user | f | f | t | t | t | f | none | f (1 row) -- rename back to keep the rest simple ALTER PUBLICATION testpub_foo RENAME TO testpub_default; ALTER PUBLICATION testpub_default OWNER TO regress_publication_user2; \dRp testpub_default - List of publications - Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ------------------+---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - testpub_default | regress_publication_user2 | f | t | t | t | f | none | f + List of publications + Name | Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +-----------------+---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + testpub_default | regress_publication_user2 | f | f | t | t | t | f | none | f (1 row) -- adding schemas and tables @@ -1385,19 +1471,19 @@ CREATE TABLE "CURRENT_SCHEMA"."CURRENT_SCHEMA"(id int); SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub1_forschema FOR TABLES IN SCHEMA pub_test1; \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1" CREATE PUBLICATION testpub2_forschema FOR TABLES IN SCHEMA pub_test1, pub_test2, pub_test3; \dRp+ testpub2_forschema - Publication testpub2_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub2_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1" "pub_test2" @@ -1411,44 +1497,44 @@ CREATE PUBLICATION testpub6_forschema FOR TABLES IN SCHEMA "CURRENT_SCHEMA", CUR CREATE PUBLICATION testpub_fortable FOR TABLE "CURRENT_SCHEMA"."CURRENT_SCHEMA"; RESET client_min_messages; \dRp+ testpub3_forschema - Publication testpub3_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub3_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "public" \dRp+ testpub4_forschema - Publication testpub4_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub4_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "CURRENT_SCHEMA" \dRp+ testpub5_forschema - Publication testpub5_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub5_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "CURRENT_SCHEMA" "public" \dRp+ testpub6_forschema - Publication testpub6_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub6_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "CURRENT_SCHEMA" "public" \dRp+ testpub_fortable - Publication testpub_fortable - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub_fortable + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "CURRENT_SCHEMA.CURRENT_SCHEMA" @@ -1482,10 +1568,10 @@ ERROR: schema "testpub_view" does not exist -- dropping the schema should reflect the change in publication DROP SCHEMA pub_test3; \dRp+ testpub2_forschema - Publication testpub2_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub2_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1" "pub_test2" @@ -1493,20 +1579,20 @@ Tables from schemas: -- renaming the schema should reflect the change in publication ALTER SCHEMA pub_test1 RENAME to pub_test1_renamed; \dRp+ testpub2_forschema - Publication testpub2_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub2_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1_renamed" "pub_test2" ALTER SCHEMA pub_test1_renamed RENAME to pub_test1; \dRp+ testpub2_forschema - Publication testpub2_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub2_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1" "pub_test2" @@ -1514,10 +1600,10 @@ Tables from schemas: -- alter publication add schema ALTER PUBLICATION testpub1_forschema ADD TABLES IN SCHEMA pub_test2; \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1" "pub_test2" @@ -1526,10 +1612,10 @@ Tables from schemas: ALTER PUBLICATION testpub1_forschema ADD TABLES IN SCHEMA non_existent_schema; ERROR: schema "non_existent_schema" does not exist \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1" "pub_test2" @@ -1538,10 +1624,10 @@ Tables from schemas: ALTER PUBLICATION testpub1_forschema ADD TABLES IN SCHEMA pub_test1; ERROR: schema "pub_test1" is already member of publication "testpub1_forschema" \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1" "pub_test2" @@ -1549,10 +1635,10 @@ Tables from schemas: -- alter publication drop schema ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA pub_test2; \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1" @@ -1560,10 +1646,10 @@ Tables from schemas: ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA pub_test2; ERROR: tables from schema "pub_test2" are not part of the publication \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1" @@ -1571,29 +1657,29 @@ Tables from schemas: ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA non_existent_schema; ERROR: schema "non_existent_schema" does not exist \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1" -- drop all schemas ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA pub_test1; \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f (1 row) -- alter publication set multiple schema ALTER PUBLICATION testpub1_forschema SET TABLES IN SCHEMA pub_test1, pub_test2; \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1" "pub_test2" @@ -1602,10 +1688,10 @@ Tables from schemas: ALTER PUBLICATION testpub1_forschema SET TABLES IN SCHEMA non_existent_schema; ERROR: schema "non_existent_schema" does not exist \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1" "pub_test2" @@ -1614,10 +1700,10 @@ Tables from schemas: -- removing the duplicate schemas ALTER PUBLICATION testpub1_forschema SET TABLES IN SCHEMA pub_test1, pub_test1; \dRp+ testpub1_forschema - Publication testpub1_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub1_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1" @@ -1696,18 +1782,18 @@ SET client_min_messages = 'ERROR'; CREATE PUBLICATION testpub3_forschema; RESET client_min_messages; \dRp+ testpub3_forschema - Publication testpub3_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub3_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f (1 row) ALTER PUBLICATION testpub3_forschema SET TABLES IN SCHEMA pub_test1; \dRp+ testpub3_forschema - Publication testpub3_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub3_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables from schemas: "pub_test1" @@ -1717,20 +1803,20 @@ CREATE PUBLICATION testpub_forschema_fortable FOR TABLES IN SCHEMA pub_test1, TA CREATE PUBLICATION testpub_fortable_forschema FOR TABLE pub_test2.tbl1, TABLES IN SCHEMA pub_test1; RESET client_min_messages; \dRp+ testpub_forschema_fortable - Publication testpub_forschema_fortable - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub_forschema_fortable + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "pub_test2.tbl1" Tables from schemas: "pub_test1" \dRp+ testpub_fortable_forschema - Publication testpub_fortable_forschema - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication testpub_fortable_forschema + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "pub_test2.tbl1" Tables from schemas: @@ -1851,18 +1937,18 @@ DROP SCHEMA sch2 cascade; SET client_min_messages = 'ERROR'; CREATE PUBLICATION pub1 FOR ALL TABLES WITH (publish_generated_columns = stored); \dRp+ pub1 - Publication pub1 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | t | t | t | t | t | stored | f + Publication pub1 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | t | f | t | t | t | t | stored | f (1 row) CREATE PUBLICATION pub2 FOR ALL TABLES WITH (publish_generated_columns = none); \dRp+ pub2 - Publication pub2 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | t | t | t | t | t | none | f + Publication pub2 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | t | f | t | t | t | t | none | f (1 row) DROP PUBLICATION pub1; @@ -1873,50 +1959,50 @@ CREATE TABLE gencols (a int, gen1 int GENERATED ALWAYS AS (a * 2) STORED); -- Generated columns in column list, when 'publish_generated_columns'='none' CREATE PUBLICATION pub1 FOR table gencols(a, gen1) WITH (publish_generated_columns = none); \dRp+ pub1 - Publication pub1 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication pub1 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "public.gencols" (a, gen1) -- Generated columns in column list, when 'publish_generated_columns'='stored' CREATE PUBLICATION pub2 FOR table gencols(a, gen1) WITH (publish_generated_columns = stored); \dRp+ pub2 - Publication pub2 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | stored | f + Publication pub2 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | stored | f Tables: "public.gencols" (a, gen1) -- Generated columns in column list, then set 'publish_generated_columns'='none' ALTER PUBLICATION pub2 SET (publish_generated_columns = none); \dRp+ pub2 - Publication pub2 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication pub2 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "public.gencols" (a, gen1) -- Remove generated columns from column list, when 'publish_generated_columns'='none' ALTER PUBLICATION pub2 SET TABLE gencols(a); \dRp+ pub2 - Publication pub2 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication pub2 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "public.gencols" (a) -- Add generated columns in column list, when 'publish_generated_columns'='none' ALTER PUBLICATION pub2 SET TABLE gencols(a, gen1); \dRp+ pub2 - Publication pub2 - Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root ---------------------------+------------+---------+---------+---------+-----------+-------------------+---------- - regress_publication_user | f | t | t | t | t | none | f + Publication pub2 + Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root +--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+---------- + regress_publication_user | f | f | t | t | t | t | none | f Tables: "public.gencols" (a, gen1) diff --git a/src/test/regress/expected/returning.out b/src/test/regress/expected/returning.out index 341b689f766..d02c2ceab53 100644 --- a/src/test/regress/expected/returning.out +++ b/src/test/regress/expected/returning.out @@ -548,16 +548,16 @@ INSERT INTO foo VALUES (5, 'subquery test') QUERY PLAN --------------------------------------------------------------- Insert on pg_temp.foo - Output: (SubPlan 1), (SubPlan 2) + Output: (SubPlan expr_1), (SubPlan expr_2) -> Result Output: 5, 'subquery test'::text, 42, '99'::bigint - SubPlan 1 + SubPlan expr_1 -> Aggregate Output: max((old.f4 + x.x)) -> Function Scan on pg_catalog.generate_series x Output: x.x Function Call: generate_series(1, 10) - SubPlan 2 + SubPlan expr_2 -> Aggregate Output: max((new.f4 + x_1.x)) -> Function Scan on pg_catalog.generate_series x_1 @@ -578,26 +578,26 @@ UPDATE foo SET f4 = 100 WHERE f1 = 5 RETURNING (SELECT old.f4 = new.f4), (SELECT max(old.f4 + x) FROM generate_series(1, 10) x) old_max, (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max; - QUERY PLAN ---------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------- Update on pg_temp.foo - Output: (SubPlan 1), (SubPlan 2), (SubPlan 3) + Output: (SubPlan expr_1), (SubPlan expr_2), (SubPlan expr_3) Update on pg_temp.foo foo_1 -> Result Output: '100'::bigint, foo_1.tableoid, foo_1.ctid -> Seq Scan on pg_temp.foo foo_1 Output: foo_1.tableoid, foo_1.ctid Filter: (foo_1.f1 = 5) - SubPlan 1 + SubPlan expr_1 -> Result Output: (old.f4 = new.f4) - SubPlan 2 + SubPlan expr_2 -> Aggregate Output: max((old.f4 + x.x)) -> Function Scan on pg_catalog.generate_series x Output: x.x Function Call: generate_series(1, 10) - SubPlan 3 + SubPlan expr_3 -> Aggregate Output: max((new.f4 + x_1.x)) -> Function Scan on pg_catalog.generate_series x_1 @@ -621,18 +621,18 @@ DELETE FROM foo WHERE f1 = 5 QUERY PLAN --------------------------------------------------------------- Delete on pg_temp.foo - Output: (SubPlan 1), (SubPlan 2) + Output: (SubPlan expr_1), (SubPlan expr_2) Delete on pg_temp.foo foo_1 -> Seq Scan on pg_temp.foo foo_1 Output: foo_1.tableoid, foo_1.ctid Filter: (foo_1.f1 = 5) - SubPlan 1 + SubPlan expr_1 -> Aggregate Output: max((old.f4 + x.x)) -> Function Scan on pg_catalog.generate_series x Output: x.x Function Call: generate_series(1, 10) - SubPlan 2 + SubPlan expr_2 -> Aggregate Output: max((new.f4 + x_1.x)) -> Function Scan on pg_catalog.generate_series x_1 diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index 7153ebba521..5a172c5d91c 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -265,27 +265,27 @@ NOTICE: f_leak => awesome science fiction (5 rows) EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle); - QUERY PLAN --------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------- Seq Scan on document - Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle)) - InitPlan 1 + Filter: ((dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) + InitPlan expr_1 -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser = CURRENT_USER) (5 rows) EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle); - QUERY PLAN --------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------- Hash Join Hash Cond: (category.cid = document.cid) - InitPlan 1 + InitPlan expr_1 -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser = CURRENT_USER) -> Seq Scan on category -> Hash -> Seq Scan on document - Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle)) + Filter: ((dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) (9 rows) -- viewpoint from regress_rls_dave @@ -329,27 +329,27 @@ NOTICE: f_leak => awesome technology book (7 rows) EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle); - QUERY PLAN -------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------ Seq Scan on document - Filter: ((cid <> 44) AND (cid <> 44) AND (cid < 50) AND (dlevel <= (InitPlan 1).col1) AND f_leak(dtitle)) - InitPlan 1 + Filter: ((cid <> 44) AND (cid <> 44) AND (cid < 50) AND (dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) + InitPlan expr_1 -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser = CURRENT_USER) (5 rows) EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle); - QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------ Hash Join Hash Cond: (category.cid = document.cid) - InitPlan 1 + InitPlan expr_1 -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser = CURRENT_USER) -> Seq Scan on category -> Hash -> Seq Scan on document - Filter: ((cid <> 44) AND (cid <> 44) AND (cid < 50) AND (dlevel <= (InitPlan 1).col1) AND f_leak(dtitle)) + Filter: ((cid <> 44) AND (cid <> 44) AND (cid < 50) AND (dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) (9 rows) -- 44 would technically fail for both p2r and p1r, but we should get an error @@ -987,18 +987,18 @@ NOTICE: f_leak => my first satire (4 rows) EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); - QUERY PLAN --------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------- Append - InitPlan 1 + InitPlan expr_1 -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser = CURRENT_USER) -> Seq Scan on part_document_fiction part_document_1 - Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle)) + Filter: ((dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) -> Seq Scan on part_document_satire part_document_2 - Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle)) + Filter: ((dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) -> Seq Scan on part_document_nonfiction part_document_3 - Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle)) + Filter: ((dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) (10 rows) -- viewpoint from regress_rls_carol @@ -1029,18 +1029,18 @@ NOTICE: f_leak => awesome technology book (10 rows) EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); - QUERY PLAN --------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------- Append - InitPlan 1 + InitPlan expr_1 -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser = CURRENT_USER) -> Seq Scan on part_document_fiction part_document_1 - Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle)) + Filter: ((dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) -> Seq Scan on part_document_satire part_document_2 - Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle)) + Filter: ((dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) -> Seq Scan on part_document_nonfiction part_document_3 - Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle)) + Filter: ((dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) (10 rows) -- viewpoint from regress_rls_dave @@ -1059,11 +1059,11 @@ NOTICE: f_leak => awesome science fiction (4 rows) EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); - QUERY PLAN ------------------------------------------------------------------------------ + QUERY PLAN +---------------------------------------------------------------------------------- Seq Scan on part_document_fiction part_document - Filter: ((cid < 55) AND (dlevel <= (InitPlan 1).col1) AND f_leak(dtitle)) - InitPlan 1 + Filter: ((cid < 55) AND (dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) + InitPlan expr_1 -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser = CURRENT_USER) (5 rows) @@ -1137,11 +1137,11 @@ NOTICE: f_leak => awesome science fiction (4 rows) EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); - QUERY PLAN ------------------------------------------------------------------------------ + QUERY PLAN +---------------------------------------------------------------------------------- Seq Scan on part_document_fiction part_document - Filter: ((cid < 55) AND (dlevel <= (InitPlan 1).col1) AND f_leak(dtitle)) - InitPlan 1 + Filter: ((cid < 55) AND (dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) + InitPlan expr_1 -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser = CURRENT_USER) (5 rows) @@ -1176,18 +1176,18 @@ NOTICE: f_leak => awesome technology book (11 rows) EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); - QUERY PLAN --------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------- Append - InitPlan 1 + InitPlan expr_1 -> Index Scan using uaccount_pkey on uaccount Index Cond: (pguser = CURRENT_USER) -> Seq Scan on part_document_fiction part_document_1 - Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle)) + Filter: ((dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) -> Seq Scan on part_document_satire part_document_2 - Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle)) + Filter: ((dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) -> Seq Scan on part_document_nonfiction part_document_3 - Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle)) + Filter: ((dlevel <= (InitPlan expr_1).col1) AND f_leak(dtitle)) (10 rows) -- only owner can change policies @@ -1437,11 +1437,11 @@ NOTICE: f_leak => 03b26944890929ff751653acb2f2af79 (1 row) EXPLAIN (COSTS OFF) SELECT * FROM only s1 WHERE f_leak(b); - QUERY PLAN ---------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------- Seq Scan on s1 - Filter: ((ANY (a = (hashed SubPlan 1).col1)) AND f_leak(b)) - SubPlan 1 + Filter: ((ANY (a = (hashed SubPlan any_1).col1)) AND f_leak(b)) + SubPlan any_1 -> Seq Scan on s2 Filter: (((x % 2) = 0) AND (y ~~ '%2f%'::text)) (5 rows) @@ -1457,11 +1457,11 @@ NOTICE: f_leak => 03b26944890929ff751653acb2f2af79 (1 row) EXPLAIN (COSTS OFF) SELECT * FROM s1 WHERE f_leak(b); - QUERY PLAN ---------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------- Seq Scan on s1 - Filter: ((ANY (a = (hashed SubPlan 1).col1)) AND f_leak(b)) - SubPlan 1 + Filter: ((ANY (a = (hashed SubPlan any_1).col1)) AND f_leak(b)) + SubPlan any_1 -> Seq Scan on s2 Filter: (((x % 2) = 0) AND (y ~~ '%af%'::text)) (5 rows) @@ -1477,11 +1477,11 @@ EXPLAIN (COSTS OFF) SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like ------------------------------------------------------------------------- Seq Scan on s2 Filter: (((x % 2) = 0) AND (y ~~ '%28%'::text)) - SubPlan 2 + SubPlan expr_1 -> Limit -> Seq Scan on s1 - Filter: (ANY (a = (hashed SubPlan 1).col1)) - SubPlan 1 + Filter: (ANY (a = (hashed SubPlan any_1).col1)) + SubPlan any_1 -> Seq Scan on s2 s2_1 Filter: (((x % 2) = 0) AND (y ~~ '%af%'::text)) (9 rows) @@ -2717,11 +2717,11 @@ NOTICE: f_leak => bbb (1 row) EXPLAIN (COSTS OFF) SELECT * FROM rls_view; - QUERY PLAN ---------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------- Seq Scan on z1 - Filter: ((NOT (ANY (a = (hashed SubPlan 1).col1))) AND ((a % 2) = 0) AND f_leak(b)) - SubPlan 1 + Filter: ((NOT (ANY (a = (hashed SubPlan any_1).col1))) AND ((a % 2) = 0) AND f_leak(b)) + SubPlan any_1 -> Seq Scan on z1_blacklist (4 rows) @@ -2735,11 +2735,11 @@ NOTICE: f_leak => bbb (1 row) EXPLAIN (COSTS OFF) SELECT * FROM rls_view; - QUERY PLAN ---------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------- Seq Scan on z1 - Filter: ((NOT (ANY (a = (hashed SubPlan 1).col1))) AND ((a % 2) = 0) AND f_leak(b)) - SubPlan 1 + Filter: ((NOT (ANY (a = (hashed SubPlan any_1).col1))) AND ((a % 2) = 0) AND f_leak(b)) + SubPlan any_1 -> Seq Scan on z1_blacklist (4 rows) @@ -2907,11 +2907,11 @@ NOTICE: f_leak => bbb (1 row) EXPLAIN (COSTS OFF) SELECT * FROM rls_view; - QUERY PLAN ---------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------- Seq Scan on z1 - Filter: ((NOT (ANY (a = (hashed SubPlan 1).col1))) AND ((a % 2) = 0) AND f_leak(b)) - SubPlan 1 + Filter: ((NOT (ANY (a = (hashed SubPlan any_1).col1))) AND ((a % 2) = 0) AND f_leak(b)) + SubPlan any_1 -> Seq Scan on z1_blacklist (4 rows) @@ -2933,11 +2933,11 @@ NOTICE: f_leak => aba (1 row) EXPLAIN (COSTS OFF) SELECT * FROM rls_view; - QUERY PLAN ---------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------- Seq Scan on z1 - Filter: ((NOT (ANY (a = (hashed SubPlan 1).col1))) AND ((a % 2) = 1) AND f_leak(b)) - SubPlan 1 + Filter: ((NOT (ANY (a = (hashed SubPlan any_1).col1))) AND ((a % 2) = 1) AND f_leak(b)) + SubPlan any_1 -> Seq Scan on z1_blacklist (4 rows) diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/expected/rowtypes.out index dd52d96d50f..677ad2ab9ad 100644 --- a/src/test/regress/expected/rowtypes.out +++ b/src/test/regress/expected/rowtypes.out @@ -1251,19 +1251,19 @@ with cte(c) as materialized (select row(1, 2)), select * from cte2 as t where (select * from (select c as c1) s where (select (c1).f1 > 0)) is not null; - QUERY PLAN ----------------------------------------------- + QUERY PLAN +--------------------------------------------------- CTE Scan on cte Output: cte.c - Filter: ((SubPlan 3) IS NOT NULL) + Filter: ((SubPlan expr_1) IS NOT NULL) CTE cte -> Result Output: '(1,2)'::record - SubPlan 3 + SubPlan expr_1 -> Result Output: cte.c - One-Time Filter: (InitPlan 2).col1 - InitPlan 2 + One-Time Filter: (InitPlan expr_2).col1 + InitPlan expr_2 -> Result Output: ((cte.c).f1 > 0) (13 rows) diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 35e8aad7701..16753b2e4c0 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1462,6 +1462,14 @@ pg_prepared_xacts| SELECT p.transaction, FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid) LEFT JOIN pg_authid u ON ((p.ownerid = u.oid))) LEFT JOIN pg_database d ON ((p.dbid = d.oid))); +pg_publication_sequences| SELECT p.pubname, + n.nspname AS schemaname, + c.relname AS sequencename + FROM pg_publication p, + LATERAL pg_get_publication_sequences((p.pubname)::text) gps(relid), + (pg_class c + JOIN pg_namespace n ON ((n.oid = c.relnamespace))) + WHERE (c.oid = gps.relid); pg_publication_tables| SELECT p.pubname, n.nspname AS schemaname, c.relname AS tablename, @@ -1798,7 +1806,8 @@ pg_stat_all_indexes| SELECT c.oid AS relid, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_lastscan(i.oid) AS last_idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, - pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch + pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch, + pg_stat_get_stat_reset_time(i.oid) AS stats_reset FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) @@ -1833,7 +1842,8 @@ pg_stat_all_tables| SELECT c.oid AS relid, pg_stat_get_total_vacuum_time(c.oid) AS total_vacuum_time, pg_stat_get_total_autovacuum_time(c.oid) AS total_autovacuum_time, pg_stat_get_total_analyze_time(c.oid) AS total_analyze_time, - pg_stat_get_total_autoanalyze_time(c.oid) AS total_autoanalyze_time + pg_stat_get_total_autoanalyze_time(c.oid) AS total_autoanalyze_time, + pg_stat_get_stat_reset_time(c.oid) AS stats_reset FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) @@ -2138,11 +2148,12 @@ pg_stat_replication_slots| SELECT s.slot_name, s.stream_txns, s.stream_count, s.stream_bytes, + s.mem_exceeded_count, s.total_txns, s.total_bytes, s.stats_reset FROM pg_replication_slots r, - LATERAL pg_stat_get_replication_slot((r.slot_name)::text) s(slot_name, spill_txns, spill_count, spill_bytes, stream_txns, stream_count, stream_bytes, total_txns, total_bytes, stats_reset) + LATERAL pg_stat_get_replication_slot((r.slot_name)::text) s(slot_name, spill_txns, spill_count, spill_bytes, stream_txns, stream_count, stream_bytes, mem_exceeded_count, total_txns, total_bytes, stats_reset) WHERE (r.datoid IS NOT NULL); pg_stat_slru| SELECT name, blks_zeroed, @@ -2200,7 +2211,8 @@ pg_stat_sys_indexes| SELECT relid, idx_scan, last_idx_scan, idx_tup_read, - idx_tup_fetch + idx_tup_fetch, + stats_reset FROM pg_stat_all_indexes WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text)); pg_stat_sys_tables| SELECT relid, @@ -2232,7 +2244,8 @@ pg_stat_sys_tables| SELECT relid, total_vacuum_time, total_autovacuum_time, total_analyze_time, - total_autoanalyze_time + total_autoanalyze_time, + stats_reset FROM pg_stat_all_tables WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text)); pg_stat_user_functions| SELECT p.oid AS funcid, @@ -2240,7 +2253,8 @@ pg_stat_user_functions| SELECT p.oid AS funcid, p.proname AS funcname, pg_stat_get_function_calls(p.oid) AS calls, pg_stat_get_function_total_time(p.oid) AS total_time, - pg_stat_get_function_self_time(p.oid) AS self_time + pg_stat_get_function_self_time(p.oid) AS self_time, + pg_stat_get_function_stat_reset_time(p.oid) AS stats_reset FROM (pg_proc p LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_function_calls(p.oid) IS NOT NULL)); @@ -2252,7 +2266,8 @@ pg_stat_user_indexes| SELECT relid, idx_scan, last_idx_scan, idx_tup_read, - idx_tup_fetch + idx_tup_fetch, + stats_reset FROM pg_stat_all_indexes WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text)); pg_stat_user_tables| SELECT relid, @@ -2284,7 +2299,8 @@ pg_stat_user_tables| SELECT relid, total_vacuum_time, total_autovacuum_time, total_analyze_time, - total_autoanalyze_time + total_autoanalyze_time, + stats_reset FROM pg_stat_all_tables WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text)); pg_stat_wal| SELECT wal_records, @@ -2370,7 +2386,8 @@ pg_statio_all_indexes| SELECT c.oid AS relid, c.relname, i.relname AS indexrelname, (pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)) AS idx_blks_read, - pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit + pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit, + pg_stat_get_stat_reset_time(i.oid) AS stats_reset FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) @@ -2394,7 +2411,8 @@ pg_statio_all_tables| SELECT c.oid AS relid, (pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid)) AS toast_blks_read, pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit, x.idx_blks_read AS tidx_blks_read, - x.idx_blks_hit AS tidx_blks_hit + x.idx_blks_hit AS tidx_blks_hit, + pg_stat_get_stat_reset_time(c.oid) AS stats_reset FROM ((((pg_class c LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) @@ -2413,7 +2431,8 @@ pg_statio_sys_indexes| SELECT relid, relname, indexrelname, idx_blks_read, - idx_blks_hit + idx_blks_hit, + stats_reset FROM pg_statio_all_indexes WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text)); pg_statio_sys_sequences| SELECT relid, @@ -2433,7 +2452,8 @@ pg_statio_sys_tables| SELECT relid, toast_blks_read, toast_blks_hit, tidx_blks_read, - tidx_blks_hit + tidx_blks_hit, + stats_reset FROM pg_statio_all_tables WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text)); pg_statio_user_indexes| SELECT relid, @@ -2442,7 +2462,8 @@ pg_statio_user_indexes| SELECT relid, relname, indexrelname, idx_blks_read, - idx_blks_hit + idx_blks_hit, + stats_reset FROM pg_statio_all_indexes WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text)); pg_statio_user_sequences| SELECT relid, @@ -2462,7 +2483,8 @@ pg_statio_user_tables| SELECT relid, toast_blks_read, toast_blks_hit, tidx_blks_read, - tidx_blks_hit + tidx_blks_hit, + stats_reset FROM pg_statio_all_tables WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text)); pg_stats| SELECT n.nspname AS schemaname, diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out index 0185ef661b1..933921d1860 100644 --- a/src/test/regress/expected/select_parallel.out +++ b/src/test/regress/expected/select_parallel.out @@ -156,9 +156,9 @@ explain (costs off) -> Parallel Append -> Parallel Seq Scan on part_pa_test_p1 pa2_1 -> Parallel Seq Scan on part_pa_test_p2 pa2_2 - SubPlan 2 + SubPlan expr_1 -> Result - SubPlan 1 + SubPlan expr_2 -> Append -> Seq Scan on part_pa_test_p1 pa1_1 Filter: (a = pa2.a) @@ -302,15 +302,15 @@ alter table tenk2 set (parallel_workers = 0); explain (costs off) select count(*) from tenk1 where (two, four) not in (select hundred, thousand from tenk2 where thousand > 100); - QUERY PLAN ----------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------ Finalize Aggregate -> Gather Workers Planned: 4 -> Partial Aggregate -> Parallel Seq Scan on tenk1 - Filter: (NOT (ANY ((two = (hashed SubPlan 1).col1) AND (four = (hashed SubPlan 1).col2)))) - SubPlan 1 + Filter: (NOT (ANY ((two = (hashed SubPlan any_1).col1) AND (four = (hashed SubPlan any_1).col2)))) + SubPlan any_1 -> Seq Scan on tenk2 Filter: (thousand > 100) (9 rows) @@ -326,11 +326,11 @@ select count(*) from tenk1 where (two, four) not in explain (costs off) select * from tenk1 where (unique1 + random())::integer not in (select ten from tenk2); - QUERY PLAN -------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------- Seq Scan on tenk1 - Filter: (NOT (ANY ((((unique1)::double precision + random()))::integer = (hashed SubPlan 1).col1))) - SubPlan 1 + Filter: (NOT (ANY ((((unique1)::double precision + random()))::integer = (hashed SubPlan any_1).col1))) + SubPlan any_1 -> Seq Scan on tenk2 (4 rows) @@ -343,10 +343,10 @@ alter table tenk2 set (parallel_workers = 2); explain (costs off) select count(*) from tenk1 where tenk1.unique1 = (Select max(tenk2.unique1) from tenk2); - QUERY PLAN ------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------- Aggregate - InitPlan 1 + InitPlan expr_1 -> Finalize Aggregate -> Gather Workers Planned: 2 @@ -355,7 +355,7 @@ explain (costs off) -> Gather Workers Planned: 4 -> Parallel Seq Scan on tenk1 - Filter: (unique1 = (InitPlan 1).col1) + Filter: (unique1 = (InitPlan expr_1).col1) (11 rows) select count(*) from tenk1 @@ -395,17 +395,17 @@ select count((unique1)) from tenk1 where hundred > 1; explain (costs off) select count((unique1)) from tenk1 where hundred = any ((select array_agg(i) from generate_series(1, 100, 15) i)::int[]); - QUERY PLAN ---------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------- Finalize Aggregate - InitPlan 1 + InitPlan expr_1 -> Aggregate -> Function Scan on generate_series i -> Gather Workers Planned: 4 -> Partial Aggregate -> Parallel Index Scan using tenk1_hundred on tenk1 - Index Cond: (hundred = ANY ((InitPlan 1).col1)) + Index Cond: (hundred = ANY ((InitPlan expr_1).col1)) (9 rows) select count((unique1)) from tenk1 @@ -1224,24 +1224,24 @@ ORDER BY 1; -> Append -> Gather Workers Planned: 4 - InitPlan 1 + InitPlan expr_1 -> Limit -> Gather Workers Planned: 4 -> Parallel Seq Scan on tenk1 tenk1_2 Filter: (fivethous = 1) -> Parallel Seq Scan on tenk1 - Filter: (fivethous = (InitPlan 1).col1) + Filter: (fivethous = (InitPlan expr_1).col1) -> Gather Workers Planned: 4 - InitPlan 2 + InitPlan expr_2 -> Limit -> Gather Workers Planned: 4 -> Parallel Seq Scan on tenk1 tenk1_3 Filter: (fivethous = 1) -> Parallel Seq Scan on tenk1 tenk1_1 - Filter: (fivethous = (InitPlan 2).col1) + Filter: (fivethous = (InitPlan expr_2).col1) (23 rows) -- test interaction with SRFs @@ -1254,10 +1254,10 @@ ORDER BY 1, 2, 3; EXPLAIN (VERBOSE, COSTS OFF) SELECT generate_series(1, two), array(select generate_series(1, two)) FROM tenk1 ORDER BY tenthous; - QUERY PLAN ---------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------- ProjectSet - Output: generate_series(1, tenk1.two), ARRAY(SubPlan 1), tenk1.tenthous + Output: generate_series(1, tenk1.two), ARRAY(SubPlan array_1), tenk1.tenthous -> Gather Merge Output: tenk1.two, tenk1.tenthous Workers Planned: 4 @@ -1268,7 +1268,7 @@ SELECT generate_series(1, two), array(select generate_series(1, two)) Sort Key: tenk1.tenthous -> Parallel Seq Scan on public.tenk1 Output: tenk1.tenthous, tenk1.two - SubPlan 1 + SubPlan array_1 -> ProjectSet Output: generate_series(1, tenk1.two) -> Result @@ -1333,11 +1333,11 @@ SELECT 1 FROM tenk1_vw_sec QUERY PLAN ------------------------------------------------------------------- Subquery Scan on tenk1_vw_sec - Filter: ((SubPlan 1) < 100) + Filter: ((SubPlan expr_1) < 100) -> Gather Workers Planned: 4 -> Parallel Index Only Scan using tenk1_unique1 on tenk1 - SubPlan 1 + SubPlan expr_1 -> Aggregate -> Seq Scan on int4_tbl Filter: (f1 < tenk1_vw_sec.unique1) diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out index 15925d99c8a..c4454e5b435 100644 --- a/src/test/regress/expected/sequence.out +++ b/src/test/regress/expected/sequence.out @@ -840,10 +840,10 @@ SELECT nextval('test_seq1'); (1 row) -- pg_get_sequence_data -SELECT * FROM pg_get_sequence_data('test_seq1'); - last_value | is_called -------------+----------- - 10 | t +SELECT last_value, is_called, page_lsn <= pg_current_wal_lsn() as lsn FROM pg_get_sequence_data('test_seq1'); + last_value | is_called | lsn +------------+-----------+----- + 10 | t | t (1 row) DROP SEQUENCE test_seq1; diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out index 625acf3019a..c7b9e575445 100644 --- a/src/test/regress/expected/sqljson.out +++ b/src/test/regress/expected/sqljson.out @@ -1093,8 +1093,8 @@ SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING QUERY PLAN --------------------------------------------------------------------- Result - Output: (InitPlan 1).col1 - InitPlan 1 + Output: (InitPlan expr_1).col1 + InitPlan expr_1 -> Aggregate Output: JSON_ARRAYAGG("*VALUES*".column1 RETURNING jsonb) -> Values Scan on "*VALUES*" diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out index 605f5070376..67e1860e984 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -666,16 +666,24 @@ SELECT last_seq_scan, last_idx_scan FROM pg_stat_all_tables WHERE relid = 'test_ (1 row) COMMIT; +SELECT stats_reset IS NOT NULL AS has_stats_reset + FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass; + has_stats_reset +----------------- + f +(1 row) + SELECT pg_stat_reset_single_table_counters('test_last_scan'::regclass); pg_stat_reset_single_table_counters ------------------------------------- (1 row) -SELECT seq_scan, idx_scan FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass; - seq_scan | idx_scan -----------+---------- - 0 | 0 +SELECT seq_scan, idx_scan, stats_reset IS NOT NULL AS has_stats_reset + FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass; + seq_scan | idx_scan | has_stats_reset +----------+----------+----------------- + 0 | 0 | t (1 row) -- ensure we start out with exactly one index and sequential scan @@ -850,6 +858,29 @@ FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass; 2 | t | 3 | t (1 row) +-- check the stats in pg_stat_all_indexes +SELECT idx_scan, :'test_last_idx' < last_idx_scan AS idx_ok, + stats_reset IS NOT NULL AS has_stats_reset + FROM pg_stat_all_indexes WHERE indexrelid = 'test_last_scan_pkey'::regclass; + idx_scan | idx_ok | has_stats_reset +----------+--------+----------------- + 3 | t | f +(1 row) + +-- check that the stats in pg_stat_all_indexes are reset +SELECT pg_stat_reset_single_table_counters('test_last_scan_pkey'::regclass); + pg_stat_reset_single_table_counters +------------------------------------- + +(1 row) + +SELECT idx_scan, stats_reset IS NOT NULL AS has_stats_reset + FROM pg_stat_all_indexes WHERE indexrelid = 'test_last_scan_pkey'::regclass; + idx_scan | has_stats_reset +----------+----------------- + 0 | t +(1 row) + ----- -- Test reset of some stats for shared table ----- diff --git a/src/test/regress/expected/strings.out b/src/test/regress/expected/strings.out index 691e475bce3..b9dc08d5f61 100644 --- a/src/test/regress/expected/strings.out +++ b/src/test/regress/expected/strings.out @@ -260,6 +260,64 @@ SELECT reverse('\xabcd'::bytea); \xcdab (1 row) +SELECT ('\x' || repeat(' ', 32))::bytea; + bytea +------- + \x +(1 row) + +SELECT ('\x' || repeat('!', 32))::bytea; +ERROR: invalid hexadecimal digit: "!" +SELECT ('\x' || repeat('/', 34))::bytea; +ERROR: invalid hexadecimal digit: "/" +SELECT ('\x' || repeat('0', 34))::bytea; + bytea +-------------------------------------- + \x0000000000000000000000000000000000 +(1 row) + +SELECT ('\x' || repeat('9', 32))::bytea; + bytea +------------------------------------ + \x99999999999999999999999999999999 +(1 row) + +SELECT ('\x' || repeat(':', 32))::bytea; +ERROR: invalid hexadecimal digit: ":" +SELECT ('\x' || repeat('@', 34))::bytea; +ERROR: invalid hexadecimal digit: "@" +SELECT ('\x' || repeat('A', 34))::bytea; + bytea +-------------------------------------- + \xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +(1 row) + +SELECT ('\x' || repeat('F', 32))::bytea; + bytea +------------------------------------ + \xffffffffffffffffffffffffffffffff +(1 row) + +SELECT ('\x' || repeat('G', 32))::bytea; +ERROR: invalid hexadecimal digit: "G" +SELECT ('\x' || repeat('`', 34))::bytea; +ERROR: invalid hexadecimal digit: "`" +SELECT ('\x' || repeat('a', 34))::bytea; + bytea +-------------------------------------- + \xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +(1 row) + +SELECT ('\x' || repeat('f', 32))::bytea; + bytea +------------------------------------ + \xffffffffffffffffffffffffffffffff +(1 row) + +SELECT ('\x' || repeat('g', 32))::bytea; +ERROR: invalid hexadecimal digit: "g" +SELECT ('\x' || repeat('~', 34))::bytea; +ERROR: invalid hexadecimal digit: "~" SET bytea_output TO escape; SELECT E'\\xDeAdBeEf'::bytea; bytea diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index 47b2af7b2e1..cf6b32d1173 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -205,11 +205,11 @@ SELECT f1 AS "Correlated Field" -- Check ROWCOMPARE cases, both correlated and not EXPLAIN (VERBOSE, COSTS OFF) SELECT ROW(1, 2) = (SELECT f1, f2) AS eq FROM SUBSELECT_TBL; - QUERY PLAN ------------------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------------------------------------- Seq Scan on public.subselect_tbl - Output: (((1 = (SubPlan 1).col1) AND (2 = (SubPlan 1).col2))) - SubPlan 1 + Output: (((1 = (SubPlan rowcompare_1).col1) AND (2 = (SubPlan rowcompare_1).col2))) + SubPlan rowcompare_1 -> Result Output: subselect_tbl.f1, subselect_tbl.f2 (5 rows) @@ -229,11 +229,11 @@ SELECT ROW(1, 2) = (SELECT f1, f2) AS eq FROM SUBSELECT_TBL; EXPLAIN (VERBOSE, COSTS OFF) SELECT ROW(1, 2) = (SELECT 3, 4) AS eq FROM SUBSELECT_TBL; - QUERY PLAN ------------------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------------------------------------- Seq Scan on public.subselect_tbl - Output: ((1 = (InitPlan 1).col1) AND (2 = (InitPlan 1).col2)) - InitPlan 1 + Output: ((1 = (InitPlan rowcompare_1).col1) AND (2 = (InitPlan rowcompare_1).col2)) + InitPlan rowcompare_1 -> Result Output: 3, 4 (5 rows) @@ -375,18 +375,18 @@ explain (verbose, costs off) select '42' union all select 43; -- check materialization of an initplan reference (bug #14524) explain (verbose, costs off) select 1 = all (select (select 1)); - QUERY PLAN -------------------------------------------- + QUERY PLAN +------------------------------------------------ Result - Output: (ALL (1 = (SubPlan 2).col1)) - SubPlan 2 + Output: (ALL (1 = (SubPlan all_1).col1)) + SubPlan all_1 -> Materialize - Output: ((InitPlan 1).col1) - InitPlan 1 + Output: ((InitPlan expr_1).col1) + InitPlan expr_1 -> Result Output: 1 -> Result - Output: (InitPlan 1).col1 + Output: (InitPlan expr_1).col1 (10 rows) select 1 = all (select (select 1)); @@ -428,8 +428,8 @@ select * from int4_tbl o where exists QUERY PLAN -------------------------------------- Seq Scan on int4_tbl o - Filter: EXISTS(SubPlan 1) - SubPlan 1 + Filter: EXISTS(SubPlan exists_1) + SubPlan exists_1 -> Limit -> Seq Scan on int4_tbl i Filter: (f1 = o.f1) @@ -988,7 +988,7 @@ select (1 = any(array_agg(f1))) = any (select false) from int4_tbl; ---------------------------- Aggregate -> Seq Scan on int4_tbl - SubPlan 1 + SubPlan any_1 -> Result (4 rows) @@ -1116,11 +1116,11 @@ select * from outer_text where (f1, f2) not in (select * from inner_text); -- explain (verbose, costs off) select 'foo'::text in (select 'bar'::name union all select 'bar'::name); - QUERY PLAN ---------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------- Result - Output: (ANY ('foo'::text = (hashed SubPlan 1).col1)) - SubPlan 1 + Output: (ANY ('foo'::text = (hashed SubPlan any_1).col1)) + SubPlan any_1 -> Append -> Result Output: 'bar'::name @@ -1140,11 +1140,11 @@ select 'foo'::text in (select 'bar'::name union all select 'bar'::name); -- explain (verbose, costs off) select row(row(row(1))) = any (select row(row(1))); - QUERY PLAN --------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------ Result - Output: (ANY ('("(1)")'::record = (SubPlan 1).col1)) - SubPlan 1 + Output: (ANY ('("(1)")'::record = (SubPlan any_1).col1)) + SubPlan any_1 -> Materialize Output: '("(1)")'::record -> Result @@ -1184,11 +1184,11 @@ language sql as 'select $1::text = $2'; create operator = (procedure=bogus_int8_text_eq, leftarg=int8, rightarg=text); explain (costs off) select * from int8_tbl where q1 in (select c1 from inner_text); - QUERY PLAN --------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------ Seq Scan on int8_tbl - Filter: (ANY ((q1)::text = (hashed SubPlan 1).col1)) - SubPlan 1 + Filter: (ANY ((q1)::text = (hashed SubPlan any_1).col1)) + SubPlan any_1 -> Seq Scan on inner_text (4 rows) @@ -1205,11 +1205,11 @@ create or replace function bogus_int8_text_eq(int8, text) returns boolean language sql as 'select $1::text = $2 and $1::text = $2'; explain (costs off) select * from int8_tbl where q1 in (select c1 from inner_text); - QUERY PLAN ------------------------------------------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------------------------------------------------- Seq Scan on int8_tbl - Filter: (ANY (((q1)::text = (hashed SubPlan 1).col1) AND ((q1)::text = (hashed SubPlan 1).col1))) - SubPlan 1 + Filter: (ANY (((q1)::text = (hashed SubPlan any_1).col1) AND ((q1)::text = (hashed SubPlan any_1).col1))) + SubPlan any_1 -> Seq Scan on inner_text (4 rows) @@ -1226,11 +1226,11 @@ create or replace function bogus_int8_text_eq(int8, text) returns boolean language sql as 'select $2 = $1::text'; explain (costs off) select * from int8_tbl where q1 in (select c1 from inner_text); - QUERY PLAN -------------------------------------------------- + QUERY PLAN +----------------------------------------------------- Seq Scan on int8_tbl - Filter: (ANY ((SubPlan 1).col1 = (q1)::text)) - SubPlan 1 + Filter: (ANY ((SubPlan any_1).col1 = (q1)::text)) + SubPlan any_1 -> Materialize -> Seq Scan on inner_text (5 rows) @@ -1249,12 +1249,12 @@ rollback; -- to get rid of the bogus operator explain (costs off) select count(*) from tenk1 t where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0); - QUERY PLAN --------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------- Aggregate -> Seq Scan on tenk1 t - Filter: ((ANY (unique2 = (hashed SubPlan 2).col1)) OR (ten < 0)) - SubPlan 2 + Filter: ((ANY (unique2 = (hashed SubPlan exists_2).col1)) OR (ten < 0)) + SubPlan exists_2 -> Index Only Scan using tenk1_unique1 on tenk1 k (5 rows) @@ -1274,10 +1274,10 @@ where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0) Aggregate -> Bitmap Heap Scan on tenk1 t Recheck Cond: (thousand = 1) - Filter: (EXISTS(SubPlan 1) OR (ten < 0)) + Filter: (EXISTS(SubPlan exists_1) OR (ten < 0)) -> Bitmap Index Scan on tenk1_thous_tenthous Index Cond: (thousand = 1) - SubPlan 1 + SubPlan exists_1 -> Index Only Scan using tenk1_unique1 on tenk1 k Index Cond: (unique1 = t.unique2) (9 rows) @@ -1299,20 +1299,20 @@ analyze exists_tbl; explain (costs off) select * from exists_tbl t1 where (exists(select 1 from exists_tbl t2 where t1.c1 = t2.c2) or c3 < 0); - QUERY PLAN --------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------- Append -> Seq Scan on exists_tbl_null t1_1 - Filter: (EXISTS(SubPlan 1) OR (c3 < 0)) - SubPlan 1 + Filter: (EXISTS(SubPlan exists_1) OR (c3 < 0)) + SubPlan exists_1 -> Append -> Seq Scan on exists_tbl_null t2_1 Filter: (t1_1.c1 = c2) -> Seq Scan on exists_tbl_def t2_2 Filter: (t1_1.c1 = c2) -> Seq Scan on exists_tbl_def t1_2 - Filter: ((ANY (c1 = (hashed SubPlan 2).col1)) OR (c3 < 0)) - SubPlan 2 + Filter: ((ANY (c1 = (hashed SubPlan exists_2).col1)) OR (c3 < 0)) + SubPlan exists_2 -> Append -> Seq Scan on exists_tbl_null t2_4 -> Seq Scan on exists_tbl_def t2_5 @@ -1348,14 +1348,14 @@ where a.thousand = b.thousand explain (verbose, costs off) select x, x from (select (select now()) as x from (values(1),(2)) v(y)) ss; - QUERY PLAN ------------------------------------------------- + QUERY PLAN +---------------------------------------------------------- Values Scan on "*VALUES*" - Output: (InitPlan 1).col1, (InitPlan 2).col1 - InitPlan 1 + Output: (InitPlan expr_1).col1, (InitPlan expr_2).col1 + InitPlan expr_1 -> Result Output: now() - InitPlan 2 + InitPlan expr_2 -> Result Output: now() (8 rows) @@ -1363,13 +1363,13 @@ explain (verbose, costs off) explain (verbose, costs off) select x, x from (select (select random()) as x from (values(1),(2)) v(y)) ss; - QUERY PLAN ------------------------------------ + QUERY PLAN +---------------------------------------- Subquery Scan on ss Output: ss.x, ss.x -> Values Scan on "*VALUES*" - Output: (InitPlan 1).col1 - InitPlan 1 + Output: (InitPlan expr_1).col1 + InitPlan expr_1 -> Result Output: random() (7 rows) @@ -1380,12 +1380,12 @@ explain (verbose, costs off) QUERY PLAN ---------------------------------------------------------------------- Values Scan on "*VALUES*" - Output: (SubPlan 1), (SubPlan 2) - SubPlan 1 + Output: (SubPlan expr_1), (SubPlan expr_2) + SubPlan expr_1 -> Result Output: now() One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1) - SubPlan 2 + SubPlan expr_2 -> Result Output: now() One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1) @@ -1399,8 +1399,8 @@ explain (verbose, costs off) Subquery Scan on ss Output: ss.x, ss.x -> Values Scan on "*VALUES*" - Output: (SubPlan 1) - SubPlan 1 + Output: (SubPlan expr_1) + SubPlan expr_1 -> Result Output: random() One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1) @@ -1420,16 +1420,16 @@ where o.ten = 0; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate - Output: sum((((ANY (i.ten = (hashed SubPlan 1).col1))))::integer) + Output: sum((((ANY (i.ten = (hashed SubPlan any_1).col1))))::integer) -> Nested Loop - Output: ((ANY (i.ten = (hashed SubPlan 1).col1))) + Output: ((ANY (i.ten = (hashed SubPlan any_1).col1))) -> Seq Scan on public.onek o Output: o.unique1, o.unique2, o.two, o.four, o.ten, o.twenty, o.hundred, o.thousand, o.twothousand, o.fivethous, o.tenthous, o.odd, o.even, o.stringu1, o.stringu2, o.string4 Filter: (o.ten = 0) -> Index Scan using onek_unique1 on public.onek i - Output: (ANY (i.ten = (hashed SubPlan 1).col1)), random() + Output: (ANY (i.ten = (hashed SubPlan any_1).col1)), random() Index Cond: (i.unique1 = o.unique1) - SubPlan 1 + SubPlan any_1 -> Seq Scan on public.int4_tbl Output: int4_tbl.f1 Filter: (int4_tbl.f1 <= o.hundred) @@ -1638,7 +1638,7 @@ select * from ---------------------------------------- Values Scan on "*VALUES*" Output: "*VALUES*".column1 - SubPlan 1 + SubPlan any_1 -> Values Scan on "*VALUES*_1" Output: "*VALUES*_1".column1 (5 rows) @@ -1665,12 +1665,12 @@ select * from int4_tbl where --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop Semi Join Output: int4_tbl.f1 - Join Filter: (CASE WHEN (ANY (int4_tbl.f1 = (hashed SubPlan 1).col1)) THEN int4_tbl.f1 ELSE NULL::integer END = b.ten) + Join Filter: (CASE WHEN (ANY (int4_tbl.f1 = (hashed SubPlan any_1).col1)) THEN int4_tbl.f1 ELSE NULL::integer END = b.ten) -> Seq Scan on public.int4_tbl Output: int4_tbl.f1 -> Seq Scan on public.tenk1 b Output: b.unique1, b.unique2, b.two, b.four, b.ten, b.twenty, b.hundred, b.thousand, b.twothousand, b.fivethous, b.tenthous, b.odd, b.even, b.stringu1, b.stringu2, b.string4 - SubPlan 1 + SubPlan any_1 -> Index Only Scan using tenk1_unique1 on public.tenk1 a Output: a.unique1 (10 rows) @@ -2798,14 +2798,14 @@ select * from tenk1 A where exists (select 1 from tenk2 B where A.hundred in (select C.hundred FROM tenk2 C WHERE c.odd = b.odd)); - QUERY PLAN ------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------- Nested Loop Semi Join - Join Filter: (ANY (a.hundred = (SubPlan 1).col1)) + Join Filter: (ANY (a.hundred = (SubPlan any_1).col1)) -> Seq Scan on tenk1 a -> Materialize -> Seq Scan on tenk2 b - SubPlan 1 + SubPlan any_1 -> Seq Scan on tenk2 c Filter: (odd = b.odd) (8 rows) @@ -2815,14 +2815,14 @@ WHERE c.odd = b.odd)); explain (costs off) SELECT * FROM tenk1 A LEFT JOIN tenk2 B ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd); - QUERY PLAN ------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------- Nested Loop Left Join - Join Filter: (ANY (a.hundred = (SubPlan 1).col1)) + Join Filter: (ANY (a.hundred = (SubPlan any_1).col1)) -> Seq Scan on tenk1 a -> Materialize -> Seq Scan on tenk2 b - SubPlan 1 + SubPlan any_1 -> Seq Scan on tenk2 c Filter: (odd = b.odd) (8 rows) @@ -2832,14 +2832,14 @@ ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd); explain (costs off) SELECT * FROM tenk1 A LEFT JOIN tenk2 B ON B.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = a.odd); - QUERY PLAN ------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------- Nested Loop Left Join - Join Filter: (ANY (b.hundred = (SubPlan 1).col1)) + Join Filter: (ANY (b.hundred = (SubPlan any_1).col1)) -> Seq Scan on tenk1 a -> Materialize -> Seq Scan on tenk2 b - SubPlan 1 + SubPlan any_1 -> Seq Scan on tenk2 c Filter: (odd = a.odd) (8 rows) @@ -2901,7 +2901,7 @@ ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd); Filter: (b.hundred = unnamed_subquery.min) -> Result Replaces: MinMaxAggregate - InitPlan 1 + InitPlan minmax_1 -> Limit -> Index Scan using tenk2_hundred on tenk2 c Index Cond: (hundred IS NOT NULL) @@ -3142,7 +3142,7 @@ WHERE unique1 IN (VALUES (0), ((2 IN (SELECT unique2 FROM onek c -> Seq Scan on onek t -> Values Scan on "*VALUES*" Filter: (t.unique1 = column1) - SubPlan 1 + SubPlan any_1 -> Index Only Scan using onek_unique2 on onek c Index Cond: (unique2 = t.unique1) (7 rows) @@ -3158,7 +3158,7 @@ WHERE unique1 IN (VALUES (0), ((2 IN (SELECT unique2 FROM onek c -> Sort Sort Key: "*VALUES*".column1 -> Values Scan on "*VALUES*" - SubPlan 1 + SubPlan any_1 -> Index Only Scan using onek_unique2 on onek c Filter: ((unique2)::double precision = ANY ('{0.479425538604203,2}'::double precision[])) -> Index Scan using onek_unique1 on onek t @@ -3177,7 +3177,7 @@ SELECT ten FROM onek t WHERE unique1 IN (VALUES (0), ((2 IN -> Sort Sort Key: "*VALUES*".column1 -> Values Scan on "*VALUES*" - SubPlan 1 + SubPlan any_1 -> Result -> Index Scan using onek_unique1 on onek t Index Cond: (unique1 = "*VALUES*".column1) diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out index 83228cfca29..3b37fafa65b 100644 --- a/src/test/regress/expected/sysviews.out +++ b/src/test/regress/expected/sysviews.out @@ -151,6 +151,7 @@ select name, setting from pg_settings where name like 'enable%'; enable_async_append | on enable_bitmapscan | on enable_distinct_reordering | on + enable_eager_aggregate | on enable_gathermerge | on enable_group_by_reordering | on enable_hashagg | on @@ -172,7 +173,7 @@ select name, setting from pg_settings where name like 'enable%'; enable_seqscan | on enable_sort | on enable_tidscan | on -(24 rows) +(25 rows) -- There are always wait event descriptions for various types. InjectionPoint -- may be present or absent, depending on history since last postmaster start. diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out index 7c089e0d598..fb77d108337 100644 --- a/src/test/regress/expected/union.out +++ b/src/test/regress/expected/union.out @@ -1217,7 +1217,7 @@ select event_id drop table events_child, events, other_events; reset enable_indexonlyscan; -- --- Test handling of UNION with provably empty inputs +-- Test handling of UNION / EXCEPT / INTERSECT with provably empty inputs -- -- Ensure the empty UNION input is pruned and de-duplication is done for the -- remaining relation. @@ -1258,14 +1258,102 @@ SELECT two FROM tenk1 WHERE 1=2 UNION SELECT four FROM tenk1 WHERE 1=2 UNION -SELECT ten FROM tenk1 WHERE 1=2; +SELECT ten FROM tenk1 WHERE 1=2 +ORDER BY 1; + QUERY PLAN +-------------------------------------- + Sort + Output: unnamed_subquery.two + Sort Key: unnamed_subquery.two + -> Result + Output: unnamed_subquery.two + Replaces: Aggregate + One-Time Filter: false +(7 rows) + +-- Ensure the planner provides a const-false Result node +EXPLAIN (COSTS OFF, VERBOSE) +SELECT two FROM tenk1 WHERE 1=2 +INTERSECT +SELECT four FROM tenk1 +ORDER BY 1; + QUERY PLAN +--------------------------------------------------------------------- + Sort + Output: unnamed_subquery.two + Sort Key: unnamed_subquery.two + -> Result + Output: unnamed_subquery.two + Replaces: Aggregate on unnamed_subquery, unnamed_subquery_1 + One-Time Filter: false +(7 rows) + +-- As above, with the inputs swapped +EXPLAIN (COSTS OFF, VERBOSE) +SELECT four FROM tenk1 +INTERSECT +SELECT two FROM tenk1 WHERE 1=2 +ORDER BY 1; + QUERY PLAN +--------------------------------------------------------------------- + Sort + Output: unnamed_subquery.four + Sort Key: unnamed_subquery.four + -> Result + Output: unnamed_subquery.four + Replaces: Aggregate on unnamed_subquery, unnamed_subquery_1 + One-Time Filter: false +(7 rows) + +-- Try with both inputs dummy +EXPLAIN (COSTS OFF, VERBOSE) +SELECT four FROM tenk1 WHERE 1=2 +INTERSECT +SELECT two FROM tenk1 WHERE 1=2 +ORDER BY 1; + QUERY PLAN +--------------------------------------------------------------------- + Sort + Output: unnamed_subquery.four + Sort Key: unnamed_subquery.four + -> Result + Output: unnamed_subquery.four + Replaces: Aggregate on unnamed_subquery, unnamed_subquery_1 + One-Time Filter: false +(7 rows) + +-- Ensure the planner provides a const-false Result node when the left input +-- is empty +EXPLAIN (COSTS OFF, VERBOSE) +SELECT two FROM tenk1 WHERE 1=2 +EXCEPT +SELECT four FROM tenk1 +ORDER BY 1; + QUERY PLAN +--------------------------------------------------------------------- + Sort + Output: unnamed_subquery.two + Sort Key: unnamed_subquery.two + -> Result + Output: unnamed_subquery.two + Replaces: Aggregate on unnamed_subquery, unnamed_subquery_1 + One-Time Filter: false +(7 rows) + +-- Ensure the planner only scans the left input when right input is empty +EXPLAIN (COSTS OFF, VERBOSE) +SELECT two FROM tenk1 +EXCEPT ALL +SELECT four FROM tenk1 WHERE 1=2 +ORDER BY 1; QUERY PLAN -------------------------------- - Result - Output: unnamed_subquery.two - Replaces: Aggregate - One-Time Filter: false -(4 rows) + Sort + Output: tenk1.two + Sort Key: tenk1.two + -> Seq Scan on public.tenk1 + Output: tenk1.two +(5 rows) -- Test constraint exclusion of UNION ALL subqueries explain (costs off) diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out index 095df0a670c..03df7e75b7b 100644 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -2750,7 +2750,7 @@ EXPLAIN (costs off) INSERT INTO rw_view1 VALUES (5); --------------------------------------------------------- Insert on base_tbl b -> Result - SubPlan 1 + SubPlan exists_1 -> Index Only Scan using ref_tbl_pkey on ref_tbl r Index Cond: (a = b.a) (5 rows) @@ -2764,7 +2764,7 @@ EXPLAIN (costs off) UPDATE rw_view1 SET a = a + 5; -> Seq Scan on base_tbl b -> Hash -> Seq Scan on ref_tbl r - SubPlan 1 + SubPlan exists_1 -> Index Only Scan using ref_tbl_pkey on ref_tbl r_1 Index Cond: (a = b.a) (9 rows) @@ -3167,21 +3167,21 @@ EXPLAIN (costs off) DELETE FROM rw_view1 WHERE id = 1 AND snoop(data); DELETE FROM rw_view1 WHERE id = 1 AND snoop(data); NOTICE: snooped value: Row 1 EXPLAIN (costs off) INSERT INTO rw_view1 VALUES (2, 'New row 2'); - QUERY PLAN ------------------------------------------------------------ + QUERY PLAN +----------------------------------------------------------------- Insert on base_tbl - InitPlan 1 + InitPlan exists_1 -> Index Only Scan using base_tbl_pkey on base_tbl t Index Cond: (id = 2) -> Result - One-Time Filter: ((InitPlan 1).col1 IS NOT TRUE) + One-Time Filter: ((InitPlan exists_1).col1 IS NOT TRUE) Update on base_tbl - InitPlan 1 + InitPlan exists_1 -> Index Only Scan using base_tbl_pkey on base_tbl t Index Cond: (id = 2) -> Result - One-Time Filter: (InitPlan 1).col1 + One-Time Filter: (InitPlan exists_1).col1 -> Index Scan using base_tbl_pkey on base_tbl Index Cond: (id = 2) (15 rows) @@ -3240,8 +3240,8 @@ SELECT * FROM v1 WHERE a=8; EXPLAIN (VERBOSE, COSTS OFF) UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6; - QUERY PLAN ------------------------------------------------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------------------------------------------------------ Update on public.t1 Update on public.t1 t1_1 Update on public.t11 t1_2 @@ -3253,8 +3253,8 @@ UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6; -> Index Scan using t1_a_idx on public.t1 t1_1 Output: t1_1.tableoid, t1_1.ctid Index Cond: ((t1_1.a > 5) AND (t1_1.a < 7)) - Filter: ((t1_1.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_1.a) AND leakproof(t1_1.a)) - SubPlan 1 + Filter: ((t1_1.a <> 6) AND EXISTS(SubPlan exists_1) AND snoop(t1_1.a) AND leakproof(t1_1.a)) + SubPlan exists_1 -> Append -> Seq Scan on public.t12 t12_1 Filter: (t12_1.a = t1_1.a) @@ -3263,15 +3263,15 @@ UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6; -> Index Scan using t11_a_idx on public.t11 t1_2 Output: t1_2.tableoid, t1_2.ctid Index Cond: ((t1_2.a > 5) AND (t1_2.a < 7)) - Filter: ((t1_2.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_2.a) AND leakproof(t1_2.a)) + Filter: ((t1_2.a <> 6) AND EXISTS(SubPlan exists_1) AND snoop(t1_2.a) AND leakproof(t1_2.a)) -> Index Scan using t12_a_idx on public.t12 t1_3 Output: t1_3.tableoid, t1_3.ctid Index Cond: ((t1_3.a > 5) AND (t1_3.a < 7)) - Filter: ((t1_3.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_3.a) AND leakproof(t1_3.a)) + Filter: ((t1_3.a <> 6) AND EXISTS(SubPlan exists_1) AND snoop(t1_3.a) AND leakproof(t1_3.a)) -> Index Scan using t111_a_idx on public.t111 t1_4 Output: t1_4.tableoid, t1_4.ctid Index Cond: ((t1_4.a > 5) AND (t1_4.a < 7)) - Filter: ((t1_4.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_4.a) AND leakproof(t1_4.a)) + Filter: ((t1_4.a <> 6) AND EXISTS(SubPlan exists_1) AND snoop(t1_4.a) AND leakproof(t1_4.a)) (30 rows) UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6; @@ -3287,8 +3287,8 @@ SELECT * FROM t1 WHERE a=100; -- Nothing should have been changed to 100 EXPLAIN (VERBOSE, COSTS OFF) UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8; - QUERY PLAN ------------------------------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------------------------------------ Update on public.t1 Update on public.t1 t1_1 Update on public.t11 t1_2 @@ -3300,8 +3300,8 @@ UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8; -> Index Scan using t1_a_idx on public.t1 t1_1 Output: t1_1.a, t1_1.tableoid, t1_1.ctid Index Cond: ((t1_1.a > 5) AND (t1_1.a = 8)) - Filter: (EXISTS(SubPlan 1) AND snoop(t1_1.a) AND leakproof(t1_1.a)) - SubPlan 1 + Filter: (EXISTS(SubPlan exists_1) AND snoop(t1_1.a) AND leakproof(t1_1.a)) + SubPlan exists_1 -> Append -> Seq Scan on public.t12 t12_1 Filter: (t12_1.a = t1_1.a) @@ -3310,15 +3310,15 @@ UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8; -> Index Scan using t11_a_idx on public.t11 t1_2 Output: t1_2.a, t1_2.tableoid, t1_2.ctid Index Cond: ((t1_2.a > 5) AND (t1_2.a = 8)) - Filter: (EXISTS(SubPlan 1) AND snoop(t1_2.a) AND leakproof(t1_2.a)) + Filter: (EXISTS(SubPlan exists_1) AND snoop(t1_2.a) AND leakproof(t1_2.a)) -> Index Scan using t12_a_idx on public.t12 t1_3 Output: t1_3.a, t1_3.tableoid, t1_3.ctid Index Cond: ((t1_3.a > 5) AND (t1_3.a = 8)) - Filter: (EXISTS(SubPlan 1) AND snoop(t1_3.a) AND leakproof(t1_3.a)) + Filter: (EXISTS(SubPlan exists_1) AND snoop(t1_3.a) AND leakproof(t1_3.a)) -> Index Scan using t111_a_idx on public.t111 t1_4 Output: t1_4.a, t1_4.tableoid, t1_4.ctid Index Cond: ((t1_4.a > 5) AND (t1_4.a = 8)) - Filter: (EXISTS(SubPlan 1) AND snoop(t1_4.a) AND leakproof(t1_4.a)) + Filter: (EXISTS(SubPlan exists_1) AND snoop(t1_4.a) AND leakproof(t1_4.a)) (30 rows) UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8; @@ -3502,10 +3502,10 @@ CREATE RULE v1_upd_rule AS ON UPDATE TO v1 DO INSTEAD CREATE VIEW v2 WITH (security_barrier = true) AS SELECT * FROM v1 WHERE EXISTS (SELECT 1); EXPLAIN (COSTS OFF) UPDATE v2 SET a = 1; - QUERY PLAN --------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------- Update on t1 - InitPlan 1 + InitPlan exists_1 -> Result -> Merge Join Merge Cond: (t1.a = v1.a) @@ -3516,7 +3516,7 @@ EXPLAIN (COSTS OFF) UPDATE v2 SET a = 1; Sort Key: v1.a -> Subquery Scan on v1 -> Result - One-Time Filter: (InitPlan 1).col1 + One-Time Filter: (InitPlan exists_1).col1 -> Seq Scan on t1 t1_1 (14 rows) diff --git a/src/test/regress/expected/update.out b/src/test/regress/expected/update.out index 1b27d132d7b..eef2bac1cbf 100644 --- a/src/test/regress/expected/update.out +++ b/src/test/regress/expected/update.out @@ -178,15 +178,15 @@ EXPLAIN (VERBOSE, COSTS OFF) UPDATE update_test t SET (a, b) = (SELECT b, a FROM update_test s WHERE s.a = t.a) WHERE CURRENT_USER = SESSION_USER; - QUERY PLAN --------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------------------------- Update on public.update_test t -> Result - Output: (SubPlan 1).col1, (SubPlan 1).col2, (rescan SubPlan 1), t.ctid + Output: (SubPlan multiexpr_1).col1, (SubPlan multiexpr_1).col2, (rescan SubPlan multiexpr_1), t.ctid One-Time Filter: (CURRENT_USER = SESSION_USER) -> Seq Scan on public.update_test t Output: t.a, t.ctid - SubPlan 1 + SubPlan multiexpr_1 -> Seq Scan on public.update_test s Output: s.b, s.a Filter: (s.a = t.a) diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out index 85c783e2e56..d4696bc3325 100644 --- a/src/test/regress/expected/vacuum.out +++ b/src/test/regress/expected/vacuum.out @@ -161,16 +161,14 @@ VACUUM (PARALLEL 2) pvactst; UPDATE pvactst SET i = i WHERE i < 1000; VACUUM (PARALLEL 0) pvactst; -- disable parallel vacuum VACUUM (PARALLEL -1) pvactst; -- error -ERROR: parallel workers for vacuum must be between 0 and 1024 +ERROR: PARALLEL option must be between 0 and 1024 LINE 1: VACUUM (PARALLEL -1) pvactst; ^ VACUUM (PARALLEL 2, INDEX_CLEANUP FALSE) pvactst; VACUUM (PARALLEL 2, FULL TRUE) pvactst; -- error, cannot use both PARALLEL and FULL ERROR: VACUUM FULL cannot be performed in parallel VACUUM (PARALLEL) pvactst; -- error, cannot use PARALLEL option without parallel degree -ERROR: parallel option requires a value between 0 and 1024 -LINE 1: VACUUM (PARALLEL) pvactst; - ^ +ERROR: parallel requires an integer value -- Test parallel vacuum using the minimum maintenance_work_mem with and without -- dead tuples. SET maintenance_work_mem TO 64; diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out index a595fa28ce1..9e2f53726f5 100644 --- a/src/test/regress/expected/window.out +++ b/src/test/regress/expected/window.out @@ -4250,14 +4250,14 @@ SELECT 1 FROM (SELECT ntile(s1.x) OVER () AS c FROM (SELECT (SELECT 1) AS x) AS s1) s WHERE s.c = 1; - QUERY PLAN ----------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------- Subquery Scan on s Filter: (s.c = 1) -> WindowAgg Window: w1 AS (ROWS UNBOUNDED PRECEDING) - Run Condition: (ntile((InitPlan 1).col1) OVER w1 <= 1) - InitPlan 1 + Run Condition: (ntile((InitPlan expr_1).col1) OVER w1 <= 1) + InitPlan expr_1 -> Result -> Result (8 rows) @@ -4338,7 +4338,7 @@ WHERE c = 1; Filter: (emp.c = 1) -> WindowAgg Window: w1 AS (ORDER BY empsalary.empno) - InitPlan 1 + InitPlan expr_1 -> Result -> Sort Sort Key: empsalary.empno DESC @@ -4537,6 +4537,22 @@ WHERE first_emp = 1 OR last_emp = 1; sales | 4 | 4800 | 08-08-2007 | 3 | 1 (6 rows) +CREATE INDEX empsalary_salary_empno_idx ON empsalary (salary, empno); +SET enable_seqscan = 0; +-- Ensure no sorting is done and that the IndexScan maintains all pathkeys +-- useful for the final sort order. +EXPLAIN (COSTS OFF) +SELECT salary, empno, row_number() OVER (ORDER BY salary) rn +FROM empsalary +ORDER BY salary, empno; + QUERY PLAN +--------------------------------------------------------------------- + WindowAgg + Window: w1 AS (ORDER BY salary ROWS UNBOUNDED PRECEDING) + -> Index Only Scan using empsalary_salary_empno_idx on empsalary +(3 rows) + +RESET enable_seqscan; -- cleanup DROP TABLE empsalary; -- test user-defined window function with named args and default args diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out index c3932c7b94c..86fdb85c6c5 100644 --- a/src/test/regress/expected/with.out +++ b/src/test/regress/expected/with.out @@ -2306,14 +2306,14 @@ explain (verbose, costs off) select f1, (with cte1(x,y) as (select 1,2) select count((select i4.f1 from cte1))) as ss from int4_tbl i4; - QUERY PLAN --------------------------------------------- + QUERY PLAN +------------------------------------------------- Seq Scan on public.int4_tbl i4 - Output: i4.f1, (SubPlan 2) - SubPlan 2 + Output: i4.f1, (SubPlan expr_1) + SubPlan expr_1 -> Aggregate - Output: count((InitPlan 1).col1) - InitPlan 1 + Output: count((InitPlan expr_2).col1) + InitPlan expr_2 -> Result Output: i4.f1 -> Result @@ -3203,7 +3203,7 @@ WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v); Output: o.k, o.v, o.* -> Result Output: 0, 'merge source SubPlan'::text - SubPlan 2 + SubPlan expr_1 -> Limit Output: ((cte_basic.b || ' merge update'::text)) -> CTE Scan on cte_basic @@ -3235,7 +3235,7 @@ WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v); CTE cte_init -> Result Output: 1, 'cte_init val'::text - InitPlan 2 + InitPlan expr_1 -> Limit Output: ((cte_init.b || ' merge update'::text)) -> CTE Scan on cte_init @@ -3278,11 +3278,11 @@ WHEN NOT MATCHED THEN INSERT VALUES(o.a, o.b || (SELECT merge_source_cte.*::text CTE merge_source_cte -> Result Output: 15, 'merge_source_cte val'::text - InitPlan 2 + InitPlan expr_1 -> CTE Scan on merge_source_cte merge_source_cte_1 Output: ((merge_source_cte_1.b || (merge_source_cte_1.*)::text) || ' merge update'::text) Filter: (merge_source_cte_1.a = 15) - InitPlan 3 + InitPlan expr_2 -> CTE Scan on merge_source_cte merge_source_cte_2 Output: ((merge_source_cte_2.*)::text || ' merge insert'::text) -> Hash Right Join |