PostgreSQL: как индексировать все внешние ключи?

Ответ 1

EDIT: так, я написал запрос ниже, а затем подумал... "зависайте, Postgresql требует, чтобы цели внешнего ключа имели уникальные индексы". Значит, я не понял, что вы имели в виду? Вы можете использовать приведенный ниже запрос, чтобы проверить, что источник ваших внешних ключей имеет индексы, заменяя "conrelid" для "confrelid" и "conkey" для "confkey" (да, да, никаких псевдонимов в запросе...)

Ну, я думаю, что нужно пройти через системные каталоги... Как обычно, лучшим руководством к системным каталогам является использование psql и do "\ set ECHO_HIDDEN 1", а затем посмотреть, какой SQL он генерирует для интересные "\ d" команды. Здесь SQL используется для поиска внешних ключей для таблицы ( "\ 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;

Кажется, что pg_constraint имеет столбцы conkey и confkey, которые выглядят так, как будто они могут быть номерами столбцов, которые определены ключом. Вероятно, confkey - это номера столбцов в внешней таблице, так как для внешних ключей он не равен нулю. Кроме того, мне потребовалось некоторое время, чтобы понять, что это SQL, чтобы отображать внешние ключи, ссылающиеся на данную таблицу. Это то, что мы хотим в любом случае.

Итак, этот запрос показывает, что данные начинают складываться:

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

Я собираюсь использовать 8.4 функции, такие как unnest... вы могли бы обойтись без.

Я закончил с:

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, ' ')

ОК, это чудовище распечатывает команды индекса кандидата и пытается сопоставить их с существующими индексами. Таким образом, вы можете просто добавить "где indexrelid is null" в конце, чтобы заставить команды создавать индексы, которые, похоже, не существуют.

Этот запрос не очень хорошо разбирается в многоколоночных внешних ключах; но imho, если вы используете их, вы заслуживаете неприятностей.

LATER EDIT: здесь запрос с предлагаемыми изменениями вверху вставлен. Таким образом, это показывает команды для создания индексов, которые не существуют, на столбцах, которые являются источником чужой ключ (не его цель).

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

Мой опыт в том, что на самом деле это не все, что полезно. Он предлагает создавать индексы для таких вещей, как коды ссылок, которые действительно не нужно индексировать.

Ответ 2

Информация находится внутри таблиц каталога. Но, похоже, вам не очень просто делать то, что вам нужно, особенно если уже созданы некоторые индексы (а также о многоколоночных индексах...)

Если у вас нет индексированного FK, вы можете сделать что-то быстрое и грязное, так как

 SELECT 'CREATE INDEX ' || table_name || '_' || column_name || '_idx ON '
   || table_name || '(' || column_name || ');'
from foreign_key_tables where schema = 'public';

Вы заменяете интересующую вас схему, выкладываете это в файл, редактируете, проверяете, молите и пишите psql. ОСТОРОЖНО эта процедура не обнаруживает уже существующие индексы.

Ah, foreign_key_tables - информационное представление, созданное как:

CREATE VIEW foreign_key_tables AS SELECT
    n.nspname AS schema,
    cl.relname AS table_name,
    a.attname AS column_name,
    ct.conname AS key_name,
    nf.nspname AS foreign_schema,
    clf.relname AS foreign_table_name,
    af.attname AS foreign_column_name,
    pg_get_constraintdef(ct.oid) AS create_sql
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class cl ON (a.attrelid = cl.oid AND cl.relkind =
'r')
JOIN pg_catalog.pg_namespace n ON (n.oid = cl.relnamespace)
JOIN pg_catalog.pg_constraint ct ON (a.attrelid = ct.conrelid AND
ct.confrelid != 0 AND ct.conkey[1] = a.attnum)
JOIN pg_catalog.pg_class clf ON (ct.confrelid = clf.oid AND clf.relkind
= 'r')
JOIN pg_catalog.pg_namespace nf ON (nf.oid = clf.relnamespace)
JOIN pg_catalog.pg_attribute af ON (af.attrelid = ct.confrelid AND
af.attnum = ct.confkey[1]);

Ответ 3

i создал script с этим кодом, кажется немного короче:

SELECT 'DROP INDEX IF EXISTS fk_' || conname || '_idx; CREATE INDEX fk_' || conname || '_idx ON ' 
       || relname || ' ' || 
       regexp_replace(
           regexp_replace(pg_get_constraintdef(pg_constraint.oid, true), 
           ' REFERENCES.*$','',''), 'FOREIGN KEY ','','') || ';'
FROM pg_constraint 
JOIN pg_class 
    ON (conrelid = pg_class.oid)
JOIN pg_namespace
    ON (relnamespace = pg_namespace.oid)
WHERE contype = 'f'
  AND nspname = 'public'
  --AND 'fk_' || conname || '_idx' NOT IN (SELECT indexname FROM pg_indexes)
  ;

комментарий в последней строке, если вы не хотите воссоздавать уже существующие индексы