Query optimisation: planner-hostile joins and unmaterialised subqueries

🤖 Written by Claude.

Follow-up sweep after #1720. That issue found that joining the 17M-row `Variant` table through the small fixed `GenomeBuildContig` table collapsed the planner's row estimate to 1, giving a nested loop that re-scanned the variant side once per contig (83s -> 6s once rewritten as an IN list).

The same shape — a huge table filtered through a small lookup table, or through an unmaterialised subquery — appears in several other places. In each case the small side can be resolved to a list of ids in Python first.

### Planner-hostile joins

**`Variant.qs_from_variant_coordinate`** (`snpdb/models/models_variant.py:728`) still filters via `locus__contig__genomebuildcontig__genome_build` plus a `locus__contig__name` string comparison. The contig is fully determined in Python by `GenomeBuild.chrom_contig_mappings` (a cached dict that already handles name/UCSC/accession aliases), so this can filter `locus__contig=contig` directly and land on the leading edge of the `snpdb_locus(contig_id, position, ref_id)` unique index with no lookup joins at all.

This is the single-variant-by-coordinate lookup behind `Variant.get_from_variant_coordinate`, so it runs per variant during HGVS resolution (`genes/hgvs/hgvs_matcher.py:721`), ClinGen allele registration (`snpdb/clingen_allele.py:312,418`) and wiki import.

**Variant search chrom matching** (`snpdb/signals/variant_search.py:277`) uses `Variant.get_chrom_q`, an OR of two `__iexact` comparisons against the joined `Contig` table, on every search-box coordinate lookup. The genome build is in hand, so the same contig resolution applies.

**OMIM built-in filter** (`analysis/models/nodes/node_counts.py:17`) is `Q(variantannotation__gene__geneannotation__omim_terms__isnull=False)` — a three-hop join off a 17M-row table into a small lookup, with no `version=` filter. `GeneAnnotation` is `unique_together ("version", "gene")`, so this also matches rows from every `GeneAnnotationVersion` rather than the current one. Runs for node counts on every analysis node.

### Unmaterialised subqueries

The codebase already establishes the fix for these twice — `analysis/models/nodes/filters/tag_node.py:52` ("Tagging is done manually so this will only ever be small - much faster to convert to list") and `classification/models/classification.py:2112` ("List is much faster than inner query"). These sites were missed:

- `VariantTag.variants_for_build_q` (`analysis/models/models_variant_tag.py:111`) — hot via TagNode on every analysis re-run and via the TaggedVariantGrid page.
- `get_has_variant_tags` (`snpdb/variant_queries.py:42`) and the gene symbol grid tag filter (`genes/grids.py:169`) — same shape.
- `get_has_classifications_q` (`snpdb/variant_queries.py:34`) round-trips `VariantAllele -> Allele -> VariantAllele -> Variant -> Classification`. `Classification` has an `allele` FK, so the allele ids can be resolved directly.

### Repeated and oversized queries

- `snpdb/views/datatable_view.py:573,579` runs the same permission-scoped `COUNT(*)` twice on every DataTable draw whenever no filter parameters are supplied (`filter_queryset` returns the queryset unchanged).
- `Variant.get_best_variant_transcript_annotation` (`snpdb/models/models_variant.py:904`) issues two queries against `VariantTranscriptAnnotation` that differ only by `canonical`.
- `analysis/models/nodes/filters/filter_node.py:22,37` uses `.count()` purely as a boolean.
- `classification/views/views.py:981` fetches whole `Classification` rows (including the wide `evidence` JSONB) to read `lab` and `created`.
- `classification/views/clinvar_export_view.py:606` counts the same queryset twice; `beacon/datasets.py:167` counts then iterates the same queryset.

### Checked and cleared

`snpdb/variants_to_vcf.py` already matches the fixed `write_qs_to_vcf` shape from #1720. The analysis node `contig_ids` joins drive tiny per-node tables. `.order_by(...).count()` sites are fine — Django clears ordering for un-sliced counts.

Start with the named entry points in snpdb/models/models_variant.py, snpdb/variant_queries.py, analysis/models/models_variant_tag.py, and the listed view and filter files; compare them with the established list-conversion examples in tag_node.py and classification.py. Trace the affected query paths and use query plans or existing performance tests to verify that redundant counts, wide fetches, joins, and subqueries are reduced without changing results.

Written by the indexing model from the issue text.