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

On Thu, 16 Oct 2025 at 10:55, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> David Rowley <dgrowleyml(at)gmail(dot)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.

Just to make sure I understand correctly, that means run a query in
dumpIndex() specifically just for the index being dumped to call
pg_get_indexdef()?

It would mean firing off quite a large number of queries to the
server, which might be especially bad when pg_dump is being run
remotely. I suppose ideally we'd have some matrix to indicate
everything we're going to need based on the given options and just
fetch those things. That'd be a pretty big overhaul.

> 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.

I expect that would help quite a bit. We do have NOT NULL constraints
in that table now, so I expect it might be bigger than pg_index in
most cases for recent versions, so the full table scan in
pg_get_constraintdef_worker() with ignore_system_indexes = on could be
more painful than the same thing in pg_get_indexdef_worker().

David

David