Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm struggling with indexes, primary keys and foreign keys... And the need of having them all.

If I have two tables, both of them have an integer as a primary key.
The first table references through a FK to the second table's primary key.

  • On both tables I have a primary key index on the ID column
  • I created a FK constraint on the table1.ref_field referencing the PK of the second table (table2.id)
  • and I added an index on table1.ref_field

Is this the best way to organize these indexes, primary and foreign keys?

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

Your design is good. If you are having a performance problem (which you can't know at design time), you should create an index on column table1.ref_field, in the same order (ASC) as the table2.id column. This will improve performance on joins between those to tables/columns. There is overhead to maintaining any index, so you want to weigh that cost against the benefit of improved performance.

PostgreSQL doesn't automatically create such indexes on foreign key columns that reference other columns, unfortunately, so you have to do it yourself.

Here is a StackOverflow question on the same topic:

Postgres and Indexes on Foreign Keys and Primary Keys

Here is a query to help determine where you might benefit from adding such an index:

Postgresql: Indexes on Foreign Keys

share|improve this answer
    
It's not always unfortunate - if you don't DELETE from the referenced table then it can save you a lot of I/O and CPU avoiding maintaining an unused index. I do think it should be created by default, just have an option to suppress its creation. –  Craig Ringer Nov 25 '13 at 5:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.