외래 키 열에도 색인이 필요할까

~10 min · indexes, foreign-keys

Level 0스키마 새싹

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

참조 무결성과 조회 속도는 별개야

... REFERENCES parent(id)를 선언하면 부모 쪽 기본 키는 색인이 있지만 자식의 외래 키 열에는 자동 색인이 생기지 않아. 제약은 관계를 지키고, 색인은 그 관계를 따라 찾는 비용을 줄이는 서로 다른 장치야.

부모 삭제와 자식 조회를 생각해

부모를 지우거나 키를 바꿀 때 PostgreSQL은 참조하는 자식이 있는지 확인해야 해. 자식 행이 적으면 차이가 작지만 큰 테이블에서는 외래 키 색인이 잠금 시간과 조회 비용을 크게 줄여.

중복 색인은 만들지 마

복합 색인의 첫 열이 이미 외래 키라면 별도 단일 열 색인이 필요 없을 수 있어. 현재 색인과 실제 JOIN·삭제 경로를 함께 확인해.

Code

외래 키와 색인은 함께 검토해·sql

CREATE TABLE orders (
    id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
    placed_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX orders_customer_id_idx ON orders (customer_id);

색인이 빠진 외래 키 찾기·sql

SELECT
    c.conrelid::regclass AS table_name,
    string_agg(a.attname, ', ') AS columns
FROM   pg_constraint c
JOIN   pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
WHERE  c.contype = 'f'
GROUP  BY c.oid, c.conrelid
HAVING NOT EXISTS (
    SELECT 1
    FROM   pg_index i
    WHERE  i.indrelid = c.conrelid
      AND  (c.conkey::int[]) <@ (i.indkey::int[])
);
-- 참조 컬럼이 어느 인덱스에도 안 덮인 모든 FK 반환.

External links

외래 키에 색인을 만들어라

Exercise

실제 데이터베이스에서 위 카탈로그 질의를 실행해 색인이 없는 모든 외래 키를 찾아. 각각 색인을 추가할지 판단하고, 행을 절대 지우지 않는 조회용 테이블처럼 예외로 남기는 경우에는 이유를 기록해.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousJSONB와 전문 검색을 위한 GIN Next →INCLUDE로 결과 열까지 싣기

이 페이지에서 버그를 발견하셨거나 피드백이 있으세요?문제 신고

🔔 답글 알림 (로그인 필요)

로그인 — 댓글을 남기려면 로그인해 주세요.

아직 댓글이 없어요. 첫 댓글을 남겨보세요.