Skip to content

Commit 68e9629

Browse files
nathan-bossartnathan-bossart
committed
Improve performance of dumpSequence().
This function dumps the sequence definitions. It is called once per sequence, and each such call executes a query to retrieve the metadata for a single sequence. This can cause pg_dump to take significantly longer, especially when there are many sequences. This commit improves the performance of this function by gathering all the sequence metadata with a single query at the beginning of pg_dump. This information is stored in a sorted array that dumpSequence() can bsearch() for what it needs. This follows a similar approach as commits d5e8930 and 2329cad, which introduced sorted arrays for role information and pg_class information, respectively. As with those commits, this patch will cause pg_dump to use more memory, but that isn't expected to be too egregious. Note that before version 10, the sequence metadata was stored in the sequence relation itself, which makes it difficult to gather all the sequence metadata with a single query. For those older versions, we continue to use the preexisting query-per-sequence approach. Reviewed-by: Euler Taveira Discussion: https://postgr.es/m/20240503025140.GA1227404%40nathanxps13
1 parent 23687e9 commit 68e9629

File tree

2 files changed

+129
-47
lines changed

2 files changed

+129
-47
lines changed

‎src/bin/pg_dump/pg_dump.c

Lines changed: 128 additions & 47 deletions
Original file line numberDiff line numberDiff line change
@@ -122,6 +122,18 @@ const char *const SeqTypeNames[] =
122122
StaticAssertDecl(lengthof(SeqTypeNames) == (SEQTYPE_BIGINT + 1),
123123
"array length mismatch");
124124

125+
typedef struct
126+
{
127+
Oid oid; /* sequence OID */
128+
SeqType seqtype; /* data type of sequence */
129+
bool cycled; /* whether sequence cycles */
130+
int64 minv; /* minimum value */
131+
int64 maxv; /* maximum value */
132+
int64 startv; /* start value */
133+
int64 incby; /* increment value */
134+
int64 cache; /* cache size */
135+
} SequenceItem;
136+
125137
typedef enum OidOptions
126138
{
127139
zeroIsError = 1,
@@ -191,6 +203,10 @@ static int nseclabels = 0;
191203
static BinaryUpgradeClassOidItem *binaryUpgradeClassOids = NULL;
192204
static int nbinaryUpgradeClassOids = 0;
193205

206+
/* sorted table of sequences */
207+
static SequenceItem *sequences = NULL;
208+
static int nsequences = 0;
209+
194210
/*
195211
* The default number of rows per INSERT when
196212
* --inserts is specified without --rows-per-insert
@@ -288,6 +304,7 @@ static void dumpTable(Archive *fout, const TableInfo *tbinfo);
288304
static void dumpTableSchema(Archive *fout, const TableInfo *tbinfo);
289305
static void dumpTableAttach(Archive *fout, const TableAttachInfo *attachinfo);
290306
static void dumpAttrDef(Archive *fout, const AttrDefInfo *adinfo);
307+
static void collectSequences(Archive *fout);
291308
static void dumpSequence(Archive *fout, const TableInfo *tbinfo);
292309
static void dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo);
293310
static void dumpIndex(Archive *fout, const IndxInfo *indxinfo);
@@ -1010,6 +1027,9 @@ main(int argc, char **argv)
10101027
if (dopt.binary_upgrade)
10111028
collectBinaryUpgradeClassOids(fout);
10121029

1030+
/* Collect sequence information. */
1031+
collectSequences(fout);
1032+
10131033
/* Lastly, create dummy objects to represent the section boundaries */
10141034
boundaryObjs = createBoundaryObjects();
10151035

@@ -17282,6 +17302,65 @@ parse_sequence_type(const char *name)
1728217302
return (SeqType) 0; /* keep compiler quiet */
1728317303
}
1728417304

