partition pruning only works for select but update

From: "James Pang (chaolpan)" <chaolpan(at)cisco(dot)com>
To: "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)org>
Subject: partition pruning only works for select but update
Date: 2022-06-28 13:15:42
Message-ID: PH0PR11MB519134D4171A126776E3E063D6B89@PH0PR11MB5191.namprd11.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2022-06-28 13:30:00 Re: partition pruning only works for select but update
Previous Message I. V. 2022-06-19 08:46:53 Re: reindex option for tuning load large data