WraithWireless
WraithWireless

Reputation: 674

Postgresql and primary key, foreign key indexing

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

Upvotes: 0

Views: 136

Answers (1)

frlan
frlan

Reputation: 7270

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.

Upvotes: 2

Related Questions