pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpartition pruning only works for select but update
7+ messages / 3 participants
[nested] [flat]
* partition pruning only works for select but update
@ 2022-06-28 13:15 James Pang (chaolpan) <chaolpan@cisco.com>
0 siblings, 1 reply; 7+ messages in thread
From: James Pang (chaolpan) @ 2022-06-28 13:15 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hi,
We have a table have range partition (about 5K partitions) , when
Explain select count(*) from table where partitionkey between to_timestamp() and to_timestamp();
It show
Aggregate (cost=15594.72..15594.73 rows=1 width=8)
-> Append (cost=0.15..15582.00 rows=5088 width=0)
Subplans Removed: 5086
But when
Explain update table set .. where partitionkey between to_timestamp() and to_timestamp();
It still show all of partitions with update ...
enable_partition_pruning keep defaut value 'on', It's expected ? and we found for update sql with same where condition, it consumes huge memory than select.
Database version is Postgres 13.4 on RHEL8.4.
Thanks,
James
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: partition pruning only works for select but update
@ 2022-06-28 13:30 Tom Lane <tgl@sss.pgh.pa.us>
parent: James Pang (chaolpan) <chaolpan@cisco.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2022-06-28 13:30 UTC (permalink / raw)
To: James Pang (chaolpan) <chaolpan@cisco.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
"James Pang (chaolpan)" <chaolpan@cisco.com> writes:
> But when
> Explain update table set .. where partitionkey between to_timestamp() and to_timestamp();
> It still show all of partitions with update ...
In releases before v14, partition pruning is far stupider for UPDATE
(and DELETE) than it is for SELECT.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: partition pruning only works for select but update
@ 2022-06-28 13:34 James Pang (chaolpan) <chaolpan@cisco.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: James Pang (chaolpan) @ 2022-06-28 13:34 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
For release v14, optimizer can handle large partition counts query ( select ,update ,delete) and partition pruning is similar as SELECT, right? We will check option to upgrade to v14.
Thanks,
James
-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Tuesday, June 28, 2022 9:30 PM
To: James Pang (chaolpan) <chaolpan@cisco.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: partition pruning only works for select but update
"James Pang (chaolpan)" <chaolpan@cisco.com> writes:
> But when
> Explain update table set .. where partitionkey between to_timestamp() and to_timestamp();
> It still show all of partitions with update ...
In releases before v14, partition pruning is far stupider for UPDATE (and DELETE) than it is for SELECT.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: partition pruning only works for select but update
@ 2022-07-01 08:30 James Pang (chaolpan) <chaolpan@cisco.com>
parent: James Pang (chaolpan) <chaolpan@cisco.com>
0 siblings, 1 reply; 7+ messages in thread
From: James Pang (chaolpan) @ 2022-07-01 08:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
We have other application depend on V13, possible to backport code changes to V13 as https://git.postgresql.org/gitweb/?p=postgresql.git;a=patch;h=86dc90056dfdbd9d1b891718d2e5614e3e432f...
Thanks,
James
-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Tuesday, June 28, 2022 9:30 PM
To: James Pang (chaolpan) <chaolpan@cisco.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: partition pruning only works for select but update
"James Pang (chaolpan)" <chaolpan@cisco.com> writes:
> But when
> Explain update table set .. where partitionkey between to_timestamp() and to_timestamp();
> It still show all of partitions with update ...
In releases before v14, partition pruning is far stupider for UPDATE (and DELETE) than it is for SELECT.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: partition pruning only works for select but update
@ 2022-07-01 13:13 Justin Pryzby <pryzby@telsasoft.com>
parent: James Pang (chaolpan) <chaolpan@cisco.com>
0 siblings, 1 reply; 7+ messages in thread
From: Justin Pryzby @ 2022-07-01 13:13 UTC (permalink / raw)
To: James Pang (chaolpan) <chaolpan@cisco.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance@lists.postgresql.org
On Fri, Jul 01, 2022 at 08:30:40AM +0000, James Pang (chaolpan) wrote:
> We have other application depend on V13, possible to backport code changes to V13 as https://git.postgresql.org/gitweb/?p=postgresql.git;a=patch;h=86dc90056dfdbd9d1b891718d2e5614e3e432f...
Do you mean that the other application needs to be updated to work with v14?
Or that you haven't checked yet if they work with v14?
In any case, I'm sure the feature won't be backpatched to v13 - it's an
improvement but not a bugfix.
--
Justin
> -----Original Message-----
> From: Tom Lane <tgl@sss.pgh.pa.us>
> Sent: Tuesday, June 28, 2022 9:30 PM
> To: James Pang (chaolpan) <chaolpan@cisco.com>
> Cc: pgsql-performance@lists.postgresql.org
> Subject: Re: partition pruning only works for select but update
>
> "James Pang (chaolpan)" <chaolpan@cisco.com> writes:
> > But when
> > Explain update table set .. where partitionkey between to_timestamp() and to_timestamp();
> > It still show all of partitions with update ...
>
> In releases before v14, partition pruning is far stupider for UPDATE (and DELETE) than it is for SELECT.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: partition pruning only works for select but update
@ 2022-07-01 13:18 Tom Lane <tgl@sss.pgh.pa.us>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2022-07-01 13:18 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: James Pang (chaolpan) <chaolpan@cisco.com>; pgsql-performance@lists.postgresql.org
Justin Pryzby <pryzby@telsasoft.com> writes:
> On Fri, Jul 01, 2022 at 08:30:40AM +0000, James Pang (chaolpan) wrote:
>> We have other application depend on V13, possible to backport code changes to V13 as https://git.postgresql.org/gitweb/?p=postgresql.git;a=patch;h=86dc90056dfdbd9d1b891718d2e5614e3e432f...
> In any case, I'm sure the feature won't be backpatched to v13 - it's an
> improvement but not a bugfix.
Even more to the point, it was an extremely major change and would take
a huge amount of QA effort to ensure that dropping it into v13 wouldn't
cause fresh problems. The PG community has exactly no interest in making
such effort.
Besides which, what do you imagine "depends on v13" actually means?
If you have an app that works on v13 but not v14, maybe it's because
it depends on the old behavior in some way.
Spend your effort on updating your app, instead.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: partition pruning only works for select but update
@ 2022-07-01 14:06 James Pang (chaolpan) <chaolpan@cisco.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: James Pang (chaolpan) @ 2022-07-01 14:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Thanks for your quick response, yes, pretty complicated logical replication between databases for our system, the logical replication tool just support V13 until now and it take long time for this vendor to support V14. We just migrate from Oracle to Postgres, a lot of partition tables and huge table data in partition tables too, and we see big difference with part SQL response time between Oracle and PGv13, especially for "updates" on tables with many partitions(>2k partitions).
I will share update and push replication tool and old app support V14 as a best way to improve partition table update for large tables, and as first step of tuning , we try to reduce partition count for these tables in PGV13.
James
-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Friday, July 1, 2022 9:18 PM
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: James Pang (chaolpan) <chaolpan@cisco.com>; pgsql-performance@lists.postgresql.org
Subject: Re: partition pruning only works for select but update
Justin Pryzby <pryzby@telsasoft.com> writes:
> On Fri, Jul 01, 2022 at 08:30:40AM +0000, James Pang (chaolpan) wrote:
>> We have other application depend on V13, possible to backport code
>> changes to V13 as
>> https://git.postgresql.org/gitweb/?p=postgresql.git;a=patch;h=86dc900
>> 56dfdbd9d1b891718d2e5614e3e432f35
> In any case, I'm sure the feature won't be backpatched to v13 - it's
> an improvement but not a bugfix.
Even more to the point, it was an extremely major change and would take a huge amount of QA effort to ensure that dropping it into v13 wouldn't cause fresh problems. The PG community has exactly no interest in making such effort.
Besides which, what do you imagine "depends on v13" actually means?
If you have an app that works on v13 but not v14, maybe it's because it depends on the old behavior in some way.
Spend your effort on updating your app, instead.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2022-07-01 14:06 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-06-28 13:15 partition pruning only works for select but update James Pang (chaolpan) <chaolpan@cisco.com>
2022-06-28 13:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-06-28 13:34 ` James Pang (chaolpan) <chaolpan@cisco.com>
2022-07-01 08:30 ` James Pang (chaolpan) <chaolpan@cisco.com>
2022-07-01 13:13 ` Justin Pryzby <pryzby@telsasoft.com>
2022-07-01 13:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-07-01 14:06 ` James Pang (chaolpan) <chaolpan@cisco.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox