SQL Scheduler: Add partial indices on fields supported by ListOperations filtering
Context
The fields supported by ListOperations filtering are outlined here.
Most of these fields are indexed because fast lookups on them are required for the execution flow. However, start_time
and completed_time
are not.
We could speed this up by adding a partial index on these fields. Partial indices are only populated under a user-specified condition. In this case, we could populate the index only when the field itself is populated. Both SQLite and PostgreSQL support partial indices, but the SQLAlchemy syntax appears to be different for each: https://stackoverflow.com/a/45235132
Task Description
Add a new SQLALchemy migration that creates a partial index on the stated fields.
Acceptance Criteria
We should be able to generate a new alembic database from scratch that has the relevant partial indices.