stage_jobs performance

[pirvudoru]

Environment

• Oban Version: 2.17.12, 2.18.3
• PostgreSQL Version: 13.15
• Elixir & Erlang/OTP Versions (elixir --version): Elixir 1.16.3 (compiled with Erlang/OTP 26)

Current Behavior

I am currently running Oban in production and we make use of scheduled future jobs to do work. Currently we have 3.5 Million oban records that are set to run up to 60 days in the future. While investigating performance issues we noticed that the top query in AWS insights was one for Oban:

DB CPU was averaging around 45%.

This seems to be originating from the stage_jobs from the Basic Engine.

After adding the index:

create index oban_jobs_scheduled_at_state_queue_index
    on oban_jobs (scheduled_at, state, queue);

CPU averages around 10%

And the average time for the query went from ~1340ms to negligible (>0.1ms)

AFAIK, usualy in the index, the order of the fields mater, and ideally would be ordered from highest variation to the lowset

[sorentwo]

The existing compound index (oban_jobs_state_queue_priority_scheduled_at_id_index) is capable of an index only scan for staging jobs, as demonstrated by the plan below:

                                                                                    QUERY PLAN
══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════
 Limit  (cost=0.42..228.92 rows=314 width=12) (actual time=0.021..0.243 rows=230 loops=1)
   ->  Index Only Scan using oban_jobs_state_queue_priority_scheduled_at_id_index on oban_jobs  (cost=0.42..228.92 rows=314 width=12) (actual time=0.020..0.232 rows=230 loops=1)
         Index Cond: ((state = ANY ('{scheduled,retryable}'::oban_job_state[])) AND (queue IS NOT NULL) AND (scheduled_at <= now()))
         Heap Fetches: 230
 Planning Time: 0.197 ms
 Execution Time: 0.262 ms

Note that the index also includes the id, which is why it can do an index only scan. The inefficient plan you saw before may have been fixed with a VACUUM ANALYZE to refresh, or by rebuilding the old index.

Of course, you're welcome to keep the new index (or create whichever indexes help your particular situation). For others, the existing compound index is able to keep that query optimized.