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.1Each 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_TIMESTAMPPartition 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.
