Skip to main content

PostgreSQL Indexes

It’s important that your queries stay fast for your users, this section outlines some resources to help you optimize you queries with indexes.

确保查询对用户保持高速响应至关重要,本节汇总了一些参考资料,帮助你借助索引优化查询性能。

This article was originally written by Caleb Meredith.

本文最初由 Caleb Meredith 撰写。

Advice — Foreign Key Indexes建议:为外键建立索引​

Many people don't realise that when you create a foreign key relation, PostgreSQL does NOT automatically create an index on the referencing column(s). That can mean that when you query based on that relation, which PostGraphile does a lot when traversing relationships, it can involve a full table scan which is very expensive. By default, PostGraphile will not add this "reverse lookup" field to the GraphQL schema unless there is an index on it for this reason (though you can force it with the +select behavior on the foreign key constraint).

很多人没有意识到,创建外键 relation 时,PostgreSQL 不会自动在引用列上创建索引。这意味着一旦基于该 relation 发起查询(PostGraphile 在遍历关联关系时会频繁执行此类查询),就可能触发开销极大的全表扫描。正因如此,除非引用列上建有索引,否则 PostGraphile 默认不会在 GraphQL schema 中添加对应的“反向查找”字段(不过你可以通过在外键约束上设置 +select behavior 来强制生成)。

Cameron Ellis has written a short article on finding missing indexes on foreign keys which utilises SQL similar to the following to automatically detect missing foreign key indexes:

Cameron Ellis 写过一篇短文《如何排查缺失的外键索引》,其中使用类似下面的 SQL 语句来自动检测遗漏的外键索引:

with indexed_tables as (
select
ns.nspname,
t.relname as table_name,
i.relname as index_name,
string_agg(a.attname::text, ', ' order by array_position(ix.indkey, a.attnum)) as column_names,
ix.indrelid,
-- cast int2vector to smallint[]
string_to_array(ix.indkey::text, ' ')::smallint[] as indkey
from pg_class i
join pg_index ix on i.oid = ix.indrelid
join pg_class t on ix.indrelid = t.oid
join pg_namespace ns on ns.oid = t.relnamespace
join pg_attribute a on a.attrelid = t.oid
where a.attnum = any(ix.indkey)
and t.relkind = 'r'
and nspname not in ('pg_catalog')
group by
ns.nspname,
t.relname,
i.relname,
ix.indrelid,
ix.indkey
order by
ns.nspname,
t.relname,
i.relname,
ix.indrelid,
ix.indkey
)
select
conrelid::regclass
,conname
,reltuples::bigint
from pg_constraint pgc
join pg_class on (conrelid = pg_class.oid)
where contype = 'f'
and not exists(
select 1
from indexed_tables
where indrelid = conrelid
and (
conkey = indkey
or (
array_length(indkey, 1) > array_length(conkey, 1)
and indkey::int[] @> conkey::int[]
)
)
)
order by reltuples desc;

You should consider integrating something like this into your CI tests to ensure that all your foreign keys are indexed.

建议将类似的检查集成到你的 CI 测试中,以确保所有外键都已正确建有索引。

Our pgRITA.com tool will also help you spot this kind of issue and more; it has a free usage tier with some essentials and is also included in certain sponsorship tiers.

我们的 pgRITA.com 工具也能帮你自动发现此类问题及更多隐患;它提供包含基础检查项的免费版,同时也包含在部分赞助等级权益中。