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

Commitb4b6923

Browse files
committed
Fix VACUUM so that it always updates pg_class.reltuples/relpages.
When we added the ability for vacuum to skip heap pages by consulting thevisibility map, we made it just not update the reltuples/relpagesstatistics if it skipped any pages. But this could leave us with extremelyout-of-date stats for a table that contains any unchanging areas,especially for TOAST tables which never get processed by ANALYZE. Inparticular this could result in autovacuum making poor decisions about whento process the table, as in recent report from Florian Helmberger. And ingeneral it's a bad idea to not update the stats at all. Instead, use theprevious values of reltuples/relpages as an estimate of the tuple densityin unvisited pages. This approach results in a "moving average" estimateof reltuples, which should converge to the correct value over multipleVACUUM and ANALYZE cycles even when individual measurements aren't verygood.This new method for updating reltuples is used by both VACUUM and ANALYZE,with the result that we no longer need the grotty interconnections thatcaused ANALYZE to not update the stats depending on what had happenedin the parent VACUUM command.Also, fix the logic for skipping all-visible pages during VACUUM so that itlooks ahead rather than behind to decide what to do, as per a suggestionfrom Greg Stark. This eliminates useless scanning of all-visible pages atthe start of the relation or just after a not-all-visible page. Inparticular, the first few pages of the relation will not be invariablyincluded in the scanned pages, which seems to help in not overweightingthem in the reltuples estimate.Back-patch to 8.4, where the visibility map was introduced.
1 parent3001b76 commitb4b6923

File tree

6 files changed

+228
-129
lines changed

6 files changed

+228
-129
lines changed

‎src/backend/commands/analyze.c

Lines changed: 24 additions & 35 deletions
Original file line numberDiff line numberDiff line change
@@ -84,8 +84,7 @@ static MemoryContext anl_context = NULL;
8484
staticBufferAccessStrategyvac_strategy;
8585

8686

87-
staticvoiddo_analyze_rel(Relationonerel,VacuumStmt*vacstmt,
88-
boolupdate_reltuples,boolinh);
87+
staticvoiddo_analyze_rel(Relationonerel,VacuumStmt*vacstmt,boolinh);
8988
staticvoidBlockSampler_Init(BlockSamplerbs,BlockNumbernblocks,
9089
intsamplesize);
9190
staticboolBlockSampler_HasMore(BlockSamplerbs);
@@ -115,18 +114,9 @@ static bool std_typanalyze(VacAttrStats *stats);
115114

