[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-bugs
Subject: Re: BUG #17484: Query does not prune partitions correctly
From: Damir Ciganović-Janković <damir.ciganovic.jankovic () gmail
Date: 2022-05-18 11:22:07
Message-ID: CA+-2sJ7B2s7z1zu9O2ZOsm9V1vLAyNBt489NZpD0gW1s0vkcZA () mail ! gmail ! com
[Download RAW message or body]
Great, thanks for your response, I will check behavior with Postgres 14 to
see if we have the same problems there.
On Wed, May 18, 2022 at 12:54 PM David Rowley <dgrowleyml@gmail.com> wrote:
> On Wed, 18 May 2022 at 22:02, PG Bug reporting form
> <noreply@postgresql.org> wrote:
> > -- Analyzes ALL 4 partitions even though we specified that we want only
> > 'FIRST'. Should have analyzed only test_FIRST_1. NOTE: same result with
> > EXPLAIN ANALYZE
> > PREPARE my_prepared_statement(text, text, text) AS UPDATE test SET
> some_id =
> > 5 WHERE timestamp_col >= CAST($1 AS timestamp(6)) AND timestamp_col <
> > CAST($2 AS timestamp(6)) AND enum_col = $3::test_enum;
> > EXPLAIN EXECUTE my_prepared_statement('2022-01-01 01:00:00', '2022-01-01
> > 02:00:00', 'FIRST');
> >
> > -- example when statement is not prepared (works as expected):
> > EXPLAIN UPDATE test SET some_id = 5 WHERE timestamp_col >=
> CAST('2022-01-01
> > 01:00:00' AS timestamp(6)) AND timestamp_col < CAST('2022-01-01
> 02:00:00' AS
> > timestamp(6)) AND enum_col = 'FIRST'::test_enum;
>
> Thanks for mentioning this, but as far as I can see, this behaves as
> expected.
>
> Execution-time pruning only exists for Append and MergeAppend in
> PostgreSQL 13. The documents [1] mention this, per:
>
> "Execution-time partition pruning currently only occurs for the Append
> and MergeAppend node types. It is not yet implemented for the
> ModifyTable node type, but that is likely to be changed in a future
> release of PostgreSQL."
>
> It might not be obvious from the plan, but your query fits into the
> ModifyTable category. That limitation was removed in PostgreSQL 14.
> See the release nodes in [2].
>
> "Improve the performance of updates and deletes on partitioned tables
> with many partitions (Amit Langote, Tom Lane)"
>
> And in particular:
>
> "This change greatly reduces the planner's overhead for such cases,
> and also allows updates/deletes on partitioned tables to use
> execution-time partition pruning."
>
> David
>
> [1] https://www.postgresql.org/docs/13/ddl-partitioning.html
> [2] https://www.postgresql.org/docs/current/release-14.html
>
[Attachment #3 (text/html)]
<div dir="ltr">Great, thanks for your response, I will check behavior with Postgres \
14 to see if we have the same problems there. </div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, May 18, 2022 at 12:54 \
PM David Rowley <<a \
href="mailto:dgrowleyml@gmail.com">dgrowleyml@gmail.com</a>> \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">On Wed, 18 May 2022 at \
22:02, PG Bug reporting form<br> <<a href="mailto:noreply@postgresql.org" \
target="_blank">noreply@postgresql.org</a>> wrote:<br> > -- Analyzes ALL 4 \
partitions even though we specified that we want only<br> > 'FIRST'. \
Should have analyzed only test_FIRST_1. NOTE: same result with<br> > EXPLAIN \
ANALYZE<br> > PREPARE my_prepared_statement(text, text, text) AS UPDATE test SET \
some_id =<br> > 5 WHERE timestamp_col >= CAST($1 AS timestamp(6)) AND \
timestamp_col <<br> > CAST($2 AS timestamp(6)) AND enum_col = \
$3::test_enum;<br> > EXPLAIN EXECUTE my_prepared_statement('2022-01-01 \
01:00:00', '2022-01-01<br> > 02:00:00', 'FIRST');<br>
><br>
> -- example when statement is not prepared (works as expected):<br>
> EXPLAIN UPDATE test SET some_id = 5 WHERE timestamp_col >= \
CAST('2022-01-01<br> > 01:00:00' AS timestamp(6)) AND timestamp_col < \
CAST('2022-01-01 02:00:00' AS<br> > timestamp(6)) AND enum_col = \
'FIRST'::test_enum;<br> <br>
Thanks for mentioning this, but as far as I can see, this behaves as expected.<br>
<br>
Execution-time pruning only exists for Append and MergeAppend in<br>
PostgreSQL 13. The documents [1] mention this, per:<br>
<br>
"Execution-time partition pruning currently only occurs for the Append<br>
and MergeAppend node types. It is not yet implemented for the<br>
ModifyTable node type, but that is likely to be changed in a future<br>
release of PostgreSQL."<br>
<br>
It might not be obvious from the plan, but your query fits into the<br>
ModifyTable category. That limitation was removed in PostgreSQL 14.<br>
See the release nodes in [2].<br>
<br>
"Improve the performance of updates and deletes on partitioned tables<br>
with many partitions (Amit Langote, Tom Lane)"<br>
<br>
And in particular:<br>
<br>
"This change greatly reduces the planner's overhead for such cases,<br>
and also allows updates/deletes on partitioned tables to use<br>
execution-time partition pruning."<br>
<br>
David<br>
<br>
[1] <a href="https://www.postgresql.org/docs/13/ddl-partitioning.html" \
rel="noreferrer" target="_blank">https://www.postgresql.org/docs/13/ddl-partitioning.html</a><br>
[2] <a href="https://www.postgresql.org/docs/current/release-14.html" \
rel="noreferrer" target="_blank">https://www.postgresql.org/docs/current/release-14.html</a><br>
</blockquote></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic