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

David Rowley <dgrowleyml@gmail.com>

From: David Rowley <dgrowleyml@gmail.com>

To: Nathan Bossart <nathandbossart@gmail.com>

Cc: Tom Lane <tgl@sss.pgh.pa.us>, andrewbille@gmail.com, pgsql-bugs@lists.postgresql.org

Date: 2025-10-15T21:38:09Z

Lists: pgsql-bugs

On Thu, 16 Oct 2025 at 05:24, Nathan Bossart <nathandbossart@gmail.com> wrote:
> I tried this with ignore_system_indexes = on, too, but it was very slow.

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.

That invalidates my timings from yesterday as I must have run the
rewritten query once the catcache was populated. Here are updated
results.

ignore_system_indexes = on 1.5k tables:
master: Execution Time: 26167.741 ms
rewritten: Execution Time: 16661.774 ms

ignore_system_indexes = off 1.5k tables:
master: Execution Time: 105.415 ms
rewritten: Execution Time: 97.030 ms

So not as good.

David