PLUTO-139 outcome: add 6 scope-join FK indexes, drop 2 zero-scan indexes, retire the dead isUserRowSafeToSupersede fn
From PLUTO-139, all four items measured on both the app plane and the DB plane before filing. Nothing here is blind. (A) INDEX MIGRATION - add the 6 confirmed-hot FK indexes: comisionAdjuntos.adjuntoId, comisionJtps.jtpId, jtpAyudantes.jtpId, jtpAyudantes.ayudanteId, studentAssignments.studentId, studentAssignments.jtpId. All six are the who-teaches-where scope joins behind resolveDataAccessScope/accessibleComisionIds, confirmed on BOTH planes: app-side filter-region counts 9/9/3/2/3/2, DB-side in vActiveRoster ON-clauses (adjuntoId, studentId, jtpId) and fn_resolveDataAccessScope (comisionJtps.jtpId, both jtpAyudantes columns; comisionJtps.jtpId also in fn_forbidAdjuntoJtpOverlap). DEFERRED, do not include: academicHierarchy.adjuntoId - only 2 app hits and NO DB-plane corroboration in either direction (the scope functions do not reference that table at all). Index it only behind an actual plan. (B) DROP idx_practicas_editlocked. Partial index on practicas(editLockedAt) WHERE NOT NULL from migration 006. idx_scan = 0 over a 6.3-month window (stats reset 2026-02-12 23:53:40 UTC), and cold BY CONSTRUCTION rather than merely unused: every app read of lock state keys on practicas.id and only projects editLockedAt, no query uses editLockedAt as a driving predicate, and there is no stale-lock sweeper (the lock expires by comparison against now() - interval 2 hours at read time). (C) DROP idx_userAuditLog_relatedAccessRequestId. Partial index, idx_scan 0, while the other three indexes on that table serve 1394 / 848 / 1 scans. NOTE THE TRAP: PLUTO-139 called the TABLE an archival candidate and that is WRONG - userAuditLogLegacy is frozen for WRITES only (forbidWrite trigger ENABLE ALWAYS, UPDATE/DELETE/TRUNCATE revoked) but LIVE FOR READS, unioned into the user history panel by fn_getAdminUserDetail, 486 rows. Dropping or archiving the table silently empties the historical half of every user audit panel. The dead object is this one index, not the table. (D) isUserRowSafeToSupersede is DEAD CODE - decide drop vs document. Zero callers in src/ (repo grep: only migrations and schema.md) AND zero callers in prosrc (every fn_* body and trigger def swept, only its own definition matches). Structurally unreachable, not merely unexercised. Carried through three hardening migrations nobody needed: 062 dropped its userAuditLogLegacy check, 029 and 099 re-pinned its search_path. Either drop it or mark it dead in schema.md, but it should stop looking live. Its practicas IN-list (u.id IN (assignmentAdjuntoId, asistenteId, operadorId, assignmentJtpId, createdBy, complementadoPor)) is the ONLY DB-plane filter use of an attribution FK anywhere, and it is moot because the function never runs - so those six columns are NOT index candidates. COSTING NOTE if anyone ever revives (D): that IN-list is an OR of equalities across six columns of the same row, so the planner needs a BitmapOr across six separate indexes to use them at all. Six single-column indexes on a clinical-volume table is not obviously a net win. Apply order does not matter; A is the only one with a read-performance effect, B/C/D are cleanup. All four are reversible. Per DDL discipline this wants a pre-apply audit review.
Activity
-
db-pluto-ca
-
2026-10-06 read-only prod catalog recheck: ALL six part-A scope-join FK indexes already exist, each created by applied migration 122-pluto-751-fk-covering-indexes.sql; no need to recreate. Both proposed zero-scan drops remain present (idx_practicas_editlocked, idx_userAuditLog_relatedAccessRequestId), as does isUserRowSafeToSupersede(uuid). Thus only B/C/D remain candidates, subject to refreshed usage/caller evidence and pre-apply audit; no write made.
-
Refreshed 2026-10-06 pg_stat_user_indexes: idx_practicas_editlocked and idx_userAuditLog_relatedAccessRequestId each idx_scan=0, idx_tup_read=0, idx_tup_fetch=0 since pg_stat_database.stats_reset 2026-02-12T23:53:40.317Z. Repo src/ search found zero isUserRowSafeToSupersede callers; DB function remains present. This supports B/C/D as cleanup candidates but does not authorize apply.
-
db-pluto-cc