17305+
/*
17306+
* bsearch() comparator for SequenceItem
17307+
*/
17308+
static int
17309+
SequenceItemCmp(const void *p1, const void *p2)
17310+
{
17311+
SequenceItem v1 = *((const SequenceItem *) p1);
17312+
SequenceItem v2 = *((const SequenceItem *) p2);
17313+
17314+
return pg_cmp_u32(v1.oid, v2.oid);
17315+
}
17316+
17317+
/*
17318+
* collectSequences
17319+
*
17320+
* Construct a table of sequence information. This table is sorted by OID for
17321+
* speed in lookup.
17322+
*/
17323+
static void
17324+
collectSequences(Archive *fout)
17325+
{
17326+
PGresult *res;
17327+
const char *query;
17328+
17329+
/*
17330+
* Before Postgres 10, sequence metadata is in the sequence itself. With
17331+
* some extra effort, we might be able to use the sorted table for those
17332+
* versions, but for now it seems unlikely to be worth it.
17333+
*/
17334+
if (fout->remoteVersion < 100000)
17335+
return;
17336+
else
17337+
query = "SELECT seqrelid, format_type(seqtypid, NULL), "
17338+
"seqstart, seqincrement, "
17339+
"seqmax, seqmin, "
17340+
"seqcache, seqcycle "
17341+
"FROM pg_catalog.pg_sequence "
17342+
"ORDER BY seqrelid";
17343+
17344+
res = ExecuteSqlQuery(fout, query, PGRES_TUPLES_OK);
17345+
17346+
nsequences = PQntuples(res);
17347+
sequences = (SequenceItem *) pg_malloc(nsequences * sizeof(SequenceItem));
17348+
17349+
for (int i = 0; i < nsequences; i++)
17350+
{
17351+
sequences[i].oid = atooid(PQgetvalue(res, i, 0));
17352+
sequences[i].seqtype = parse_sequence_type(PQgetvalue(res, i, 1));
17353+
sequences[i].startv = strtoi64(PQgetvalue(res, i, 2), NULL, 10);
17354+
sequences[i].incby = strtoi64(PQgetvalue(res, i, 3), NULL, 10);
17355+
sequences[i].maxv = strtoi64(PQgetvalue(res, i, 4), NULL, 10);
17356+
sequences[i].minv = strtoi64(PQgetvalue(res, i, 5), NULL, 10);
17357+
sequences[i].cache = strtoi64(PQgetvalue(res, i, 6), NULL, 10);
17358+
sequences[i].cycled = (strcmp(PQgetvalue(res, i, 7), "t") == 0);
17359+
}
17360+
17361+
PQclear(res);
17362+
}
17363+
1728517364
/*
1728617365
* dumpSequence
1728717366
* write the declaration (not data) of one user-defined sequence
@@ -17290,37 +17369,36 @@ static void
1729017369
dumpSequence(Archive *fout, const TableInfo *tbinfo)
1729117370
{
1729217371
DumpOptions *dopt = fout->dopt;
17293-
PGresult *res;
17294-
SeqType seqtype;
17295-
bool cycled;
17372+
SequenceItem *seq;
1729617373
bool is_ascending;
1729717374
int64 default_minv,
17298-
default_maxv,
17299-
minv,
17300-
maxv,
17301-
startv,
17302-
incby,
17303-
cache;
17375+
default_maxv;
1730417376
PQExpBuffer query = createPQExpBuffer();
1730517377
PQExpBuffer delqry = createPQExpBuffer();
1730617378
char *qseqname;
1730717379
TableInfo *owning_tab = NULL;
1730817380

1730917381
qseqname = pg_strdup(fmtId(tbinfo->dobj.name));
1731017382

17383+
/*
17384+
* For versions >= 10, the sequence information is gathered in a sorted
17385+
* table before any calls to dumpSequence(). See collectSequences() for
17386+
* more information.
17387+
*/
1731117388
if (fout->remoteVersion >= 100000)
1731217389
{
17313-
appendPQExpBuffer(query,
17314-
"SELECT format_type(seqtypid, NULL), "
17315-
"seqstart, seqincrement, "
17316-
"seqmax, seqmin, "
17317-
"seqcache, seqcycle "
17318-
"FROM pg_catalog.pg_sequence "
17319-
"WHERE seqrelid = '%u'::oid",
17320-
tbinfo->dobj.catId.oid);
17390+
SequenceItem key = {0};
17391+
17392+
Assert(sequences);
17393+
17394+
key.oid = tbinfo->dobj.catId.oid;
17395+
seq = bsearch(&key, sequences, nsequences,
17396+
sizeof(SequenceItem), SequenceItemCmp);
1732117397
}
1732217398
else
1732317399
{
17400+
PGresult *res;
17401+
1732417402
/*
1732517403
* Before PostgreSQL 10, sequence metadata is in the sequence itself.
1732617404
*
@@ -17332,46 +17410,47 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
1733217410
"start_value, increment_by, max_value, min_value, "
1733317411
"cache_value, is_cycled FROM %s",
1733417412
fmtQualifiedDumpable(tbinfo));
17335-
}
17336-
17337-
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
1733817413

17339-
if (PQntuples(res) != 1)
17340-
pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
17341-
"query to get data of sequence \"%s\" returned %d rows (expected 1)",
17342-
PQntuples(res)),
17343-
tbinfo->dobj.name, PQntuples(res));
17414+
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
1734417415

17345-
seqtype = parse_sequence_type(PQgetvalue(res, 0, 0));
17346-
startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
17347-
incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
17348-
maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
17349-
minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
17350-
cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
17351-
cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
17416+
if (PQntuples(res) != 1)
17417+
pg_fatal(ngettext("query to get data of sequence \"%s\" returned %d row (expected 1)",
17418+
"query to get data of sequence \"%s\" returned %d rows (expected 1)",
17419+
PQntuples(res)),
17420+
tbinfo->dobj.name, PQntuples(res));
17421+
17422+
seq = pg_malloc0(sizeof(SequenceItem));
17423+
seq->seqtype = parse_sequence_type(PQgetvalue(res, 0, 0));
17424+
seq->startv = strtoi64(PQgetvalue(res, 0, 1), NULL, 10);
17425+
seq->incby = strtoi64(PQgetvalue(res, 0, 2), NULL, 10);
17426+
seq->maxv = strtoi64(PQgetvalue(res, 0, 3), NULL, 10);
17427+
seq->minv = strtoi64(PQgetvalue(res, 0, 4), NULL, 10);
17428+
seq->cache = strtoi64(PQgetvalue(res, 0, 5), NULL, 10);
17429+
seq->cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
1735217430

17353-
PQclear(res);
17431+
PQclear(res);
17432+
}
1735417433

1735517434
/* Calculate default limits for a sequence of this type */
17356-
is_ascending = (incby >= 0);
17357-
if (seqtype == SEQTYPE_SMALLINT)
17435+
is_ascending = (seq->incby >= 0);
17436+
if (seq->seqtype == SEQTYPE_SMALLINT)
1735817437
{
1735917438
default_minv = is_ascending ? 1 : PG_INT16_MIN;
1736017439
default_maxv = is_ascending ? PG_INT16_MAX : -1;
1736117440
}
17362-
else if (seqtype == SEQTYPE_INTEGER)
17441+
else if (seq->seqtype == SEQTYPE_INTEGER)
1736317442
{
1736417443
default_minv = is_ascending ? 1 : PG_INT32_MIN;
1736517444
default_maxv = is_ascending ? PG_INT32_MAX : -1;
1736617445
}
17367-
else if (seqtype == SEQTYPE_BIGINT)
17446+
else if (seq->seqtype == SEQTYPE_BIGINT)
1736817447
{
1736917448
default_minv = is_ascending ? 1 : PG_INT64_MIN;
1737017449
default_maxv = is_ascending ? PG_INT64_MAX : -1;
1737117450
}
1737217451
else
1737317452
{
17374-
pg_fatal("unrecognized sequence type: %d", seqtype);
17453+
pg_fatal("unrecognized sequence type: %d", seq->seqtype);
1737517454
default_minv = default_maxv = 0; /* keep compiler quiet */
1737617455
}
1737717456

@@ -17422,27 +17501,27 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
1742217501
"UNLOGGED " : "",
1742317502
fmtQualifiedDumpable(tbinfo));
1742417503

