Missing index on metabase_field.fk_target_field_id causes slow FK introspection queries
- Dominant language
- Clojure
- Stars
- 49.3k
- Forks
- 6.8k
- Avg merge
- 1d 13h
- Merged PRs (30d)
- 653
Description
## Summary
`metabase_field.fk_target_field_id` has no index, causing full sequential scans on every FK introspection query. This is the same class of problem fixed in #9519 (which added `idx_field_parent_id`) — `fk_target_field_id` was missed.
Verified against `master`: [`metabase_postgres.sql`](https://github.com/metabase/metabase/blob/master/resources/migrations/initialization/metabase_postgres.sql) defines the column but never creates an index on it.
## Observed query (from pg_stat_statements)
```sql
SELECT * FROM metabase_field WHERE fk_target_field_id = ?
```
On our a moderately-sized instance this is the number 1 most expensive query by total execution time on the entire Postgres cluster — ahead of all application queries — with thousands of calls, tens of thousands of block reads, and over 100 seconds cumulative exec time. It is a sequential scan because there is no index on `fk_target_field_id`.
## Current indexes on metabase_field
```
idx_field_entity_qualified_id — btree (('field_' || id))
idx_field_name_lower — btree (lower(name))
idx_field_parent_id — btree (parent_id) ← added by #9519
idx_field_table_id — btree (table_id)
idx_unique_field — unique btree (name, table_id, unique_field_helper)
metabase_field_pkey — unique btree (id)
```
`fk_target_field_id` is absent.
## Proposed fix
Add to migrations:
```sql
CREATE INDEX idx_field_fk_target_field_id ON metabase_field (fk_target_field_id);
```
The column is nullable; Postgres excludes NULLs from B-tree indexes automatically, so no partial index is needed — the index stays compact for instances that don't use FK relationships.
## Environment
- Metabase version: 0.60.x (latest)
- Metabase internal database: PostgreSQL 17
Contributor guide
Research direction
Start with resources/migrations/initialization/metabase_postgres.sql and compare the existing metabase_field indexes with the index added by #9519. Check the migration conventions for PostgreSQL, then verify that the fk_target_field_id lookup can use the new index. Done means the index is created by the appropriate migration without affecting nullable values.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, sql
- Domain
- databases, performance
- Issue type
- Bug
- Difficulty
- 2/5
- Estimated time
- 1-3 hours
- Activity status
- Quiet
- Clarity
- Clearly specified
- Newbie friendliness
- 62/100