summaryrefslogtreecommitdiff
path: root/src/test/regress
diff options
context:
space:
mode:
Diffstat (limited to 'src/test/regress')
-rw-r--r--src/test/regress/expected/partition_join.out98
-rw-r--r--src/test/regress/expected/rules.out4
-rw-r--r--src/test/regress/expected/stats_ext.out272
-rw-r--r--src/test/regress/expected/subselect.out43
-rw-r--r--src/test/regress/expected/sysviews.out2
-rw-r--r--src/test/regress/pg_regress.c2
-rw-r--r--src/test/regress/sql/partition_join.sql20
-rw-r--r--src/test/regress/sql/stats_ext.sql20
-rw-r--r--src/test/regress/sql/subselect.sql2
9 files changed, 128 insertions, 335 deletions
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 713828be335..17d27ef3d46 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -63,18 +63,21 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b =
(4 rows)
-- inner join with partially-redundant join clauses
+-- (avoid a mergejoin, because the planner thinks that an non-partitionwise
+-- merge join is the cheapest plan, and we want to test a partitionwise join)
+BEGIN;
+SET LOCAL enable_mergejoin = false;
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
- QUERY PLAN
----------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------
Sort
Sort Key: t1.a
-> Append
- -> Merge Join
- Merge Cond: (t1_1.a = t2_1.a)
- -> Index Scan using iprt1_p1_a on prt1_p1 t1_1
- -> Sort
- Sort Key: t2_1.b
+ -> Hash Join
+ Hash Cond: (t1_1.a = t2_1.a)
+ -> Seq Scan on prt1_p1 t1_1
+ -> Hash
-> Seq Scan on prt2_p1 t2_1
Filter: (a = b)
-> Hash Join
@@ -89,7 +92,7 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a =
-> Hash
-> Seq Scan on prt2_p3 t2_3
Filter: (a = b)
-(22 rows)
+(21 rows)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
a | c | b | c
@@ -101,6 +104,7 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a =
24 | 0024 | 24 | 0024
(5 rows)
+COMMIT;
-- left outer join, 3-way
EXPLAIN (COSTS OFF)
SELECT COUNT(*) FROM prt1 t1
@@ -1244,11 +1248,12 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (
450 | 0 | 0450
(4 rows)
--- test merge joins
+-- test merge joins, slightly modifying the query to ensure that we still
+-- get a fully partitionwise join
SET enable_hashjoin TO off;
SET enable_nestloop TO off;
EXPLAIN (COSTS OFF)
-SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (SELECT (t1.a + t1.b)/2 FROM prt1_e t1 WHERE t1.c = 0)) AND t1.b = 0 ORDER BY t1.a;
+SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (SELECT (t1.a + t1.b)/2 FROM prt1_e t1 WHERE t1.c = 0)) ORDER BY t1.a;
QUERY PLAN
------------------------------------------------------------------
Merge Append
@@ -1258,7 +1263,6 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (
-> Sort
Sort Key: t1_3.a
-> Seq Scan on prt1_p1 t1_3
- Filter: (b = 0)
-> Merge Semi Join
Merge Cond: (t1_6.b = (((t1_9.a + t1_9.b) / 2)))
-> Sort
@@ -1273,7 +1277,6 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (
-> Sort
Sort Key: t1_4.a
-> Seq Scan on prt1_p2 t1_4
- Filter: (b = 0)
-> Merge Semi Join
Merge Cond: (t1_7.b = (((t1_10.a + t1_10.b) / 2)))
-> Sort
@@ -1288,7 +1291,6 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (
-> Sort
Sort Key: t1_5.a
-> Seq Scan on prt1_p3 t1_5
- Filter: (b = 0)
-> Merge Semi Join
Merge Cond: (t1_8.b = (((t1_11.a + t1_11.b) / 2)))
-> Sort
@@ -1298,9 +1300,9 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (
Sort Key: (((t1_11.a + t1_11.b) / 2))
-> Seq Scan on prt1_e_p3 t1_11
Filter: (c = 0)
-(47 rows)
+(44 rows)
-SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (SELECT (t1.a + t1.b)/2 FROM prt1_e t1 WHERE t1.c = 0)) AND t1.b = 0 ORDER BY t1.a;
+SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (SELECT (t1.a + t1.b)/2 FROM prt1_e t1 WHERE t1.c = 0)) ORDER BY t1.a;
a | b | c
-----+---+------
0 | 0 | 0000
@@ -4922,27 +4924,27 @@ ANALYZE plt3_adv;
-- merged partition when re-called with plt1_adv_p1 for the second list value
-- '0001' of that partition
EXPLAIN (COSTS OFF)
-SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.c = t2.c)) FULL JOIN plt3_adv t3 ON (t1.c = t3.c) WHERE coalesce(t1.a, 0) % 5 != 3 AND coalesce(t1.a, 0) % 5 != 4 ORDER BY t1.c, t1.a, t2.a, t3.a;
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c)) FULL JOIN plt3_adv t3 ON (t1.a = t3.a AND t1.c = t3.c) WHERE coalesce(t1.a, 0) % 5 != 3 AND coalesce(t1.a, 0) % 5 != 4 ORDER BY t1.c, t1.a, t2.a, t3.a;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Sort
Sort Key: t1.c, t1.a, t2.a, t3.a
-> Append
-> Hash Full Join
- Hash Cond: (t1_1.c = t3_1.c)
+ Hash Cond: ((t1_1.a = t3_1.a) AND (t1_1.c = t3_1.c))
Filter: (((COALESCE(t1_1.a, 0) % 5) <> 3) AND ((COALESCE(t1_1.a, 0) % 5) <> 4))
-> Hash Left Join
- Hash Cond: (t1_1.c = t2_1.c)
+ Hash Cond: ((t1_1.a = t2_1.a) AND (t1_1.c = t2_1.c))
-> Seq Scan on plt1_adv_p1 t1_1
-> Hash
-> Seq Scan on plt2_adv_p1 t2_1
-> Hash
-> Seq Scan on plt3_adv_p1 t3_1
-> Hash Full Join
- Hash Cond: (t1_2.c = t3_2.c)
+ Hash Cond: ((t1_2.a = t3_2.a) AND (t1_2.c = t3_2.c))
Filter: (((COALESCE(t1_2.a, 0) % 5) <> 3) AND ((COALESCE(t1_2.a, 0) % 5) <> 4))
-> Hash Left Join
- Hash Cond: (t1_2.c = t2_2.c)
+ Hash Cond: ((t1_2.a = t2_2.a) AND (t1_2.c = t2_2.c))
-> Seq Scan on plt1_adv_p2 t1_2
-> Hash
-> Seq Scan on plt2_adv_p2 t2_2
@@ -4950,7 +4952,7 @@ SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1_adv t1 LEFT JOIN plt2_adv t
-> Seq Scan on plt3_adv_p2 t3_2
(23 rows)
-SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.c = t2.c)) FULL JOIN plt3_adv t3 ON (t1.c = t3.c) WHERE coalesce(t1.a, 0) % 5 != 3 AND coalesce(t1.a, 0) % 5 != 4 ORDER BY t1.c, t1.a, t2.a, t3.a;
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c)) FULL JOIN plt3_adv t3 ON (t1.a = t3.a AND t1.c = t3.c) WHERE coalesce(t1.a, 0) % 5 != 3 AND coalesce(t1.a, 0) % 5 != 4 ORDER BY t1.c, t1.a, t2.a, t3.a;
a | c | a | c | a | c
----+------+----+------+----+------
0 | 0000 | | | |
@@ -4959,56 +4961,16 @@ SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1_adv t1 LEFT JOIN plt2_adv t
15 | 0000 | | | |
20 | 0000 | | | |
1 | 0001 | | | 1 | 0001
- 1 | 0001 | | | 6 | 0001
- 1 | 0001 | | | 11 | 0001
- 1 | 0001 | | | 16 | 0001
- 1 | 0001 | | | 21 | 0001
- 6 | 0001 | | | 1 | 0001
6 | 0001 | | | 6 | 0001
- 6 | 0001 | | | 11 | 0001
- 6 | 0001 | | | 16 | 0001
- 6 | 0001 | | | 21 | 0001
- 11 | 0001 | | | 1 | 0001
- 11 | 0001 | | | 6 | 0001
11 | 0001 | | | 11 | 0001
- 11 | 0001 | | | 16 | 0001
- 11 | 0001 | | | 21 | 0001
- 16 | 0001 | | | 1 | 0001
- 16 | 0001 | | | 6 | 0001
- 16 | 0001 | | | 11 | 0001
16 | 0001 | | | 16 | 0001
- 16 | 0001 | | | 21 | 0001
- 21 | 0001 | | | 1 | 0001
- 21 | 0001 | | | 6 | 0001
- 21 | 0001 | | | 11 | 0001
- 21 | 0001 | | | 16 | 0001
21 | 0001 | | | 21 | 0001
2 | 0002 | 2 | 0002 | |
- 2 | 0002 | 7 | 0002 | |
- 2 | 0002 | 12 | 0002 | |
- 2 | 0002 | 17 | 0002 | |
- 2 | 0002 | 22 | 0002 | |
- 7 | 0002 | 2 | 0002 | |
7 | 0002 | 7 | 0002 | |
- 7 | 0002 | 12 | 0002 | |
- 7 | 0002 | 17 | 0002 | |
- 7 | 0002 | 22 | 0002 | |
- 12 | 0002 | 2 | 0002 | |
- 12 | 0002 | 7 | 0002 | |
12 | 0002 | 12 | 0002 | |
- 12 | 0002 | 17 | 0002 | |
- 12 | 0002 | 22 | 0002 | |
- 17 | 0002 | 2 | 0002 | |
- 17 | 0002 | 7 | 0002 | |
- 17 | 0002 | 12 | 0002 | |
17 | 0002 | 17 | 0002 | |
- 17 | 0002 | 22 | 0002 | |
- 22 | 0002 | 2 | 0002 | |
- 22 | 0002 | 7 | 0002 | |
- 22 | 0002 | 12 | 0002 | |
- 22 | 0002 | 17 | 0002 | |
22 | 0002 | 22 | 0002 | |
-(55 rows)
+(15 rows)
DROP TABLE plt1_adv;
DROP TABLE plt2_adv;
@@ -5233,8 +5195,11 @@ CREATE TABLE fract_t1 PARTITION OF fract_t FOR VALUES FROM ('1000') TO ('2000');
INSERT INTO fract_t (id) (SELECT generate_series(0, 1999));
ANALYZE fract_t;
-- verify plan; nested index only scans
+-- (avoid merge joins, because the costs of partitionwise and non-partitionwise
+-- merge joins tend to be almost equal, and we want this test to be stable)
SET max_parallel_workers_per_gather = 0;
SET enable_partitionwise_join = on;
+SET enable_mergejoin = off;
EXPLAIN (COSTS OFF)
SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY x.id ASC LIMIT 10;
QUERY PLAN
@@ -5242,14 +5207,14 @@ SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY x.id AS
Limit
-> Merge Append
Sort Key: x.id
- -> Merge Left Join
- Merge Cond: (x_1.id = y_1.id)
+ -> Nested Loop Left Join
-> Index Only Scan using fract_t0_pkey on fract_t0 x_1
-> Index Only Scan using fract_t0_pkey on fract_t0 y_1
- -> Merge Left Join
- Merge Cond: (x_2.id = y_2.id)
+ Index Cond: (id = x_1.id)
+ -> Nested Loop Left Join
-> Index Only Scan using fract_t1_pkey on fract_t1 x_2
-> Index Only Scan using fract_t1_pkey on fract_t1 y_2
+ Index Cond: (id = x_2.id)
(11 rows)
EXPLAIN (COSTS OFF)
@@ -5366,6 +5331,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 1000;
-> Seq Scan on pht1_p3 p2_3
(17 rows)
+RESET enable_mergejoin;
SET max_parallel_workers_per_gather = 1;
SET debug_parallel_query = on;
-- Partial paths should also be smart enough to employ limits
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 94e45dd4d57..85d795dbd63 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2153,10 +2153,10 @@ pg_stat_replication_slots| SELECT s.slot_name,
s.total_txns,
s.total_bytes,
s.slotsync_skip_count,
- s.slotsync_skip_at,
+ s.slotsync_last_skip,
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, mem_exceeded_count, total_txns, total_bytes, slotsync_skip_count, slotsync_skip_at, 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, slotsync_skip_count, slotsync_last_skip, stats_reset)
WHERE (r.datoid IS NOT NULL);
pg_stat_slru| SELECT name,
blks_zeroed,
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out
index eb70ea5a2ee..b2a06579135 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -196,8 +196,10 @@ Statistics objects:
"public.ab1_a_b_stats" ON a, b FROM ab1; STATISTICS 0
ANALYZE ab1;
-SELECT stxname, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct,
- jsonb_pretty(d.stxddependencies::text::jsonb) AS stxddependencies, stxdmcv, stxdinherit
+SELECT stxname,
+ replace(d.stxdndistinct, '}, ', E'},\n') AS stxdndistinct,
+ replace(d.stxddependencies, '}, ', E'},\n') AS stxddependencies,
+ stxdmcv, stxdinherit
FROM pg_statistic_ext s LEFT JOIN pg_statistic_ext_data d ON (d.stxoid = s.oid)
WHERE s.stxname = 'ab1_a_b_stats';
stxname | stxdndistinct | stxddependencies | stxdmcv | stxdinherit
@@ -477,43 +479,16 @@ SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, (
-- correct command
CREATE STATISTICS s10 ON a, b, c FROM ndistinct;
ANALYZE ndistinct;
-SELECT s.stxkind, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct
+SELECT s.stxkind, replace(d.stxdndistinct, '}, ', E'},\n') AS stxdndistinct
FROM pg_statistic_ext s, pg_statistic_ext_data d
WHERE s.stxrelid = 'ndistinct'::regclass
AND d.stxoid = s.oid;
- stxkind | stxdndistinct
----------+--------------------------
- {d,f,m} | [ +
- | { +
- | "ndistinct": 11,+
- | "attributes": [ +
- | 3, +
- | 4 +
- | ] +
- | }, +
- | { +
- | "ndistinct": 11,+
- | "attributes": [ +
- | 3, +
- | 6 +
- | ] +
- | }, +
- | { +
- | "ndistinct": 11,+
- | "attributes": [ +
- | 4, +
- | 6 +
- | ] +
- | }, +
- | { +
- | "ndistinct": 11,+
- | "attributes": [ +
- | 3, +
- | 4, +
- | 6 +
- | ] +
- | } +
- | ]
+ stxkind | stxdndistinct
+---------+---------------------------------------------
+ {d,f,m} | [{"attributes": [3, 4], "ndistinct": 11}, +
+ | {"attributes": [3, 6], "ndistinct": 11}, +
+ | {"attributes": [4, 6], "ndistinct": 11}, +
+ | {"attributes": [3, 4, 6], "ndistinct": 11}]
(1 row)
-- minor improvement, make sure the ctid does not break the matching
@@ -589,43 +564,16 @@ INSERT INTO ndistinct (a, b, c, filler1)
mod(i,23) || ' dollars and zero cents'
FROM generate_series(1,1000) s(i);
ANALYZE ndistinct;
-SELECT s.stxkind, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct
+SELECT s.stxkind, replace(d.stxdndistinct, '}, ', E'},\n') AS stxdndistinct
FROM pg_statistic_ext s, pg_statistic_ext_data d
WHERE s.stxrelid = 'ndistinct'::regclass
AND d.stxoid = s.oid;
- stxkind | stxdndistinct
----------+----------------------------
- {d,f,m} | [ +
- | { +
- | "ndistinct": 221, +
- | "attributes": [ +
- | 3, +
- | 4 +
- | ] +
- | }, +
- | { +
- | "ndistinct": 247, +
- | "attributes": [ +
- | 3, +
- | 6 +
- | ] +
- | }, +
- | { +
- | "ndistinct": 323, +
- | "attributes": [ +
- | 4, +
- | 6 +
- | ] +
- | }, +
- | { +
- | "ndistinct": 1000,+
- | "attributes": [ +
- | 3, +
- | 4, +
- | 6 +
- | ] +
- | } +
- | ]
+ stxkind | stxdndistinct
+---------+-----------------------------------------------
+ {d,f,m} | [{"attributes": [3, 4], "ndistinct": 221}, +
+ | {"attributes": [3, 6], "ndistinct": 247}, +
+ | {"attributes": [4, 6], "ndistinct": 323}, +
+ | {"attributes": [3, 4, 6], "ndistinct": 1000}]
(1 row)
-- correct estimates
@@ -684,7 +632,7 @@ SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, (
(1 row)
DROP STATISTICS s10;
-SELECT s.stxkind, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct
+SELECT s.stxkind, replace(d.stxdndistinct, '}, ', E'},\n') AS stxdndistinct
FROM pg_statistic_ext s, pg_statistic_ext_data d
WHERE s.stxrelid = 'ndistinct'::regclass
AND d.stxoid = s.oid;
@@ -768,43 +716,16 @@ SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, (
CREATE STATISTICS s10 (ndistinct) ON (a+1), (b+100), (2*c) FROM ndistinct;
ANALYZE ndistinct;
-SELECT s.stxkind, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct
+SELECT s.stxkind, replace(d.stxdndistinct, '}, ', E'},\n') AS stxdndistinct
FROM pg_statistic_ext s, pg_statistic_ext_data d
WHERE s.stxrelid = 'ndistinct'::regclass
AND d.stxoid = s.oid;
- stxkind | stxdndistinct
----------+----------------------------
- {d,e} | [ +
- | { +
- | "ndistinct": 221, +
- | "attributes": [ +
- | -1, +
- | -2 +
- | ] +
- | }, +
- | { +
- | "ndistinct": 247, +
- | "attributes": [ +
- | -1, +
- | -3 +
- | ] +
- | }, +
- | { +
- | "ndistinct": 323, +
- | "attributes": [ +
- | -2, +
- | -3 +
- | ] +
- | }, +
- | { +
- | "ndistinct": 1000,+
- | "attributes": [ +
- | -1, +
- | -2, +
- | -3 +
- | ] +
- | } +
- | ]
+ stxkind | stxdndistinct
+---------+--------------------------------------------------
+ {d,e} | [{"attributes": [-1, -2], "ndistinct": 221}, +
+ | {"attributes": [-1, -3], "ndistinct": 247}, +
+ | {"attributes": [-2, -3], "ndistinct": 323}, +
+ | {"attributes": [-1, -2, -3], "ndistinct": 1000}]
(1 row)
SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY (a+1), (b+100)');
@@ -847,43 +768,16 @@ SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b
CREATE STATISTICS s10 (ndistinct) ON a, b, (2*c) FROM ndistinct;
ANALYZE ndistinct;
-SELECT s.stxkind, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct
+SELECT s.stxkind, replace(d.stxdndistinct, '}, ', E'},\n') AS stxdndistinct
FROM pg_statistic_ext s, pg_statistic_ext_data d
WHERE s.stxrelid = 'ndistinct'::regclass
AND d.stxoid = s.oid;
- stxkind | stxdndistinct
----------+----------------------------
- {d,e} | [ +
- | { +
- | "ndistinct": 221, +
- | "attributes": [ +
- | 3, +
- | 4 +
- | ] +
- | }, +
- | { +
- | "ndistinct": 247, +
- | "attributes": [ +
- | 3, +
- | -1 +
- | ] +
- | }, +
- | { +
- | "ndistinct": 323, +
- | "attributes": [ +
- | 4, +
- | -1 +
- | ] +
- | }, +
- | { +
- | "ndistinct": 1000,+
- | "attributes": [ +
- | 3, +
- | 4, +
- | -1 +
- | ] +
- | } +
- | ]
+ stxkind | stxdndistinct
+---------+------------------------------------------------
+ {d,e} | [{"attributes": [3, 4], "ndistinct": 221}, +
+ | {"attributes": [3, -1], "ndistinct": 247}, +
+ | {"attributes": [4, -1], "ndistinct": 323}, +
+ | {"attributes": [3, 4, -1], "ndistinct": 1000}]
(1 row)
SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, b');
@@ -1434,48 +1328,14 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE
CREATE STATISTICS func_deps_stat (dependencies) ON a, b, c FROM functional_dependencies;
ANALYZE functional_dependencies;
-- print the detected dependencies
-SELECT jsonb_pretty(dependencies::text::jsonb) AS dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat';
- dependencies
------------------------------
- [ +
- { +
- "degree": 1.000000,+
- "attributes": [ +
- 3 +
- ], +
- "dependency": 4 +
- }, +
- { +
- "degree": 1.000000,+
- "attributes": [ +
- 3 +
- ], +
- "dependency": 6 +
- }, +
- { +
- "degree": 1.000000,+
- "attributes": [ +
- 4 +
- ], +
- "dependency": 6 +
- }, +
- { +
- "degree": 1.000000,+
- "attributes": [ +
- 3, +
- 4 +
- ], +
- "dependency": 6 +
- }, +
- { +
- "degree": 1.000000,+
- "attributes": [ +
- 3, +
- 6 +
- ], +
- "dependency": 4 +
- } +
- ]
+SELECT replace(dependencies, '}, ', E'},\n') AS dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat';
+ dependencies
+--------------------------------------------------------------
+ [{"attributes": [3], "dependency": 4, "degree": 1.000000}, +
+ {"attributes": [3], "dependency": 6, "degree": 1.000000}, +
+ {"attributes": [4], "dependency": 6, "degree": 1.000000}, +
+ {"attributes": [3, 4], "dependency": 6, "degree": 1.000000},+
+ {"attributes": [3, 6], "dependency": 4, "degree": 1.000000}]
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1''');
@@ -1814,48 +1674,14 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE
CREATE STATISTICS func_deps_stat (dependencies) ON (a * 2), upper(b), (c + 1) FROM functional_dependencies;
ANALYZE functional_dependencies;
-- print the detected dependencies
-SELECT jsonb_pretty(dependencies::text::jsonb) AS dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat';
- dependencies
------------------------------
- [ +
- { +
- "degree": 1.000000,+
- "attributes": [ +
- -1 +
- ], +
- "dependency": -2 +
- }, +
- { +
- "degree": 1.000000,+
- "attributes": [ +
- -1 +
- ], +
- "dependency": -3 +
- }, +
- { +
- "degree": 1.000000,+
- "attributes": [ +
- -2 +
- ], +
- "dependency": -3 +
- }, +
- { +
- "degree": 1.000000,+
- "attributes": [ +
- -1, +
- -2 +
- ], +
- "dependency": -3 +
- }, +
- { +
- "degree": 1.000000,+
- "attributes": [ +
- -1, +
- -3 +
- ], +
- "dependency": -2 +
- } +
- ]
+SELECT replace(dependencies, '}, ', E'},\n') AS dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat';
+ dependencies
+-----------------------------------------------------------------
+ [{"attributes": [-1], "dependency": -2, "degree": 1.000000}, +
+ {"attributes": [-1], "dependency": -3, "degree": 1.000000}, +
+ {"attributes": [-2], "dependency": -3, "degree": 1.000000}, +
+ {"attributes": [-1, -2], "dependency": -3, "degree": 1.000000},+
+ {"attributes": [-1, -3], "dependency": -2, "degree": 1.000000}]
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) = 2 AND upper(b) = ''1''');
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index cf6b32d1173..774c22b6e3a 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -844,16 +844,19 @@ create index on unique_tbl_p3(a);
analyze unique_tbl_p;
set enable_partitionwise_join to on;
-- Ensure that the unique-ification works for partition-wise join
+-- (Only one of the two joins will be done partitionwise, but that's good
+-- enough for our purposes.)
explain (verbose, costs off)
select * from unique_tbl_p t1, unique_tbl_p t2
where (t1.a, t2.a) in (select a, a from unique_tbl_p t3)
order by t1.a, t2.a;
QUERY PLAN
------------------------------------------------------------------------------------------------
- Merge Append
- Sort Key: t1.a
- -> Nested Loop
- Output: t1_1.a, t1_1.b, t2_1.a, t2_1.b
+ Merge Join
+ Output: t1.a, t1.b, t2.a, t2.b
+ Merge Cond: (t1.a = t2.a)
+ -> Merge Append
+ Sort Key: t1.a
-> Nested Loop
Output: t1_1.a, t1_1.b, t3_1.a
-> Unique
@@ -863,15 +866,6 @@ order by t1.a, t2.a;
-> Index Scan using unique_tbl_p1_a_idx on public.unique_tbl_p1 t1_1
Output: t1_1.a, t1_1.b
Index Cond: (t1_1.a = t3_1.a)
- -> Memoize
- Output: t2_1.a, t2_1.b
- Cache Key: t1_1.a
- Cache Mode: logical
- -> Index Scan using unique_tbl_p1_a_idx on public.unique_tbl_p1 t2_1
- Output: t2_1.a, t2_1.b
- Index Cond: (t2_1.a = t1_1.a)
- -> Nested Loop
- Output: t1_2.a, t1_2.b, t2_2.a, t2_2.b
-> Nested Loop
Output: t1_2.a, t1_2.b, t3_2.a
-> Unique
@@ -881,15 +875,6 @@ order by t1.a, t2.a;
-> Index Scan using unique_tbl_p2_a_idx on public.unique_tbl_p2 t1_2
Output: t1_2.a, t1_2.b
Index Cond: (t1_2.a = t3_2.a)
- -> Memoize
- Output: t2_2.a, t2_2.b
- Cache Key: t1_2.a
- Cache Mode: logical
- -> Index Scan using unique_tbl_p2_a_idx on public.unique_tbl_p2 t2_2
- Output: t2_2.a, t2_2.b
- Index Cond: (t2_2.a = t1_2.a)
- -> Nested Loop
- Output: t1_3.a, t1_3.b, t2_3.a, t2_3.b
-> Nested Loop
Output: t1_3.a, t1_3.b, t3_3.a
-> Unique
@@ -902,14 +887,16 @@ order by t1.a, t2.a;
-> Index Scan using unique_tbl_p3_a_idx on public.unique_tbl_p3 t1_3
Output: t1_3.a, t1_3.b
Index Cond: (t1_3.a = t3_3.a)
- -> Memoize
- Output: t2_3.a, t2_3.b
- Cache Key: t1_3.a
- Cache Mode: logical
+ -> Materialize
+ Output: t2.a, t2.b
+ -> Append
+ -> Index Scan using unique_tbl_p1_a_idx on public.unique_tbl_p1 t2_1
+ Output: t2_1.a, t2_1.b
+ -> Index Scan using unique_tbl_p2_a_idx on public.unique_tbl_p2 t2_2
+ Output: t2_2.a, t2_2.b
-> Index Scan using unique_tbl_p3_a_idx on public.unique_tbl_p3 t2_3
Output: t2_3.a, t2_3.b
- Index Cond: (t2_3.a = t1_3.a)
-(59 rows)
+(44 rows)
reset enable_partitionwise_join;
drop table unique_tbl_p;
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 3b37fafa65b..0411db832f1 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -182,7 +182,7 @@ select type, count(*) > 0 as ok FROM pg_wait_events
type | ok
-----------+----
Activity | t
- BufferPin | t
+ Buffer | t
Client | t
Extension | t
IO | t
diff --git a/src/test/regress/pg_regress.c b/src/test/regress/pg_regress.c
index efc41fca2ba..58d41207605 100644
--- a/src/test/regress/pg_regress.c
+++ b/src/test/regress/pg_regress.c
@@ -693,7 +693,7 @@ static
const char *
get_expectfile(const char *testname, const char *file)
{
- char *file_type;
+ const char *file_type;
_resultmap *rm;
/*
diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql
index 30f15ee9acb..d153297acba 100644
--- a/src/test/regress/sql/partition_join.sql
+++ b/src/test/regress/sql/partition_join.sql
@@ -35,9 +35,14 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b =
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
-- inner join with partially-redundant join clauses
+-- (avoid a mergejoin, because the planner thinks that an non-partitionwise
+-- merge join is the cheapest plan, and we want to test a partitionwise join)
+BEGIN;
+SET LOCAL enable_mergejoin = false;
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
+COMMIT;
-- left outer join, 3-way
EXPLAIN (COSTS OFF)
@@ -219,13 +224,14 @@ EXPLAIN (COSTS OFF)
SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (SELECT (t1.a + t1.b)/2 FROM prt1_e t1 WHERE t1.c = 0)) AND t1.b = 0 ORDER BY t1.a;
SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (SELECT (t1.a + t1.b)/2 FROM prt1_e t1 WHERE t1.c = 0)) AND t1.b = 0 ORDER BY t1.a;
--- test merge joins
+-- test merge joins, slightly modifying the query to ensure that we still
+-- get a fully partitionwise join
SET enable_hashjoin TO off;
SET enable_nestloop TO off;
EXPLAIN (COSTS OFF)
-SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (SELECT (t1.a + t1.b)/2 FROM prt1_e t1 WHERE t1.c = 0)) AND t1.b = 0 ORDER BY t1.a;
-SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (SELECT (t1.a + t1.b)/2 FROM prt1_e t1 WHERE t1.c = 0)) AND t1.b = 0 ORDER BY t1.a;
+SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (SELECT (t1.a + t1.b)/2 FROM prt1_e t1 WHERE t1.c = 0)) ORDER BY t1.a;
+SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (SELECT (t1.a + t1.b)/2 FROM prt1_e t1 WHERE t1.c = 0)) ORDER BY t1.a;
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t3.c = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
@@ -1155,8 +1161,8 @@ ANALYZE plt3_adv;
-- merged partition when re-called with plt1_adv_p1 for the second list value
-- '0001' of that partition
EXPLAIN (COSTS OFF)
-SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.c = t2.c)) FULL JOIN plt3_adv t3 ON (t1.c = t3.c) WHERE coalesce(t1.a, 0) % 5 != 3 AND coalesce(t1.a, 0) % 5 != 4 ORDER BY t1.c, t1.a, t2.a, t3.a;
-SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.c = t2.c)) FULL JOIN plt3_adv t3 ON (t1.c = t3.c) WHERE coalesce(t1.a, 0) % 5 != 3 AND coalesce(t1.a, 0) % 5 != 4 ORDER BY t1.c, t1.a, t2.a, t3.a;
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c)) FULL JOIN plt3_adv t3 ON (t1.a = t3.a AND t1.c = t3.c) WHERE coalesce(t1.a, 0) % 5 != 3 AND coalesce(t1.a, 0) % 5 != 4 ORDER BY t1.c, t1.a, t2.a, t3.a;
+SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c)) FULL JOIN plt3_adv t3 ON (t1.a = t3.a AND t1.c = t3.c) WHERE coalesce(t1.a, 0) % 5 != 3 AND coalesce(t1.a, 0) % 5 != 4 ORDER BY t1.c, t1.a, t2.a, t3.a;
DROP TABLE plt1_adv;
DROP TABLE plt2_adv;
@@ -1216,8 +1222,11 @@ INSERT INTO fract_t (id) (SELECT generate_series(0, 1999));
ANALYZE fract_t;
-- verify plan; nested index only scans
+-- (avoid merge joins, because the costs of partitionwise and non-partitionwise
+-- merge joins tend to be almost equal, and we want this test to be stable)
SET max_parallel_workers_per_gather = 0;
SET enable_partitionwise_join = on;
+SET enable_mergejoin = off;
EXPLAIN (COSTS OFF)
SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY x.id ASC LIMIT 10;
@@ -1240,6 +1249,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
-- If almost all the data should be fetched - prefer SeqScan
EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 1000;
+RESET enable_mergejoin;
SET max_parallel_workers_per_gather = 1;
SET debug_parallel_query = on;
-- Partial paths should also be smart enough to employ limits
diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql
index 3f8e03f28a0..76ee9d29c08 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -125,8 +125,10 @@ ALTER TABLE ab1 ALTER a SET STATISTICS -1;
ALTER STATISTICS ab1_a_b_stats SET STATISTICS 0;
\d ab1
ANALYZE ab1;
-SELECT stxname, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct,
- jsonb_pretty(d.stxddependencies::text::jsonb) AS stxddependencies, stxdmcv, stxdinherit
+SELECT stxname,
+ replace(d.stxdndistinct, '}, ', E'},\n') AS stxdndistinct,
+ replace(d.stxddependencies, '}, ', E'},\n') AS stxddependencies,
+ stxdmcv, stxdinherit
FROM pg_statistic_ext s LEFT JOIN pg_statistic_ext_data d ON (d.stxoid = s.oid)
WHERE s.stxname = 'ab1_a_b_stats';
ALTER STATISTICS ab1_a_b_stats SET STATISTICS -1;
@@ -298,7 +300,7 @@ CREATE STATISTICS s10 ON a, b, c FROM ndistinct;
ANALYZE ndistinct;
-SELECT s.stxkind, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct
+SELECT s.stxkind, replace(d.stxdndistinct, '}, ', E'},\n') AS stxdndistinct
FROM pg_statistic_ext s, pg_statistic_ext_data d
WHERE s.stxrelid = 'ndistinct'::regclass
AND d.stxoid = s.oid;
@@ -339,7 +341,7 @@ INSERT INTO ndistinct (a, b, c, filler1)
ANALYZE ndistinct;
-SELECT s.stxkind, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct
+SELECT s.stxkind, replace(d.stxdndistinct, '}, ', E'},\n') AS stxdndistinct
FROM pg_statistic_ext s, pg_statistic_ext_data d
WHERE s.stxrelid = 'ndistinct'::regclass
AND d.stxoid = s.oid;
@@ -365,7 +367,7 @@ SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, (
DROP STATISTICS s10;
-SELECT s.stxkind, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct
+SELECT s.stxkind, replace(d.stxdndistinct, '}, ', E'},\n') AS stxdndistinct
FROM pg_statistic_ext s, pg_statistic_ext_data d
WHERE s.stxrelid = 'ndistinct'::regclass
AND d.stxoid = s.oid;
@@ -400,7 +402,7 @@ CREATE STATISTICS s10 (ndistinct) ON (a+1), (b+100), (2*c) FROM ndistinct;
ANALYZE ndistinct;
-SELECT s.stxkind, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct
+SELECT s.stxkind, replace(d.stxdndistinct, '}, ', E'},\n') AS stxdndistinct
FROM pg_statistic_ext s, pg_statistic_ext_data d
WHERE s.stxrelid = 'ndistinct'::regclass
AND d.stxoid = s.oid;
@@ -424,7 +426,7 @@ CREATE STATISTICS s10 (ndistinct) ON a, b, (2*c) FROM ndistinct;
ANALYZE ndistinct;
-SELECT s.stxkind, jsonb_pretty(d.stxdndistinct::text::jsonb) AS stxdndistinct
+SELECT s.stxkind, replace(d.stxdndistinct, '}, ', E'},\n') AS stxdndistinct
FROM pg_statistic_ext s, pg_statistic_ext_data d
WHERE s.stxrelid = 'ndistinct'::regclass
AND d.stxoid = s.oid;
@@ -709,7 +711,7 @@ CREATE STATISTICS func_deps_stat (dependencies) ON a, b, c FROM functional_depen
ANALYZE functional_dependencies;
-- print the detected dependencies
-SELECT jsonb_pretty(dependencies::text::jsonb) AS dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat';
+SELECT replace(dependencies, '}, ', E'},\n') AS dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat';
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE a = 1 AND b = ''1''');
@@ -845,7 +847,7 @@ CREATE STATISTICS func_deps_stat (dependencies) ON (a * 2), upper(b), (c + 1) FR
ANALYZE functional_dependencies;
-- print the detected dependencies
-SELECT jsonb_pretty(dependencies::text::jsonb) AS dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat';
+SELECT replace(dependencies, '}, ', E'},\n') AS dependencies FROM pg_stats_ext WHERE statistics_name = 'func_deps_stat';
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a * 2) = 2 AND upper(b) = ''1''');
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index 36a8a0aa1d5..7b4ebff46d8 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -419,6 +419,8 @@ analyze unique_tbl_p;
set enable_partitionwise_join to on;
-- Ensure that the unique-ification works for partition-wise join
+-- (Only one of the two joins will be done partitionwise, but that's good
+-- enough for our purposes.)
explain (verbose, costs off)
select * from unique_tbl_p t1, unique_tbl_p t2
where (t1.a, t2.a) in (select a, a from unique_tbl_p t3)
generated by cgit v1.2.3 (git 2.46.0) at 2025-12-06 04:15:46 +0000