Need for indexes on foreign keys

Question

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?

Answer 1

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