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
array_agg(attname) column_name_list,
array_agg(attnum) as column_list,
from pg_attribute
join (select conrelid::regclass,
unnest(conkey) as column_index,
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, ' ')
nspname = 'your_schema' and
indexrelid is null
order by relname;


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s