public inbox for [email protected]
help / color / mirror / Atom feedRe: About PostgreSQL Query Plan
2+ messages / 2 participants
[nested] [flat]
* Re: About PostgreSQL Query Plan
@ 2025-01-14 10:09 David Rowley <[email protected]>
2025-01-14 10:45 ` Re: About PostgreSQL Query Plan Eşref Halıcıoğlu <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: David Rowley @ 2025-01-14 10:09 UTC (permalink / raw)
To: Eşref Halıcıoğlu <[email protected]>; +Cc: [email protected] <[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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: About PostgreSQL Query Plan
2025-01-14 10:09 Re: About PostgreSQL Query Plan David Rowley <[email protected]>
@ 2025-01-14 10:45 ` Eşref Halıcıoğlu <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Eşref Halıcıoğlu @ 2025-01-14 10:45 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: [email protected] <[email protected]>
<div><div>Hello,</div><div> </div><div>Okay, thank you very much, Mr. David, for your support and the information,</div><div> </div><div>Eşref</div></div><div> </div><div>----------------</div><div>To: Eşref Halıcıoğlu ([email protected]);</div><div>Cc: [email protected];</div><div>Subject: About PostgreSQL Query Plan;</div><div>14.01.2025, 13:09, "David Rowley" <[email protected]>:</div><blockquote><p>On Tue, 14 Jan 2025 at 03:45, Eşref Halıcıoğlu<br /><<a href="mailto:[email protected]" rel="noopener noreferrer">[email protected]</a>> wrote:</p><blockquote> I do not fully understand the logic of this issue. I would be very grateful if you can share information on the subject.<br /><br /> The query plan is as follows.<br /><br /> Update on "test_table1" tt1 (cost=0.13..159112.84 rows=0 width=0)<br /> Update on "test_table1_partition_2020_10" tt1<br /> Update on "test_table1_partition_2020_11" tt1</blockquote><p>...<br /> Update on "test_table1_partition_2025_12" tt1</p><blockquote> Update on "test_table1_partition_default" tt1<br /> -> Nested Loop (cost=0.13..159112.84 rows=1 width=53)<br /> -> Seq Scan on "temp_test_table1" temp (cost=0.00..19.20 rows=920 width=31)<br /> -> Append (cost=0.13..172.29 rows=64 width=38)<br /> Subplans Removed: 60</blockquote><p><br />The partitions mentioned in the "Update on" portion of the EXPLAIN<br />aren't being scanned. These are just result relations that potentially<br />could have tuples routed to them. The key part of the EXPLAIN output<br />to knowing that the unrelated partitions are pruned is from which<br />partitions are mentioned below the "Append" node. You can see that 60<br />of your 64 partitions were pruned with the "Subplans Removed: 60"<br />part. The executor is only going to scan the 4 remaining ones that you<br />see below the "Append".<br /><br />I wouldn't worry too much about the additional partitions mentioned in<br />the "Update on". We maybe could do a bit more work to initialise those<br />more lazily as we do for INSERT statements, but I'd be surprised if it<br />was a problem for 64 partitions, especially so for an update statement<br />that might be touching 3 months of data. Nothing about these existing<br />in the "Update on" portion of the EXPLAIN output means that that<br />partition will be scanned by the UPDATE statement, rest assured.<br /><br />David</p></blockquote><div> </div><div> </div><div>-- </div><div><img src="https://avatars.mds.yandex.net/get-mail-signature/1640102/65bb545ae0f44457010ad3aaa33d1fca/orig"; /></div><div> </div>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-01-14 10:45 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-14 10:09 Re: About PostgreSQL Query Plan David Rowley <[email protected]>
2025-01-14 10:45 ` Eşref Halıcıoğlu <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox