public inbox for [email protected]  

help / color / mirror / Atom feed

From: Frédéric Yhuel <[email protected]>
To: Dimitrios Apostolou <[email protected]>
To: [email protected]
Subject: Re: ALTER TABLE ADD FOREIGN KEY to partitioned table, is not parallelized
Date: Wed, 4 Jun 2025 09:00:00 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>



On 6/3/25 17:34, Dimitrios Apostolou wrote:
> The backend process for each of the above ALTER TABLE commands, does not
>    parallelize the foreign key checks for the different partitions. I
>    know, because in the logs I see gigabytes of temporary files being
>    written, with the CONTEXT showing queries issued incrementally on
>    all the different partitions:
> 
>    :LOG:      temporary file: path "pg_tblspc/16390/PG_17_202406281/ 
> pgsql_tmp/pgsql_tmp3363462.579", size 1073741824
>    :CONTEXT:  SQL statement "SELECT fk."columnX" FROM ONLY 
> "public"."table_partition_214" fk
>               LEFT OUTER JOIN ONLY "public"."another_table" pk
>                   ON ( pk."columnX" OPERATOR(pg_catalog.=) fk."columnX")
>               WHERE pk."columnX" IS NULL AND (fk."columnX" IS NOT NULL)"
> 
>    Why can't the backend issue these queries in parallel workers?

This has been discussed here: 
https://www.postgresql.org/message-id/flat/0d21e3b4-dcde-290c-875e-6ed5013e8e52%40dalibo.com

Perhaps we should exhume this patch, but I believe the optimal strategy 
is to perform a VACUUM between the data and post-data to build the 
visibility map. The anti-join can then use an efficient index-only scan.

Best regards,
Frédéric

view thread (9+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: ALTER TABLE ADD FOREIGN KEY to partitioned table, is not parallelized
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox