Tag Archives: foreign key

Automatically create index on all Foreign Keys (PostgreSQL)

Postgres does not automatically create indexes on Foreign Keys.

The above query generates the create index statement for all foreign keys with no indexes:

select 'create index ' || conname || '_idx on ' || conrelid ||
' (' || array_to_string(column_name_list, ',') || ');' as create_script
from (select distinct
conrelid,
conname,
array_agg(attname) column_name_list,
array_agg(attnum) as column_list,
nspname
from pg_attribute
join (select conrelid::regclass,
conname,
unnest(conkey) as column_index,
nspname
from (select distinct
conrelid, conname, conkey, nspname
from pg_constraint
join pg_class on pg_class.oid = pg_constraint.conrelid
join pg_namespace on pg_namespace.oid = pg_class.relnamespace
where contype = 'f' and nspname !~ '^pg_'
and nspname <> 'information_schema'
) fkey
) fkey
on fkey.conrelid = pg_attribute.attrelid
and fkey.column_index = pg_attribute.attnum
group by conrelid, conname, nspname
) candidate_index
join pg_class on pg_class.oid = candidate_index.conrelid
left join pg_index on pg_index.indrelid = conrelid
and indkey::text = array_to_string(column_list, ' ')
where
nspname = 'your_schema' and
indexrelid is null
order by relname;

Advertisements