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
