From: Nathan Bossart <nathan@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Improve performance of dumpSequence().
Date: Wed, 31 Jul 2024 15:13:58 +0000
Message-ID: <E1sZB1a-0023GV-0J@gemulon.postgresql.org> (raw)
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 d5e8930f50 and 2329cad1b9, 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
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/68e9629985981ce8f8f04b5a9f8b3781eacaafd6
Modified Files
--------------
src/bin/pg_dump/pg_dump.c | 175 ++++++++++++++++++++++++++++-----------
src/tools/pgindent/typedefs.list | 1 +
2 files changed, 129 insertions(+), 47 deletions(-)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: nathan@postgresql.org, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Improve performance of dumpSequence().
In-Reply-To: <E1sZB1a-0023GV-0J@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox