Kinto / kinto / 22151083528

100%

master: 100%

push

github

Enable index range scans for last_modified queries (100x speedup in some cases) (#3645)

* Enable index range scans for last_modified queries

Kinto has an excellent composite index (parent_id, resource_name,
last_modified DESC) that can satisfy filtered listings, pagination, and
sorting from a single B-tree scan. However, last_modified is wrapped in
as_epoch() in WHERE and ORDER BY clauses, which prevents PostgreSQL from
using this index for range scans and sort elimination.

Since as_epoch and from_epoch are exact inverses for all timestamps
stored in the table, we can move the conversion from the column side to
the value side. Instead of `as_epoch(column) >= value`, we generate
`column >= from_epoch(value)`. The bound parameter remains an integer;
from_epoch() is applied server-side.

What changed:
- _format_conditions: For modified_field scalar comparisons, generate
  last_modified <op> from_epoch(:value) instead of as_epoch(last_modified)
  <op> :value. IN/EXCLUDE operators (if ever used on last_modified) retain
  existing behavior as a safe fallback.
- resource_timestamp: ORDER BY uses last_modified DESC instead of
  as_epoch(last_modified) DESC
- purge_deleted: last_modified < from_epoch(:before) instead of
  as_epoch(last_modified) < :before
- Schema migration 25→26: Updated bump_timestamp() trigger function with
  ORDER BY last_modified DESC

Implementation note: We wrap the parameter placeholder in from_epoch()
rather than wrapping the column in as_epoch(), preserving output format
while restoring index usage.

Performance impact:
- Every paginated listing (pagination generates last_modified < X filters)
- Every resource_timestamp call (runs on every list response for ETag header)
- Every write operation (the trigger fires on every INSERT and UPDATE)
- Every purge_deleted call with a before parameter

The trigger fix alone is a major win for write-heavy workloads. Going from
"scan all rows for this parent_id+resource_name, evaluate as_epoch ... (continued)

1526 of 1526 branches covered (100.0%)

26 of 26 new or added lines in 2 files covered. (100.0%)

7814 of 7814 relevant lines covered (100.0%)

4.0 hits per line