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

Commitd611f8b

Browse files
committed
CREATE INDEX: don't update table stats if autovacuum=off.
We previously fixed this for binary upgrade in71b6617, but asimilar problem remained when dumping statistics without data.Fix by not opportunistically updating table stats during CREATE INDEXwhen autovacuum is disabled. For stats to be stable at all, the serverneeds to be aware that it should not take every opportunity to updatestats. Per discussion, autovacuum=off is a signal that the userexpects stats to be stable; though if necessary, we could createa more specific mode in the future.Reported-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>Discussion:https://postgr.es/m/CAExHW5vf9D+8-a5_BEX3y=2y_xY9hiCxV1=C+FnxDvfprWvkng@mail.gmail.comDiscussion:https://postgr.es/m/ca81cbf6e6ea2af838df972801ad4da52640a503.camel%40j-davis.com
1 parent19e57f4 commitd611f8b

File tree

3 files changed

+68
-0
lines changed

3 files changed

+68
-0
lines changed

‎src/backend/catalog/index.c

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -63,6 +63,7 @@
6363
#include"optimizer/optimizer.h"
6464
#include"parser/parser.h"
6565
#include"pgstat.h"
66+
#include"postmaster/autovacuum.h"
6667
#include"rewrite/rewriteManip.h"
6768
#include"storage/bufmgr.h"
6869
#include"storage/lmgr.h"
@@ -2840,6 +2841,27 @@ index_update_stats(Relation rel,
28402841
*/
28412842
update_stats=reltuples >=0&& !IsBinaryUpgrade;
28422843

2844+
/*
2845+
* If autovacuum is off, user may not be expecting table relstats to
2846+
* change. This can be important when restoring a dump that includes
2847+
* statistics, as the table statistics may be restored before the index is
2848+
* created, and we want to preserve the restored table statistics.
2849+
*/
2850+
if (AutoVacuumingActive())
2851+
{
2852+
if (rel->rd_rel->relkind==RELKIND_RELATION||
2853+
rel->rd_rel->relkind==RELKIND_TOASTVALUE||
2854+
rel->rd_rel->relkind==RELKIND_MATVIEW)
2855+
{
2856+
StdRdOptions*options= (StdRdOptions*)rel->rd_options;
2857+
2858+
if (options!=NULL&& !options->autovacuum.enabled)
2859+
update_stats= false;
2860+
}
2861+
}
2862+
else
2863+
update_stats= false;
2864+
28432865
/*
28442866
* Finish I/O and visibility map buffer locks before
28452867
* systable_inplace_update_begin() locks the pg_class buffer. The rd_rel

‎src/test/regress/expected/stats_import.out

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12,7 +12,36 @@ CREATE TABLE stats_import.test(
1212
arange int4range,
1313
tags text[]
1414
) WITH (autovacuum_enabled = false);
15+
SELECT
16+
pg_catalog.pg_restore_relation_stats(
17+
'relation', 'stats_import.test'::regclass,
18+
'relpages', 18::integer,
19+
'reltuples', 21::real,
20+
'relallvisible', 24::integer,
21+
'relallfrozen', 27::integer);
22+
pg_restore_relation_stats
23+
---------------------------
24+
t
25+
(1 row)
26+
27+
-- CREATE INDEX on a table with autovac disabled should not overwrite
28+
-- stats
1529
CREATE INDEX test_i ON stats_import.test(id);
30+
SELECT relname, relpages, reltuples, relallvisible, relallfrozen
31+
FROM pg_class
32+
WHERE oid = 'stats_import.test'::regclass
33+
ORDER BY relname;
34+
relname | relpages | reltuples | relallvisible | relallfrozen
35+
---------+----------+-----------+---------------+--------------
36+
test | 18 | 21 | 24 | 27
37+
(1 row)
38+
39+
SELECT pg_clear_relation_stats('stats_import.test'::regclass);
40+
pg_clear_relation_stats
41+
-------------------------
42+
43+
(1 row)
44+
1645
--
1746
-- relstats tests
1847
--

‎src/test/regress/sql/stats_import.sql

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -15,8 +15,25 @@ CREATE TABLE stats_import.test(
1515
tagstext[]
1616
) WITH (autovacuum_enabled= false);
1717

18+
SELECT
19+
pg_catalog.pg_restore_relation_stats(
20+
'relation','stats_import.test'::regclass,
21+
'relpages',18::integer,
22+
'reltuples',21::real,
23+
'relallvisible',24::integer,
24+
'relallfrozen',27::integer);
25+
26+
-- CREATE INDEX on a table with autovac disabled should not overwrite
27+
-- stats
1828
CREATEINDEXtest_iONstats_import.test(id);
1929

30+
SELECT relname, relpages, reltuples, relallvisible, relallfrozen
31+
FROM pg_class
32+
WHEREoid='stats_import.test'::regclass
33+
ORDER BY relname;
34+
35+
SELECT pg_clear_relation_stats('stats_import.test'::regclass);
36+
2037
--
2138
-- relstats tests
2239
--

0 commit comments

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