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

РЕДАКТИРОВАТЬ : итак, я написал запрос ниже и подумал ... «Постойте, Postgresql требует, чтобы цели внешнего ключа имели уникальные индексы». Думаю, я неправильно понял, что вы имели в виду? Вы можете использовать приведенный ниже запрос, чтобы проверить, что источник ваших внешних ключей имеет индексы, заменив «conrelid» на «confrelid» и «conkey» на «confkey» (да, да, в запросе нет псевдонимов ...)

Что ж, я думаю, что должна быть возможность просматривать системные каталоги ... Как обычно, лучший справочник по системным каталогам - это использовать psql и выполнить "\ 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 - это номера столбцов во внешней таблице, поскольку они не равны NULL только для внешних ключей. Кроме того, мне потребовалось время, чтобы понять, что это 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, ' ')

Хорошо, это чудовище распечатывает команды кандидатов-индексов и пытается сопоставить их с существующими индексами. Таким образом, вы можете просто добавить в конце «where indexrelid is null», чтобы получить команды для создания индексов, которые кажутся несуществующими.

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

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

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