Expression indexes for findDominioCandidates (a3104407): full seq scans on every call including 404s

Sub-tickets

Activity

• wi-cli-venus created · 5w ago
• wi-cli-venus assigned · 3w ago
  db04-mba-l-ca
• db04-mba-l-ca progress · 3w ago
  db04-mba-l-ca 2026-09-24 04:07 ART — SELECT-only pg_indexes check: zero public.vehiculos expression indexes and zero rutap.vehiculo_historial_normalizado expression indexes whose definition contains regexp_replace in DEV movilba_dev and PROD movilba. The proposed normalization indexes are absent; EXPLAIN runtime was not remeasured. Status unchanged.
• db04-mba-l-ca progress · 3w ago
  db04-mba-l-ca 2026-09-24 04:02 ART — Timestamp correction only: the immediately preceding sweep body on this item carried a future 04:04/04:07 time; the BWI event createdAt is authoritative. No change to its measured findings or status.
• coder03-mba-l-cc assigned · 1w ago
  db03-mba-l-cc / db
• db03-mba-l-cc progress · 1w ago
  db03-mba-l-cc 2026-10-05 15:31 ART - REMEASURED; the root cause is NOT the missing index. EXPLAIN ANALYZE of the exact findDominioCandidates query (vehiculos.ts:555, LATEST_PROJECTED_SNAPSHOT_ID inlined), 3 runs each: PROD hit 357-394 ms, 404 309-311 ms; DEV hit 79-83 ms, 404 16 ms. In PROD, JIT is 272 of 303 ms (Emission 234 ms). The seq scans the ticket targets cost about 1 ms (vehiculos 107 buffers, vh_norm 2283 buffers, 10.6 ms). Estimated cost 108044 > jit_above_cost 100000 (default, both bases), inflated by the per-row EXISTS subplan estimate; DEV estimates 11433 and never JITs. With SET jit=off, PROD: 12.7 ms. Other app reads, PROD jit on -> off: v_vehiculos_export 717 -> 135 ms, v_vehiculos_detalle 988 -> 427 ms, v_vehiculos_list 60 -> 58 ms. The index proposal is dropped. Fix: docs/migrations/2026-10-05-movilba275-app-role-jit-off-DEV-Y-PROD.sql = ALTER ROLE <app role> SET jit = off (PROD svc_nextjs via superuser; DEV movilba_user), the same rolconfig mechanism as the existing statement_timeout=5s; new sessions only; rollback is RESET jit. Rehearsed under ROLLBACK in both bases. Waiting on audit, then apply PROD then DEV. NOT MEASURED: other app queries one by one, and the pool recycle time.
• audit-mba-l-cc audited · 1w ago
  audit pass: ALTER ROLE jit=off migration SQL, both legs
  payload

  {"scope":"ALTER ROLE jit=off migration SQL, both legs","outcome":"pass","evidence":"Gated by expected_database check, default ROLLBACK, apply flag explicit. Measurement in bwi matches DM: PROD JIT 272/303ms EXPLAIN ANALYZE 3 runs, jit=off->12.7ms; v_vehiculos_export 717->135, v_vehiculos_detalle 988->427, v_vehiculos_list unaffected (no JIT there); DEV never JITs (cost estimate below threshold). Rollback = RESET jit, documented. Index proposal correctly dropped per measurement. Rehearsed under ROLLBACK both bases per DM.","reportRef":null}

• db03-mba-l-cc progress · 1w ago
  db03-mba-l-cc 2026-10-05 15:32 ART - APPLIED after audit PASS 18:32Z. PROD via superuser (current_database=movilba): svc_nextjs rolconfig is now {search_path=public,statement_timeout=5s,jit=off}; a new svc_nextjs session reads jit=off. DEV (movilba_dev): movilba_user {jit=off}; a new session reads off. The app picks it up on pooled-connection recycle, which was NOT forced. NEXT: SHIP the migration file to coder03.
• db03-mba-l-cc completed · 1w ago
  db03 2026-10-05: live. jit=off at role level, PROD svc_nextjs rolconfig {search_path=public,statement_timeout=5s,jit=off}, DEV movilba_user {jit=off}. findDominioCandidates on PROD 303 -> 12.7 ms. SQL versioned in docs/migrations/2026-10-05-movilba275-app-role-jit-off-DEV-Y-PROD.sql, shipped in v5.6.162; PROD/DEV/pod serve v5.6.163. Rollback: ALTER ROLE <role> RESET jit.