Skip to content

Commit bb62df4

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 ab2e2ce commit bb62df4

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
@@ -3112,6 +3112,47 @@ cookConstraint(ParseState *pstate,
31123112
return expr;
31133113
}
31143114

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

31163157
/*
31173158
* 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
@@ -1725,6 +1725,9 @@ index_concurrently_swap(Oid newIndexId, Oid oldIndexId, const char *oldName)
17251725
}
17261726
}
17271727

1728+
/* Copy data of pg_statistic from the old index to the new one */
1729+
CopyStatistics(oldIndexId, newIndexId);
1730+
17281731
/* Close relations */
17291732
table_close(pg_class, RowExclusiveLock);
17301733
table_close(pg_index, RowExclusiveLock);

‎src/include/catalog/heap.h

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

138139
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
@@ -2419,6 +2419,17 @@ CREATE UNIQUE INDEX concur_exprs_index_pred ON concur_exprs_tab (c1)
24192419
CREATE UNIQUE INDEX concur_exprs_index_pred_2
24202420
ON concur_exprs_tab ((1 / c1))
24212421
WHERE ('-H') >= (c2::TEXT) COLLATE "C";
2422+
ANALYZE concur_exprs_tab;
2423+
SELECT starelid::regclass, count(*) FROM pg_statistic WHERE starelid IN (
2424+
'concur_exprs_index_expr'::regclass,
2425+
'concur_exprs_index_pred'::regclass,
2426+
'concur_exprs_index_pred_2'::regclass)
2427+
GROUP BY starelid ORDER BY starelid::regclass::text;
2428+
starelid | count
2429+
-------------------------+-------
2430+
concur_exprs_index_expr | 1
2431+
(1 row)
2432+
24222433
SELECT pg_get_indexdef('concur_exprs_index_expr'::regclass);
24232434
pg_get_indexdef
24242435
---------------------------------------------------------------------------------------------------------------
@@ -2476,6 +2487,17 @@ SELECT pg_get_indexdef('concur_exprs_index_pred_2'::regclass);
24762487
CREATE UNIQUE INDEX concur_exprs_index_pred_2 ON public.concur_exprs_tab USING btree (((1 / c1))) WHERE ('-H'::text >= (c2 COLLATE "C"))
24772488
(1 row)
24782489

2490+
-- Statistics should remain intact.
2491+
SELECT starelid::regclass, count(*) FROM pg_statistic WHERE starelid IN (
2492+
'concur_exprs_index_expr'::regclass,
2493+
'concur_exprs_index_pred'::regclass,
2494+
'concur_exprs_index_pred_2'::regclass)
2495+
GROUP BY starelid ORDER BY starelid::regclass::text;
2496+
starelid | count
2497+
-------------------------+-------
2498+
concur_exprs_index_expr | 1
2499+
(1 row)
2500+
24792501
DROP TABLE concur_exprs_tab;
24802502
-- Temporary tables and on-commit actions, where CONCURRENTLY is ignored.
24812503
-- 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
@@ -1003,6 +1003,12 @@ CREATE UNIQUE INDEX concur_exprs_index_pred ON concur_exprs_tab (c1)
10031003
CREATE UNIQUE INDEX concur_exprs_index_pred_2
10041004
ON concur_exprs_tab ((1 / c1))
10051005
WHERE ('-H') >= (c2::TEXT) COLLATE "C";
1006+
ANALYZE concur_exprs_tab;
1007+
SELECT starelid::regclass, count(*) FROM pg_statistic WHERE starelid IN (
1008+
'concur_exprs_index_expr'::regclass,
1009+
'concur_exprs_index_pred'::regclass,
1010+
'concur_exprs_index_pred_2'::regclass)
1011+
GROUP BY starelid ORDER BY starelid::regclass::text;
10061012
SELECT pg_get_indexdef('concur_exprs_index_expr'::regclass);
10071013
SELECT pg_get_indexdef('concur_exprs_index_pred'::regclass);
10081014
SELECT pg_get_indexdef('concur_exprs_index_pred_2'::regclass);
@@ -1015,6 +1021,12 @@ ALTER TABLE concur_exprs_tab ALTER c2 TYPE TEXT;
10151021
SELECT pg_get_indexdef('concur_exprs_index_expr'::regclass);
10161022
SELECT pg_get_indexdef('concur_exprs_index_pred'::regclass);
10171023
SELECT pg_get_indexdef('concur_exprs_index_pred_2'::regclass);
1024+
-- Statistics should remain intact.
1025+
SELECT starelid::regclass, count(*) FROM pg_statistic WHERE starelid IN (
1026+
'concur_exprs_index_expr'::regclass,
1027+
'concur_exprs_index_pred'::regclass,
1028+
'concur_exprs_index_pred_2'::regclass)
1029+
GROUP BY starelid ORDER BY starelid::regclass::text;
10181030
DROP TABLE concur_exprs_tab;
10191031

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

0 commit comments

Comments
 (0)