Partition pruning not working on updates
| От | Mr.Bim |
|---|---|
| Тема | Partition pruning not working on updates |
| Дата | |
| Msg-id | 698440190.2631047.1690255075203@mail.yahoo.com обсуждение исходный текст |
| Ответы |
Re: Partition pruning not working on updates
(David Rowley <dgrowleyml@gmail.com>)
|
| Список | pgsql-hackers |
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.
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Thomas MunroДата:
Сообщение: Re: odd buildfarm failure - "pg_ctl: control file appears to be corrupt"