PostgreSQLで外部キー制約が付与された列にインデックスがあるかを調べる

PostgreSQLでは、外部キー制約を付与した列に自動でインデックスは作成されないため、必要に応じて明示的にインデックスを作成する必要があります。

このインデックスを作成するかどうかの方針については、Should I Create an Index on Foreign Keys in PostgreSQL?でも書かれているように、「パフォーマンス向上が見込まれる場合のみ作成する方針」や、「漏れが怖いため全て作成する方針」など、プロジェクトやチームによって様々だと思います。ただ、どのような方針を取るにせよ、設計と実際のインデックス作成状況のズレを検知する作業はしばしば発生します。この作業はテーブル数が多くなってくると目視で行うのはあまり現実的ではなくなってきます。

そこで、この作業を自動化するために、外部キー制約が付与された列にインデックスが存在しているかを一覧で確認できるSQLを書いてみました。

SQLの内容については、コメントで補足説明を入れています。なお、複数列からなる外部キーには非対応となっています(今後の課題)。

WITH base AS (
    SELECT
        cons.conname,
        cons.conkey,
        cons.conrelid  AS src_rel_id,
        attr1.attname  AS src_attr_name,
        cons.confrelid AS dst_rel_id,
        attr2.attname  AS dst_attr_name
    FROM
        pg_constraint cons
            -- conkeyに参照元の列番号があるので、pg_attributeとJOINしてテーブル名と列名を取得する。
            -- = ANYなのはconkeyがarrayのため。
            JOIN pg_attribute attr1
                 ON attr1.attrelid = cons.conrelid AND attr1.attnum = ANY(cons.conkey)
            -- confkeyに参照先の列番号があるので、pg_attributeとJOINしてテーブル名と列名を取得する。
            --  = ANYなのはconfkeyがarrayのため。
            JOIN pg_attribute attr2
                 ON attr2.attrelid = cons.confrelid AND attr2.attnum = ANY(cons.confkey)
    WHERE
        -- pg_constraintテーブルのcontype = 'f'（Foreign Key）で外部キー制約のみを取得。
        cons.contype = 'f'
        -- conkey, confkeyの長さをチェックして複数列からなる外部キー制約を除外
        AND (array_length(cons.conkey, 1) = 1 OR array_length(cons.confkey, 1) = 1)
)
SELECT
    base.conname AS 外部キー制約名,
    base.src_rel_id::regclass AS 参照元テーブル名,
    base.src_attr_name AS 参照元カラム名,
    base.dst_rel_id::regclass AS 参照先テーブル名,
    base.dst_attr_name AS 参照先カラム名,
    COALESCE (
        (
            SELECT
                string_agg(idx.indexrelid::regclass::text, ' , ')
            FROM
                pg_index idx
            WHERE
                idx.indrelid = base.src_rel_id
                -- 部分インデックスと関数インデックスは除外する
                AND indpred IS NULL
                AND indexprs IS NULL
                -- 外部キー列がインデックスの先頭にあるかを判定する
                -- - indkeyはint2vectorで0始まり
                -- - conkeyはarrayで1始まり
                AND idx.indkey[0] = base.conkey[1]
        ),
        '無し'
    ) AS 外部キー制約の列が先頭にあるインデックス
FROM
    base
;

なお、RLSを利用しているケースで、先頭にそのためのキー(以下の例ではtenant_id)があるインデックスも確認したい場合は以下のような列を追加すると確認できます。ただし、RLSが無効な条件下ではインデックスが使われず意図しない負荷が発生するケースがあるため、 そのようなインデックスには注意が必要です。

    COALESCE (
        (
            SELECT
                string_agg(idx.indexrelid::regclass::text, ' , ')
            FROM
                pg_index idx
                    JOIN pg_attribute attr
                        ON idx.indrelid = attr.attrelid AND idx.indkey[0] = attr.attnum
            WHERE
                idx.indrelid = base.src_rel_id
                AND idx.indpred IS NULL
                AND idx.indexprs IS NULL
                AND attr.attname = 'tenant_id'
                AND idx.indkey[1] = base.conkey[1]
        ),
        '無し'
    ) AS tenant_idが先頭で外部キー列が2番目にあるインデックス,