Re: BUG #19086: pg_dump --data-only selects and do not uses index definitions for the dumped tables. - Mailing list pgsql-bugs

David Rowley <dgrowleyml@gmail.com> writes:
> Seems to be due to pg_get_indexdef / pg_get_constraintdef operating on
> a cold cat cache. Getting rid of those the rewritten version runs in
> 1.8 seconds with 100k tables for me.

I wonder how much win could be had by postponing those function calls
so that they only act on indexes we're going to dump.  It might be
a net loss in the default dump-everything case, though.

Also, it looks to me like getIndexes does not even look at the result
of pg_get_constraintdef unless contype == 'x'.  So there should be
some low-hanging fruit with

-                         "pg_catalog.pg_get_constraintdef(c.oid, false) AS condef, "
+                         "CASE WHEN c.contype = 'x' THEN "
+                         "pg_catalog.pg_get_constraintdef(c.oid, false) "
+                         "END AS condef, "

This wouldn't matter except with primary/unique constraints, but
surely there are plenty of those in a typical DB.

            regards, tom lane