postgreSQL update queries running longer

Question

I have a query where processing status code for staging records are updated at the end of the processing, we have created daily partition on this table based on row_created_timestamp. On an average, we get around 10 - 15 million records in Staging table to process.

SQL looks similar to below:

EXPLAIN (ANALYZE, BUFFERS)
update midas_user.staging_role_player
set collection_process_status_code='IN PROGRESS'
where
staging_role_player_uuid in (select staging_role_player_uuid
from midas_user.staging_role_player
where row_created_tmst between '2023-05-17' and '2023-05-18' and
collection_process_status_code='NEW')

I ran for a date range having only 150,000 rows. I dont have index on collection_process_status_code as there are only 3 possible values for this attribute.

Updating question with the DDL statement and also the EXPLAIN (ANALYZE, BUFFER)

QUERY PLAN
Update on staging_role_player  (cost=968710.98..971459.28 rows=0 width=0) (actual time=176637.313..176637.319 rows=0 loops=1)
  Update on staging_role_player_0516 staging_role_player_2
  Update on staging_role_player_0517 staging_role_player_3
  Update on staging_role_player_0518 staging_role_player_4
  Update on staging_role_player_0519 staging_role_player_5
  Buffers: shared hit=2656732 read=1084010 dirtied=134653, temp read=2394 written=3893
  I/O Timings: read=155442.103
  ->  Nested Loop  (cost=968710.98..971459.28 rows=4137863 width=70) (actual time=36593.293..159444.797 rows=157244 loops=1)
        Buffers: shared hit=2003824 read=1067940, temp read=2394 written=3893
        I/O Timings: read=142423.964
        ->  HashAggregate  (cost=968710.42..968712.42 rows=200 width=47) (actual time=36586.762..37177.618 rows=157244 loops=1)
              Group Key: (staging_role_player_1.staging_role_player_uuid)::text
              Batches: 21  Memory Usage: 4169kB  Disk Usage: 15552kB
              Buffers: shared hit=35068 read=835280, temp read=2394 written=3893
              I/O Timings: read=33281.599
              ->  Result  (cost=0.00..968329.08 rows=152537 width=47) (actual time=320.436..36272.315 rows=157244 loops=1)
                    Buffers: shared hit=35068 read=835280
                    I/O Timings: read=33281.599
                    ->  Append  (cost=0.00..968329.08 rows=152537 width=47) (actual time=320.434..36232.186 rows=157244 loops=1)
                          Buffers: shared hit=35068 read=835280
                          I/O Timings: read=33281.599
                          ->  Seq Scan on staging_role_player_0517 staging_role_player_6  (cost=0.00..931761.79 rows=152536 width=47) (actual time=320.432..36161.219 rows=157244 loops=1)
                                Filter: ((row_created_tmst >= '2023-05-17 00:00:00+00'::timestamp with time zone) AND (row_created_tmst <= '2023-05-18 00:00:00+00'::timestamp with time zone) AND ((collection_process_status_code)::text = 'NEW'::text))
                                Rows Removed by Filter: 3782515
                                Buffers: shared hit=27860 read=835280
                                I/O Timings: read=33281.599
                          ->  Index Scan using staging_role_player_0518_pkey on staging_role_player_0518 staging_role_player_7  (cost=0.42..35804.61 rows=1 width=46) (actual time=44.689..44.689 rows=0 loops=1)
                                Index Cond: ((row_created_tmst >= '2023-05-17 00:00:00+00'::timestamp with time zone) AND (row_created_tmst <= '2023-05-18 00:00:00+00'::timestamp with time zone))
                                Filter: ((collection_process_status_code)::text = 'NEW'::text)
                                Buffers: shared hit=7208
        ->  Append  (cost=0.56..13.69 rows=4 width=46) (actual time=0.750..0.774 rows=1 loops=157244)
              Buffers: shared hit=1968756 read=232660
              I/O Timings: read=109142.365
              ->  Index Scan using staging_role_player_0516_pkey on staging_role_player_0516 staging_role_player_2  (cost=0.56..4.97 rows=1 width=46) (actual time=0.185..0.185 rows=0 loops=157244)
                    Index Cond: ((staging_role_player_uuid)::text = (staging_role_player_1.staging_role_player_uuid)::text)
                    Buffers: shared hit=584063 read=44913
                    I/O Timings: read=26133.943
              ->  Index Scan using staging_role_player_0517_pkey on staging_role_player_0517 staging_role_player_3  (cost=0.56..7.24 rows=1 width=47) (actual time=0.560..0.562 rows=1 loops=157244)
                    Index Cond: ((staging_role_player_uuid)::text = (staging_role_player_1.staging_role_player_uuid)::text)
                    Buffers: shared hit=598473 read=187747
                    I/O Timings: read=83008.422
              ->  Index Scan using staging_role_player_0518_pkey on staging_role_player_0518 staging_role_player_4  (cost=0.42..1.31 rows=1 width=46) (actual time=0.015..0.015 rows=0 loops=157244)
                    Index Cond: ((staging_role_player_uuid)::text = (staging_role_player_1.staging_role_player_uuid)::text)
                    Buffers: shared hit=471732
              ->  Index Scan using staging_role_player_0519_pkey on staging_role_player_0519 staging_role_player_5  (cost=0.14..0.16 rows=1 width=100) (actual time=0.002..0.002 rows=0 loops=157244)
                    Index Cond: ((staging_role_player_uuid)::text = (staging_role_player_1.staging_role_player_uuid)::text)
                    Buffers: shared hit=314488
