Postgresql - Do Foreign Key Constraints Automatically Create Indexes?

Question

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?

• user1822: Please show us the full create table script
• Erwin Brandstetter: Also, you mention "Postgres 9.3" as well as "psql", but it's unclear what you are actually working with.
• Erwin Brandstetter: Tagged it [pgadmin] according to information in your answer.

Answer 1

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.

Answer 2

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 ...