RomM version
4.8.1

Describe the bug
I have 60k ROMs, so listing all games was very slow. I started to analyse the SQL queries using pg_stat_statements.

I found that roms table has a foreign key to platforms and rom_files has a foreign key to roms, but no index is created to improve join speed of these tables.

To Reproduce

1. Create a new empty database.
2. Connect to the database with psql or pgcli.
3. Inspect structure \d roms or \d rom_files.

Expected behavior
Index on rom(platform_id) column and rom_files(rom_id) column should exist.

Screenshots

Server:

• Alpine 3.22.3
• Docker
• PostgreSQL 17

Client:
Chrome browser

Additional context
I manually created the indices and query time when listing games of a platform went down from 4500ms to 200ms.

I used these commands:

CREATE INDEX IF NOT EXISTS idx_custom_rom_files_rom_id ON rom_files(rom_id);
CREATE INDEX IF NOT EXISTS idx_custom_roms_platform_id ON roms(platform_id);
ANALYZE rom_files;
ANALYZE roms;