116115
/*
117116
*analyze_rel() -- analyze one relation
118-
*
119-
* If update_reltuples is true, we update reltuples and relpages columns
120-
* in pg_class. Caller should pass false if we're part of VACUUM ANALYZE,
121-
* and the VACUUM didn't skip any pages. We only have an approximate count,
122-
* so we don't want to overwrite the accurate values already inserted by the
123-
* VACUUM in that case. VACUUM always scans all indexes, however, so the
124-
* pg_class entries for indexes are never updated if we're part of VACUUM
125-
* ANALYZE.
126117
*/
127118
void
128-
analyze_rel(Oidrelid,VacuumStmt*vacstmt,
129-
BufferAccessStrategybstrategy,boolupdate_reltuples)
119+
analyze_rel(Oidrelid,VacuumStmt*vacstmt,BufferAccessStrategybstrategy)
130120
{
131121
Relationonerel;
132122

@@ -238,13 +228,13 @@ analyze_rel(Oid relid, VacuumStmt *vacstmt,
238228
/*
239229
* Do the normal non-recursive ANALYZE.
240230
*/
241-
do_analyze_rel(onerel,vacstmt,update_reltuples,false);
231+
do_analyze_rel(onerel,vacstmt, false);
242232

243233
/*
244234
* If there are child tables, do recursive ANALYZE.
245235
*/
246236
if (onerel->rd_rel->relhassubclass)
247-
do_analyze_rel(onerel,vacstmt,false,true);
237+
do_analyze_rel(onerel,vacstmt, true);
248238

249239
/*
250240
* Close source relation now, but keep lock so that no one deletes it
@@ -267,8 +257,7 @@ analyze_rel(Oid relid, VacuumStmt *vacstmt,
267257
*do_analyze_rel() -- analyze one relation, recursively or not
268258
*/
269259
staticvoid
270-
do_analyze_rel(Relationonerel,VacuumStmt*vacstmt,
271-
boolupdate_reltuples,boolinh)
260+
do_analyze_rel(Relationonerel,VacuumStmt*vacstmt,boolinh)
272261
{
273262
intattr_cnt,
274263
tcnt,
@@ -437,9 +426,9 @@ do_analyze_rel(Relation onerel, VacuumStmt *vacstmt,
437426
}
438427

439428
/*
440-
* Quit if no analyzable columns and no pg_class update needed.
429+
* Quit if no analyzable columns.
441430
*/
442-
if (attr_cnt <=0&& !analyzableindex&& !update_reltuples)
431+
if (attr_cnt <=0&& !analyzableindex)
443432
gotocleanup;
444433

445434
/*
@@ -549,10 +538,10 @@ do_analyze_rel(Relation onerel, VacuumStmt *vacstmt,
549538
}
550539

551540
/*
552-
* Update pages/tuples stats in pg_class,but not if we'reinside a VACUUM
553-
*that got a more precise number.
541+
* Update pages/tuples stats in pg_class ...but not if we'redoing
542+
*inherited stats.
554543
*/
555-
if (update_reltuples)
544+
if (!inh)
556545
vac_update_relstats(onerel,
557546
RelationGetNumberOfBlocks(onerel),
558547
totalrows,hasindex,InvalidTransactionId);
@@ -562,7 +551,7 @@ do_analyze_rel(Relation onerel, VacuumStmt *vacstmt,
562551
* VACUUM ANALYZE, don't overwrite the accurate count already inserted by
563552
* VACUUM.
564553
*/
565-
if (!(vacstmt->options&VACOPT_VACUUM))
554+
if (!inh&& !(vacstmt->options&VACOPT_VACUUM))
566555
{
567556
for (ind=0;ind<nindexes;ind++)
568557
{
@@ -577,13 +566,12 @@ do_analyze_rel(Relation onerel, VacuumStmt *vacstmt,
577566
}
578567

579568
/*
580-
* Report ANALYZE to the stats collector, too; likewise, tell it to adopt
581-
*these numbers only if we're not inside a VACUUM that got a better
582-
*number.However, a call with inh = true shouldn't reset the stats.
569+
* Report ANALYZE to the stats collector, too. However, if doing
570+
*inherited stats we shouldn't report, because the stats collector only
571+
*tracks per-table stats.
583572
*/
584573
if (!inh)
585-
pgstat_report_analyze(onerel,update_reltuples,
586-
totalrows,totaldeadrows);
574+
pgstat_report_analyze(onerel,totalrows,totaldeadrows);
587575

588576
/* We skip to here if there were no analyzable columns */
589577
cleanup:
@@ -1243,18 +1231,19 @@ acquire_sample_rows(Relation onerel, HeapTuple *rows, int targrows,
12431231
qsort((void*)rows,numrows,sizeof(HeapTuple),compare_rows);
12441232

12451233
/*
1246-
* Estimate total numbers of rows in relation.
1234+
* Estimate total numbers of rows in relation. For live rows, use
1235+
* vac_estimate_reltuples; for dead rows, we have no source of old
1236+
* information, so we have to assume the density is the same in unseen
1237+
* pages as in the pages we scanned.
12471238
*/
1239+
*totalrows=vac_estimate_reltuples(onerel, true,
1240+
totalblocks,
1241+
bs.m,
1242+
liverows);
12481243
if (bs.m>0)
1249-
{
1250-
*totalrows=floor((liverows*totalblocks) /bs.m+0.5);
1251-
*totaldeadrows=floor((deadrows*totalblocks) /bs.m+0.5);
1252-
}
1244+
*totaldeadrows=floor((deadrows /bs.m)*totalblocks+0.5);
12531245
else
1254-
{
1255-
*totalrows=0.0;
12561246
*totaldeadrows=0.0;
1257-
}
12581247

12591248
/*
12601249
* Emit some interesting relation info

‎src/backend/commands/vacuum.c

Lines changed: 82 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -20,6 +20,8 @@
2020
*/
2121
#include"postgres.h"
2222

23+
#include<math.h>
24+
2325
#include"access/clog.h"
2426
#include"access/genam.h"
2527
#include"access/heapam.h"
@@ -62,7 +64,7 @@ static BufferAccessStrategy vac_strategy;
6264
staticList*get_rel_oids(Oidrelid,constRangeVar*vacrel);
6365
staticvoidvac_truncate_clog(TransactionIdfrozenXID);
6466
staticboolvacuum_rel(Oidrelid,VacuumStmt*vacstmt,booldo_toast,
65-
boolfor_wraparound,bool*scanned_all);
67+
boolfor_wraparound);
6668

6769

6870
/*
@@ -219,12 +221,10 @@ vacuum(VacuumStmt *vacstmt, Oid relid, bool do_toast,
219221
foreach(cur,relations)
220222
{
221223
Oidrelid=lfirst_oid(cur);
222-
boolscanned_all= false;
223224

224225
if (vacstmt->options&VACOPT_VACUUM)
225226
{
226-
if (!vacuum_rel(relid,vacstmt,do_toast,for_wraparound,
227-
&scanned_all))
227+
if (!vacuum_rel(relid,vacstmt,do_toast,for_wraparound))
228228
continue;
229229
}
230230

@@ -241,7 +241,7 @@ vacuum(VacuumStmt *vacstmt, Oid relid, bool do_toast,
241241
PushActiveSnapshot(GetTransactionSnapshot());
242242
}
243243

244-
analyze_rel(relid,vacstmt,vac_strategy, !scanned_all);
244+
analyze_rel(relid,vacstmt,vac_strategy);
245245

246246
if (use_own_xacts)
247247
{
@@ -453,6 +453,79 @@ vacuum_set_xid_limits(int freeze_min_age,
453453
}
454454

455455

456+
/*
457+
* vac_estimate_reltuples() -- estimate the new value for pg_class.reltuples
458+
*
459+
*If we scanned the whole relation then we should just use the count of
460+
*live tuples seen; but if we did not, we should not trust the count
461+
*unreservedly, especially not in VACUUM, which may have scanned a quite
462+
*nonrandom subset of the table. When we have only partial information,
463+
*we take the old value of pg_class.reltuples as a measurement of the
464+
*tuple density in the unscanned pages.
465+
*
466+
*This routine is shared by VACUUM and ANALYZE.
467+
*/
468+
double
469+
vac_estimate_reltuples(Relationrelation,boolis_analyze,
470+
BlockNumbertotal_pages,
471+
BlockNumberscanned_pages,
472+
doublescanned_tuples)
473+
{
474+
BlockNumberold_rel_pages=relation->rd_rel->relpages;
475+
doubleold_rel_tuples=relation->rd_rel->reltuples;
476+
doubleold_density;
477+
doublenew_density;
478+
doublemultiplier;
479+
doubleupdated_density;
480+
481+
/* If we did scan the whole table, just use the count as-is */
482+
if (scanned_pages >=total_pages)
483+
returnscanned_tuples;
484+
485+
/*
486+
* If scanned_pages is zero but total_pages isn't, keep the existing
487+
* value of reltuples.
488+
*/
489+
if (scanned_pages==0)
490+
returnold_rel_tuples;
491+
492+
/*
493+
* If old value of relpages is zero, old density is indeterminate; we
494+
* can't do much except scale up scanned_tuples to match total_pages.
495+
*/
496+
if (old_rel_pages==0)
497+
returnfloor((scanned_tuples /scanned_pages)*total_pages+0.5);
498+
499+
/*
500+
* Okay, we've covered the corner cases. The normal calculation is to
501+
* convert the old measurement to a density (tuples per page), then
502+
* update the density using an exponential-moving-average approach,
503+
* and finally compute reltuples as updated_density * total_pages.
504+
*
505+
* For ANALYZE, the moving average multiplier is just the fraction of
506+
* the table's pages we scanned. This is equivalent to assuming
507+
* that the tuple density in the unscanned pages didn't change. Of
508+
* course, it probably did, if the new density measurement is different.
509+
* But over repeated cycles, the value of reltuples will converge towards
510+
* the correct value, if repeated measurements show the same new density.
511+
*
512+
* For VACUUM, the situation is a bit different: we have looked at a
513+
* nonrandom sample of pages, but we know for certain that the pages we
514+
* didn't look at are precisely the ones that haven't changed lately.
515+
* Thus, there is a reasonable argument for doing exactly the same thing
516+
* as for the ANALYZE case, that is use the old density measurement as
517+
* the value for the unscanned pages.
518+
*
519+
* This logic could probably use further refinement.
520+
*/
521+
old_density=old_rel_tuples /old_rel_pages;
522+
new_density=scanned_tuples /scanned_pages;
523+
multiplier= (double)scanned_pages / (double)total_pages;
524+
updated_density=old_density+ (new_density-old_density)*multiplier;
525+
returnfloor(updated_density*total_pages+0.5);
526+
}
527+
528+
456529
/*
457530
*vac_update_relstats() -- update statistics for one relation
458531
*
@@ -480,7 +553,7 @@ vacuum_set_xid_limits(int freeze_min_age,
480553
*somebody vacuuming pg_class might think they could delete a tuple
481554
*marked with xmin = our xid.
482555
*
483-
*This routine is shared by VACUUM andstand-aloneANALYZE.
556+
*This routine is shared by VACUUM and ANALYZE.
484557
*/
485558
void
486559
vac_update_relstats(Relationrelation,
@@ -758,14 +831,10 @@ vac_truncate_clog(TransactionId frozenXID)
758831
*many small transactions. Otherwise, two-phase locking would require
759832
*us to lock the entire database during one pass of the vacuum cleaner.
760833
*
761-
*We'll return true in *scanned_all if the vacuum scanned all heap
762-
*pages, and updated pg_class.
763-
*
764834
*At entry and exit, we are not inside a transaction.
765835
*/
766836
staticbool
767-
vacuum_rel(Oidrelid,VacuumStmt*vacstmt,booldo_toast,boolfor_wraparound,
768-
bool*scanned_all)
837+
vacuum_rel(Oidrelid,VacuumStmt*vacstmt,booldo_toast,boolfor_wraparound)
769838
{
770839
LOCKMODElmode;
771840
Relationonerel;
@@ -775,9 +844,6 @@ vacuum_rel(Oid relid, VacuumStmt *vacstmt, bool do_toast, bool for_wraparound,
775844
intsave_sec_context;
776845
intsave_nestlevel;
777846

778-
if (scanned_all)
779-
*scanned_all= false;
780-
781847
/* Begin a transaction for vacuuming this relation */
782848
StartTransactionCommand();
783849

@@ -971,7 +1037,7 @@ vacuum_rel(Oid relid, VacuumStmt *vacstmt, bool do_toast, bool for_wraparound,
9711037
vacstmt->freeze_min_age,vacstmt->freeze_table_age);
9721038
}
9731039
else
974-
lazy_vacuum_rel(onerel,vacstmt,vac_strategy,scanned_all);
1040+
lazy_vacuum_rel(onerel,vacstmt,vac_strategy);
9751041

9761042
/* Roll back any GUC changes executed by index functions */
9771043
AtEOXact_GUC(false,save_nestlevel);
@@ -997,7 +1063,7 @@ vacuum_rel(Oid relid, VacuumStmt *vacstmt, bool do_toast, bool for_wraparound,
9971063
* totally unimportant for toast relations.
9981064
*/
9991065
if (toast_relid!=InvalidOid)
1000-
vacuum_rel(toast_relid,vacstmt, false,for_wraparound,NULL);
1066+
vacuum_rel(toast_relid,vacstmt, false,for_wraparound);
10011067

10021068
/*
10031069
* Now release the session-level lock on the master table.

0 commit comments

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