外键和主键上的Postgres和索引

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?

Postgres会自动将索引放在外键和主键上吗?我怎么能告诉?是否有一个命令将返回表上的所有索引?

6 个解决方案

#1

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

PostgreSQL自动地在主键和惟一约束上创建索引，而不是在外键关系的引用方面。

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.

当Pg创建隐式索引时，它将发出一条通知级消息，您可以在psql和/或系统日志中看到这条消息，因此您可以看到它何时发生。自动创建的索引在一个表的\d输出中也是可见的。

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.

PostgreSQL自动为每个唯一约束和主键约束创建索引，以实现惟一性。因此，不需要为主键列显式地创建索引。

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.

注意，如果您使用main -foreign-key，比如在mto - n表中使用2 FK作为PK，那么您将在PK上有一个索引，并且可能不需要创建任何额外的索引。

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.

虽然在引用端外键列(或包含)上创建索引通常是一个好主意，但它不是必需的。添加的每个索引都会稍微降低DML操作的速度，因此在每次插入、更新或删除时都要付出性能代价。如果索引很少被使用，它可能不值得拥有。

#2

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.

如果您想进一步研究(例如列和排序)，需要查看pg_catalog.pg_index。使用psql -E [dbname]可以方便地确定如何查询目录。

#3

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

Yes - for primary key, no - for foreign key(更多在文档中)。

\d <table_name>

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

在“psql”中，显示了包含所有索引的表的描述。

#4

This query will list missing indexes on foreign keys, original source.

此查询将在外键上列出丢失的索引，原始源。

-- check for FKs where there is no matching index
-- on the referencing side
-- or a bad index

WITH fk_actions ( code, action ) AS (
    VALUES ( 'a', 'error' ),
        ( 'r', 'restrict' ),
        ( 'c', 'cascade' ),
        ( 'n', 'set null' ),
        ( 'd', 'set default' )
),
fk_list AS (
    SELECT pg_constraint.oid as fkoid, conrelid, confrelid as parentid,
        conname, relname, nspname,
        fk_actions_update.action as update_action,
        fk_actions_delete.action as delete_action,
        conkey as key_cols
    FROM pg_constraint
        JOIN pg_class ON conrelid = pg_class.oid
        JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
        JOIN fk_actions AS fk_actions_update ON confupdtype = fk_actions_update.code
        JOIN fk_actions AS fk_actions_delete ON confdeltype = fk_actions_delete.code
    WHERE contype = 'f'
),
fk_attributes AS (
    SELECT fkoid, conrelid, attname, attnum
    FROM fk_list
        JOIN pg_attribute
            ON conrelid = attrelid
            AND attnum = ANY( key_cols )
    ORDER BY fkoid, attnum
),
fk_cols_list AS (
    SELECT fkoid, array_agg(attname) as cols_list
    FROM fk_attributes
    GROUP BY fkoid
),
index_list AS (
    SELECT indexrelid as indexid,
        pg_class.relname as indexname,
        indrelid,
        indkey,
        indpred is not null as has_predicate,
        pg_get_indexdef(indexrelid) as indexdef
    FROM pg_index
        JOIN pg_class ON indexrelid = pg_class.oid
    WHERE indisvalid
),
fk_index_match AS (
    SELECT fk_list.*,
        indexid,
        indexname,
        indkey::int[] as indexatts,
        has_predicate,
        indexdef,
        array_length(key_cols, 1) as fk_colcount,
        array_length(indkey,1) as index_colcount,
        round(pg_relation_size(conrelid)/(1024^2)::numeric) as table_mb,
        cols_list
    FROM fk_list
        JOIN fk_cols_list USING (fkoid)
        LEFT OUTER JOIN index_list
            ON conrelid = indrelid
            AND (indkey::int2[])[0:(array_length(key_cols,1) -1)] @> key_cols

),
fk_perfect_match AS (
    SELECT fkoid
    FROM fk_index_match
    WHERE (index_colcount - 1) <= fk_colcount
        AND NOT has_predicate
        AND indexdef LIKE '%USING btree%'
),
fk_index_check AS (
    SELECT 'no index' as issue, *, 1 as issue_sort
    FROM fk_index_match
    WHERE indexid IS NULL
    UNION ALL
    SELECT 'questionable index' as issue, *, 2
    FROM fk_index_match
    WHERE indexid IS NOT NULL
        AND fkoid NOT IN (
            SELECT fkoid
            FROM fk_perfect_match)
),
parent_table_stats AS (
    SELECT fkoid, tabstats.relname as parent_name,
        (n_tup_ins + n_tup_upd + n_tup_del + n_tup_hot_upd) as parent_writes,
        round(pg_relation_size(parentid)/(1024^2)::numeric) as parent_mb
    FROM pg_stat_user_tables AS tabstats
        JOIN fk_list
            ON relid = parentid
),
fk_table_stats AS (
    SELECT fkoid,
        (n_tup_ins + n_tup_upd + n_tup_del + n_tup_hot_upd) as writes,
        seq_scan as table_scans
    FROM pg_stat_user_tables AS tabstats
        JOIN fk_list
            ON relid = conrelid
)
SELECT nspname as schema_name,
    relname as table_name,
    conname as fk_name,
    issue,
    table_mb,
    writes,
    table_scans,
    parent_name,
    parent_mb,
    parent_writes,
    cols_list,
    indexdef
FROM fk_index_check
    JOIN parent_table_stats USING (fkoid)
    JOIN fk_table_stats USING (fkoid)
WHERE table_mb > 9
    AND ( writes > 1000
        OR parent_writes > 1000
        OR parent_mb > 10 )
ORDER BY issue_sort, table_mb DESC, table_name, fk_name;

#5

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

对于外键，如果引用的表上没有索引，则不会创建约束。

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

不需要引用表上的索引(尽管需要)，因此不会隐式创建索引。

#6

I love how this is explained in the article Cool performance features of EclipseLink 2.5

我很喜欢EclipseLink 2.5中的Cool性能特性

Indexing Foreign Keys

外键索引

The first feature is auto indexing of foreign keys. Most people incorrectly assume that databases index foreign keys by default. Well, they don't. Primary keys are auto indexed, but foreign keys are not. This means any query based on the foreign key will be doing full table scans. This is any OneToMany, ManyToMany or ElementCollection relationship, as well as many OneToOne relationships, and most queries on any relationship involving joins or object comparisons. This can be a major perform issue, and you should always index your foreign keys fields.

第一个特性是外键的自动索引。大多数人错误地认为数据库默认索引外键。好吧,他们不喜欢。主键是自动索引的，但是外键不是。这意味着基于外键的任何查询都将执行全表扫描。这是任何一个OneToMany，许多tomany或ElementCollection关系，以及许多OneToOne关系，以及对涉及连接或对象比较的任何关系的大多数查询。这可能是一个主要的执行问题，您应该始终索引您的外键字段。