cal/major-domo-database
1
0
Fork 0
Code Issues 25 Pull Requests 25 Actions Packages Projects Releases Wiki Activity

Add missing indexes on foreign key columns in high-volume tables #74

New Issue
Open
opened 2026-03-27 04:06:37 +00:00 by cal · 1 comment
cal commented 2026-03-27 04:06:37 +00:00
Owner
Copy Link

Problem

PostgreSQL does NOT auto-index foreign key columns (unlike SQLite where the DB is small enough to not matter). The following high-volume columns lack indexes:

  • stratplay(game_id) — FK join column
  • stratplay(batter_id) — filtered in batting stats aggregation
  • stratplay(pitcher_id) — filtered in pitching stats aggregation
  • stratplay(runner_id) — filtered in running stats
  • stratgame(season) — heavily filtered
  • stratgame(season, week, game_num) — standings recalculation query order

These missing indexes cause sequential scans on the largest tables.

Fix

Add a migration to create these indexes. Also add index declarations to the Peewee model Meta classes.

Severity

Medium — performance degradation, especially as data grows.

## Problem PostgreSQL does NOT auto-index foreign key columns (unlike SQLite where the DB is small enough to not matter). The following high-volume columns lack indexes: - `stratplay(game_id)` — FK join column - `stratplay(batter_id)` — filtered in batting stats aggregation - `stratplay(pitcher_id)` — filtered in pitching stats aggregation - `stratplay(runner_id)` — filtered in running stats - `stratgame(season)` — heavily filtered - `stratgame(season, week, game_num)` — standings recalculation query order These missing indexes cause sequential scans on the largest tables. ## Fix Add a migration to create these indexes. Also add index declarations to the Peewee model `Meta` classes. ## Severity Medium — performance degradation, especially as data grows.
Claude added the
ai-working
 label 2026-03-27 10:01:08 +00:00
Claude removed the
ai-working
 label 2026-03-27 10:07:09 +00:00
Claude commented 2026-03-27 10:07:13 +00:00
Collaborator
Copy Link

PR opened: #95

Added Meta classes with indexes to StratGame (indexes on season and composite season/week/game_num) and StratPlay (indexes on game_id, batter_id, pitcher_id, runner_id). Also created migrations/2026-03-27_add_stratplay_stratgame_indexes.sql with CREATE INDEX IF NOT EXISTS statements for all 6 indexes.

PR opened: #95 Added `Meta` classes with `indexes` to `StratGame` (indexes on `season` and composite `season/week/game_num`) and `StratPlay` (indexes on `game_id`, `batter_id`, `pitcher_id`, `runner_id`). Also created `migrations/2026-03-27_add_stratplay_stratgame_indexes.sql` with `CREATE INDEX IF NOT EXISTS` statements for all 6 indexes.
Claude added the
ai-pr-opened
 label 2026-03-27 10:07:17 +00:00
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels   
ai-changes-requested

   
ai-pr-opened

   
ai-reviewed

   
ai-reviewing

   
ai-reviewing

   
ai-working
No Label
ai-changes-requested
ai-pr-opened
ai-reviewed
ai-reviewing
ai-reviewing
ai-working
Milestone
Clear milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
No Assignees
2 Participants
Notifications
Due Date
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: cal/major-domo-database#74
No description provided.
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?