select 'Hello, World🌟!' as greeting

select 'Hello, World🌟!' as greeting

🌍 Привет мир!

Продолжим говорить про индексы, ведь понимание их работы играет важную роль в создании эффективной и производительной базы данных. Если кто-то еще не читал мои заметки про индексы, советую обратиться к ним #DATABASE. Сегодня мы обсудим, как обнаружить внешние 🗝️ ключи (foreign keys) без индексов. Информация будет ориентирована на PostgreSQL, но может быть полезна и для других СУБД.

🚀 Мотивация

При сопровождении базы данных важно задуматься о ее производительности. Вместо увеличения ресурсов стоит сначала попытаться оптимизировать работу самостоятельно, например, проверив индексы у внешних 🗝️ ключей. В отличие от primary key, где автоматически создается B-tree индекс, для foreign key индексы не создаются. Это может привести к замедлению запросов и узким местам в производительности.

⁉️ Зачем вообще индексировать столбец с внешним ключом?

Индексы на столбцах внешних ключей могут значительно ускорить запросы, так как они участвуют в JOIN операциях и фильтрах. Без индексов базе данных приходится полностью сканировать таблицы, что может замедлить время выполнения запроса.

🔍 И как же найти внешние ключи без индексов?

Можно найти с помощь SQL запроса, который получит все внешние 🗝️ ключи в базе данных и затем проверит, есть ли индексы для этих столбцов:
Cсылка на SQL скрипт

🔉 Я не буду рассказывать про каждую строку, но выделю наиболее важные моменты, которые, на мой взгляд, сформируют базовое понимание происходящего:

1️⃣ Первая часть запроса, реализуется с использованием CTE с именем fk_constraints:

💡 Информация обо всех внешних ключах в базе данных извлекается из таблицы pg_constraint с помощью фильтра c.contype = 'f', который выбирает только те строки, которые являются внешними ключами.

💡 c.conrelid и c.confrelid (это уникальные идентификаторы OID ) из таблицы pg_constraint, можно использовать для получения имен таблиц.

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

💡 Размер таблицы, в которой существует внешний ключ, можно получить, используя функцию pg_catalog.pg_size_pretty(pg_catalog.pg_relation_size(c.conrelid)) AS size.

2️⃣ Вторая часть, основной запрос, использует данные из CTE fk_constraints:

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

💡 Информация об индексах хранится в таблице pg_index, которая хранит данные о всех индексах в базе данных.

💡 Индекс соотносится с таблицей, в которой находится внешний ключ. Это означает, что идентификатор таблицы индекса (i.indrelid) должен совпадать с идентификатором таблицы внешнего ключа (fk.conrelid).

💡 Проверяется, чтобы индекс не был частичным (partial) — для этого условие i.indpred должно быть равно NULL. Это важно, потому что индекс должен охватывать все строки таблицы.

💡 В последнем условии where проверяется, чтобы столбцы, на которые ссылается внешний ключ, были включены в индекс, и при этом важна правильная последовательность этих столбцов в индексе, иначе это будет неэффективно.

💡 В итоге мы либо находим индекс, либо получаем запись (Unindexed) о том, что внешний ключ не проиндексирован.

✍️ Пример результата:

Результат покажет список таблиц с внешними ключами, где будет указано, какие из них имеют индекс, а какие нет:

Table: user_group
Constraint: fk_permission_id
Columns: permission_id
Size: 20 MB
Referenced Table: permission
Index Name: Unindexed

Table: user_group
Constraint: fk_user_id
Columns: user_id
Size: 20 MB
Referenced Table: user
Index Name: idx_user_group_user_id

‼️ Используйте данный скрипт правильно, отсутствие индекса на внешнем ключе может быть вполне обоснованным.

💬 Делитесь своим мнением в комментариях👇! Если вам понравилась статья, не забудьте поставить лайк! 👍

#DATABASE