Benchmark Postgres join_collapse_limit on variantgrid.com and SA Path VG4 test

🤖 Written by Claude

## Summary

Analysis node queries that join more relations than Postgres's `join_collapse_limit` (default **8**) are never reordered by the planner — it just runs the joins in the order Django emitted them. When that happens a highly selective filter can end up applied *last*, after the whole variant set has been materialised.

We need numbers from real deployments before deciding whether to turn it on for analysis queries.

## The finding (dev box, 16.79M variants)

Analysis 48, node #547 — a `GeneListNode` ("Custom: UBE2E2", 31 rows) whose parent is an `AllVariantsNode` (854,757 rows).

The gene list narrows to **469 variant ids**, but at `join_collapse_limit=8` Postgres builds the entire 855k-row All Variants set first — parallel seq scans over `snpdb_variant` (16.8M), `snpdb_locus` (8M), the zygosity count partition (16.7M) and `snpdb_sequence` twice — and hash-joins those 469 ids on top:

```
Hash Join (rows=13) <- 469 gene-list ids, joined LAST
Parallel Hash Join (rows=1,184,024) Join Filter: (regex OR regex OR ... OR svlen)
Parallel Seq Scan on snpdb_variant 5,598,172 rows x 3 workers
Parallel Seq Scan on snpdb_locus 2,674,567 rows x 3
Parallel Seq Scan on ...zygositycount_collection_1 873,399 rows x 3
Parallel Seq Scan on snpdb_sequence x2 (ref and alt)
Buffers: shared hit=8668 read=294738, temp read=42392 written=42428
```

Changing **only** `join_collapse_limit`, no code change:

| | execution | planning | top node |
|---|---|---|---|
| default (8) | 21,840 ms | 3.0 ms | Gather |
| **16** | **3.7 ms** | 5.6 ms | Nested Loop |

The query has ~10 relations after Django flattens it (`snpdb_variant`, zygosity partition ×2, `snpdb_locus`, `snpdb_sequence` ×2, `annotation_variantgeneoverlap_version_N`, `genes_releasegenesymbol`, `genes_releasegenesymbolgene`, `genes_genelistgenesymbol`) — over the limit of 8, so no reordering is attempted.

Isolation checks on the dev box:
- `join_collapse_limit` is the knob that matters. `from_collapse_limit=16` alone changes nothing (7,981 ms).
- Stable under GEQO — 2.3 ms at `geqo_threshold` 12, 20, and `geqo=off`.

## Why it isn't simply "set it globally"

The ~30-way `select_subclasses()` join (already called out at `variantgrid/settings/components/default_settings.py:112`) regresses:

```
limit=8 planning= 21.5 ms execution= 8.9 ms
limit=16 planning=199.3 ms execution= 2.4 ms
```

That query runs a lot — `get_parent_subclasses()` (`analysis/models/nodes/analysis_node.py:361`) per node, plus `node_utils.py:106/138`, `analysis_update_tasks.py:125`, `models_analysis.py:312/338` and the views per load. On a 37-node analysis that is dozens of ~180 ms planning hits.

So if we adopt it, it should be **scoped to node variant queries**, via a context manager alongside `_statement_timeout` in `library/django_utils/major_operation.py` (which already does the same SET/RESET dance on the connection). Node *graph* queries are disjoint from node *variant* queries, so scoping gets the win without the cost.

## Dev-box sweep so far

Across all 123 plannable nodes on this box, comparing planned cost at 8 vs 16:

- plan changed on **3** nodes (#545, #546, #547 — all GeneListNodes under All Variants), each ~2062x cheaper by estimated cost
- **no node planned a more expensive plan**
- worst single-node planning time at 16 was 7.5 ms (vs 8.3 ms at 8)

A full A/B with `EXPLAIN (ANALYZE)` over all 46 analyses is running locally; I'll add those numbers when it finishes. But this dev box is not representative of production data volumes or analysis shapes — hence this issue.

## What to run

`profile_analysis_nodes` now takes `--join-collapse-limit` (integer, or `both` to run each profile twice at the server default and then 16). It reuses the existing `_PgSessionSettings` A/B machinery, so nothing cluster-wide is touched — it is a session `SET`, `RESET` on exit.

On **variantgrid.com** and **SA Path VG4 test**, pick a spread of real analyses (ideally including slow ones users have complained about, and any with GeneList/Intersection/Venn/MergeNodes over an All Variants or large cohort source):

```bash
python3 manage.py profile_analysis_nodes \
--analysis \
--explain --join-collapse-limit both \
--out /tmp/jcl_ab_$(hostname)_$(date +%Y%m%d_%H%M%S)
```

Add `--rerun` if we also want `qs.count()` wall times rather than just EXPLAIN ANALYZE.

Worth capturing alongside the run:

```sql
SHOW join_collapse_limit; SHOW from_collapse_limit; SHOW geqo_threshold;
SHOW max_parallel_workers_per_gather; SHOW work_mem; SHOW random_page_cost;
```

plus `SELECT count(*) FROM snpdb_variant;` so the numbers can be compared across deployments.

## What to report back

From `nodes.csv`, per node, the `join_collapse_limit=8` vs `16` pair of:

- `explain_execution_ms` — the win
- `explain_planning_ms` — the cost
- `node_type` and `count` — which node shapes are affected

The questions to answer:

1. How many nodes change plan at all? (dev: 3 of 123)
2. Does any node get **slower**, in execution or planning? (dev: none)
3. How big is the win on the slow real-world analyses, in wall-clock?
4. Does planning time blow up anywhere — particularly on the widest node graphs?

## Decision this feeds

Whether to enable a raised `join_collapse_limit` for analysis node variant queries, scoped as above.

Related but independent, and worth deciding separately:

- `AllVariantsNode` builds "everything but reference" as an OR of five type predicates including three regexes over `locus__ref__seq`/`alt__seq`. Replacing it with the negation (`Variant.get_no_reference_q()`) when exactly the complement is unticked took node #544 from 21.8s to 7.3s here. The five types are a genuine partition on this DB (all 10 pairwise intersections = 0; 0 of 16,794,517 variants match none), so `~T` == OR of the other four — but the All Variants *page* type list is **not** a partition (no `reference`, symbolic split per alt), so the rule must be gated on the exhaustive five.
- `GeneList.get_gene_ids_for_gene_lists()` returns a live `values_list` queryset that nests three more relations. Materialising it to a list of ids drops the node #547 query from 9 relations to 6 — under the default limit — and gives the same win at stock Postgres settings (nodes #545/#546/#547: 13,011/13,755/11,385 ms → 11.2/8.9/7.2 ms, identical row counts). That may be the better fix if the benchmarks say a raised limit is risky in production.

Run manage.py profile_analysis_nodes with --explain --join-collapse-limit both against representative analyses on variantgrid.com and SA Path VG4 test, capturing the listed PostgreSQL settings and variant count. Compare nodes.csv pairs for plan changes, execution and planning time, affected node types, and slower cases; done means reporting those findings to support the scoped join_collapse_limit decision.

Written by the indexing model from the issue text.