0

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;
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.

  • 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 Oct 29 '19 at 19:22
  • 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. – gavenkoa Oct 29 '19 at 19:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.