Hi,
The commit
https://github.com/postgres/postgres/commit/b17ff07aa3eb142d2cde2ea00e4a4e8f63686f96
Introduced the CopyStatistics function.
To do the work, CopyStatistics uses a less efficient function
to update/insert tuples at catalog systems.
The comment at indexing.c says:
"Avoid using it for multiple tuples, since opening the indexes
* and building the index info structures is moderately expensive.
* (Use CatalogTupleInsertWithInfo in such cases.)"
So inspired by the comment, changed in some fews places,
the CatalogInsert/CatalogUpdate to more efficient functions
CatalogInsertWithInfo/CatalogUpdateWithInfo.
With quick tests, resulting in small performance.
head:
1. REINDEX TABLE CONCURRENTLY pgbench_accounts;
Time: 77,805 ms
Time: 74,836 ms
Time: 73,480 ms
2. REINDEX TABLE CONCURRENTLY pgbench_tellers;
Time: 22,260 ms
Time: 22,205 ms
Time: 21,008 ms
patched:
1. REINDEX TABLE CONCURRENTLY pgbench_accounts;
Time: 65,048 ms
Time: 61,853 ms
Time: 61,119 ms
2. REINDEX TABLE CONCURRENTLY pgbench_tellers;
Time: 15,999 ms
Time: 15,961 ms
Time: 13,264 ms
There are other places that this could be useful,
but a careful analysis is necessary.
regards,
Ranier Vilela
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 9b03579e6e..cf15051a05 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -2856,8 +2856,7 @@ CopyStatistics(Oid fromrelid, Oid torelid)
SysScanDesc scan;
ScanKeyData key[1];
Relation statrel;
-
- statrel = table_open(StatisticRelationId, RowExclusiveLock);
+ CatalogIndexState indstate;
/* Now search for stat records */
ScanKeyInit(&key[0],
@@ -2865,6 +2864,9 @@ CopyStatistics(Oid fromrelid, Oid torelid)
BTEqualStrategyNumber, F_OIDEQ,
ObjectIdGetDatum(fromrelid));
+ statrel = table_open(StatisticRelationId, RowExclusiveLock);
+ indstate = CatalogOpenIndexes(statrel);
+
scan = systable_beginscan(statrel, StatisticRelidAttnumInhIndexId,
true, NULL, 1, key);
@@ -2878,13 +2880,14 @@ CopyStatistics(Oid fromrelid, Oid torelid)
/* update the copy of the tuple and insert it */
statform->starelid = torelid;
- CatalogTupleInsert(statrel, tup);
+ CatalogTupleInsertWithInfo(statrel, tup, indstate);
heap_freetuple(tup);
}
systable_endscan(scan);
+ CatalogCloseIndexes(indstate);
table_close(statrel, RowExclusiveLock);
}
diff --git a/src/backend/catalog/pg_enum.c b/src/backend/catalog/pg_enum.c
index 114715498d..785fbc1669 100644
--- a/src/backend/catalog/pg_enum.c
+++ b/src/backend/catalog/pg_enum.c
@@ -69,6 +69,7 @@ EnumValuesCreate(Oid enumTypeOid, List *vals)
bool nulls[Natts_pg_enum];
ListCell *lc;
HeapTuple tup;
+ CatalogIndexState indstate;
num_elems = list_length(vals);
@@ -113,6 +114,9 @@ EnumValuesCreate(Oid enumTypeOid, List *vals)
/* and make the entries */
memset(nulls, false, sizeof(nulls));
+ /* open the Catalog Indexes for Insert Tuples */
+ indstate = CatalogOpenIndexes(pg_enum);
+
elemno = 0;
foreach(lc, vals)
{
@@ -137,7 +141,7 @@ EnumValuesCreate(Oid enumTypeOid, List *vals)
tup = heap_form_tuple(RelationGetDescr(pg_enum), values, nulls);
- CatalogTupleInsert(pg_enum, tup);
+ CatalogTupleInsertWithInfo(pg_enum, tup, indstate);
heap_freetuple(tup);
elemno++;
@@ -145,6 +149,7 @@ EnumValuesCreate(Oid enumTypeOid, List *vals)
/* clean up */
pfree(oids);
+ CatalogCloseIndexes(indstate);
table_close(pg_enum, RowExclusiveLock);
}
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index a7966fff83..5cfcdf8ba7 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -1624,12 +1624,14 @@ static void
update_attstats(Oid relid, bool inh, int natts, VacAttrStats **vacattrstats)
{
Relation sd;
+ CatalogIndexState indstate;
int attno;
if (natts <= 0)
return; /* nothing to do */
sd = table_open(StatisticRelationId, RowExclusiveLock);
+ indstate = CatalogOpenIndexes(sd);
for (attno = 0; attno < natts; attno++)
{
@@ -1735,18 +1737,19 @@ update_attstats(Oid relid, bool inh, int natts, VacAttrStats **vacattrstats)
nulls,
replaces);
ReleaseSysCache(oldtup);
- CatalogTupleUpdate(sd, &stup->t_self, stup);
+ CatalogTupleUpdateWithInfo(sd, &stup->t_self, stup, indstate);
}
else
{
/* No, insert new tuple */
stup = heap_form_tuple(RelationGetDescr(sd), values, nulls);
- CatalogTupleInsert(sd, stup);
+ CatalogTupleInsertWithInfo(sd, stup, indstate);
}
heap_freetuple(stup);
}
+ CatalogCloseIndexes(indstate);
table_close(sd, RowExclusiveLock);
}
diff --git a/src/backend/commands/tsearchcmds.c b/src/backend/commands/tsearchcmds.c
index 4cc4e3c00f..b95b989c5a 100644
--- a/src/backend/commands/tsearchcmds.c
+++ b/src/backend/commands/tsearchcmds.c
@@ -1004,14 +1004,16 @@ DefineTSConfiguration(List *names, List *parameters, ObjectAddress *copied)
ScanKeyData skey;
SysScanDesc scan;
HeapTuple maptup;
-
- mapRel = table_open(TSConfigMapRelationId, RowExclusiveLock);
+ CatalogIndexState indstate;
ScanKeyInit(&skey,
Anum_pg_ts_config_map_mapcfg,
BTEqualStrategyNumber, F_OIDEQ,
ObjectIdGetDatum(sourceOid));
+ mapRel = table_open(TSConfigMapRelationId, RowExclusiveLock);
+ indstate = CatalogOpenIndexes(mapRel);
+
scan = systable_beginscan(mapRel, TSConfigMapIndexId, true,
NULL, 1, &skey);
@@ -1032,12 +1034,14 @@ DefineTSConfiguration(List *names, List *parameters, ObjectAddress *copied)
newmaptup = heap_form_tuple(mapRel->rd_att, mapvalues, mapnulls);
- CatalogTupleInsert(mapRel, newmaptup);
+ CatalogTupleInsertWithInfo(mapRel, newmaptup, indstate);
heap_freetuple(newmaptup);
}
systable_endscan(scan);
+
+ CatalogCloseIndexes(indstate);
}
address = makeConfigurationDependencies(tup, false, mapRel);
@@ -1225,6 +1229,7 @@ MakeConfigurationMapping(AlterTSConfigurationStmt *stmt,
Oid *dictIds;
int ndict;
ListCell *c;
+ CatalogIndexState indstate;
tsform = (Form_pg_ts_config) GETSTRUCT(tup);
cfgId = tsform->oid;
@@ -1275,6 +1280,9 @@ MakeConfigurationMapping(AlterTSConfigurationStmt *stmt,
i++;
}
+ /* open the Catalog Indexes for Update/Insert Tuples */
+ indstate = CatalogOpenIndexes(relMap);
+
if (stmt->replace)
{
/*
@@ -1334,7 +1342,7 @@ MakeConfigurationMapping(AlterTSConfigurationStmt *stmt,
newtup = heap_modify_tuple(maptup,
RelationGetDescr(relMap),
repl_val, repl_null, repl_repl);
- CatalogTupleUpdate(relMap, &newtup->t_self, newtup);
+ CatalogTupleUpdateWithInfo(relMap, &newtup->t_self, newtup, indstate);
}
}
@@ -1359,12 +1367,13 @@ MakeConfigurationMapping(AlterTSConfigurationStmt *stmt,
values[Anum_pg_ts_config_map_mapdict - 1] = ObjectIdGetDatum(dictIds[j]);
tup = heap_form_tuple(relMap->rd_att, values, nulls);
- CatalogTupleInsert(relMap, tup);
+ CatalogTupleInsertWithInfo(relMap, tup, indstate);
heap_freetuple(tup);
}
}
}
+ CatalogCloseIndexes(indstate);
EventTriggerCollectAlterTSConfig(stmt, cfgId, dictIds, ndict);
}
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 265a48af7e..f9521bc1dd 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -1551,6 +1551,7 @@ AddRoleMems(const char *rolename, Oid roleid,
{
Relation pg_authmem_rel;
TupleDesc pg_authmem_dsc;
+ CatalogIndexState indstate;
ListCell *specitem;
ListCell *iditem;
Oid currentUserId = GetUserId();
@@ -1718,6 +1719,9 @@ AddRoleMems(const char *rolename, Oid roleid,
ReleaseSysCacheList(memlist);
}
+ /* open the Catalog Indexes for Update/Insert Tuples */
+ indstate = CatalogOpenIndexes(pg_authmem_rel);
+
/* Now perform the catalog updates. */
forboth(specitem, memberSpecs, iditem, memberIds)
{
@@ -1789,7 +1793,7 @@ AddRoleMems(const char *rolename, Oid roleid,
tuple = heap_modify_tuple(authmem_tuple, pg_authmem_dsc,
new_record,
new_record_nulls, new_record_repl);
- CatalogTupleUpdate(pg_authmem_rel, &tuple->t_self, tuple);
+ CatalogTupleUpdateWithInfo(pg_authmem_rel, &tuple->t_self, tuple, indstate);
ReleaseSysCache(authmem_tuple);
}
@@ -1829,7 +1833,7 @@ AddRoleMems(const char *rolename, Oid roleid,
new_record[Anum_pg_auth_members_oid - 1] = objectId;
tuple = heap_form_tuple(pg_authmem_dsc,
new_record, new_record_nulls);
- CatalogTupleInsert(pg_authmem_rel, tuple);
+ CatalogTupleInsertWithInfo(pg_authmem_rel, tuple, indstate);
/* updateAclDependencies wants to pfree array inputs */
newmembers[0] = grantorId;
@@ -1842,6 +1846,7 @@ AddRoleMems(const char *rolename, Oid roleid,
/* CCI after each change, in case there are duplicates in list */
CommandCounterIncrement();
}
+ CatalogCloseIndexes(indstate);
/*
* Close pg_authmem, but keep lock till commit.
