PostgreSQLだと外部参照キーにデフォルトでインデックスが貼られていない

- https://qiita.com/ingtk/items/43c7b7a2ae5cbfc7ae6a
- https://zenn.dev/onozaty/articles/postgresql-fk-index-ckeck
- https://zenn.dev/awonosuke/articles/96b3d580860e4d
- https://www.xmisao.com/2020/03/29/difference-between-foreign-key-constraint-in-mysql-and-postgresql.html

Transcript

1. PostgreSQLだと外部参照キーにデフォルトでインデ ックスが貼られていない yamatai12 1
2. 自己紹介 yamatai12（Webエンジニア） SNS X(taiyama1212) Qiita(yamatai12) Zenn(yamatai12) 趣味は筋トレだったが、椎間板ヘルニアになったのでお休み プロフィールです、よろしくお願いします 2
3. 今日のまとめ PostgreSQLでは基本的に外部参照キーにはインデックスを貼る方が良い 3
4. 背景 インデックスについて学んでいた。 以下を見た時にMySQLではデフォルトで外部キーにインデックスが作成されていた認 識だったので、PostgreSQLはどうなのか調べた 外部キーとなるカラムにインデックスが作成されていると、結合操作の性能が向 上します。 4
5. PostgreSQLの外部参照キーはインデックスがデフォ ルトで貼られていない 被参照テーブルからの行のDELETEや被参照行のUPDATEは、古い値と一致する行 に対して参照テーブルのスキャンが必要となるので、参照行にもインデックスを 付けるのは大抵は良い考えです。 これは常に必要という訳ではなく、また、イン デックスの方法には多くの選択肢がありますので、外部キー制約の宣言では参照 列のインデックスが自動的に作られるということはありません。 https://www.postgresql.jp/document/18/html/sql-createtable.html 5
6. なぜ「被参照列の更新」でインデックスが必要か 6
7. 親テーブルの行をDELETE(UPDATE)する時、PostgreSQLは以下を 行う 親テーブルから削除すると ↓ DELETE FROM users WHERE id = 100; 参照整合性チェック: ordersテーブルでuser_id = 100の行があるか検索 参照動作の実行: ON DELETE CASCADEなら該当する子テーブルの行も削除 7
8. MySQLだとデフォルトでインデックスが貼られる MySQL では、外部キーカラムにインデックスを付ける必要があります。外部キー 制約はあるが所定のカラムのインデックスがないテーブルを作成する場合、イン デックスが作成されます。 https://dev.mysql.com/doc/refman/8.0/ja/constraint-foreign-key.html 8
9. まとめ PostgreSQLでは基本的に外部参照キーにはインデックスを貼る方が良い ただしインデックスを貼る必要が本当にあるかはその都度考えたい 検索が行われない データ量が少ない データが偏っている カラムの選択性が低い 更新頻度が高い 次回実際に検証してみたい 9
10. 参考 https://qiita.com/ingtk/items/43c7b7a2ae5cbfc7ae6a https://zenn.dev/onozaty/articles/postgresql-fk-index-ckeck https://zenn.dev/awonosuke/articles/96b3d580860e4d https://www.xmisao.com/2020/03/29/difference-between-foreign-key-constraint-in- mysql-and-postgresql.html https://www.postgresql.jp/document/current/html/ddl-constraints.html#DDL- CONSTRAINTS-FK 10