Missing indexes on job table cause severe Postgres CPU under scheduler load #3358

Summary

On a large Docspell installation (~2.3M job rows, tens of thousands waiting), Postgres CPU stays extremely high because the scheduler repeatedly sequential-scans the entire job table. The table only has a primary key on jid — there are no indexes supporting the hot scheduler / job-store queries.

Observed impact (production)

• Lifetime ~98M sequential scans on job
• Hot queries each take ~1.1–1.5s with parallel seq scans (~170k buffers read)
• Many concurrent SELECT … FROM job from JOEX (pool-size > 1 amplifies this)
• Cache hit ratio degraded; waits on DataFileRead / BufferMapping

Hot queries (no supporting index)

1. Pick next job (QJob.selectNextJob):
   WHERE group_ = ? AND (state = 'waiting' OR state = 'stuck') ORDER BY state, priority, submitted LIMIT 1

2. Pick next group (QJob.selectNextGroup):
   distinct group_ among waiting/stuck jobs

3. insertAllIfNew / findNonFinalByTracker (RJob.findNonFinalByTracker):
   WHERE tracker = ? AND state IN ('waiting','scheduled','running','stuck')

Bulk tasks such as all-previews / all-page-count call (3) for every submitted child job (batches of 50), which multiplies the seq-scan cost while the waiting queue is large.

Proposed fix

Flyway migration (postgres / mariadb / h2), for example:

CREATE INDEX IF NOT EXISTS job_group_state_prio_submitted_idx
  ON job (group_, state, priority, submitted);

CREATE INDEX IF NOT EXISTS job_tracker_state_idx
  ON job (tracker, state);

CREATE INDEX IF NOT EXISTS attachment_created_idx
  ON attachment (created);

(attachment(created) helps findWithoutPreview which orders by created.)

Validation (EXPLAIN ANALYZE on a copy of production-like data)

Related notes

• V1.26.0 / V1.38.0 added many indexes (including joblog) but never indexed job for these access paths.
• Pruning old success jobs helps table size, but indexes are still required for a healthy waiting queue.
• Secondary: long-running all-previews / all-page-count hold server-side cursors for hours; that is operational, not fixed by these indexes alone.

Environment

• PostgreSQL 17
• Docspell JOEX with scheduler.pool-size = 5
• Large backlog of make-preview / make-page-count in group docspell-system

Draft implementation (fork): tiborrr#2