外键和主键的Postgres和索引

本文翻译自：Postgres and Indexes on Foreign Keys and Primary Keys

Does Postgres automatically put indexes on Foreign Keys and Primary Keys? Postgres会自动将索引放在外键和主键上吗？ How can I tell? 我该怎么说？ Is there a command that will return all indexes on a table? 是否有一个命令可以返回表上的所有索引？

---

#1楼

参考：https://stackoom.com/question/44UE/外键和主键的Postgres和索引

---

#2楼

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;

---

#3楼

I love how this is explained in the article Cool performance features of EclipseLink 2.5 我喜欢在EclipseLink 2.5的Cool performance features文章中解释这一点

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 . 这是任何OneToMany ， ManyToMany或ElementCollection关系，以及许多 OneToOne关系，以及涉及连接或对象比较的任何关系的大多数查询 。 This can be a major perform issue, and you should always index your foreign keys fields. 这可能是一个主要的执行问题，您应始终索引外键字段。

---

#4楼

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. 当Pg创建一个隐式索引时，它会发出一条NOTICE -level消息，你可以在psql和/或系统日志中看到它，这样你就可以看到它何时发生。 Automatically created indexes are visible in \\d output for a table, too. 自动创建的索引也在表的\\d输出中可见。

The documentation on unique indexes says: 有关唯一索引的文档说：

PostgreSQL automatically creates an index for each unique constraint and primary key constraint to enforce uniqueness. PostgreSQL自动为每个唯一约束和主键约束创建索引以强制唯一性。 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. 由于引用表中的行的DELETE或引用列的UPDATE将需要扫描引用表以查找与旧值匹配的行，因此通常最好对引用列建立索引。 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. 请注意，如果您使用主外键，例如2 FK作为M-to-N表中的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 . 您添加的每个索引都会略微降低DML操作，因此您需要为每个INSERT ， UPDATE或DELETE支付性能开销。 If the index is rarely used it may not be worth having. 如果很少使用该索引，则可能不值得拥有。

---

#5楼

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. in “psql”显示包含其所有索引的表的描述。

---

#6楼

For a PRIMARY KEY , an index will be created with the following message: 对于PRIMARY KEY ，将使用以下消息创建索引：

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. 对于一个FOREIGN KEY ，约束也不会，如果有在和借鉴编辑表中没有索引创建。

An index on referenc ing table is not required (though desired), and therefore will not be implicitly created. 上和借鉴ING表的索引不是必需的（尽管需要的话），并且因此将不被隐式地创建。