Postgresql – usecase to create separate indices on the foreign keys of junction/associative table

foreign keyindexindex-tuningpostgresqlprimary-key

Imagine an associative/junction table junction_table_ with (pseudocode) columns like:

id_of_table_x REFERENCES TABLE X
id_of_table_y REFERENCES TABLE Y

PRIMARY KEY (id_of_table_x, id_of_table_y)

So id_of_table_x and id_of_table_y are foreign keys referencing some primary keys of other tables and are used as composite primary key.

Now I could add:

CREATE INDEX some_index ON junction_table_ (id_of_table_x);

and

CREATE INDEX some_index ON junction_table_ (id_of_table_y);

Would this have any (positive) effect?

What would be a valid use case for separate indices on the single columns?

e.g. queries that WHERE-filter on one of the two foreign keys within a join or delete operations which need to check whether the target is still referenced – do they profit from separate, additonal indices.

Best Answer

You don't need a separate index for the first column of your primary key (id_of_table_x in your example) because Postgres will happily use that index in every situation where it would use the single-column index on the first column.

If you very often delete rows from the referenced table, then adding another index on the second column helps validating the foreign key during deleting.

The index on the second column might help for joins that only use that (but not the first column).