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
Appearance settings

Commit d04900d

Browse filesBrowse files
tglsfdctglsfdc
committed
When updating reltuples after ANALYZE, just extrapolate from our sample.
The existing logic for updating pg_class.reltuples trusted the sampling results only for the pages ANALYZE actually visited, preferring to believe the previous tuple density estimate for all the unvisited pages. While there's some rationale for doing that for VACUUM (first that VACUUM is likely to visit a very nonrandom subset of pages, and second that we know for sure that the unvisited pages did not change), there's no such rationale for ANALYZE: by assumption, it's looked at an unbiased random sample of the table's pages. Furthermore, in a very large table ANALYZE will have examined only a tiny fraction of the table's pages, meaning it cannot slew the overall density estimate very far at all. In a table that is physically growing, this causes reltuples to increase nearly proportionally to the change in relpages, regardless of what is actually happening in the table. This has been observed to cause reltuples to become so much larger than reality that it effectively shuts off autovacuum, whose threshold for doing anything is a fraction of reltuples. (Getting to the point where that would happen seems to require some additional, not well understood, conditions. But it's undeniable that if reltuples is seriously off in a large table, ANALYZE alone will not fix it in any reasonable number of iterations, especially not if the table is continuing to grow.) Hence, restrict the use of vac_estimate_reltuples() to VACUUM alone, and in ANALYZE, just extrapolate from the sample pages on the assumption that they provide an accurate model of the whole table. If, by very bad luck, they don't, at least another ANALYZE will fix it; in the old logic a single bad estimate could cause problems indefinitely. In HEAD, let's remove vac_estimate_reltuples' is_analyze argument altogether; it was never used for anything and now it's totally pointless. But keep it in the back branches, in case any third-party code is calling this function. Per bug #15005. Back-patch to all supported branches. David Gould, reviewed by Alexander Kuzmenkov, cosmetic changes by me Discussion: https://postgr.es/m/20180117164916.3fdcf2e9@engels
1 parent 38f7831 commit d04900d
Copy full SHA for d04900d

File tree

Expand file treeCollapse file tree

5 files changed

+27
-44
lines changed
Filter options
Expand file treeCollapse file tree

5 files changed

+27
-44
lines changed

‎contrib/pgstattuple/pgstatapprox.c

Copy file name to clipboardExpand all lines: contrib/pgstattuple/pgstatapprox.c
+1-1Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -184,7 +184,7 @@ statapprox_heap(Relation rel, output_type *stat)
184184

185185
stat->table_len = (uint64) nblocks * BLCKSZ;
186186

