Postgres and Indexes on Foreign Keys and Primary Keys

Question

Does Postgres automatically put indexes on Foreign Keys and Primary Keys? How can I tell? Is there a command that will return all indexes on a table?

Answer 1

It does automatically create indexes on primary keys. It will tell you (in the console) if it created such an implicit index. Indexes are also automatically created for unique-constraints.

The doc says: PostgreSQL automatically creates an index for each unique constraint and primary key constraint to enforce uniqueness. Thus, it is not necessary to create an index explicitly for primary key columns.

Therefore you have to create indexes on foreign-keys yourself. However, if you use primary-foreign-keys, like 2 FK's as a PK in a M-to-N table, you will have an index on the PK and might not need an extra index.

Answer 2

Yes - for primary keys, no - for foreign keys (more in the docs).

\d <table_name>

in "psql" shows a description of a table including all its indexes.

Answer 3

For a PRIMARY KEY, an index will be created with the following message:

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "index" for table "table"

For a FOREIGN KEY, the constraint will not be created if there is no index on the referenc**ed** table.

An index on referenc**ing** table is not required (though desired), and therefore will not be implicitly created.

Answer 4

If you want to list the indexes of all the tables in your schema(s) from your program, all the information is on hand in the catalog:

select
     n.nspname  as "Schema"
    ,t.relname  as "Table"
    ,c.relname  as "Index"
from
          pg_catalog.pg_class c
     join pg_catalog.pg_namespace n on n.oid        = c.relnamespace
     join pg_catalog.pg_index i     on i.indexrelid = c.oid
     join pg_catalog.pg_class t     on i.indrelid   = t.oid
where
        c.relkind = 'i'
    and n.nspname not in ('pg_catalog', 'pg_toast')
    and pg_catalog.pg_table_is_visible(c.oid)
order by
     n.nspname
    ,t.relname
    ,c.relname

If you want to delve further (such as columns and ordering), you need to look at pg_catalog.pg_index. Using psql -E [dbname] comes in handy for figuring out how to query the catalog.