Unindexed foreign keys
From PostgreSQL wiki
Revision as of 23:23, 7 July 2013 by Brick
Unindexed Foreign Keys
I'm not sure of the original source of this query, but it might be from Michael Fuhr in the pgsql-performance mailing lists back in 2007.
It has been modified in several ways:
- Size of referencing and referenced tables added,
- Suggestion on index to add,
- Searches only for single-column or compound indexes where the leading column is the referencing key.
Tested in PostgreSQL 9.2 (might work with earlier versions).
create or replace view unindexed_foreign_keys as select referencing_tbl, referencing_column, existing_fk_on_referencing_tbl, referenced_tbl, referenced_column, pg_size_pretty(referencing_tbl_bytes) as referencing_tbl_size, pg_size_pretty(referenced_tbl_bytes) as referenced_tbl_size, suggestion from ( select (case when n1.nspname is not null then n1.nspname else 'public' end) || '.' || c1.relname as referencing_tbl, a1.attname as referencing_column, t.conname as existing_fk_on_referencing_tbl, (case when n2.nspname is not null then n2.nspname else 'public' end) || '.' || c2.relname as referenced_tbl, a2.attname as referenced_column, pg_relation_size( ((case when n1.nspname is not null then n1.nspname else 'public' end) || '.' || c1.relname) ) as referencing_tbl_bytes, pg_relation_size( ((case when n2.nspname is not null then n2.nspname else 'public' end) || '.' || c2.relname) ) as referenced_tbl_bytes, 'Create an index on column "' || quote_ident(a1.attname) || '" in table "' || (case when n1.nspname is not null then n1.nspname else 'public' end) || '.' || c1.relname || '"' as suggestion from pg_constraint t join pg_attribute a1 on a1.attrelid = t.conrelid and a1.attnum = t.conkey join pg_class c1 on c1.oid = t.conrelid join pg_namespace n1 on n1.oid = c1.relnamespace join pg_class c2 on c2.oid = t.confrelid join pg_namespace n2 on n2.oid = c2.relnamespace join pg_attribute a2 on a2.attrelid = t.confrelid and a2.attnum = t.confkey where t.contype = 'f' and not exists (select 1 from pg_index i where i.indrelid = t.conrelid and i.indkey = t.conkey) ) as y order by referencing_tbl_bytes desc, referenced_tbl_bytes desc, referencing_tbl, referenced_tbl, referencing_column, referenced_column;