Различия

Показаны различия между двумя версиями страницы.

--- it:postgresql:foreign_keys [2024/10/04 07:23] – создано ura2404
+++ it:postgresql:foreign_keys [2024/10/04 07:26] (текущий) – ura2404
@@ Строка 2: / Строка 2: @@
 [[https://www.cybertec-postgresql.com/en/index-your-foreign-key/ | Foreign Key Indexing and Performance in PostgreSQL]] {{it:postgresql:foreign_keys:foreign_key_indexing_and_performance_in_postgresql.mht|mht}}
+Postgres не строит индекс по src полю
+__**Поиск недостающих  индексов, скрипт вернёт список всех foreign key, для которых нет индекса src поля**__
+<code sql>
+SELECT c.conrelid::regclass AS "table",
+       /* list of key column names in order */
+       string_agg(a.attname, ',' ORDER BY x.n) AS columns,
+       pg_catalog.pg_size_pretty(
+          pg_catalog.pg_relation_size(c.conrelid)
+       ) AS size,
+       c.conname AS constraint,
+       c.confrelid::regclass AS referenced_table
+FROM pg_catalog.pg_constraint c
+   /* enumerated key column numbers per foreign key */
+   CROSS JOIN LATERAL
+      unnest(c.conkey) WITH ORDINALITY AS x(attnum, n)
+   /* name for each key column */
+   JOIN pg_catalog.pg_attribute a
+      ON a.attnum = x.attnum
+         AND a.attrelid = c.conrelid
+WHERE NOT EXISTS
+        /* is there a matching index for the constraint? */
+        (SELECT 1 FROM pg_catalog.pg_index i
+         WHERE i.indrelid = c.conrelid
+           /* it must not be a partial index */
+           AND i.indpred IS NULL
+           /* the first index columns must be the same as the
+              key columns, but order doesn't matter */
+           AND (i.indkey::smallint[])[0:cardinality(c.conkey)-1]
+               OPERATOR(pg_catalog.@>) c.conkey)
+  AND c.contype = 'f'
+GROUP BY c.conrelid, c.conname, c.confrelid
+ORDER BY pg_catalog.pg_relation_size(c.conrelid) DESC;
+</code>