Index definitions have drifted from the current Quartz.NET table scripts

[bittercoder]

The indexes this library creates have drifted from the ones in the Quartz.NET table scripts. Comparing against database/tables/tables_*.sql @ v3.19.1: of the 9 indexes upstream creates, 1 matches on columns (under a different name), 1 exists with different columns, and 7 aren't created. This library adds another 9 that upstream doesn't have. All four providers define the same 11 indexes, so it isn't provider-specific.

Current behaviour

Same name, different columns:

• IDX_{prefix}T_NFT_ST on QRTZ_TRIGGERS. Upstream is (SCHED_NAME, TRIGGER_STATE, NEXT_FIRE_TIME), here it's (NEXT_FIRE_TIME, TRIGGER_STATE).

In upstream, not created here:

• QRTZ_TRIGGERS: IDX_QRTZ_T_J (SCHED_NAME, JOB_NAME, JOB_GROUP), IDX_QRTZ_T_G_N (SCHED_NAME, TRIGGER_GROUP, TRIGGER_NAME), IDX_QRTZ_T_C (SCHED_NAME, CALENDAR_NAME), IDX_QRTZ_T_NFT_ST_MISFIRE (SCHED_NAME, MISFIRE_INSTR, NEXT_FIRE_TIME, TRIGGER_STATE) (in the SQL Server and MySQL scripts only)
• QRTZ_JOB_DETAILS: IDX_QRTZ_J_G_N (SCHED_NAME, JOB_GROUP, JOB_NAME)
• QRTZ_FIRED_TRIGGERS: IDX_QRTZ_FT_INST_JOB_REQ_RCVRY (SCHED_NAME, INSTANCE_NAME, REQUESTS_RECOVERY), IDX_QRTZ_FT_J_G (SCHED_NAME, JOB_NAME, JOB_GROUP)

Created here, not in upstream:

• QRTZ_TRIGGERS: T_NEXT_FIRE_TIME (NEXT_FIRE_TIME), T_STATE (TRIGGER_STATE)
• QRTZ_JOB_DETAILS: J_REQ_RECOVERY (REQUESTS_RECOVERY)
• QRTZ_FIRED_TRIGGERS: FT_TRIG_NAME, FT_TRIG_GROUP, FT_TRIG_INST_NAME, FT_JOB_NAME, FT_JOB_GROUP, FT_JOB_REQ_RECOVERY, all single-column

(IDX_{prefix}FT_TRIG_NM_GP is the one that matches on columns, same as upstream's IDX_QRTZ_FT_T_G.)

The T_NFT_ST difference is the one that changes query plans:

// src/AppAny.Quartz.EntityFrameworkCore.Migrations.SqlServer/EntityTypeConfigurations/QuartzTriggerEntityTypeConfiguration.cs
builder.HasIndex(x => new { x.NextFireTime, x.TriggerState })   // upstream: SCHED_NAME, TRIGGER_STATE, NEXT_FIRE_TIME
  .HasDatabaseName($"IDX_{prefix}T_NFT_ST");

AdoJobStore runs this on every scheduler poll to acquire triggers:

WHERE t.SCHED_NAME = @schedulerName AND TRIGGER_STATE = @state AND NEXT_FIRE_TIME <= @noLaterThan
      AND (MISFIRE_INSTR = -1 OR (MISFIRE_INSTR <> -1 AND NEXT_FIRE_TIME >= @noEarlierThan))
ORDER BY NEXT_FIRE_TIME ASC, PRIORITY DESC

Upstream's column order puts the two equality predicates first and the range predicate last. Here NEXT_FIRE_TIME leads with the range predicate and SCHED_NAME isn't in the index at all, so TRIGGER_STATE can't be a seek predicate and a database holding more than one scheduler name scans all of them together. T_NEXT_FIRE_TIME and T_STATE are both subsets of the same index, so they add write cost to the most frequently updated table without adding a lookup path.

IDX_QRTZ_T_NEXT_FIRE_TIME and IDX_QRTZ_T_STATE are both in the pristine-reset script's DROP INDEX block and aren't among the indexes it recreates, so I think these are names from an older schema revision.

Possible fix

Align each provider's HasIndex calls with the upstream set: correct T_NFT_ST to (SchedulerName, TriggerState, NextFireTime), add the seven missing indexes, drop the nine extras.

Two things I'm not too sure about:

• Regenerating migrations will emit DropIndex / CreateIndex against live tables. They're all non-unique so probably low risk, but it likely wants a release note.
• IDX_QRTZ_T_NFT_ST_MISFIRE is only in the SQL Server and MySQL scripts upstream, not PostgreSQL or SQLite. So matching upstream means either keeping that per-provider difference or picking one set for all four.