187-
stat->tuple_count = vac_estimate_reltuples(rel, false, nblocks, scanned,
187+
stat->tuple_count = vac_estimate_reltuples(rel, nblocks, scanned,
188188
stat->tuple_count + misc_count);
189189

190190
/*

‎src/backend/commands/analyze.c

Copy file name to clipboardExpand all lines: src/backend/commands/analyze.c
+11-8Lines changed: 11 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1249,19 +1249,22 @@ acquire_sample_rows(Relation onerel, int elevel,
12491249
qsort((void *) rows, numrows, sizeof(HeapTuple), compare_rows);
12501250

12511251
/*
1252-
* Estimate total numbers of rows in relation. For live rows, use
1253-
* vac_estimate_reltuples; for dead rows, we have no source of old
1254-
* information, so we have to assume the density is the same in unseen
1255-
* pages as in the pages we scanned.
1252+
* Estimate total numbers of live and dead rows in relation, extrapolating
1253+
* on the assumption that the average tuple density in pages we didn't
1254+
* scan is the same as in the pages we did scan. Since what we scanned is
1255+
* a random sample of the pages in the relation, this should be a good
1256+
* assumption.
12561257
*/
1257-
*totalrows = vac_estimate_reltuples(onerel, true,
1258-
totalblocks,
1259-
bs.m,
1260-
liverows);
12611258
if (bs.m > 0)
1259+
{
1260+
*totalrows = floor((liverows / bs.m) * totalblocks + 0.5);
12621261
*totaldeadrows = floor((deadrows / bs.m) * totalblocks + 0.5);
1262+
}
12631263
else
1264+
{
1265+
*totalrows = 0.0;
12641266
*totaldeadrows = 0.0;
1267+
}
12651268

12661269
/*
12671270
* Emit some interesting relation info

‎src/backend/commands/vacuum.c

Copy file name to clipboardExpand all lines: src/backend/commands/vacuum.c
+13-33Lines changed: 13 additions & 33 deletions
Original file line numberDiff line numberDiff line change
@@ -766,26 +766,23 @@ vacuum_set_xid_limits(Relation rel,
766766
* vac_estimate_reltuples() -- estimate the new value for pg_class.reltuples
767767
*
768768
* If we scanned the whole relation then we should just use the count of
769-
* live tuples seen; but if we did not, we should not trust the count
770-
* unreservedly, especially not in VACUUM, which may have scanned a quite
771-
* nonrandom subset of the table. When we have only partial information,
772-
* we take the old value of pg_class.reltuples as a measurement of the
769+
* live tuples seen; but if we did not, we should not blindly extrapolate
770+
* from that number, since VACUUM may have scanned a quite nonrandom
771+
* subset of the table. When we have only partial information, we take
772+
* the old value of pg_class.reltuples as a measurement of the
773773
* tuple density in the unscanned pages.
774-
*
775-
* This routine is shared by VACUUM and ANALYZE.
776774
*/
777775
double
778-
vac_estimate_reltuples(Relation relation, bool is_analyze,
776+
vac_estimate_reltuples(Relation relation,
779777
BlockNumber total_pages,
780778
BlockNumber scanned_pages,
781779
double scanned_tuples)
782780
{
783781
BlockNumber old_rel_pages = relation->rd_rel->relpages;
784782
double old_rel_tuples = relation->rd_rel->reltuples;
785783
double old_density;
786-
double new_density;
787-
double multiplier;
788-
double updated_density;
784+
double unscanned_pages;
785+
double total_tuples;
789786

790787
/* If we did scan the whole table, just use the count as-is */
791788
if (scanned_pages >= total_pages)
@@ -809,31 +806,14 @@ vac_estimate_reltuples(Relation relation, bool is_analyze,
809806

810807
/*
811808
* Okay, we've covered the corner cases. The normal calculation is to
812-
* convert the old measurement to a density (tuples per page), then update
813-
* the density using an exponential-moving-average approach, and finally
814-
* compute reltuples as updated_density * total_pages.
815-
*
816-
* For ANALYZE, the moving average multiplier is just the fraction of the
817-
* table's pages we scanned. This is equivalent to assuming that the
818-
* tuple density in the unscanned pages didn't change. Of course, it
819-
* probably did, if the new density measurement is different. But over
820-
* repeated cycles, the value of reltuples will converge towards the
821-
* correct value, if repeated measurements show the same new density.
822-
*
823-
* For VACUUM, the situation is a bit different: we have looked at a
824-
* nonrandom sample of pages, but we know for certain that the pages we
825-
* didn't look at are precisely the ones that haven't changed lately.
826-
* Thus, there is a reasonable argument for doing exactly the same thing
827-
* as for the ANALYZE case, that is use the old density measurement as the
828-
* value for the unscanned pages.
829-
*
830-
* This logic could probably use further refinement.
809+
* convert the old measurement to a density (tuples per page), then
810+
* estimate the number of tuples in the unscanned pages using that figure,
811+
* and finally add on the number of tuples in the scanned pages.
831812
*/
832813
old_density = old_rel_tuples / old_rel_pages;
833-
new_density = scanned_tuples / scanned_pages;
834-
multiplier = (double) scanned_pages / (double) total_pages;
835-
updated_density = old_density + (new_density - old_density) * multiplier;
836-
return floor(updated_density * total_pages + 0.5);
814+
unscanned_pages = (double) total_pages - (double) scanned_pages;
815+
total_tuples = old_density * unscanned_pages + scanned_tuples;
816+
return floor(total_tuples + 0.5);
837817
}
838818

839819

‎src/backend/commands/vacuumlazy.c

Copy file name to clipboardExpand all lines: src/backend/commands/vacuumlazy.c
+1-1Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1286,7 +1286,7 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
12861286
vacrelstats->new_dead_tuples = nkeep;
12871287

12881288
/* now we can compute the new value for pg_class.reltuples */
1289-
vacrelstats->new_rel_tuples = vac_estimate_reltuples(onerel, false,
1289+
vacrelstats->new_rel_tuples = vac_estimate_reltuples(onerel,
12901290
nblocks,
12911291
vacrelstats->tupcount_pages,
12921292
num_tuples);

‎src/include/commands/vacuum.h

Copy file name to clipboardExpand all lines: src/include/commands/vacuum.h
+1-1Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -162,7 +162,7 @@ extern void vacuum(int options, List *relations, VacuumParams *params,
162162
extern void vac_open_indexes(Relation relation, LOCKMODE lockmode,
163163
int *nindexes, Relation **Irel);
164164
extern void vac_close_indexes(int nindexes, Relation *Irel, LOCKMODE lockmode);
165-
extern double vac_estimate_reltuples(Relation relation, bool is_analyze,
165+
extern double vac_estimate_reltuples(Relation relation,
166166
BlockNumber total_pages,
167167
BlockNumber scanned_pages,
168168
double scanned_tuples);

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.