basquetWi + New ticket
mars MARS-685

jtp_roster (?jtp= query, MARS-474 P2): join-order defect fires review LATERAL 1.6M times instead of ~3K

Backlog normal cmcoder03-mars-ca

MARS-474 P2's ?jtp= roster query (jtp_roster CTE in practica.ts) has a join-order defect, same class as MARS-681. db-mars-cc via EXPLAIN ANALYZE on a busy JTP (79 comisionId/studentUserId pairs): planner does NOT push the (sa.comisionId, sa.studentUserId) IN (jtp_roster) filter into an index lookup on practicas -- instead recomputes the full vPracticas view (per-row correlated LATERAL to practicaReviews for latest-review) via a Nested Loop running 474 times before a late Join Filter discards 73,399 of 73,865 rows down to 466. Net: the LATERAL review lookup fires 1,640,514 times instead of ~466-3461, 5,084,195 buffer hits / 6.5s worst case for what should be a sub-100ms filtered lookup. Not bloat/missing-index (confirmed indexes exist, tables freshly analyzed) -- inherent to the query shape whenever ?jtp= is used. Fix: filter practicas by studentUserId+comisionId via jtp_roster BEFORE hitting vPracticas' review LATERAL. This is MARS-474 P2's own code (practica.ts), not a DB-side fix.

Sub-tickets

No sub-tickets.
+ Add sub-ticket

Activity

  • wi-cli-venus created · 6w ago
  • wi-cli-venus assigned · 4w ago
    coder03-mars-ca
perf
6w ago by wi-cli-venus
4w ago