Skip to content

Commit b17ff07

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 aecaa04 commit b17ff07

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
@@ -3142,6 +3142,47 @@ cookConstraint(ParseState *pstate,
31423142
return expr;
31433143
}
31443144

3145+
/*
3146+
* CopyStatistics --- copy entries in pg_statistic from one rel to another
3147+
*/
3148+
void
3149+
CopyStatistics(Oid fromrelid, Oid torelid)
3150+
{
3151+
HeapTuple tup;
3152+
SysScanDesc scan;
3153+
ScanKeyData key[1];
3154+
Relation statrel;
3155+
3156+
statrel = table_open(StatisticRelationId, RowExclusiveLock);
3157+
3158+
/* Now search for stat records */
3159+
ScanKeyInit(&key[0],
3160+
Anum_pg_statistic_starelid,
3161+
BTEqualStrategyNumber, F_OIDEQ,
3162+
ObjectIdGetDatum(fromrelid));
3163+
3164+
scan = systable_beginscan(statrel, StatisticRelidAttnumInhIndexId,
3165+
true, NULL, 1, key);
3166+
3167+
while (HeapTupleIsValid((tup = systable_getnext(scan))))
3168+
{
3169+
Form_pg_statistic statform;
3170+
3171+
/* make a modifiable copy */
3172+
tup = heap_copytuple(tup);
3173+
statform = (Form_pg_statistic) GETSTRUCT(tup);
3174+
3175+
/* update the copy of the tuple and insert it */
3176+
statform->starelid = torelid;
3177+
CatalogTupleInsert(statrel, tup);
3178+
3179+
heap_freetuple(tup);
3180+
}
3181+
3182+
systable_endscan(scan);
3183+
3184+
table_close(statrel, RowExclusiveLock);
3185+
}
31453186

31463187
/*
31473188
* 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
@@ -1711,6 +1711,9 @@ index_concurrently_swap(Oid newIndexId, Oid oldIndexId, const char *oldName)
17111711
}
17121712
}
17131713

1714+
/* Copy data of pg_statistic from the old index to the new one */
1715+
CopyStatistics(oldIndexId, newIndexId);
1716+
17141717
/* Close relations */
17151718
table_close(pg_class, RowExclusiveLock);
17161719
table_close(pg_index, RowExclusiveLock);

‎src/include/catalog/heap.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -134,6 +134,7 @@ extern void RemoveAttributeById(Oid relid, AttrNumber attnum);
134134
extern void RemoveAttrDefault(Oid relid, AttrNumber attnum,
135135
DropBehavior behavior, bool complain, bool internal);
136136
extern void RemoveAttrDefaultById(Oid attrdefId);
137+
extern void CopyStatistics(Oid fromrelid, Oid torelid);
137138
extern void RemoveStatistics(Oid relid, AttrNumber attnum);
138139

139140
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
@@ -2551,6 +2551,17 @@ CREATE UNIQUE INDEX concur_exprs_index_pred ON concur_exprs_tab (c1)
25512551
CREATE UNIQUE INDEX concur_exprs_index_pred_2
25522552
ON concur_exprs_tab ((1 / c1))
25532553
WHERE ('-H') >= (c2::TEXT) COLLATE "C";
2554+
ANALYZE concur_exprs_tab;
2555+
SELECT starelid::regclass, count(*) FROM pg_statistic WHERE starelid IN (
2556+
'concur_exprs_index_expr'::regclass,
2557+
'concur_exprs_index_pred'::regclass,
2558+
'concur_exprs_index_pred_2'::regclass)
2559+
GROUP BY starelid ORDER BY starelid::regclass::text;
2560+
starelid | count
2561+
-------------------------+-------
2562+
concur_exprs_index_expr | 1
2563+
(1 row)
2564+
25542565
SELECT pg_get_indexdef('concur_exprs_index_expr'::regclass);
25552566
pg_get_indexdef
25562567
---------------------------------------------------------------------------------------------------------------
@@ -2608,6 +2619,17 @@ SELECT pg_get_indexdef('concur_exprs_index_pred_2'::regclass);
26082619
CREATE UNIQUE INDEX concur_exprs_index_pred_2 ON public.concur_exprs_tab USING btree (((1 / c1))) WHERE ('-H'::text >= (c2 COLLATE "C"))
26092620
(1 row)
26102621

2622+
-- Statistics should remain intact.
2623+
SELECT starelid::regclass, count(*) FROM pg_statistic WHERE starelid IN (
2624+
'concur_exprs_index_expr'::regclass,
2625+
'concur_exprs_index_pred'::regclass,
2626+
'concur_exprs_index_pred_2'::regclass)
2627+
GROUP BY starelid ORDER BY starelid::regclass::text;
2628+
starelid | count
2629+
-------------------------+-------
2630+
concur_exprs_index_expr | 1
2631+
(1 row)
2632+
26112633
DROP TABLE concur_exprs_tab;
26122634
-- Temporary tables and on-commit actions, where CONCURRENTLY is ignored.
26132635
-- 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
@@ -1079,6 +1079,12 @@ CREATE UNIQUE INDEX concur_exprs_index_pred ON concur_exprs_tab (c1)
10791079
CREATE UNIQUE INDEX concur_exprs_index_pred_2
10801080
ON concur_exprs_tab ((1 / c1))
10811081
WHERE ('-H') >= (c2::TEXT) COLLATE "C";
1082+
ANALYZE concur_exprs_tab;
1083+
SELECT starelid::regclass, count(*) FROM pg_statistic WHERE starelid IN (
1084+
'concur_exprs_index_expr'::regclass,
1085+
'concur_exprs_index_pred'::regclass,
1086+
'concur_exprs_index_pred_2'::regclass)
1087+
GROUP BY starelid ORDER BY starelid::regclass::text;
10821088
SELECT pg_get_indexdef('concur_exprs_index_expr'::regclass);
10831089
SELECT pg_get_indexdef('concur_exprs_index_pred'::regclass);
10841090
SELECT pg_get_indexdef('concur_exprs_index_pred_2'::regclass);
@@ -1091,6 +1097,12 @@ ALTER TABLE concur_exprs_tab ALTER c2 TYPE TEXT;
10911097
SELECT pg_get_indexdef('concur_exprs_index_expr'::regclass);
10921098
SELECT pg_get_indexdef('concur_exprs_index_pred'::regclass);
10931099
SELECT pg_get_indexdef('concur_exprs_index_pred_2'::regclass);
1100+
-- Statistics should remain intact.
1101+
SELECT starelid::regclass, count(*) FROM pg_statistic WHERE starelid IN (
1102+
'concur_exprs_index_expr'::regclass,
1103+
'concur_exprs_index_pred'::regclass,
1104+
'concur_exprs_index_pred_2'::regclass)
1105+
GROUP BY starelid ORDER BY starelid::regclass::text;
10941106
DROP TABLE concur_exprs_tab;
10951107

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

0 commit comments

Comments
 (0)