diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/modules/injection_points/specs/reindex-concurrently-upsert-on-constraint.spec | 6 | ||||
| -rw-r--r-- | src/test/modules/injection_points/specs/reindex-concurrently-upsert-partitioned.spec | 6 | ||||
| -rw-r--r-- | src/test/modules/injection_points/specs/reindex-concurrently-upsert.spec | 6 | ||||
| -rw-r--r-- | src/test/modules/test_slru/t/002_multixact_wraparound.pl | 22 | ||||
| -rw-r--r-- | src/test/perl/PostgreSQL/Test/Cluster.pm | 4 | ||||
| -rw-r--r-- | src/test/recovery/t/048_vacuum_horizon_floor.pl | 2 | ||||
| -rw-r--r-- | src/test/regress/expected/partition_join.out | 98 | ||||
| -rw-r--r-- | src/test/regress/expected/rules.out | 4 | ||||
| -rw-r--r-- | src/test/regress/expected/stats_ext.out | 272 | ||||
| -rw-r--r-- | src/test/regress/expected/subselect.out | 43 | ||||
| -rw-r--r-- | src/test/regress/expected/sysviews.out | 2 | ||||
| -rw-r--r-- | src/test/regress/pg_regress.c | 2 | ||||
| -rw-r--r-- | src/test/regress/sql/partition_join.sql | 20 | ||||
| -rw-r--r-- | src/test/regress/sql/stats_ext.sql | 20 | ||||
| -rw-r--r-- | src/test/regress/sql/subselect.sql | 2 |
15 files changed, 157 insertions, 352 deletions
diff --git a/src/test/modules/injection_points/specs/reindex-concurrently-upsert-on-constraint.spec b/src/test/modules/injection_points/specs/reindex-concurrently-upsert-on-constraint.spec index 8126256460c..4bbdda3cf04 100644 --- a/src/test/modules/injection_points/specs/reindex-concurrently-upsert-on-constraint.spec +++ b/src/test/modules/injection_points/specs/reindex-concurrently-upsert-on-constraint.spec @@ -85,7 +85,7 @@ step s4_wakeup_to_set_dead permutation s3_setup_wait_before_set_dead s3_start_reindex(s1_start_upsert, s2_start_upsert) - s1_start_upsert + s1_start_upsert(s4_wakeup_s2) s4_wakeup_to_set_dead s2_start_upsert(s1_start_upsert) s4_wakeup_s1 @@ -94,7 +94,7 @@ permutation permutation s3_setup_wait_before_swap s3_start_reindex(s1_start_upsert, s2_start_upsert) - s1_start_upsert + s1_start_upsert(s4_wakeup_s2) s4_wakeup_to_swap s2_start_upsert(s1_start_upsert) s4_wakeup_s2 @@ -103,7 +103,7 @@ permutation permutation s3_setup_wait_before_set_dead s3_start_reindex(s1_start_upsert, s2_start_upsert) - s1_start_upsert + s1_start_upsert(s4_wakeup_s2) s2_start_upsert(s1_start_upsert) s4_wakeup_s1 s4_wakeup_to_set_dead diff --git a/src/test/modules/injection_points/specs/reindex-concurrently-upsert-partitioned.spec b/src/test/modules/injection_points/specs/reindex-concurrently-upsert-partitioned.spec index b57e11f3947..c3504b9ef38 100644 --- a/src/test/modules/injection_points/specs/reindex-concurrently-upsert-partitioned.spec +++ b/src/test/modules/injection_points/specs/reindex-concurrently-upsert-partitioned.spec @@ -88,7 +88,7 @@ step s4_wakeup_to_set_dead permutation s3_setup_wait_before_set_dead s3_start_reindex(s1_start_upsert, s2_start_upsert) - s1_start_upsert + s1_start_upsert(s4_wakeup_s2) s4_wakeup_to_set_dead s2_start_upsert(s1_start_upsert) s4_wakeup_s1 @@ -97,7 +97,7 @@ permutation permutation s3_setup_wait_before_swap s3_start_reindex(s1_start_upsert, s2_start_upsert) - s1_start_upsert + s1_start_upsert(s4_wakeup_s2) s4_wakeup_to_swap s2_start_upsert(s1_start_upsert) s4_wakeup_s2 @@ -106,7 +106,7 @@ permutation permutation s3_setup_wait_before_set_dead s3_start_reindex(s1_start_upsert, s2_start_upsert) - s1_start_upsert + s1_start_upsert(s4_wakeup_s2) s2_start_upsert(s1_start_upsert) s4_wakeup_s1 s4_wakeup_to_set_dead diff --git a/src/test/modules/injection_points/specs/reindex-concurrently-upsert.spec b/src/test/modules/injection_points/specs/reindex-concurrently-upsert.spec index f12b2fca486..1b043a48ff4 100644 --- a/src/test/modules/injection_points/specs/reindex-concurrently-upsert.spec +++ b/src/test/modules/injection_points/specs/reindex-concurrently-upsert.spec @@ -86,7 +86,7 @@ step s4_wakeup_to_set_dead permutation s3_setup_wait_before_set_dead s3_start_reindex(s1_start_upsert, s2_start_upsert) - s1_start_upsert + s1_start_upsert(s4_wakeup_s2) s4_wakeup_to_set_dead s2_start_upsert(s1_start_upsert) s4_wakeup_s1 @@ -95,7 +95,7 @@ permutation permutation s3_setup_wait_before_swap s3_start_reindex(s1_start_upsert, s2_start_upsert) - s1_start_upsert + s1_start_upsert(s4_wakeup_s2) s4_wakeup_to_swap s2_start_upsert(s1_start_upsert) s4_wakeup_s2 @@ -104,7 +104,7 @@ permutation permutation s3_setup_wait_before_set_dead s3_start_reindex(s1_start_upsert, s2_start_upsert) - s1_start_upsert + s1_start_upsert(s4_wakeup_s2) s2_start_upsert(s1_start_upsert) s4_wakeup_s1 s4_wakeup_to_set_dead diff --git a/src/test/modules/test_slru/t/002_multixact_wraparound.pl b/src/test/modules/test_slru/t/002_multixact_wraparound.pl index de37d845b11..169333fc564 100644 --- a/src/test/modules/test_slru/t/002_multixact_wraparound.pl +++ b/src/test/modules/test_slru/t/002_multixact_wraparound.pl @@ -25,19 +25,31 @@ command_ok( ], "set the cluster's next multitransaction to 0xFFFFFFF8"); +# Extract a few values from pg_resetwal --dry-run output that we need for +# the calculations below +my $out = (run_command([ 'pg_resetwal', '--dry-run', $node->data_dir ]))[0]; +$out =~ /^Database block size: *(\d+)$/m or die; +my $blcksz = $1; +$out =~ /^Pages per SLRU segment: *(\d+)$/m or die; +my $slru_pages_per_segment = $1; + # Fixup the SLRU files to match the state we reset to. -# initialize SLRU file with zeros (65536 entries * 4 bytes = 262144 bytes) -my $slru_file = "$node_pgdata/pg_multixact/offsets/FFFF"; +# initialize the 'offsets' SLRU file containing the new next multixid +# with zeros +my $multixact_offsets_per_page = $blcksz / 4; # sizeof(MultiXactOffset) == 4 +my $segno = + int(0xFFFFFFF8 / $multixact_offsets_per_page / $slru_pages_per_segment); +my $slru_file = sprintf('%s/pg_multixact/offsets/%04X', $node_pgdata, $segno); open my $fh, ">", $slru_file or die "could not open \"$slru_file\": $!"; binmode $fh; -# Write 65536 entries of 4 bytes each (all zeros) -syswrite($fh, "\0" x 262144) == 262144 +my $bytes_per_seg = $slru_pages_per_segment * $blcksz; +syswrite($fh, "\0" x $bytes_per_seg) == $bytes_per_seg or die "could not write to \"$slru_file\": $!"; close $fh; -# remove old SLRU file +# remove old file unlink("$node_pgdata/pg_multixact/offsets/0000") or die "could not unlink \"$node_pgdata/pg_multixact/offsets/0000\": $!"; diff --git a/src/test/perl/PostgreSQL/Test/Cluster.pm b/src/test/perl/PostgreSQL/Test/Cluster.pm index 35413f14019..747528c4af1 100644 --- a/src/test/perl/PostgreSQL/Test/Cluster.pm +++ b/src/test/perl/PostgreSQL/Test/Cluster.pm @@ -1647,10 +1647,10 @@ sub new or BAIL_OUT("could not create data directory \"$node->{_basedir}\": $!"); - $node->dump_info; - $node->_set_pg_version; + $node->dump_info; + my $ver = $node->{_pg_version}; # Use a subclass as defined below (or elsewhere) if this version diff --git a/src/test/recovery/t/048_vacuum_horizon_floor.pl b/src/test/recovery/t/048_vacuum_horizon_floor.pl index 668eedd71b2..9cdf6cee8a7 100644 --- a/src/test/recovery/t/048_vacuum_horizon_floor.pl +++ b/src/test/recovery/t/048_vacuum_horizon_floor.pl @@ -194,7 +194,7 @@ $node_primary->poll_query_until( qq[ SELECT count(*) >= 1 FROM pg_stat_activity WHERE pid = $vacuum_pid - AND wait_event = 'BufferPin'; + AND wait_event = 'BufferCleanup'; ], 't'); 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) |