Analysis node grid: small gene-filtered source nodes time out — gene semi-join planned last

🤖 Written by Claude

Small gene-filtered source nodes time out in the node grid. Rollbar shows repeated `OperationalError: canceling statement due to statement timeout` from `node_grid_handler` on `vgaws` (code version `9be0ee53b`), 2026-08-13/14.

Both nodes are `AllVariantsNode` restricted to gene symbol **KIF5B**, with tiny result sets:

| analysis | node | build | count |
|---|---|---|---|
| 12062 | 80816 | GRCh38 | 1074 |
| 12063 | 80818 | GRCh37 | 1153 |

(a third node in the report, 80857, has since been deleted)

These are far below `ANALYSIS_GRID_SORT_MAX_ROWS` (10,000), so sorting stays enabled — correctly. This is **not** the #1651 sort problem; the sort is not what blows up.

## Root cause: the gene filter is applied last

`VariantTranscriptAnnotation.get_overlapping_genes_q()` (`annotation/models/models.py:2223`) emits the gene restriction as a subquery:

```sql
pk IN (SELECT variant_id
FROM annotation_variantgeneoverlap_version_90
WHERE gene_id = ANY('{ENSG00000170759,3799,ENSG05220049235}') AND version_id = 90)
```

That inner scan returns 6,080 rows and costs ~6,700 — it is by far the most selective thing in the query. But Postgres plans it as a **semi-join applied at the very end**. It first builds the entire annotated variant relation for the build:

- parallel seq scan of `snpdb_locus` (43M rows)
- parallel seq scan of the `snpdb_variantzygositycount` partition (12M rows, from the node's zygosity count filter)
- sort + merge against all 4.4M rows of `annotation_clinvar_version_212`
- the `variantannotation` / `transcriptversion` / `geneversion` / `gene` / `hgnc` / `uniprot` / `dbnsfp` joins

...roughly **5M rows**, and only then semi-joins down to ~350. Estimated total cost 20.8M for node 80818 and 14.9M for 80816.

The trigger is Postgres's default `join_collapse_limit` / `from_collapse_limit` of **8**. The grid SQL is ~16.7 KB over **~18 relations** in the main join tree, so the planner cannot search the join-order space and is stuck with a left-deep order that applies the one selective predicate last.

## Measurements (EXPLAIN ANALYZE against prod, node 80818, `extra_filters=default`, `sidx=pk`)

| variant | planning | execution |
|---|---|---|
| baseline — what production runs | — | **timed out at 180s** |
| `enable_mergejoin=off` | — | **timed out at 180s** |
| `join_collapse_limit = from_collapse_limit = 20` | 378 ms | **260 ms** |
| explicit `Q(pk__in=[…1153 ids])` | 35 ms | **125 ms** |

Turning off merge join does not help, so the merge semi-join is a symptom rather than the cause. Materialising the node's PKs takes 0.1s, so the explicit-PK form is ~0.25s end to end, versus the 5-minute `MAJOR_OPERATION_STATEMENT_TIMEOUT_SECONDS` cancellation users are currently hitting.

With explicit PKs the plan becomes a bitmap index scan on `snpdb_variant_pkey` for the 1153 IDs, then nested loops for every annotation join — each annotation touched once per output row at any node size.

## Why the existing #546 mitigation doesn't fire

`AnalysisNode.get_small_parent_arg_q_dict()` (`analysis/models/nodes/analysis_node.py:571`) already performs exactly this substitution, and its docstring describes exactly this failure mode. But it only applies to a node's **parents**. These are source nodes with no parents, so it never runs. Separately, `ANALYSIS_NODE_STORE_ID_SIZE_MAX` is 1000, just below both node counts (1074, 1153), so even the parent path would have declined them.

## Scope

Not specific to KIF5B or to `AllVariantsNode`. The same `pk__in=(subquery)` shape against a mostly-unfiltered variant relation is used by `GeneListNode`, `MOINode`, `TissueNode`, `PhenotypeNode`, `TrioNode` and `QuadNode`.

## Possible fixes

1. **Extend the #546 explicit-PK substitution to a node's own filters in the grid** — when `node.count` is known and small, materialise PKs via the existing `AnalysisNode.get_cached_node_pks()` and filter the grid queryset on `Q(pk__in=[...])`. Fastest measured option, and robust because it doesn't depend on the planner finding a good join order. Needs a threshold decision — `ANALYSIS_NODE_STORE_ID_SIZE_MAX` (1000) excludes these very nodes; `ANALYSIS_GRID_SORT_MAX_ROWS` (10,000) is the natural bound since the grid already treats that as "small enough to handle row-wise".
2. **Raise `join_collapse_limit` / `from_collapse_limit`** for grid connections. One-line change, 700x improvement measured — but at ~18 relations it crosses `geqo_threshold` (12), so GEQO would pick plans non-deterministically. A stability sweep across collapse limits 10/12/16/20/30, with and without `geqo`, is still running; results to follow.

Related: #1651, #1628, #1279, #1176, #546.

Start with AnalysisNode.get_small_parent_arg_q_dict() and get_cached_node_pks() in analysis/models/nodes/analysis_node.py, then inspect VariantTranscriptAnnotation.get_overlapping_genes_q() in annotation/models/models.py. Reproduce the node-grid query and compare the proposed small-node substitution with the planner-setting alternative; done means small source-node grids avoid statement timeouts across the listed node types without breaking the existing row and sorting limits.

Written by the indexing model from the issue text.