Annotation dump: single-pass scan (fetch rows + size range lock together) to ~halve dump DB cost

[davmlaw]

🤖 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 <filters> 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).