Обсуждение: Partition pruning not working on updates
Hello,
Partition pruning is not working on the updates query, am I missing something?
Able get around this by manually pinning the table partition to the date partition but, it's a manual process.
PostgreSQL 13.8 on AWS
Partition table using pg_partman v4.5.1
Each daily partition contains about 15 million rows which require to be processed using an update on the column p_state.
Table:
public.stat
Partitioned table "public.stat"
Column | Type | Collation | Nullable | Default
---------------------+-----------------------------+-----------+----------+-------------------------
creationdate | timestamp without time zone | | not null |
ver | text | | |
id | text | | |
name | text | | |
ip | text | | not null |
tags | jsonb | | |
list | jsonb | | |
updated_tstamp | timestamp without time zone | | |
p_state | character varying | | | 'N'::character varying
insert_tstamp | timestamp without time zone | | | CURRENT_TIMESTAMP
Partition key: RANGE (creationdate)
Indexes:
"pk_public.stat" PRIMARY KEY, btree (creationdate, ip)
"idx_public.p_state" btree (p_state)
Number of partitions: 47 (Use \d+ to list them.)
Query:
explain WITH update_pr as (SELECT * FROM public.stat WHERE p_state = 'N' AND creationdate > current_timestamp - INTERVAL '5' day ORDER BY creationdate LIMIT 4000000) UPDATE public.stat s set p_state = 'PR' FROM update_pr u WHERE s.p_state = 'N' AND s.ip = u.ip AND s.creationdate = u.creationdate;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on public.stat s (cost=11.45..193994.25 rows=47 width=858)
Update on public.stat_p2023_06_25 s_1
Update on public.stat_p2023_06_26 s_2
...
Update on public.stat_p2023_08_09 s_46
Update on public.stat_default s_47
-> Merge Join (cost=11.45..4128.23 rows=1 width=966)
Merge Cond: (u.creationdate = s_1.creationdate)
Join Filter: (s_1.ip = u.ip)
-> Subquery Scan on u (cost=11.30..4036.97 rows=34432 width=322)
-> Limit (cost=11.30..3692.65 rows=34432 width=272)
-> Merge Append (cost=11.30..3692.65 rows=34432 width=272)
Sort Key: public.stat.creationdate
Subplans Removed: 29
-> Index Scan using public.stat_p2023_07_24_pkey on public.stat_p2023_07_24 public.stat_1 (cost=0.29..1474.75 rows=23641 width=272)
Index Cond: (creationdate > (CURRENT_TIMESTAMP - '1 day'::interval day))
Filter: ((p_state)::text = 'N'::text)
-> Index Scan using public.stat_p2023_07_25_pkey on public.stat_p2023_07_25 public.stat_2 (cost=0.29..673.21 rows=10746 width=273)
Index Cond: (creationdate > (CURRENT_TIMESTAMP - '1 day'::interval day))
Filter: ((p_state)::text = 'N'::text)
-> Index Scan using public.stat_p2023_07_26_pkey on public.stat_p2023_07_26 public.stat_3 (cost=0.15..11.89 rows=1 width=664)
.. -> Index Scan using public.stat_p2023_08_02_pkey on public.stat_p2023_08_02 public.stat_10 (cost=0.15..11.89 rows=1 width=664)
Index Cond: (creationdate > (CURRENT_TIMESTAMP - '1 day'::interval day))
Filter: ((p_state)::text = 'N'::text)
-> Index Scan using public.stat_p2023_08_03_pkey on public.stat_p2023_08_03 public.stat_11 (cost=0.15..11.89 rows=1 width=664)
Index Cond: (creationdate > (CURRENT_TIMESTAMP - '1 day'::interval day))
Filter: ((p_state)::text = 'N'::text)
-> Index Scan using public.stat_p2023_08_04_pkey on public.stat_p2023_08_04 public.stat_12 (cost=0.15..11.89 rows=1 width=664)
Index Cond: (creationdate > (CURRENT_TIMESTAMP - '1 day'::interval day))
Filter: ((p_state)::text = 'N'::text)
-> Index Scan using public.stat_p2023_08_05_pkey on public.stat_p2023_08_05 public.stat_13 (cost=0.15..11.89 rows=1 width=664)
Index Cond: (creationdate > (CURRENT_TIMESTAMP - '1 day'::interval day))
Filter: ((p_state)::text = 'N'::text)
-> Index Scan using public.stat_p2023_08_06_pkey on public.stat_p2023_08_06 public.stat_14 (cost=0.15..11.89 rows=1 width=664)
Index Cond: (creationdate > (CURRENT_TIMESTAMP - '1 day'::interval day))
Filter: ((p_state)::text = 'N'::text)
-> Index Scan using public.stat_p2023_08_07_pkey on public.stat_p2023_08_07 public.stat_15 (cost=0.15..11.89 rows=1 width=664)
Index Cond: (creationdate > (CURRENT_TIMESTAMP - '1 day'::interval day))
Filter: ((p_state)::text = 'N'::text)
-> Index Scan using public.stat_p2023_08_08_pkey on public.stat_p2023_08_08 public.stat_16 (cost=0.15..11.89 rows=1 width=664)
Index Cond: (creationdate > (CURRENT_TIMESTAMP - '1 day'::interval day))
Filter: ((p_state)::text = 'N'::text)
-> Index Scan using public.stat_p2023_08_09_pkey on public.stat_p2023_08_09 public.stat_17 (cost=0.15..11.89 rows=1 width=664)
Index Cond: (creationdate > (CURRENT_TIMESTAMP - '1 day'::interval day))
Filter: ((p_state)::text = 'N'::text)
-> Index Scan using public.stat_default_pkey on public.stat_default public.stat_18 (cost=0.15..11.89 rows=1 width=664)
Index Cond: (creationdate > (CURRENT_TIMESTAMP - '1 day'::interval day))
Filter: ((p_state)::text = 'N'::text)
-> Materialize (cost=0.15..2.18 rows=1 width=638)
-> Index Scan using public.stat_p2023_06_25_pkey on public.stat_p2023_06_25 s_1 (cost=0.15..2.17 rows=1 width=638)
Index Cond: ((creationdate >= (CURRENT_TIMESTAMP - '1 day'::interval day)) AND (creationdate <= (CURRENT_TIMESTAMP - '1 day'::interval day)))
Filter: ((p_state)::text = 'N'::text)
-> Merge Join (cost=11.45..4128.23 rows=1 width=966)
Merge Cond: (u_1.creationdate = s_2.creationdate)
Join Filter: (s_2.ip = u_1.ip)
-> Subquery Scan on u_1 (cost=11.30..4036.97 rows=34432 width=322)
-> Limit (cost=11.30..3692.65 rows=34432 width=272)
-> Merge Append (cost=11.30..3692.65 rows=34432 width=272)
Any help on this is appreciated...
B.
On Tue, 25 Jul 2023 at 20:45, Mr.Bim <bmopat@yahoo.com> wrote:
> Partition pruning is not working on the updates query, am I missing something?
In PG13, partition pruning for UPDATE and DELETE only works during
query planning. Because you're using CURRENT_TIMESTAMP, that's not an
immutable expression which can be evaluated during query planning.
This means that the only possible way to prune partitions when using
CURRENT_TIMESTAMP is during query execution. Unfortunately, execution
time pruning does not work for UPDATE/DELETE in PG13. PG14 is the
first version to have that.
There's a note in the documents [1] about this which reads:
"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."
If you were never to imbed that query in a view or use PREPAREd
statements, then you could replace CURRENT_TIMESTAMP with
'now'::timestamp. 'now' will be evaluated at parse time and that
means it'll be a known value to the planner. However, unless you
somehow can be completely certain that this query will never be put
inside a view or used with prepared statements, then I'd highly
recommend not doing this. SQLs tend to get copied and pasted and it
one day might get pasted into the wrong place (e.g. a view) and that
could cause problems.
Example with a view:
postgres=# create view v_test as select 'now'::timestamp;
CREATE VIEW
postgres=# explain verbose select * from v_test;
QUERY PLAN
---------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=8)
Output: '2023-07-26 00:17:32.370399'::timestamp without time zone
(2 rows)
postgres=# explain verbose select * from v_test;
QUERY PLAN
---------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=8)
Output: '2023-07-26 00:17:32.370399'::timestamp without time zone
(2 rows)
note that the view always just returns the time when the view was created.
My recommendation, if this is a show-stopper for you, would be to
consider using a newer version of PostgreSQL.
David
[1] https://www.postgresql.org/docs/13/ddl-partitioning.html