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

On Thu, 16 Oct 2025 at 13:45, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> David Rowley <dgrowleyml(at)gmail(dot)com> writes:
> > Wouldn't it be more like:
>
> > CASE WHEN c.conrelid IS NULL THEN
> > pg_catalog.pg_get_indexdef(i.indexrelid) ELSE '' END AS indexdef
>
> I'd leave out the ELSE so that you get a null if the function
> isn't run, but yeah. (The places saving these query results would
> need PQgetisnull tests, too.)

Just to put that to the test, I tried the attached.

select 'create table t'||t||'(a int primary key, b int not null);
create index on t'||t||'(b)' from generate_Series(1,10000)t;
\gexec

master
$ PGOPTIONS='-c ignore_system_indexes=1' time pg_dump --schema-only
postgres >> /dev/null
2:23.75elapsed

$ PGOPTIONS='-c ignore_system_indexes=0' time pg_dump --schema-only
postgres >> /dev/null
0:01.08 elapsed

patched:
$ PGOPTIONS='-c ignore_system_indexes=1' time pg_dump --schema-only
postgres >> /dev/null
0:40.28elapsed

$ PGOPTIONS='-c ignore_system_indexes=0' time pg_dump --schema-only
postgres >> /dev/null
0:00.78elapsed

i.e about 3.5x faster with ignore_system_indexes and 38% faster without.

David