Subject: Re: About PostgreSQL Query Plan

From: David Rowley <[email-masked]>
Date: Tue, 14 Jan 2025 23:09:19 +1300

On Tue, 14 Jan 2025 at 03:45, Eşref Halıcıoğlu
<[email-masked]> wrote:
> I do not fully understand the logic of this issue. I would be very grateful if you can share information on the subject.
>
> The query plan is as follows.
>
> Update on "test_table1" tt1  (cost=0.13..159112.84 rows=0 width=0)
>   Update on "test_table1_partition_2020_10" tt1
>   Update on "test_table1_partition_2020_11" tt1
...
 Update on "test_table1_partition_2025_12" tt1
>   Update on "test_table1_partition_default" tt1
>   ->  Nested Loop  (cost=0.13..159112.84 rows=1 width=53)
>         ->  Seq Scan on "temp_test_table1" temp  (cost=0.00..19.20 rows=920 width=31)
>         ->  Append  (cost=0.13..172.29 rows=64 width=38)
>               Subplans Removed: 60

The partitions mentioned in the "Update on" portion of the EXPLAIN
aren't being scanned. These are just result relations that potentially
could have tuples routed to them.  The key part of the EXPLAIN output
to knowing that the unrelated partitions are pruned is from which
partitions are mentioned below the "Append" node. You can see that 60
of your 64 partitions were pruned with the "Subplans Removed: 60"
part. The executor is only going to scan the 4 remaining ones that you
see below the "Append".

I wouldn't worry too much about the additional partitions mentioned in
the "Update on". We maybe could do a bit more work to initialise those
more lazily as we do for INSERT statements, but I'd be surprised if it
was a problem for 64 partitions, especially so for an update statement
that might be touching 3 months of data. Nothing about these existing
in the "Update on" portion of the EXPLAIN output means that that
partition will be scanned by the UPDATE statement, rest assured.

David

---

From: Eşref Halıcıoğlu <[email-masked]>
Date: Tue, 14 Jan 2025 13:45:29 +0300

Hello,

Okay, thank you very much, Mr. David, for your support and the information,

Eşref

----------------

To: Eşref Halıcıoğlu ([email-masked]);

Cc: [email-masked];

Subject: About PostgreSQL Query Plan;

14.01.2025, 13:09, "David Rowley" <[email-masked]>:

...

David

--