[james4k]

> Its of note that Postgres does not automatically create an index when creating the primary key, it is a separate step which you must do if not using an ORM. (http://postgresguide.com/performance/indexes.html#primary-ke...)

This directly contradicts with what the PostgreSQL documentation says.

> Adding a primary key will automatically create a unique btree index on the column or group of columns used in the primary key. (http://www.postgresql.org/docs/9.1/interactive/ddl-constrain...)

Is there a fundamental difference to the default pkey index versus an explicitly created index?

[joevandyk]

perhaps he meant a foreign key, not primary key.

postgresql doesn't automatically create indexes for foreign keys.

> Is there a fundamental difference to the default pkey index versus an explicitly created index?

i don't know of any in practice.

[craigkerstiens]

You're correct it was intended to be foreign keys, correctly updated now.

I believe the difference would be on the constraint. A primary key is intended to be unique, where as an index does not alway carry that constraint.

[jeltz]

A primary key is the same thing as a unique index (any index which enforces unique entries) + a not null constraint.

It might make a difference in the heuristic for finding functional dependencies for group by though. This feature was introduced in 9.1 and allows one to not always have to list all columns in the GROUP BY clause.