Postgres: Can I associate index with FK constraint?

Question

Postgres FK constraint and index are independent.

Recently I was looked for unused indexes and found trick to eliminate indexes needed for constraint enforcing by:

 WHERE NOT EXISTS (SELECT 1 
 FROM pg_constraint c 
 WHERE c.conindid = pg_stat_user_indexes.indexrelid)

We created FK constraints independently from indexes.

Is it possible to somehow associate index to FK constraint so pg_constraint.conindid will point from FK to index?

Something like:

ALTER TABLE my ALTER CONSTRAINT my_your_fk USING INDEX my_your_fk_i;

UPDATE I see something related in Postgres docs:

ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;

Answer 1

I am not 100% certain what you are asking.

Your query shows the association of foreign key constraints to indexes at the target.

There is no association between indexes on the source and foreign key constraints.

You can use this query to find all foreign keys which have no index at the source that supports fast updates and key modifications on the target table:

SELECT conrelid::regclass, conname
FROM pg_constraint AS c
WHERE c.contype = 'f'
 AND NOT EXISTS (SELECT 1 FROM pg_index AS i
 WHERE i.indrelid = c.conrelid
 AND (i.indkey::smallint[])[0:cardinality(c.conkey)-1] @> c.conkey);

The strange condition will test if the first index columns are identical to the columns in the foreign key definition.

• gavenkoa: conindid::regclass helps me to understand about source/destination of FK in your explanation. Now I see that there is no direct link between FK constraint and origin table indexes (but there is reference to PK or UNIQUE of target via conindid). Your query helps to find FKs that are not covered by supporting index (which is optional) via conrelid & conkey arrays on i.indrelid = c.conrelid. Thanks!
• gavenkoa: It make only sense for UNIQUE constraint to have link to index of source table. And there is syntax for this: ALTER TABLE my ADD CONSTRAINT my_un UNIQUE (col1, col2) USING INDEX my_un_idx; I confused FK & UNIQUE.