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

From: Nathan Bossart <nathandbossart(at)gmail(dot)com>
To: David Rowley <dgrowleyml(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, andrewbille(at)gmail(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #19086: pg_dump --data-only selects and do not uses index definitions for the dumped tables.
Date: 2025-10-15 16:24:34
Message-ID: aO_KwnHm2AVn0E4m@nathan
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

On Wed, Oct 15, 2025 at 12:56:25PM +1300, David Rowley wrote:
> 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

I tried this with 100K tables and saw the following (ignore_system_indexes
= off):

master:
Planning Time: 1.672 ms
Execution Time: 4077.008 ms

rewritten:
Planning Time: 1.641 ms
Execution Time: 3427.206 ms

I tried this with ignore_system_indexes = on, too, but it was very slow.

--
nathan

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Masahiko Sawada 2025-10-15 18:35:07 Re: TRAP: failed Assert("outerPlan != NULL") in postgres_fdw.c
Previous Message Yuri Zamyatin 2025-10-15 14:21:29 Re: BUG #19078: Segfaults in tts_minimal_store_tuple() following pg_upgrade