PostgreSQL /flow_runs/filter times out for state-filtered START_TIME_ASC queries on large flow_run tables

Author: SuperLamicCreated Sep 15, 2026Updated Sep 15, 2026

Labelsbug

Bug summary

We observed POST /flow_runs/filter returning HTTP 500 for the following query against a self-hosted Prefect server using PostgreSQL:

python

from datetime import datetime, timedelta, timezone

from prefect.client.orchestration import get_client
from prefect.client.schemas.filters import (
    FlowRunFilter,
    FlowRunFilterStartTime,
    FlowRunFilterState,
    FlowRunFilterStateType,
)
from prefect.client.schemas.objects import StateType
from prefect.client.schemas.sorting import FlowRunSort


async with get_client() as client:
    runs = await client.read_flow_runs(
        flow_run_filter=FlowRunFilter(
            state=FlowRunFilterState(
                type=FlowRunFilterStateType(any_=[StateType.RUNNING])
            ),
            start_time=FlowRunFilterStartTime(
                before_=datetime.now(timezone.utc) - timedelta(minutes=15)
            ),
        ),
        sort=FlowRunSort.START_TIME_ASC,
        limit=200,
        offset=0,
    )

Our flow_run table contained approximately 6.35 million rows (26 GB), including approximately 27,000 RUNNING rows.

Prefect generated this query shape:

sql

SELECT ...
FROM flow_run
WHERE flow_run.state_type IN ('RUNNING')
  AND coalesce(flow_run.start_time, flow_run.expected_start_time) <= :cutoff
ORDER BY coalesce(flow_run.start_time, flow_run.expected_start_time) ASC
LIMIT 200 OFFSET 0;

The existing (state_type, start_time) index cannot serve the coalesce(...) expression. The existing indexes on coalesce(start_time, expected_start_time) do not include state_type.

PostgreSQL consequently scanned the ordered expression index and filtered by state afterward:

Limit  (actual time=59751.738..61319.498 rows=200)
  ->  Index Scan Backward using ix_flow_run__coalesce_start_time_expected_start_time_desc
        Filter: (state_type = 'RUNNING'::state_type)
        Rows Removed by Filter: 5497752
Execution Time: 61319.544 ms

The API request ultimately returned:

PrefectHTTPStatusError: Server error '500 Internal Server Error'
for url '/api/flow_runs/filter'
Response: {'exception_message': 'Internal Server Error'}

The corresponding server exception ended in:

File "prefect/server/api/flow_runs.py", line 551, in read_flow_runs
    db_flow_runs = await models.flow_runs.read_flow_runs(...)
File "prefect/server/models/flow_runs.py", line 341, in read_flow_runs
    result = await session.execute(query)
...
File "asyncpg/prepared_stmt.py", line 177, in fetch
    data = await self.__bind_execute(args, 0, timeout)
...
TimeoutError

We verified that adding the following index resolves the pathological plan:

sql

CREATE INDEX CONCURRENTLY ix_flow_run__state_type_coalesce_start_time
ON flow_run (
    state_type,
    (coalesce(start_time, expected_start_time))
);

After adding it, the identical API request returned 200 rows in 0.385 seconds. A flow that previously failed on its first page subsequently fetched 4,000 matching rows across 20 pages in 5.7 seconds and completed successfully.

Expected behavior: filtering by state_type and start_time while sorting by START_TIME_ASC should use an index compatible with the SQL generated by Prefect and should not time out on a large PostgreSQL-backed server.

Version info

Version:              3.6.29
API version:          0.8.4
Python version:       3.11.15
Git commit:           51b7ff44
Built:                Fri, May 01, 2026 09:34 PM
OS/Arch:              linux/x86_64
Profile:              ephemeral
Server type:          server
Pydantic version:     2.13.3
Server:
  Database:           postgresql
  PostgreSQL version: 17.10
Integrations:
  prefect-kubernetes: 0.7.8
  prefect-redis:      0.2.11

Additional context

No response

Source: PrefectHQ/prefect

View original on GitHub View discussion on GitHub