Postgres and Indexes on Foreign Keys and Primary Keys
PostgreSQL automatically creates indexes on primary keys and unique constraints, but not on the referencing side of foreign key relationships.
When Pg creates an implicit index it will emit a NOTICE
-level message that you can see in psql
and/or the system logs, so you can see when it happens. Automatically created indexes are visible in \d
output for a table, too.
The documentation on unique indexes says:
PostgreSQL automatically creates an index for each unique constraint and primary key constraint to enforce uniqueness. Thus, it is not necessary to create an index explicitly for primary key columns.
and the documentation on constraints says:
Since a DELETE of a row from the referenced table or an UPDATE of a referenced column will require a scan of the referencing table for rows matching the old value, it is often a good idea to index the referencing columns. Because this is not always needed, and there are many choices available on how to index, declaration of a foreign key constraint does not automatically create an index on the referencing columns.
Therefore you have to create indexes on foreign-keys yourself if you want them.
Note that if you use primary-foreign-keys, like 2 FK's as a PK in a M-to-N table, you will have an index on the PK and probably don't need to create any extra indexes.
While it's usually a good idea to create an index on (or including) your referencing-side foreign key columns, it isn't required. Each index you add slows DML operations down slightly, so you pay a performance cost on every INSERT
, UPDATE
or DELETE
. If the index is rarely used it may not be worth having.
This query will list missing indexes on foreign keys, original source.
Edit: Note that it will not check small tables (less then 9 MB) and some other cases. See final WHERE
statement.
-- check for FKs where there is no matching index-- on the referencing side-- or a bad indexWITH fk_actions ( code, action ) AS ( VALUES ( 'a', 'error' ), ( 'r', 'restrict' ), ( 'c', 'cascade' ), ( 'n', 'set null' ), ( 'd', 'set default' )),fk_list AS ( SELECT pg_constraint.oid as fkoid, conrelid, confrelid as parentid, conname, relname, nspname, fk_actions_update.action as update_action, fk_actions_delete.action as delete_action, conkey as key_cols FROM pg_constraint JOIN pg_class ON conrelid = pg_class.oid JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid JOIN fk_actions AS fk_actions_update ON confupdtype = fk_actions_update.code JOIN fk_actions AS fk_actions_delete ON confdeltype = fk_actions_delete.code WHERE contype = 'f'),fk_attributes AS ( SELECT fkoid, conrelid, attname, attnum FROM fk_list JOIN pg_attribute ON conrelid = attrelid AND attnum = ANY( key_cols ) ORDER BY fkoid, attnum),fk_cols_list AS ( SELECT fkoid, array_agg(attname) as cols_list FROM fk_attributes GROUP BY fkoid),index_list AS ( SELECT indexrelid as indexid, pg_class.relname as indexname, indrelid, indkey, indpred is not null as has_predicate, pg_get_indexdef(indexrelid) as indexdef FROM pg_index JOIN pg_class ON indexrelid = pg_class.oid WHERE indisvalid),fk_index_match AS ( SELECT fk_list.*, indexid, indexname, indkey::int[] as indexatts, has_predicate, indexdef, array_length(key_cols, 1) as fk_colcount, array_length(indkey,1) as index_colcount, round(pg_relation_size(conrelid)/(1024^2)::numeric) as table_mb, cols_list FROM fk_list JOIN fk_cols_list USING (fkoid) LEFT OUTER JOIN index_list ON conrelid = indrelid AND (indkey::int2[])[0:(array_length(key_cols,1) -1)] @> key_cols),fk_perfect_match AS ( SELECT fkoid FROM fk_index_match WHERE (index_colcount - 1) <= fk_colcount AND NOT has_predicate AND indexdef LIKE '%USING btree%'),fk_index_check AS ( SELECT 'no index' as issue, *, 1 as issue_sort FROM fk_index_match WHERE indexid IS NULL UNION ALL SELECT 'questionable index' as issue, *, 2 FROM fk_index_match WHERE indexid IS NOT NULL AND fkoid NOT IN ( SELECT fkoid FROM fk_perfect_match)),parent_table_stats AS ( SELECT fkoid, tabstats.relname as parent_name, (n_tup_ins + n_tup_upd + n_tup_del + n_tup_hot_upd) as parent_writes, round(pg_relation_size(parentid)/(1024^2)::numeric) as parent_mb FROM pg_stat_user_tables AS tabstats JOIN fk_list ON relid = parentid),fk_table_stats AS ( SELECT fkoid, (n_tup_ins + n_tup_upd + n_tup_del + n_tup_hot_upd) as writes, seq_scan as table_scans FROM pg_stat_user_tables AS tabstats JOIN fk_list ON relid = conrelid)SELECT nspname as schema_name, relname as table_name, conname as fk_name, issue, table_mb, writes, table_scans, parent_name, parent_mb, parent_writes, cols_list, indexdefFROM fk_index_check JOIN parent_table_stats USING (fkoid) JOIN fk_table_stats USING (fkoid)WHERE table_mb > 9 AND ( writes > 1000 OR parent_writes > 1000 OR parent_mb > 10 )ORDER BY issue_sort, table_mb DESC, table_name, fk_name;
If you want to list the indexes of all the tables in your schema(s) from your program, all the information is on hand in the catalog:
select n.nspname as "Schema" ,t.relname as "Table" ,c.relname as "Index"from pg_catalog.pg_class c join pg_catalog.pg_namespace n on n.oid = c.relnamespace join pg_catalog.pg_index i on i.indexrelid = c.oid join pg_catalog.pg_class t on i.indrelid = t.oidwhere c.relkind = 'i' and n.nspname not in ('pg_catalog', 'pg_toast') and pg_catalog.pg_table_is_visible(c.oid)order by n.nspname ,t.relname ,c.relname
If you want to delve further (such as columns and ordering), you need to look at pg_catalog.pg_index. Using psql -E [dbname]
comes in handy for figuring out how to query the catalog.