Annotation dump: single-pass scan (fetch rows + size range lock together) to ~halve dump DB cost
- Dominant language
- Python
- Stars
- 30
- Forks
- 3
- Avg merge
- 9h 28m
- Merged PRs (30d)
- 42
Description
🤖 Written by Claude
## Summary
The annotation dump path scans each batch's filtered variant range **twice** — once to size the range lock, once to fetch the rows to write. Collapsing this into a single pass should roughly halve the DB-side dump cost. This is most impactful for **external annotation** dumps (#1568), where the on-box work is *entirely* the DB dump (VEP runs off-box), so ~halving the scan ≈ halves external dump wall-time.
## Current behaviour (the double scan)
Per batch, both of these apply the same `variantannotation__isnull=True` anti-join + contig + SV-length filters over the same contiguous `Variant.pk` range:
1. **Boundary/count pass** — `_get_unannotated_count_min_max()` (`annotation/annotation_versions.py`) aggregates `Count`/`Min`/`Max` over the range purely to size the `AnnotationRangeLock`.
2. **Fetch pass** — `_unannotated_variants_to_vcf()` → `write_qs_to_vcf()` (`annotation/tasks/annotate_variants.py`, `snpdb/variants_to_vcf.py`) re-derives the identical set to read + write the VCF.
The two passes aren't equal cost (pass 1 aggregates with no row transfer; pass 2 transfers + formats), so the saving is the *filtered-scan* portion — exactly the expensive part when the anti-join / SV filters are heavy.
## Proposed change
Fetch once and reuse. A single `WHERE pk > watermark AND ORDER BY pk LIMIT batch_max` that walks the pk index, whose returned rows *are* the count (`len`), min (`rows[0]`), and max (`rows[-1]`), handed straight to the VCF writer. One scan instead of two.
Likely shape: have `_get_unannotated_count_min_max()` (or a new sibling) also return the row values, threaded into `dump_variants()`.
## Keep the coordinate sort
Not changing the VCF output order: VEP is measurably faster on coordinate-sorted input (sequential FASTA access + per-chromosome cache loaded once), and `pk` order ≠ coordinate order (pk clusters by insertion time, scattered across the genome). The `order_by("locus__contig__genomebuildcontig__order", "locus__position")` stays.
## Caveats / things to test
- `get_annotation_range_lock_and_unannotated_count()` is **shared with the in-VM scheduler** — the change lands on both the normal and external dump paths.
- It determines the **deterministic batch boundaries** that external import matching relies on (origin and clone must independently produce identical `[min, max]`). The single-pass `LIMIT` must yield the same "next `batch_max` unannotated by pk" set as today's block-scan — so origin + clone need the change deployed together.
- Preserve the `batch_min` / `batch_max` early-exit semantics of the current block loop.
## Testing / verification
- Assert the new path produces **identical** range-lock `[min, max]` + counts vs. the current block-scan on a test DB (proves boundaries unchanged → external matching stays valid).
- Benchmark before/after using each run's `dump_start` / `dump_end`.
## Context
Came out of #1568 (external annotation) work. Related: the same PR added a fix hoisting the per-run VEP version-identity call out of the dump loop (it was running VEP once per dumped VCF).
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.