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

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.

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.