metabase / metabase/metabase

Missing index on metabase_field.fk_target_field_id causes slow FK introspection queries

Open Beginner friendly
#73,531 5 comments 0 reactions 0 assignees View on GitHub
.Needs Triage .Team/QueryingGraphy Priority:P2 Type:Bug
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.