Postgres是否自动将索引放在外键和主键上?我怎么知道呢?是否有返回表上所有索引的命令?
当前回答
我很喜欢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, 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.
其他回答
我很喜欢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, 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.
PostgreSQL自动在主键和唯一约束上创建索引,但不会在外键关系的引用端创建索引。
当Pg创建一个隐式索引时,它将发出一个notice级别的消息,您可以在psql和/或系统日志中看到该消息,因此您可以看到它何时发生。自动创建的索引在表的\d输出中也是可见的。
关于唯一索引的文档说:
PostgreSQL自动为每个唯一的约束和主键约束创建索引,以加强唯一性。因此,没有必要显式地为主键列创建索引。
约束条件的文档说:
从引用表中的一行的DELETE或引用表中的UPDATE开始 所引用的列需要对引用表进行扫描 与旧值匹配的行,通常是一个好主意 引用列。因为这并不总是需要的,而且有 关于如何索引、声明外键有许多选择 约束不会自动在引用上创建索引 列。
因此,如果需要外键,就必须自己创建索引。
注意,如果使用主外键,比如在M-to-N表中使用2个FK作为PK,您将在PK上有一个索引,可能不需要创建任何额外的索引。
虽然在引用端外键列上创建索引(或包含)通常是个好主意,但这并不是必需的。您添加的每个索引都会略微降低DML操作的速度,因此在每次INSERT、UPDATE或DELETE上都要付出性能代价。如果索引很少被使用,那么它可能就不值得拥有。
下面是一个bash脚本,它使用@sergeyB的SQL生成SQL,为外键上缺失的索引创建索引。
#!/bin/bash
read -r -d '' SQL <<EOM
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_to_string(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 relname as table_name,
cols_list
FROM fk_index_check
JOIN parent_table_stats USING (fkoid)
JOIN fk_table_stats USING (fkoid)
ORDER BY issue_sort, table_mb DESC, table_name;
EOM
DB_NAME="dbname"
DB_USER="dbuser"
DB_PASSWORD="dbpass"
DB_HOSTNAME="hostname"
DB_PORT=5432
export PGPASSWORD="$DB_PASSWORD"
psql -h $DB_HOSTNAME -p $DB_PORT -U $DB_USER -d $DB_NAME -t -A -F"," -c "$SQL" | while read -r line; do
IFS=','
parts=($line)
unset IFS
tableName=${parts[0]}
colsList=${parts[1]}
indexName="${tableName}_${colsList//:/_}_index"
printf -- "\n--Index: %s\nDROP INDEX IF EXISTS %s;\n
CREATE INDEX %s\n\t\tON %s USING btree\n\t(%s);
" "$indexName" "$indexName" "$indexName" "$tableName" "$colsList"
done
对于PRIMARY KEY,索引将被创建,并带有以下消息:
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "index" for table "table"
对于一个FOREIGN KEY,如果引用的表上没有索引,约束将不会被创建。
引用表上的索引不是必需的(尽管是需要的),因此不会隐式创建。
如果你想列出程序模式中所有表的索引,所有信息都在目录中:
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
如果想深入研究(如列和排序),需要查看pg_catalog.pg_index。使用psql -E [dbname]可以方便地了解如何查询编目。
推荐文章
- NVL和Coalesce之间的Oracle差异
- 在SQL server查询中将NULL替换为0
- 在SQL中修改表的模式名
- 如何在SQL Server 2005的一条语句中更新两个表?
- 如何创建临时表与SELECT * INTO tempTable从CTE查询
- 用于查找计数为>的记录的SQL查询
- 使用PSQL命令查找主机名和端口
- “从Table1左连接Table2”和“从Table2右连接Table1”可以互换吗?
- 在SQL Server的选择语句中使用带TOP的变量,而不是动态的
- 自然连接和内部连接的区别
- MySQL现在()+1天
- 在SQL中转换月号到月名函数
- 改变一个varchar列的最大长度?
- 外键约束:何时使用ON UPDATE和ON DELETE
- 暂时关闭约束(MS SQL)