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