Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit9b626f6

Browse files
committed
Avoid having vacuum set reltuples to 0 on non-empty relations in the
presence of page pins, which leads to serious estimation errors in theplanner. This particularly affects small heavily-accessed tables,especially where locking (e.g. from FK constraints) forces frequentvacuums for mxid cleanup.Fix by keeping separate track of pages whose live tuples were actuallycounted vs. pages that were only scanned for freezing purposes. Thus,reltuples can only be set to 0 if all pages of the relation wereactually counted.Backpatch to all supported versions.Per bug #14057 from Nicolas Baccelli, analyzed by me.Discussion:https://postgr.es/m/20160331103739.8956.94469@wrigleys.postgresql.org
1 parent41306a5 commit9b626f6

File tree

4 files changed

+119
-4
lines changed

4 files changed

+119
-4
lines changed

‎src/backend/commands/vacuumlazy.c

Lines changed: 11 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -108,7 +108,8 @@ typedef struct LVRelStats
108108
BlockNumberscanned_pages;/* number of pages we examined */
109109
BlockNumberpinskipped_pages;/* # of pages we skipped due to a pin */
110110
BlockNumberfrozenskipped_pages;/* # of frozen pages we skipped */
111-
doublescanned_tuples;/* counts only tuples on scanned pages */
111+
BlockNumbertupcount_pages;/* pages whose tuples we counted */
112+
doublescanned_tuples;/* counts only tuples on tupcount_pages */
112113
doubleold_rel_tuples;/* previous value of pg_class.reltuples */
113114
doublenew_rel_tuples;/* new estimated total # of tuples */
114115
doublenew_dead_tuples;/* new estimated total # of dead tuples */
@@ -293,6 +294,10 @@ lazy_vacuum_rel(Relation onerel, int options, VacuumParams *params,
293294
* density") with nonzero relpages and reltuples=0 (which means "zero
294295
* tuple density") unless there's some actual evidence for the latter.
295296
*
297+
* It's important that we use tupcount_pages and not scanned_pages for the
298+
* check described above; scanned_pages counts pages where we could not
299+
* get cleanup lock, and which were processed only for frozenxid purposes.
300+
*
296301
* We do update relallvisible even in the corner case, since if the table
297302
* is all-visible we'd definitely like to know that. But clamp the value
298303
* to be not more than what we're setting relpages to.
@@ -302,7 +307,7 @@ lazy_vacuum_rel(Relation onerel, int options, VacuumParams *params,
302307
*/
303308
new_rel_pages=vacrelstats->rel_pages;
304309
new_rel_tuples=vacrelstats->new_rel_tuples;
305-
if (vacrelstats->scanned_pages==0&&new_rel_pages>0)
310+
if (vacrelstats->tupcount_pages==0&&new_rel_pages>0)
306311
{
307312
new_rel_pages=vacrelstats->old_rel_pages;
308313
new_rel_tuples=vacrelstats->old_rel_tuples;
@@ -489,6 +494,7 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
489494
nblocks=RelationGetNumberOfBlocks(onerel);
490495
vacrelstats->rel_pages=nblocks;
491496
vacrelstats->scanned_pages=0;
497+
vacrelstats->tupcount_pages=0;
492498
vacrelstats->nonempty_pages=0;
493499
vacrelstats->latestRemovedXid=InvalidTransactionId;
494500

@@ -811,6 +817,7 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
811817
}
812818

813819
vacrelstats->scanned_pages++;
820+
vacrelstats->tupcount_pages++;
814821

815822
page=BufferGetPage(buf);
816823

@@ -1254,7 +1261,7 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
12541261
/* now we can compute the new value for pg_class.reltuples */
12551262
vacrelstats->new_rel_tuples=vac_estimate_reltuples(onerel, false,
12561263
nblocks,
1257-
vacrelstats->scanned_pages,
1264+
vacrelstats->tupcount_pages,
12581265
num_tuples);
12591266

12601267
/*
@@ -1618,7 +1625,7 @@ lazy_cleanup_index(Relation indrel,
16181625

16191626
ivinfo.index=indrel;
16201627
ivinfo.analyze_only= false;
1621-
ivinfo.estimated_count= (vacrelstats->scanned_pages<vacrelstats->rel_pages);
1628+
ivinfo.estimated_count= (vacrelstats->tupcount_pages<vacrelstats->rel_pages);
16221629
ivinfo.message_level=elevel;
16231630
ivinfo.num_heap_tuples=vacrelstats->new_rel_tuples;
16241631
ivinfo.strategy=vac_strategy;
Lines changed: 62 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,62 @@
1+
Parsed test spec with 2 sessions
2+
3+
starting permutation: modify vac stats
4+
step modify:
5+
insert into smalltbl select max(id)+1 from smalltbl;
6+
delete from smalltbl where id in (select min(id) from smalltbl);
7+
8+
step vac:
9+
vacuum smalltbl;
10+
11+
step stats:
12+
select relpages, reltuples from pg_class
13+
where oid='smalltbl'::regclass;
14+
15+
relpages reltuples
16+
17+
1 20
18+
19+
starting permutation: modify open fetch1 vac close stats
20+
step modify:
21+
insert into smalltbl select max(id)+1 from smalltbl;
22+
delete from smalltbl where id in (select min(id) from smalltbl);
23+
24+
step open:
25+
begin;
26+
declare c1 cursor for select * from smalltbl;
27+
28+
step fetch1:
29+
fetch next from c1;
30+
31+
id
32+
33+
2
34+
step vac:
35+
vacuum smalltbl;
36+
37+
step close:
38+
commit;
39+
40+
step stats:
41+
select relpages, reltuples from pg_class
42+
where oid='smalltbl'::regclass;
43+
44+
relpages reltuples
45+
46+
1 20
47+
48+
starting permutation: modify vac stats
49+
step modify:
50+
insert into smalltbl select max(id)+1 from smalltbl;
51+
delete from smalltbl where id in (select min(id) from smalltbl);
52+
53+
step vac:
54+
vacuum smalltbl;
55+
56+
step stats:
57+
select relpages, reltuples from pg_class
58+
where oid='smalltbl'::regclass;
59+
60+
relpages reltuples
61+
62+
1 20

‎src/test/isolation/isolation_schedule

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -56,4 +56,5 @@ test: alter-table-2
5656
test: alter-table-3
5757
test: create-trigger
5858
test: async-notify
59+
test: vacuum-reltuples
5960
test: timeouts
Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,45 @@
1+
# Test for vacuum's handling of reltuples when pages are skipped due
2+
# to page pins. We absolutely need to avoid setting reltuples=0 in
3+
# such cases, since that interferes badly with planning.
4+
5+
setup {
6+
createtablesmalltbl
7+
asselectiasidfromgenerate_series(1,20)i;
8+
altertablesmalltblset (autovacuum_enabled=off);
9+
}
10+
setup {
11+
vacuumanalyzesmalltbl;
12+
}
13+
14+
teardown {
15+
droptablesmalltbl;
16+
}
17+
18+
session"worker"
19+
step"open" {
20+
begin;
21+
declarec1cursorforselect*fromsmalltbl;
22+
}
23+
step"fetch1" {
24+
fetchnextfromc1;
25+
}
26+
step"close" {
27+
commit;
28+
}
29+
step"stats" {
30+
selectrelpages,reltuplesfrompg_class
31+
whereoid='smalltbl'::regclass;
32+
}
33+
34+
session"vacuumer"
35+
step"vac" {
36+
vacuumsmalltbl;
37+
}
38+
step"modify" {
39+
insertintosmalltblselectmax(id)+1fromsmalltbl;
40+
deletefromsmalltblwhereidin (selectmin(id)fromsmalltbl);
41+
}
42+
43+
permutation"modify""vac""stats"
44+
permutation"modify""open""fetch1""vac""close""stats"
45+
permutation"modify""vac""stats"

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp