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

On Wed, 15 Oct 2025 at 11:03, Nathan Bossart <nathandbossart@gmail.com> wrote:
> FWIW the getIndexes() query does tend to be one of the slowest, even with
> intact system indexes.  I've no concrete proposals, but there might be some
> room for improvement.  I don't think we gain all that much by simply
> avoiding the query in probably-somewhat-rare use-cases.  IMHO it ought to
> be reworked for efficiency.

The extra slowness comes from all the subqueries in the targetlist, 3
of which are going to pg_attribute using the same join condition. That
results in 3 separate scans of pg_attribute, 2 more than needed.

The query could be made more efficient generally by doing a left join
to pg_attribute instead and then GROUP BY i.indexrelid.

I tried rewriting the query so that pg_attribute is joined to rather
than subqueries. With 1500 tables I get:

master:

ignore_system_indexes = on
Execution Time: 6853.262 ms

ignore_system_indexes = off
Execution Time: 66.781 ms

Rewritten query:

ignore_system_indexes = on
Execution Time: 53.351 ms

ignore_system_indexes = off
Execution Time: 56.965 ms

David