public inbox for [email protected]
help / color / mirror / Atom feedFrom: David Rowley <[email protected]>
To: Eşref Halıcıoğlu <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: About PostgreSQL Query Plan
Date: Tue, 14 Jan 2025 23:09:19 +1300
Message-ID: <CAApHDvqURruKPqe3hW+pA4eyjXWTk4xvWTB6POn=S2PAaxQGwQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
On Tue, 14 Jan 2025 at 03:45, Eşref Halıcıoğlu
<[email protected]> 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
view thread (2+ 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: About PostgreSQL Query Plan
In-Reply-To: <CAApHDvqURruKPqe3hW+pA4eyjXWTk4xvWTB6POn=S2PAaxQGwQ@mail.gmail.com>
* 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