postgresql - Disadvantages of removing unused foreign key indexes? -

postgresql - Disadvantages of removing unused foreign key indexes? -

foreign key indexes never used can see in pg_stat_user_indexes table. want remove them. there disadvantages of removing foreign keys indexes due non-usage.

db=# select * pg_stat_user_indexes relname='order_cancellationreason'; relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch -------+------------+------------+--------------------------+---------------------------------------------+----------+--------------+--------------- 29835 | 31055 | public | order_cancellationreason | order_cancellationreason_address_id | 0 | 0 | 0 29835 | 31053 | public | order_cancellationreason | order_cancellationreason_cancelled_by_id | 0 | 0 | 0 db=#\d+ order_cancellationreason ... indexes: "order_cancellationreason_cancelled_by_id" btree (cancelled_by_id) "order_cancellationreason_address_id" btree (address_id) foreign-key constraints: "cargo_id_refs_id_f4ffe34d" foreign key (cargo_id) references cargo_cargoinfo(id) deferrable deferred "order_cancellationreason_address_id_fkey" foreign key (address_id) references invoice_address(id) deferrable deferred "order_cancellationreason_cancelled_by_id_fkey" foreign key (cancelled_by_id) references auth_user(id) deferrable deferred

indexes on referring foreign key column not required referential integrity enforcement. need unique index on referred-to column that.

the downside removing index on referring column checks , enforcement can slow. if effort delete row fk target table, database must check whether value appears in referring column , either reject delete or, on delete cascade keys, delete it. either way, without index on referring column lead seqscan. little referring tables that's fine, big referring table , multiple fk checks beingness made, performance can awful.

if application doesn't ever utilize fk checks, ie they're there declare info model , peace of mind rather expectation they'll checked , enforced, removing referring column index may fine.

if app performs deletes or updates on referred-to table, i'd recommend keeping index.

i haven't verified fk enforcement checks appear in pg_stat_user_indexes. i'd recommend checking yourself; seek running deletes know fail on referred-to table, , see if index stats on referring column change.

postgresql indexing foreign-keys