0

On https://stackoverflow.com/questions/10356484/how-to-add-on-delete-cascade-constraints#= a user, kgrittn, commented saying that

But I notice that you have not created indexes on referencing columns... Deletes on the referenced table will take a long time without those, if you get many rows in those tables. Some databases automatically create an index on the referencing column(s); PostgreSQL leaves that up to you, since there are some cases where it isn't worthwhile.

I'm having difficulty understanding this completely. Is he saying that primary keys are not created automatically with an index or is he saying that foreign keys should be indexed (in particular cases that is). I've looked at the PostgreSQL documentation and it appears from there that an index is created for primary keys automatically. Is there a command I can use to list all indexes?

Thanks

Community
  • 1
  • 1
WraithWireless
  • 634
  • 9
  • 21
  • The comment is about the *refering* field(s) from the refering table. The *target* table and field(s) must at least have an unique constraint. – wildplasser May 20 '15 at 10:56

1 Answers1

2

A primary key is behind the scenes a special kind of a unique index. The quote referencing, that it might be a good idea to create an index also on columns, where the primary key is used as an foreign key.

frlan
  • 6,950
  • 3
  • 31
  • 72