17425-
if (seqtype != SEQTYPE_BIGINT)
17426-
appendPQExpBuffer(query, " AS %s\n", SeqTypeNames[seqtype]);
17504+
if (seq->seqtype != SEQTYPE_BIGINT)
17505+
appendPQExpBuffer(query, " AS %s\n", SeqTypeNames[seq->seqtype]);
1742717506
}
1742817507

17429-
appendPQExpBuffer(query, " START WITH " INT64_FORMAT "\n", startv);
17508+
appendPQExpBuffer(query, " START WITH " INT64_FORMAT "\n", seq->startv);
1743017509

17431-
appendPQExpBuffer(query, " INCREMENT BY " INT64_FORMAT "\n", incby);
17510+
appendPQExpBuffer(query, " INCREMENT BY " INT64_FORMAT "\n", seq->incby);
1743217511

17433-
if (minv != default_minv)
17434-
appendPQExpBuffer(query, " MINVALUE " INT64_FORMAT "\n", minv);
17512+
if (seq->minv != default_minv)
17513+
appendPQExpBuffer(query, " MINVALUE " INT64_FORMAT "\n", seq->minv);
1743517514
else
1743617515
appendPQExpBufferStr(query, " NO MINVALUE\n");
1743717516

17438-
if (maxv != default_maxv)
17439-
appendPQExpBuffer(query, " MAXVALUE " INT64_FORMAT "\n", maxv);
17517+
if (seq->maxv != default_maxv)
17518+
appendPQExpBuffer(query, " MAXVALUE " INT64_FORMAT "\n", seq->maxv);
1744017519
else
1744117520
appendPQExpBufferStr(query, " NO MAXVALUE\n");
1744217521

1744317522
appendPQExpBuffer(query,
1744417523
" CACHE " INT64_FORMAT "%s",
17445-
cache, (cycled ? "\n CYCLE" : ""));
17524+
seq->cache, (seq->cycled ? "\n CYCLE" : ""));
1744617525

1744717526
if (tbinfo->is_identity_sequence)
1744817527
{
@@ -17528,6 +17607,8 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
1752817607
tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
1752917608
tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
1753017609

17610+
if (fout->remoteVersion < 100000)
17611+
pg_free(seq);
1753117612
destroyPQExpBuffer(query);
1753217613
destroyPQExpBuffer(delqry);
1753317614
free(qseqname);

‎src/tools/pgindent/typedefs.list

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2573,6 +2573,7 @@ SeqScanState
25732573
SeqTable
25742574
SeqTableData
25752575
SeqType
2576+
SequenceItem
25762577
SerCommitSeqNo
25772578
SerialControl
25782579
SerialIOData

0 commit comments

Comments
 (0)