How to improve performance on query with nested INs?

Question

I am trying to improve the performance of this query using joins instead of INs.

Also I am using the GUI tablePlus with Postgres. What are some good tools to assist with performance?

UPDATE app_public.challenge_progress
SET "current" = 0
WHERE status = 'IN_PROGRESS'
AND milestone_id IN
     (SELECT cp.milestone_id
      FROM app_public.challenge_milestones cm
      JOIN app_public.challenge_progress cp ON cm.milestone_id = cp.milestone_id
      WHERE cp.milestone_id IN
          (SELECT milestone_id
           FROM app_public.challenge_milestones
           WHERE TYPE like '%_MONTHLY'))

I have tried this but I am not sure if it's better perf:

UPDATE app_public.challenge_progress
SET "current" = 0
FROM app_public.challenge_milestones cm
JOIN app_public.challenge_progress cp ON cm.milestone_id = cp.milestone_id
WHERE cp.status = 'IN_PROGRESS'
AND cm."type" like '%_MONTHLY'

Answer 1

You can at least untangle the statement to this:

UPDATE app_public.challenge_progress cp
SET    "current" = 0
FROM   app_public.challenge_milestones cm
WHERE  cm.type LIKE '%_MONTHLY'
AND    cp.status = 'IN_PROGRESS'
AND    cp.milestone_id = cm.milestone_id 
AND    cp."current" IS DISTINCT FROM 0;  -- optional, to skip empty updates

Most importantly, you can join more tables with FROM in an UPDATE statement like demonstrated. (Do not JOIN to the target table additionally like you tried in the question!)

This is assuming that app_public.challenge_milestones.milestone_id is a UNIQUE column. Else, throw in a subquery with DISTINCT or GROUP BY.

And app_public.challenge_progress."current" can be NULL. Else, simplify the last line to AND cp."current" <> 0;. See:

• How do I (or can I) SELECT DISTINCT on multiple columns?

More performance optimization may be possible (with indexes?), depending on undisclosed details of your setup.

Answer 2

in postgres 15, you can use MERGE.

BEGIN;
CREATE TABLE challenge_progress (
    "current" int,
    status text,
    milestone_id int
);
CREATE TABLE challenge_milestones (
    milestone_id int,
    type text
);
INSERT INTO challenge_progress (status, milestone_id)
    VALUES ('IN_PROGRESS', 1),
    ('IN_PROGRESS', 2),
    ('IN_PROGRESS', 3),
    ('DONE', 4);
INSERT INTO challenge_milestones (milestone_id, type)
    VALUES (1, '1_MONTHLY'),
    (2, '1_MONTHLY'),
    (3, '1_MONTHLY'),
    (4, '4_MONTHLY');
COMMIT;

then merge:

MERGE INTO challenge_progress cp
USING challenge_milestones cm ON cp.milestone_id = cm.milestone_id
WHEN MATCHED
    AND cm.TYPE LIKE '%_MONTHLY'
    AND cp.status = 'IN_PROGRESS' THEN
    UPDATE SET
        "current" = 0;