partition_query.sql

CREATE INDEX idx_tasks_group_key ON tasks (group_key, id) WHERE status = 'QUEUED';

CREATE INDEX idx_tasks_status ON tasks (status);

EXPLAIN ANALYZE WITH
    ordered_tasks AS (
        SELECT
            t.id,
            t."status",
            t."group_key",
            row_number() OVER (PARTITION BY t."group_key" ORDER BY t."id" ASC) AS rn
        FROM
            tasks t
        WHERE
            "status" = 'QUEUED'
        ORDER BY rn, t.id ASC
    ),
    eligible_tasks AS (
        SELECT
            t1.id
        FROM
            ordered_tasks t1
        FOR UPDATE SKIP LOCKED
        LIMIT 100
    )
UPDATE tasks
SET
    "status" = 'RUNNING'
FROM
    eligible_tasks
WHERE
    tasks.id = eligible_tasks.id
RETURNING tasks.*;

                                                                                         QUERY PLAN                                                                                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on tasks  (cost=1457.35..1731.85 rows=100 width=42) (actual time=89.427..113.481 rows=100 loops=1)
   ->  Hash Join  (cost=1457.35..1731.85 rows=100 width=42) (actual time=88.853..102.067 rows=100 loops=1)
         Hash Cond: (tasks.id = eligible_tasks.id)
         ->  Seq Scan on tasks  (cost=0.00..236.00 rows=10000 width=14) (actual time=0.513..8.592 rows=10000 loops=1)
         ->  Hash  (cost=1456.10..1456.10 rows=100 width=40) (actual time=88.168..88.174 rows=100 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 16kB
               ->  Subquery Scan on eligible_tasks  (cost=1453.85..1456.10 rows=100 width=40) (actual time=87.810..88.052 rows=100 loops=1)
                     ->  Limit  (cost=1453.85..1455.10 rows=100 width=8) (actual time=87.764..87.903 rows=100 loops=1)
                           ->  Subquery Scan on t1  (cost=1453.85..1577.60 rows=9900 width=8) (actual time=87.749..87.844 rows=100 loops=1)
                                 ->  Sort  (cost=1453.85..1478.60 rows=9900 width=28) (actual time=87.743..87.775 rows=100 loops=1)
                                       Sort Key: (row_number() OVER (?)), t.id
                                       Sort Method: top-N heapsort  Memory: 37kB
                                       ->  WindowAgg  (cost=0.29..796.82 rows=9900 width=28) (actual time=0.675..72.156 rows=9200 loops=1)
                                             ->  Index Only Scan using idx_tasks_group_key on tasks t  (cost=0.29..623.57 rows=9900 width=16) (actual time=0.477..28.364 rows=9200 loops=1)
                                                   Heap Fetches: 6700
 Planning Time: 15.832 ms
 Execution Time: 114.348 ms
(17 rows)