postgres.git / summary / log / commit / refs

commit    9407dbbcb5b587cbefc4af14d1612b49abcb143b
Author:   Tom Lane <tgl@sss.pgh.pa.us>
Date:     Tue Aug 31 17:53:33 2021 +0000

    Cache the results of format_type() queries in pg_dump.
    
    There's long been a "TODO: there might be some value in caching
    the results" annotation on pg_dump's getFormattedTypeName function;
    but we hadn't gotten around to checking what it was costing us to
    repetitively look up type names.  It turns out that when dumping the
    current regression database, about 10% of the total number of queries
    issued are duplicative format_type() queries.  However, Hubert Depesz
    Lubaczewski reported a not-unusual case where these account for over
    half of the queries issued by pg_dump.  Individually these queries
    aren't expensive, but when network lag is a factor, they add up to a
    problem.  We can very easily add some caching to getFormattedTypeName
    to solve it.
    
    Since this is such a simple fix and can have a visible performance
    benefit, back-patch to all supported branches.
    
    Discussion: https://postgr.es/m/20210826084430.GA26282@depesz.com


 src/bin/pg_dump/pg_dump.c | 13 +++++++++++--
 src/bin/pg_dump/pg_dump.h |  6 ++++--
 2 files changed, 15 insertions(+), 4 deletions(-)

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 46f5f2cbc9a..ef0a226c75b 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -5206,6 +5206,7 @@ getTypes(Archive *fout, int *numTypes)
 		tyinfo[i].dobj.name = pg_strdup(PQgetvalue(res, i, i_typname));
 		tyinfo[i].dobj.namespace =
 			findNamespace(atooid(PQgetvalue(res, i, i_typnamespace)));
+		tyinfo[i].ftypname = NULL;	/* may get filled later */
 		tyinfo[i].rolname = pg_strdup(PQgetvalue(res, i, i_rolname));
 		tyinfo[i].typacl = pg_strdup(PQgetvalue(res, i, i_typacl));
 		tyinfo[i].rtypacl = pg_strdup(PQgetvalue(res, i, i_rtypacl));
@@ -18761,12 +18762,11 @@ findDumpableDependencies(ArchiveHandle *AH, const DumpableObject *dobj,
  *
  * This does not guarantee to schema-qualify the output, so it should not
  * be used to create the target object name for CREATE or ALTER commands.
- *
- * TODO: there might be some value in caching the results.
  */
 static char *
 getFormattedTypeName(Archive *fout, Oid oid, OidOptions opts)
 {
+	TypeInfo   *typeInfo;
 	char	   *result;
 	PQExpBuffer query;
 	PGresult   *res;
@@ -18779,6 +18779,11 @@ getFormattedTypeName(Archive *fout, Oid oid, OidOptions opts)
 			return pg_strdup("NONE");
 	}
 
+	/* see if we have the result cached in the type's TypeInfo record */
+	typeInfo = findTypeByOid(oid);
+	if (typeInfo && typeInfo->ftypname)
+		return pg_strdup(typeInfo->ftypname);
+
 	query = createPQExpBuffer();
 	appendPQExpBuffer(query, "SELECT pg_catalog.format_type('%u'::pg_catalog.oid, NULL)",
 					  oid);
@@ -18791,6 +18796,10 @@ getFormattedTypeName(Archive *fout, Oid oid, OidOptions opts)
 	PQclear(res);
 	destroyPQExpBuffer(query);
 
+	/* cache a copy for later requests */
+	if (typeInfo)
+		typeInfo->ftypname = pg_strdup(result);
+
 	return result;
 }
 
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index df2aad2a3e2..8b2a8b67e7e 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -164,9 +164,11 @@ typedef struct _typeInfo
 	DumpableObject dobj;
 
 	/*
-	 * Note: dobj.name is the pg_type.typname entry.  format_type() might
-	 * produce something different than typname
+	 * Note: dobj.name is the raw pg_type.typname entry.  ftypname is the
+	 * result of format_type(), which will be quoted if needed, and might be
+	 * schema-qualified too.
 	 */
+	char	   *ftypname;
 	char	   *rolname;		/* name of owner, or empty string */
 	char	   *typacl;
 	char	   *rtypacl;


[parent: 4090ff2a99b7]