SQL 外键和主键上的 Postgres 和索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/970562/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres and Indexes on Foreign Keys and Primary Keys
提问by mainstringargs
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 是否自动将索引放在外键和主键上？我怎么知道？是否有一个命令可以返回表上的所有索引？
回答by Philipp
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 psqland/or the system logs, so you can see when it happens. Automatically created indexes are visible in \doutput for a table, too.
当 Pg 创建一个隐式索引时，它会发出一个NOTICE您可以在psql和/或系统日志中看到的-level 消息，因此您可以看到它何时发生。自动创建的索引在\d表的输出中也是可见的。
The documentation on unique indexessays:
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 constraintssays:
和约束的文件说：
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.
请注意，如果您使用主外键，例如 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, UPDATEor DELETE. If the index is rarely used it may not be worth having.
虽然在（或包括）引用端外键列上创建索引通常是个好主意，但这不是必需的。您添加的每个索引都会稍微减慢 DML 操作的速度，因此您需要为每个INSERT,UPDATE或支付性能成本DELETE。如果索引很少使用，它可能不值得拥有。
回答by dland
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。Usingpsql -E [dbname]有助于弄清楚如何查询目录。
回答by SergeyB
This query will list missing indexes on foreign keys, original source.
此查询将列出外键上缺少索引，原始来源。
Edit: Note that it will not check small tables (less then 9 MB) and some other cases. See final WHEREstatement.
编辑：请注意，它不会检查小表（小于 9 MB）和其他一些情况。见最后WHERE声明。
-- 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;
回答by Milen A. Radev
回答by Nabi
I love how this is explained in the article Cool performance features of EclipseLink 2.5
我喜欢这篇文章中对 EclipseLink 2.5 的酷性能特性的解释
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, ManyToManyor ElementCollectionrelationship, as well as manyOneToOnerelationships, 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、 ManyToMany或ElementCollection关系，以及许多OneToOne关系，以及对涉及连接或对象比较的任何关系的大多数查询。这可能是一个主要的执行问题，您应该始终索引外键字段。
回答by Quassnoi
For a PRIMARY KEY, an index will be created with the following message:
对于 a 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 referencedtable.
对于FOREIGN KEY，约束也不会，如果有对和借鉴没有索引创建编表。
An index on referencingtable is not required (though desired), and therefore will not be implicitly created.
上和借鉴索引ING表不是必需的（尽管需要的话），并且因此将不被隐式地创建。
