Tip for external Postgres: jit=off cut graph expansion from ~5s to ~70ms for us

[Xodus-CO]

Passing this along in case it helps anyone else running Hindsight against their own Postgres with larger banks.

We were seeing slow recall and a lot of graph-expansion timeouts, and the DB sat at its CPU limit even though the buffer cache hit rate was 99.9% and nothing was waiting on IO. pg_stat_statements showed most of the time in the expand_observations queries was Postgres JIT compilation, not execution. On 0.10.3 with Postgres's default jit = on, the main variant averaged ~5.0 s per call, and ~4.7 s of that was JIT. Across the whole database JIT was 63% of total query time. The query estimates a high cost on our data (one entity links to ~31k units), which pushes it past jit_above_cost (100000), so it gets compiled on every call.

Starting Postgres with -c jit=off fixed it. The same query went to ~150 ms on 0.10.3 and about 60-100 ms on 1.1.0, with zero JIT functions compiled, and DB CPU went from pinned at 2 CPUs to well under half of that. We didn't change anything else for that part.

If you want to check whether you're affected:

select left(query, 60), calls, round(mean_exec_time) mean_ms,
       round((jit_generation_time + jit_inlining_time
              + jit_optimization_time + jit_emission_time) / calls) jit_ms_per_call
from pg_stat_statements
where jit_functions > 0
order by jit_generation_time + jit_inlining_time
         + jit_optimization_time + jit_emission_time desc
limit 5;

If the expand_observations statements are at the top with JIT time close to their mean time, it's probably this.

Setup, for reference: Hindsight 0.10.3 then 1.1.0, Postgres 17 + pgvector 0.8.7, 58 banks, ~280k memory_units, 2 CPU / 16 GB container. I haven't tested whether this matters with the embedded pg0 database, so I can't say how many people it applies to. It might be worth a line in the external-Postgres docs if it turns out to be common.