Hello Tom,
29.03.2024 16:51, Tom Lane wrote:
Alexander Lakhin <exclus...@gmail.com> writes:
I think that deviation can be explained by the fact that cost_index() takes
baserel->allvisfrac (derived from pg_class.relallvisible) into account for
the index-only-scan case, and I see the following difference when a test
run fails:
relname | relpages | reltuples | relallvisible | indisvalid |
autovacuum_count | autoanalyze_count
----------------------+----------+-----------+---------------+------------+------------------+-------------------
- tenk1 | 345 | 10000 | 345 | |
0 | 0
+ tenk1 | 345 | 10000 | 305 | |
0 | 0
Ouch. So what's triggering that? The intention of test_setup
surely is to provide a uniform starting point.
Thanks for your attention to the issue!
Please try the attached...
Best regards,
Alexander
diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c
index f0f8d4259c..d60c117378 100644
--- a/src/backend/storage/buffer/bufmgr.c
+++ b/src/backend/storage/buffer/bufmgr.c
@@ -5041,6 +5041,7 @@ ConditionalLockBufferForCleanup(Buffer buffer)
Assert(BufferIsValid(buffer));
+if (rand() % 10 == 0) return false;
if (BufferIsLocal(buffer))
{
refcount = LocalRefCount[-buffer - 1];
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index 70ab47a92f..93c1f4f08d 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1932,6 +1932,32 @@ ORDER BY unique1;
42
(3 rows)
+explain (verbose)
+SELECT thousand, tenthous FROM tenk1
+WHERE thousand < 2 AND tenthous IN (1001,3000)
+ORDER BY thousand;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
+ Index Only Scan using tenk1_thous_tenthous on public.tenk1 (cost=0.29..4.71 rows=1 width=8)
+ Output: thousand, tenthous
+ Index Cond: (tenk1.thousand < 2)
+ Filter: (tenk1.tenthous = ANY ('{1001,3000}'::integer[]))
+(4 rows)
+
+select c.relname,c.relpages,c.reltuples,c.relallvisible,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 'tenk1%' order by c.relname;
+ relname | relpages | reltuples | relallvisible | indisvalid | autovacuum_count | autoanalyze_count
+----------------------+----------+-----------+---------------+------------+------------------+-------------------
+ tenk1 | 345 | 10000 | 345 | | 0 | 0
+ tenk1_hundred | 11 | 10000 | 0 | t | |
+ tenk1_thous_tenthous | 30 | 10000 | 0 | t | |
+ tenk1_unique1 | 30 | 10000 | 0 | t | |
+ tenk1_unique2 | 30 | 10000 | 0 | t | |
+(5 rows)
+
explain (costs off)
SELECT thousand, tenthous FROM tenk1
WHERE thousand < 2 AND tenthous IN (1001,3000)
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index d49ce9f300..732b54fb35 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -765,6 +765,17 @@ SELECT unique1 FROM tenk1
WHERE unique1 IN (1,42,7)
ORDER BY unique1;
+explain (verbose)
+SELECT thousand, tenthous FROM tenk1
+WHERE thousand < 2 AND tenthous IN (1001,3000)
+ORDER BY thousand;
+
+select c.relname,c.relpages,c.reltuples,c.relallvisible,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 'tenk1%' order by c.relname;
+
explain (costs off)
SELECT thousand, tenthous FROM tenk1
WHERE thousand < 2 AND tenthous IN (1001,3000)
