postgres.git / summary / log / commit / refs
commit 1db689715d44276407dc4d6fadbc11da8d391bd9
Author: David Rowley <drowley@postgresql.org>
Date: Thu Mar 21 08:21:05 2024 +0000
First released in: master (reverted before release)
Reverted by: ec1a405adc24 (in 17.0)
Temporarily install debugging in partition_prune test
The buildfarm animal parula has been sporadically failing in the
partition_prune test for the past week or so. It appears like an
auto-vacuum or auto-analyze has run on one of the partitions of the "ab"
table, causing the plan to change. This is unexpected as the table is
empty.
Here we install some telemetry to find out if this is the case. This
also joins in pg_index to see if something has gone wrong with the index
which could result in the planner being unable to use that index.
We can revert this once we've figured out the cause of the plan
instability.
Reported-by: Tom Lane
Investigation-by: Tom Lane
Discussion: https://postgr.es/m/4009739.1710878318%40sss.pgh.pa.us
src/test/regress/expected/partition_prune.out | 62 +++++++++++++++++++++++++++
src/test/regress/sql/partition_prune.sql | 14 ++++++
2 files changed, 76 insertions(+)
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 7ca98397aec..46b78ba3c41 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -2449,6 +2449,37 @@ create index ab_a3_b3_a_idx on ab_a3_b3 (a);
set enable_hashjoin = 0;
set enable_mergejoin = 0;
set enable_memoize = 0;
+-- Temporarily install some debugging to investigate plan instability.
+select c.relname,c.relpages,c.reltuples,i.indisvalid,s.autovacuum_count,s.autoanalyze_count
+from pg_class c
+left join pg_stat_all_tables s on c.oid = s.relid
+left join pg_index i on c.oid = i.indexrelid
+where c.relname like 'ab\_%' order by c.relname;
+ relname | relpages | reltuples | indisvalid | autovacuum_count | autoanalyze_count
+----------------+----------+-----------+------------+------------------+-------------------
+ ab_a1 | 0 | -1 | | 0 | 0
+ ab_a1_b1 | 0 | -1 | | 0 | 0
+ ab_a1_b1_a_idx | 1 | 0 | t | |
+ ab_a1_b2 | 0 | -1 | | 0 | 0
+ ab_a1_b2_a_idx | 1 | 0 | t | |
+ ab_a1_b3 | 0 | -1 | | 0 | 0
+ ab_a1_b3_a_idx | 1 | 0 | t | |
+ ab_a2 | 0 | -1 | | 0 | 0
+ ab_a2_b1 | 0 | -1 | | 0 | 0
+ ab_a2_b1_a_idx | 1 | 0 | t | |
+ ab_a2_b2 | 0 | -1 | | 0 | 0
+ ab_a2_b2_a_idx | 1 | 0 | t | |
+ ab_a2_b3 | 0 | -1 | | 0 | 0
+ ab_a2_b3_a_idx | 1 | 0 | t | |
+ ab_a3 | 0 | -1 | | 0 | 0
+ ab_a3_b1 | 0 | -1 | | 0 | 0
+ ab_a3_b1_a_idx | 1 | 0 | t | |
+ ab_a3_b2 | 0 | -1 | | 0 | 0
+ ab_a3_b2_a_idx | 1 | 0 | t | |
+ ab_a3_b3 | 0 | -1 | | 0 | 0
+ ab_a3_b3_a_idx | 1 | 0 | t | |
+(21 rows)
+
select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(0, 0, 1)');
explain_parallel_append
--------------------------------------------------------------------------------------------------------
@@ -2824,6 +2855,37 @@ deallocate ab_q3;
deallocate ab_q4;
deallocate ab_q5;
deallocate ab_q6;
+-- Temporarily install some debugging to investigate plan instability.
+select c.relname,c.relpages,c.reltuples,i.indisvalid,s.autovacuum_count,s.autoanalyze_count
+from pg_class c
+left join pg_stat_all_tables s on c.oid = s.relid
+left join pg_index i on c.oid = i.indexrelid
+where c.relname like 'ab\_%' order by c.relname;
+ relname | relpages | reltuples | indisvalid | autovacuum_count | autoanalyze_count
+----------------+----------+-----------+------------+------------------+-------------------
+ ab_a1 | 0 | -1 | | 0 | 0
+ ab_a1_b1 | 0 | -1 | | 0 | 0
+ ab_a1_b1_a_idx | 1 | 0 | t | |
+ ab_a1_b2 | 0 | -1 | | 0 | 0
+ ab_a1_b2_a_idx | 1 | 0 | t | |
+ ab_a1_b3 | 0 | -1 | | 0 | 0
+ ab_a1_b3_a_idx | 1 | 0 | t | |
+ ab_a2 | 0 | -1 | | 0 | 0
+ ab_a2_b1 | 0 | -1 | | 0 | 0
+ ab_a2_b1_a_idx | 1 | 0 | t | |
+ ab_a2_b2 | 0 | -1 | | 0 | 0
+ ab_a2_b2_a_idx | 1 | 0 | t | |
+ ab_a2_b3 | 0 | -1 | | 0 | 0
+ ab_a2_b3_a_idx | 1 | 0 | t | |
+ ab_a3 | 0 | -1 | | 0 | 0
+ ab_a3_b1 | 0 | -1 | | 0 | 0
+ ab_a3_b1_a_idx | 1 | 0 | t | |
+ ab_a3_b2 | 0 | -1 | | 0 | 0
+ ab_a3_b2_a_idx | 1 | 0 | t | |
+ ab_a3_b3 | 0 | -1 | | 0 | 0
+ ab_a3_b3_a_idx | 1 | 0 | t | |
+(21 rows)
+
-- UPDATE on a partition subtree has been seen to have problems.
insert into ab values (1,2);
explain (analyze, costs off, summary off, timing off)
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index a09b27d820c..dc716938610 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -607,6 +607,13 @@ set enable_hashjoin = 0;
set enable_mergejoin = 0;
set enable_memoize = 0;
+-- Temporarily install some debugging to investigate plan instability.
+select c.relname,c.relpages,c.reltuples,i.indisvalid,s.autovacuum_count,s.autoanalyze_count
+from pg_class c
+left join pg_stat_all_tables s on c.oid = s.relid
+left join pg_index i on c.oid = i.indexrelid
+where c.relname like 'ab\_%' order by c.relname;
+
select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(0, 0, 1)');
-- Ensure the same partitions are pruned when we make the nested loop
@@ -674,6 +681,13 @@ deallocate ab_q4;
deallocate ab_q5;
deallocate ab_q6;
+-- Temporarily install some debugging to investigate plan instability.
+select c.relname,c.relpages,c.reltuples,i.indisvalid,s.autovacuum_count,s.autoanalyze_count
+from pg_class c
+left join pg_stat_all_tables s on c.oid = s.relid
+left join pg_index i on c.oid = i.indexrelid
+where c.relname like 'ab\_%' order by c.relname;
+
-- UPDATE on a partition subtree has been seen to have problems.
insert into ab values (1,2);
explain (analyze, costs off, summary off, timing off)
[parent: 6185c9737cf4]