On Thu, 16 Oct 2025 at 13:45, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> David Rowley <dgrowleyml@gmail.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
