🌐 AI搜索 & 代理 主页
Skip to content

Commit 41a033b

Browse files
michaelpqmichaelpq
committed
Preserve index data in pg_statistic across REINDEX CONCURRENTLY
Statistics associated to an index got lost after running REINDEX CONCURRENTLY, while the non-concurrent case preserves these correctly. The concurrent and non-concurrent operations need to be consistent for the end-user, and missing statistics would force to wait for a new analyze to happen, which could take some time depending on the activity of the existing autovacuum workers. This issue is fixed by copying any existing entries in pg_statistic associated to the old index to the new one. Note that this copy is already done with the data of the index in the stats collector. Reported-by: Fabrízio de Royes Mello Author: Michael Paquier, Fabrízio de Royes Mello Reviewed-by: Justin Pryzby Discussion: https://postgr.es/m/CAFcNs+qpFPmiHd1oTXvcPdvAHicJDA9qBUSujgAhUMJyUMb+SA@mail.gmail.com Backpatch-through: 12
1 parent 741b84e commit 41a033b

File tree

5 files changed

+79
-0
lines changed

5 files changed

+79
-0
lines changed

‎src/backend/catalog/heap.c‎

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3113,6 +3113,47 @@ cookConstraint(ParseState *pstate,
31133113
return expr;
31143114
}
31153115

3116+
/*
3117+
* CopyStatistics --- copy entries in pg_statistic from one rel to another
3118+
*/
3119+
void
3120+
CopyStatistics(Oid fromrelid, Oid torelid)
3121+
{
3122+
HeapTuple tup;
3123+
SysScanDesc scan;
3124+
ScanKeyData key[1];
3125+
Relation statrel;
3126+
3127+
statrel = table_open(StatisticRelationId, RowExclusiveLock);
3128+
3129+
/* Now search for stat records */
3130+
ScanKeyInit(&key[0],
3131+
Anum_pg_statistic_starelid,
3132+
BTEqualStrategyNumber, F_OIDEQ,
3133+
ObjectIdGetDatum(fromrelid));
3134+
3135+
scan = systable_beginscan(statrel, StatisticRelidAttnumInhIndexId,
3136+
true, NULL, 1, key);
3137+
3138+
while (HeapTupleIsValid((tup = systable_getnext(scan))))
3139+
{
3140+
Form_pg_statistic statform;
3141+
3142+
/* make a modifiable copy */
3143+
tup = heap_copytuple(tup);
3144+
statform = (Form_pg_statistic) GETSTRUCT(tup);
3145+
3146+
/* update the copy of the tuple and insert it */
3147+
statform->starelid = torelid;
3148+
CatalogTupleInsert(statrel, tup);
3149+
3150+
heap_freetuple(tup);
3151+
}
3152+
3153+
systable_endscan(scan);
3154+
3155+
table_close(statrel, RowExclusiveLock);
3156+
}
31163157

31173158
/*
31183159
* RemoveStatistics --- remove entries in pg_statistic for a rel or column

‎src/backend/catalog/index.c‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1714,6 +1714,9 @@ index_concurrently_swap(Oid newIndexId, Oid oldIndexId, const char *oldName)
17141714
}
17151715
}
17161716

1717+
/* Copy data of pg_statistic from the old index to the new one */
1718+
CopyStatistics(oldIndexId, newIndexId);
1719+
17171720
/* Close relations */
17181721
table_close(pg_class, RowExclusiveLock);
17191722
table_close(pg_index, RowExclusiveLock);

‎src/include/catalog/heap.h‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -132,6 +132,7 @@ extern void RemoveAttributeById(Oid relid, AttrNumber attnum);
132132
extern void RemoveAttrDefault(Oid relid, AttrNumber attnum,
133133
DropBehavior behavior, bool complain, bool internal);
134134
extern void RemoveAttrDefaultById(Oid attrdefId);
135+
extern void CopyStatistics(Oid fromrelid, Oid torelid);
135136
extern void RemoveStatistics(Oid relid, AttrNumber attnum);
136137