Planning:
  Buffers: shared hit=204
Planning Time: 1.473 ms
Execution Time: 176640.595 ms

Table DDL: 
CREATE TABLE IF NOT EXISTS midas_user.staging_role_player
(
    staging_role_player_uuid character varying(36) COLLATE pg_catalog."default" NOT NULL,
    staging_source_record_uuid character varying(36) COLLATE pg_catalog."default" NOT NULL,
    role_player_id bigint,
    role_player_key character varying(1024) COLLATE pg_catalog."default" NOT NULL,
    ingestion_id character varying(256) COLLATE pg_catalog."default" NOT NULL,
    role_player_type_dnb_cd integer NOT NULL,
    subject_type_dnb_cd integer,
    senior_principal_indc boolean,
    source_organization_indc boolean,
    operating_status_dnb_cd integer,
    transfer_reason_dnb_cd integer,
    transfer_dt date,
    operating_status_dt date,
    control_dt date,
    start_yr character varying(4) COLLATE pg_catalog."default",
    dnb_perished_dt date,
    dnb_source_change_tmst timestamp with time zone,
    marketability_indc boolean,
    stop_distribution_indc boolean,
    inquiry_count_classification_txt character varying(2) COLLATE pg_catalog."default",
    control_type_dnb_cd integer,
    full_report_dt date,
    last_report_dt date,
    collection_process_status_code character varying(16) COLLATE pg_catalog."default" NOT NULL,
    source_provided_record_id character varying(256) COLLATE pg_catalog."default",
    row_modified_tmst timestamp with time zone NOT NULL,
    row_created_tmst timestamp with time zone NOT NULL,
    row_created_identifier_txt character varying(256) COLLATE pg_catalog."default" NOT NULL,
    row_modified_identifier_txt character varying(256) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT staging_role_player_pk PRIMARY KEY (staging_role_player_uuid, row_created_tmst)
) PARTITION BY RANGE (row_created_tmst);

CREATE INDEX IF NOT EXISTS staging_role_player_fk1
    ON midas_user.staging_role_player USING btree
    (staging_source_record_uuid COLLATE pg_catalog."default" ASC NULLS LAST)
;
-- Partitions SQL

CREATE TABLE midas_user.staging_role_player_0508 PARTITION OF midas_user.staging_role_player
    FOR VALUES FROM ('2023-05-17 00:00:00+00') TO ('2023-05-18 00:00:00+00');

Posting this question here to get to know if there is a better way to perform one attribute update in postgreSQL table.

Edit to the question: I checked Work Memory in PostgreS and it is set up as 4 MB. Just checking if increasing the work memory will help the query to run faster.

Answer 1

First, you could create an index to speed up the subquery:

CREATE INDEX ON midas_user.staging_role_player (
   collection_process_status_code,
   row_created_tmst
);

Perhaps it would be enough to reverse the column order in the primary key; I don't know how many rows are filtered out by collection_process_status_code = 'NEW'.

But the main problem is that the number of distinct staging_role_player_uuid is estimated so badly. This may be due to the fact that PostgreSQL does not gather statistics on partitioned tables automatically. Try

ANALYZE midas_user.staging_role_player;

If that still gives you the bad nested loop join, you could experiment with setting enable_nestloop = off to see if that gives you a better execution time. If yes, you can change the parameter as a last resort, but only for the execution of this one query.