gistfile1.txt

EXPLAIN UPDATE participants dest_participants SET seqno = 
    (SELECT n FROM
        (SELECT participants.id id, row_number() OVER (PARTITION BY projects.account_id ORDER BY participants.id) n
         FROM participants JOIN projects ON participants.project_id=projects.id)
    t WHERE t.id=dest_participants.id)
WHERE dest_participants.seqno IS NULL;

                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Update on participants dest_participants  (cost=0.00..6895397.53 rows=438 width=336)
   ->  Seq Scan on participants dest_participants  (cost=0.00..6895397.53 rows=438 width=336)
         Filter: (seqno IS NULL)
         SubPlan 1
           ->  Subquery Scan on t  (cost=12936.57..15734.95 rows=1 width=8)
                 Filter: (t.id = dest_participants.id)
                 ->  WindowAgg  (cost=12936.57..14658.65 rows=86104 width=8)
                       ->  Sort  (cost=12936.57..13151.83 rows=86104 width=8)
                             Sort Key: projects.account_id, participants.id
                             ->  Hash Join  (cost=19.69..4699.21 rows=86104 width=8)
                                   Hash Cond: (participants.project_id = projects.id)
                                   ->  Seq Scan on participants  (cost=0.00..3490.26 rows=87526 width=8)
                                   ->  Hash  (cost=14.39..14.39 rows=424 width=8)
                                         ->  Seq Scan on projects  (cost=0.00..14.39 rows=424 width=8)
                                               Filter: (account_id <> 2)