41

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?

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
stUrb
  • 747
  • 2
  • 9
  • 15

1 Answers1

39

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

Community
  • 1
peterk411
  • 947
  • 1
  • 9
  • 14
  • 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 05:32
  • Craig Ringer Not only DELETE can have impact on that, if you have select from the partent to the child tables, you will have performance issues too. This documentation from Oracle explain more: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:292016138754 – Luciano Andress Martini Jun 28 '19 at 13:48