-1

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.

1

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

|improve this answer|||||
  • Thank you for your answer! Could you please also explain why the index doesn’t provide any benefit on the first column, while it does on the second? What if i filter on the first column in a join, or delete something in table X that is referenced by id_of_table_x? Thanks! – Nick Feb 12 at 15:44
  • Because index columns are used from left to right. Any query using the single-column index will also use the two-column index with that column as the first column. – a_horse_with_no_name Feb 12 at 15:53
  • Yes, that’s it. Thanks for the additional info! For everyone who finds this, there’s also a thorough explanation here in the PostgreSQL docs that explains how this works for different index types and especially the default B-Tree indices: postgresql.org/docs/10/indexes-multicolumn.html – Nick Feb 12 at 15:58

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.