PostgreSQL：如何索引所有外键？ [英] PostgreSQL: How to index all foreign keys?
问题描述
我正在使用一个大型的PostgreSQL数据库，我试图调整它以获得更多的性能。
我们的查询和更新似乎是使用外键进行大量查找。
是一个相对简单的方法来添加索引到所有的外键，而不必通过每个表（〜140）和手动。
在研究这个，我我们会发现没有办法让Postgres自动为你做这些（像MySQL一样），但我也很高兴听到。
编辑：所以，我写下面的查询，然后想...挂起，Postgresql要求外键的目标必须有唯一的索引。所以我想我误解了你的意思？您可以使用以下查询来检查您的外键的源是否具有索引，方法是将conrelid替换为confrelid，将conkey替换为confkey（yeah，yeah，no aliases in the查询...）
好吧，我想应该可以通过系统目录...像往常一样，系统目录的最佳指南是使用psql并执行\set ECHO_HIDDEN 1然后看看它生成的有趣的\d命令的SQL。下面是用于查找表外键的SQL（\d tablename）：
- $ 1是表OID，例如'tablename':: regclass
SELECT conname，conrelid :: pg_catalog.regclass，
pg_catalog.pg_get_constraintdef（c.oid，true）as condef
FROM pg_catalog.pg_constraint c
WHERE c.confrelid = $ 1 AND c.contype ='f'ORDER BY 1;
似乎pg_constraint有列 conkey code> confkey 看起来像是可以是键定义的列号。可能 confkey 是外表中的列号，因为外键只有非空值。此外，花了一段时间，意识到这是SQL显示外键引用给定的表。
因此，此查询显示数据开始形成：
从pg_attribute中选择confrelid，conname，column_index，attname
join（select confrelid :: regclass，conname，unnest（confkey）as column_index
from pg_constraint
其中confrelid ='ticket_status':: regclass）fkey
on fkey.confrelid = pg_attribute.attrelid
和fkey.column_index = pg_attribute.attnum
我最后得到：
select pg_index.indexrelid :: regclass，'create index'|| relname || '_'||
array_to_string（column_name_list，'_'）|| '_idx on'|| confrelid ||
'（'|| array_to_string（column_name_list，'，'）||'）'
from（select distinct
confrelid，
array_agg（attname）column_name_list，
array_agg（attnum）as column_list
from pg_attribute
join（select confrelid :: regclass，
conname，
unnest（confkey）as column_index
from $ b confrelid，conname，confkey
从pg_constraint
连接pg_class pg_class.oid = pg_constraint.confrelid
连接pg_namespace在pg_namespace.oid = pg_class.relnamespace
其中nspname！〜' ^ pg_'和nspname<>'information_schema'
）fkey
）fkey
on fkey.confrelid = pg_attribute.attrelid
and fkey.column_index = pg_attribute.attnum
group by confrelid，conname
）candidate_index
join pg_class on pg_class.oid = candidate_index.confrelid
left join pg_index on pg_index.indrelid = confrelid
and indkey :: text = array_to_string（column_list，''）
OK，这个怪物打印出候选索引命令，他们与现有的指数。所以你可以简单地在末尾添加where indexrelid is null来获取命令来创建似乎不存在的索引。
此查询不处理与多列外键非常好;
后编辑：这里的查询与建议的编辑在顶部put in。所以这显示了创建不存在的索引的命令，这些列是外键（而不是其目标）的源。 选择pg_index.indexrelid :: regclass，'create index'|| relname || '_'||
array_to_string（column_name_list，'_'）|| '_idx on'|| conrelid ||
'（'|| array_to_string（column_name_list，'，'）||'）'
from（select distinct
conrelid，
array_agg（attname）column_name_list，
array_agg（attnum）as column_list
from pg_attribute
join（select conrelid :: regclass，
conname，
unnest（conkey）as column_index
from $ b conrelid，conname，conkey
from pg_constraint
连接pg_class on pg_class.oid = pg_constraint.conrelid
连接pg_namespace在pg_namespace.oid = pg_class.relnamespace
其中nspname！〜' ^ pg_'和nspname<>'information_schema'
）fkey
）fkey
on fkey.conrelid = pg_attribute.attrelid
and fkey.column_index = pg_attribute.attnum
group by conrelid，conname
）candidate_index
join pg_class on pg_class.oid = candidate_index.conrelid
left join pg_index on pg_index.indrelid = conrelid
and indkey :: text = array_to_string（column_list，''）
其中indexrelid为null
我的经验是，真的一切有用。它建议为像真正不需要索引的引用代码这样的事情创建索引。
I am working with a large PostgreSQL database, and I am trying to tune it to get more performance.
Our queries and updates seem to be doing a lot of lookups using foreign keys.
What I would like is a relatively simple way to add Indexes to all of our foreign keys without having to go through every table (~140) and doing it manually.
In researching this, I've come to find that there is no way to have Postgres do this for you automatically (like MySQL does), but I would be happy to hear otherwise there, too.
EDIT: so, I wrote the query below and then thought... "hang on, Postgresql requires that foreign key targets have to have unique indices." So I guess I misunderstood what you meant? You can use the below query to check that the source of your foreign keys have indices by substituing "conrelid" for "confrelid" and "conkey" for "confkey" (yeah, yeah, no aliases in the query...)
Well, I guess it should be possible to go through the system catalogues... As usual, the best guide to the system catalogues is to use psql and do "\set ECHO_HIDDEN 1" and then see what SQL it generates for interesting "\d" commands. Here's the SQL used to find the foreign keys for a table ("\d tablename") :
-- $1 is the table OID, e.g. 'tablename'::regclass
SELECT conname, conrelid::pg_catalog.regclass,
pg_catalog.pg_get_constraintdef(c.oid, true) as condef
FROM pg_catalog.pg_constraint c
WHERE c.confrelid = $1 AND c.contype = 'f' ORDER BY 1;
Seems that pg_constraint has columns conkey and confkey that look like they could be the column numbers that the key is defined across. Probably confkey is the column numbers in the foreign table since it's only non-null for foreign keys. Also, took me a while to realise this is the SQL to show foreign keys referencing the given table. Which is what we want anyway.
So something this query shows the data beginning to take shape:
select confrelid, conname, column_index, attname
from pg_attribute
join (select confrelid::regclass, conname, unnest(confkey) as column_index
from pg_constraint
where confrelid = 'ticket_status'::regclass) fkey
on fkey.confrelid = pg_attribute.attrelid
and fkey.column_index = pg_attribute.attnum
I'm going to be using 8.4 features like unnest... you might be able to get along without.
I ended up with:
select pg_index.indexrelid::regclass, 'create index ' || relname || '_' ||
array_to_string(column_name_list, '_') || '_idx on ' || confrelid ||
' (' || array_to_string(column_name_list, ',') || ')'
from (select distinct
confrelid,
array_agg(attname) column_name_list,
array_agg(attnum) as column_list
from pg_attribute
join (select confrelid::regclass,
conname,
unnest(confkey) as column_index
from (select distinct
confrelid, conname, confkey
from pg_constraint
join pg_class on pg_class.oid = pg_constraint.confrelid
join pg_namespace on pg_namespace.oid = pg_class.relnamespace
where nspname !~ '^pg_' and nspname <> 'information_schema'
) fkey
) fkey
on fkey.confrelid = pg_attribute.attrelid
and fkey.column_index = pg_attribute.attnum
group by confrelid, conname
) candidate_index
join pg_class on pg_class.oid = candidate_index.confrelid
left join pg_index on pg_index.indrelid = confrelid
and indkey::text = array_to_string(column_list, ' ')
OK, this monstrosity prints out the candidate index commands and tries to match them up with existing indices. So you can simply add "where indexrelid is null" on the end to get the commands to create indices that don't seem to exist.
This query doesn't deal with multi-column foreign keys very well; but imho if you're using those, you deserve trouble.
LATER EDIT: here's the query with the proposed edits up at the top put in. So this shows the commands to create indices that don't exist, on columns that are the source of a foreign key (not its target).
select pg_index.indexrelid::regclass, 'create index ' || relname || '_' ||
array_to_string(column_name_list, '_') || '_idx on ' || conrelid ||
' (' || array_to_string(column_name_list, ',') || ')'
from (select distinct
conrelid,
array_agg(attname) column_name_list,
array_agg(attnum) as column_list
from pg_attribute
join (select conrelid::regclass,
conname,
unnest(conkey) as column_index
from (select distinct
conrelid, conname, conkey
from pg_constraint
join pg_class on pg_class.oid = pg_constraint.conrelid
join pg_namespace on pg_namespace.oid = pg_class.relnamespace
where nspname !~ '^pg_' and nspname <> 'information_schema'
) fkey
) fkey
on fkey.conrelid = pg_attribute.attrelid
and fkey.column_index = pg_attribute.attnum
group by conrelid, conname
) candidate_index
join pg_class on pg_class.oid = candidate_index.conrelid
left join pg_index on pg_index.indrelid = conrelid
and indkey::text = array_to_string(column_list, ' ')
where indexrelid is null
My experience is that this isn't really all that useful. It suggests creating indices for things like reference codes that really don't need to be indexed.
这篇关于PostgreSQL：如何索引所有外键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
