Postgres and Indexes on Foreign Keys and Primary Keys

2018-06-27 07:00:26

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?

PostgreSQL automatically creates indexes on primary keys and unique constraints, but not on the referencing side of foreign key relationships.

When Pg creates an implicit index it will emit a NOTICE -level message that you can see in psql and/or the system logs, so you can see when it happens. Automatically created indexes are visible in d output for a table, too.

The documentation on unique indexes 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.

and the documentation on constraints says:

Since a DELETE of a row from the referenced table or an UPDATE of a referenced column will require a scan of the referencing table for rows matching the old value, it is often a good idea to index the referencing columns. 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.

Therefore you have to create indexes on foreign-keys yourself if you want them.

Note that 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 probably don't need to create any extra indexes.

While it's usually a good idea to create an index on (or including) your referencing-side foreign key columns, it isn't required. Each index you add slows DML operations down slightly, so you pay a performance cost on every INSERT , UPDATE or DELETE . If the index is rarely used it may not be worth having.

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.

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.

链接地址: http://www.djcxy.com/p/76300.html

上一篇: 将大型表上的外键编入索引的最佳做法

下一篇: Postgres和索引外键和主键