From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
cc: Nathan Bossart <nathandbossart@gmail.com>, andrewbille@gmail.com,
        pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19086: pg_dump --data-only selects and do not uses index
 definitions for the dumped tables.
In-reply-to: 
 <CAApHDvrffhKALh3jQM6Kmar5nt=2xgHC_0ErhTFvM32b_JT-Cw@mail.gmail.com>
References: <19086-871ff11017d03dc5@postgresql.org> <aO6mUj9H2WpEFylG@nathan>
 <1000041.1760471415@sss.pgh.pa.us> <aO7IoIEnQDbtFzrs@nathan>
 <CAApHDvoU2O-_zKNL-toHwkDcMXCzzvgRUTnA3baEEhCB4vqMRw@mail.gmail.com>
 <aO_KwnHm2AVn0E4m@nathan>
 <CAApHDvrcM4G0==52jFoAvmxk6ZRjXKq7nkik=fqq6BH=dSJsYA@mail.gmail.com>
 <1324003.1760565328@sss.pgh.pa.us>
 <CAApHDvrffhKALh3jQM6Kmar5nt=2xgHC_0ErhTFvM32b_JT-Cw@mail.gmail.com>
Comments: In-reply-to David Rowley <dgrowleyml@gmail.com>
	message dated "Thu, 16 Oct 2025 12:07:24 +1300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1334976.1760571380.1@sss.pgh.pa.us>
Date: Wed, 15 Oct 2025 19:36:20 -0400
Message-ID: <1334977.1760571380@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1334977.1760571380%40sss.pgh.pa.us>
Precedence: bulk

David Rowley <dgrowleyml@gmail.com> writes:
> On Thu, 16 Oct 2025 at 10:55, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I wonder how much win could be had by postponing those function calls
>> so that they only act on indexes we're going to dump.  It might be
>> a net loss in the default dump-everything case, though.

> Just to make sure I understand correctly, that means run a query in
> dumpIndex() specifically just for the index being dumped to call
> pg_get_indexdef()?

We could do it like that, but it's probably better to keep the
single-query approach.  A way that could work is

(1) Drop pg_get_indexdef and pg_get_constraintdef from getIndexes'
initial query.

(2) As we scan the results of that query, build new OID-list
strings listing just the index OIDs and constraint OIDs that
we require definition strings for.

(3) Still within getIndexes, run those queries and insert the
results into the arrays built in step 1.

On its face, this will be slower than doing it all in one query,
at least in the normal case where we need most or all indexes.
But what I'm hoping is that we need at most one of the two
function calls for any one index, depending on whether it's a
constraint or not.  That could buy back enough to justify the
extra overhead, perhaps.

(Hmm ... but on the third hand, if we only need one of the
two strings, couldn't we mechanize that by wrapping the
pg_get_indexdef call in CASE WHEN c.contype IS DISTINCT FROM 'x'
?)

			regards, tom lane