populate: self.upstream (Diagram.trace) built unconditionally per make() — ~53 ms + 59 information_schema queries/key

[dimitri-yatsenko]

Summary

AutoPopulate._populate1 unconditionally constructs self._upstream = Diagram.trace(self & key)
for every populate key (src/datajoint/autopopulate.py:673), even when make() never accesses
self.upstream. Profiling shows this costs ~53 ms and 59 information_schema round-trips per key
— a large populate() throughput regression introduced with the 2.3 self.upstream feature.

Profiling

MySQL 8.0, N=1000 tight loop, synthetic chain Session(Manual) → A → B → C (all Computed, so C
has 3 ancestors), gc disabled, conn.query wrapped to count SQL round-trips.

measurement	mean	median
Diagram.trace(C & key)	52.9 ms, 59 SQL round-trips	52.2 ms
— load_all_upstream() only	5.0 ms, 3 round-trips	4.9 ms
— propagation walk + headings	43.7 ms, ~56 round-trips	40.5 ms
no-op C & key	13.4 µs	13.8 µs

Ratio: trace ≈ 3,900× the no-op. A 1,000-key populate pays ~53 s + ~59,000 metadata queries
of overhead before any make() work.

Root causes:

• load_all_upstream() (dependencies.py:262) calls load(force=True), which clear()s and
  re-queries information_schema for all PKs + FKs and rebuilds the whole graph on every call —
  the _loaded flag is ignored (dependencies.py:143).
• The dominant ~44 ms is _propagate_restrictions_upstream building a fresh FreeTable per ancestor
  per pass and .restrict()ing it, each materializing a table heading (a metadata query).

The comment at autopopulate.py:668 ("Lazy — only the graph copy runs here; the expensive SQL fetch
fires when the user accesses self.upstream[T]") is inaccurate — the construction itself is
neither lazy nor in-memory.

Fix (priority order)

1. Build the trace lazily — don't construct in _populate1; build on first self.upstream
   access (cached per current key). Keys whose make() never touch upstream then pay ~0. This
   alone removes the regression.
2. Cache the dependency load — load_all_upstream() should honor _loaded / cache within a
   populate run instead of load(force=True) every call.
3. Reuse FreeTable headings across propagation passes so ancestor headings aren't re-fetched
   per key.

Triage

Slated for v2.3.1 (per review triage). self.upstream is a headline 2.3 feature, so shipping it
with this per-key tax is a regression we want fixed before tagging 2.3.1. Benchmark script and
methodology available on request.

References

• Surfaced in the 2.3.1 dj.Diagram review (finding F1).
• Code: autopopulate.py:673 (_populate1), diagram.py:395 (trace),
  dependencies.py:143 (load) / :262 (load_all_upstream).