137138
extern const FormData_pg_attribute *SystemAttributeDefinition(AttrNumber attno);

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

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2406,6 +2406,17 @@ CREATE UNIQUE INDEX concur_exprs_index_pred ON concur_exprs_tab (c1)
24062406
CREATE UNIQUE INDEX concur_exprs_index_pred_2
24072407
ON concur_exprs_tab ((1 / c1))
24082408
WHERE ('-H') >= (c2::TEXT) COLLATE "C";
2409+
ANALYZE concur_exprs_tab;
2410+
SELECT starelid::regclass, count(*) FROM pg_statistic WHERE starelid IN (
2411+
'concur_exprs_index_expr'::regclass,
2412+
'concur_exprs_index_pred'::regclass,
2413+
'concur_exprs_index_pred_2'::regclass)
2414+
GROUP BY starelid ORDER BY starelid::regclass::text;
2415+
starelid | count
2416+
-------------------------+-------
2417+
concur_exprs_index_expr | 1
2418+
(1 row)
2419+
24092420
SELECT pg_get_indexdef('concur_exprs_index_expr'::regclass);
24102421
pg_get_indexdef
24112422
---------------------------------------------------------------------------------------------------------------
@@ -2463,6 +2474,17 @@ SELECT pg_get_indexdef('concur_exprs_index_pred_2'::regclass);
24632474
CREATE UNIQUE INDEX concur_exprs_index_pred_2 ON public.concur_exprs_tab USING btree (((1 / c1))) WHERE ('-H'::text >= (c2 COLLATE "C"))
24642475
(1 row)
24652476

2477+
-- Statistics should remain intact.
2478+
SELECT starelid::regclass, count(*) FROM pg_statistic WHERE starelid IN (
2479+
'concur_exprs_index_expr'::regclass,
2480+
'concur_exprs_index_pred'::regclass,
2481+
'concur_exprs_index_pred_2'::regclass)
2482+
GROUP BY starelid ORDER BY starelid::regclass::text;
2483+
starelid | count
2484+
-------------------------+-------
2485+
concur_exprs_index_expr | 1
2486+
(1 row)
2487+
24662488
DROP TABLE concur_exprs_tab;
24672489
-- Temporary tables and on-commit actions, where CONCURRENTLY is ignored.
24682490
-- ON COMMIT PRESERVE ROWS, the default.

‎src/test/regress/sql/create_index.sql‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -997,6 +997,12 @@ CREATE UNIQUE INDEX concur_exprs_index_pred ON concur_exprs_tab (c1)
997997
CREATE UNIQUE INDEX concur_exprs_index_pred_2
998998
ON concur_exprs_tab ((1 / c1))
999999
WHERE ('-H') >= (c2::TEXT) COLLATE "C";
1000+
ANALYZE concur_exprs_tab;
1001+
SELECT starelid::regclass, count(*) FROM pg_statistic WHERE starelid IN (
1002+
'concur_exprs_index_expr'::regclass,
1003+
'concur_exprs_index_pred'::regclass,
1004+
'concur_exprs_index_pred_2'::regclass)
1005+
GROUP BY starelid ORDER BY starelid::regclass::text;
10001006
SELECT pg_get_indexdef('concur_exprs_index_expr'::regclass);
10011007
SELECT pg_get_indexdef('concur_exprs_index_pred'::regclass);
10021008
SELECT pg_get_indexdef('concur_exprs_index_pred_2'::regclass);
@@ -1009,6 +1015,12 @@ ALTER TABLE concur_exprs_tab ALTER c2 TYPE TEXT;
10091015
SELECT pg_get_indexdef('concur_exprs_index_expr'::regclass);
10101016
SELECT pg_get_indexdef('concur_exprs_index_pred'::regclass);
10111017
SELECT pg_get_indexdef('concur_exprs_index_pred_2'::regclass);
1018+
-- Statistics should remain intact.
1019+
SELECT starelid::regclass, count(*) FROM pg_statistic WHERE starelid IN (
1020+
'concur_exprs_index_expr'::regclass,
1021+
'concur_exprs_index_pred'::regclass,
1022+
'concur_exprs_index_pred_2'::regclass)
1023+
GROUP BY starelid ORDER BY starelid::regclass::text;
10121024
DROP TABLE concur_exprs_tab;
10131025

10141026
-- Temporary tables and on-commit actions, where CONCURRENTLY is ignored.

0 commit comments

Comments
 (0)