pg_dump: optimize dumpFunc()

From: Nathan Bossart <nathandbossart(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: pg_dump: optimize dumpFunc()
Date: 2024-08-01 16:52:57
Message-ID: Zqu9aaYtNyHj8V7m@nathan
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I've recently committed some optimizations for dumping sequences and
pg_class information (commits 68e9629, bd15b7d, and 2329cad), and I noticed
that we are also executing a query per function in pg_dump. Commit be85727
optimized this by preparing the query ahead of time, but I found that we
can improve performance further by gathering all the relevant data in a
single query. Here are the results I see for a database with 10k simple
functions with and without the attached patch:

with patch:

$ time pg_dump postgres >/dev/null
pg_dump postgres > /dev/null 0.04s user 0.01s system 40% cpu 0.118 total
$ time pg_dump postgres >/dev/null
pg_dump postgres > /dev/null 0.04s user 0.01s system 41% cpu 0.107 total
$ time pg_dump postgres >/dev/null
pg_dump postgres > /dev/null 0.04s user 0.01s system 42% cpu 0.103 total
$ time pg_dump postgres >/dev/null
pg_dump postgres > /dev/null 0.04s user 0.01s system 44% cpu 0.105 total

without patch:

$ time pg_dump postgres >/dev/null
pg_dump postgres > /dev/null 0.05s user 0.03s system 32% cpu 0.253 total
$ time pg_dump postgres >/dev/null
pg_dump postgres > /dev/null 0.05s user 0.03s system 32% cpu 0.252 total
$ time pg_dump postgres >/dev/null
pg_dump postgres > /dev/null 0.06s user 0.03s system 32% cpu 0.251 total
$ time pg_dump postgres >/dev/null
pg_dump postgres > /dev/null 0.06s user 0.03s system 33% cpu 0.254 total

This one looks a little different than the sequence/pg_class commits. Much
of the function information isn't terribly conducive to parsing into
fixed-size variables in an array, so instead I've opted to just leave the
PGresult around for reference by dumpFunc(). This patch also creates an
ordered array of function OIDs to speed up locating the relevant index in
the PGresult for use in calls to PQgetvalue().

I may be running out of opportunities where this style of optimization
makes much difference. I'll likely start focusing on the restore side
soon.

--
nathan

Attachment Content-Type Size
v1-0001-optimize-dumpFunc.patch text/plain 10.7 KB

Browse pgsql-hackers by date

  From Date Subject
Previous Message Andres Freund 2024-08-01 16:44:08 Re: PG17beta2: SMGR: inconsistent type for nblocks