5

Per the PGSQL 9.3 docs:

Because this is not always needed, and there are many choices available on how to index, declaration of a foreign key constraint does not automatically create an index on the referencing columns.

Yet after I create such a constraint, when I run \d <table> I see that an index does in fact get created:

Indexes: "table_primary_key" PRIMARY KEY, btree (id) "fki_table_product_foreign_key" btree (product)

What could explain why this gets created? Do I still need to create another index on my product column to improve performance of queries on it?

  • 1
    Please show us the full create table script – a_horse_with_no_name Sep 6 '14 at 18:02
  • 2
    Also, you mention "Postgres 9.3" as well as "psql", but it's unclear what you are actually working with. – Erwin Brandstetter Sep 6 '14 at 21:31
  • Tagged it [pgadmin] according to information in your answer. – Erwin Brandstetter Sep 8 '14 at 20:28
4

What could explain why this gets created?

You created the index. Or the client software you are working with did it for you. Postgres certainly didn't.

Do I still need to create another index on my product column to improve performance of queries on it?

No. A second index on the same column would be a total waste. It's not even certain the first one is a win. Probable, but not certain, just like the manual states ...

4

I discovered that pgAdmin III does this by default. When you create a new foreign key constraint using the GUI, there is a checkbox "Auto FK Index" on the Definition tab. I hadn't noticed this until just now so it explains why the index was being created.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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