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

В первой статье цикла рассмотрена проверка корректности выбора индекса для полей, содержащих массив значений.

Список статей

Статический анализ структуры базы данных (часть 1)
Статический анализ структуры базы данных (часть 2)
Статический анализ структуры базы данных (часть 3)
Статический анализ структуры базы данных в проекте GitLab

Индексы

В PostgreSQL реализована поддержка нескольких типов типов индексов [1, 2]. При выборе типа и структуры индекса необходимо учитывать тип данных, распределение значений, сценарии использования (какие операции сравнения будут использоваться, к каким колонкам чаще будут применяться условия фильтрации).

Основные задачи, решение которых возлагается на индексы:

поддержка работы ограничений (CONSTRAINT)
ускорение поиска данных

Затраты на поддержку созданных индексов со стороны СУБД это хранение, обновление при изменении данных, а также учет индексов при подготовке плана выполнения запроса. Дополнительно отметим время восстановления из резервной копии, построение индексов может занимать значительное время.

Выявление "лишних" индексов задача сложная и, как правило, требует для оценки "полезности" индекса актуальной статистики из PROD окружения и понимания сценариев использования данных. Статический анализ позволяет выявить часть явных проблем и подобрать индексы-кандидаты для более полной проверки.

Проблемы у индекса

У индекса есть несколько свойств, отражающих его состояние [3]

indisvalid - будет ли индекс применяться в запросах
indisready - будет ли индекс обновляться при изменении данных

Если индекс задействован в поддержке работы ограничения, например, уникальности значения, то проблемы индекса отразятся на работе соответствующего ограничения.

Задача: найти индексы, у которых есть признаки проблем при использовании.

Скрытый текст

SELECT
    c.relname,  -- наименование отношения
    ic.relname, -- наименование индекса
    i.indisvalid,
    i.indisready,
    (SELECT string_agg(format('%I', c.conname), ',') FROM pg_catalog.pg_constraint AS c WHERE c.conindid = ic.oid) -- в каких ограничениях задействован
FROM pg_catalog.pg_index AS i
	INNER JOIN pg_catalog.pg_class AS ic ON i.indexrelid = ic.oid
    INNER JOIN pg_catalog.pg_class AS c ON i.indrelid = c.oid
WHERE
	NOT i.indisvalid OR NOT i.indisready
ORDER BY 1, 2

Похожие индексы

Полностью совпадающие индексы встречаются редко. В практических задачах поиска похожих по структуре индексов целесообразно допустить некоторые упрощения:

игнорировать ASC/DESC сортировку значений (не порядок полей в составном индексе)
игнорировать NULLS LAST / NULLS FIRST

Как правило, индексы отличающиеся только этими свойствами при ручной проверке признаются дубликатами.

Скрытый текст

DROP TABLE IF EXISTS public.i1001_1 CASCADE;
CREATE TABLE public.i1001_1
(
    id    integer GENERATED ALWAYS AS IDENTITY NOT NULL,
    value integer NOT NULL,
    CONSTRAINT i1001_1_pk PRIMARY KEY (id),
    CONSTRAINT i1001_1_unique UNIQUE (value)
);

CREATE UNIQUE INDEX i_id_unique ON public.i1001_1 USING btree (id);
CREATE UNIQUE INDEX i_id_unique_desc ON public.i1001_1 USING btree (id DESC);
CREATE INDEX i_id ON public.i1001_1 USING btree (id);
CREATE INDEX i_id_partial ON public.i1001_1 USING btree (id) WHERE (id > 0);
CREATE UNIQUE INDEX i_id_unique_to_lower_text ON public.i1001_1 USING btree (lower(id::text));

CREATE UNIQUE INDEX i_value_unique ON public.i1001_1 USING btree (value);
CREATE UNIQUE INDEX i_value_unique_desc ON public.i1001_1 USING btree (value DESC);
CREATE INDEX i_value ON public.i1001_1 USING btree (value);


-- unique and regular index with identical columns
CREATE UNIQUE INDEX i_id_value_unique ON public.i1001_1 USING btree (id, value);
CREATE INDEX i_id_value ON public.i1001_1 USING btree (id, value);

-- with include
CREATE UNIQUE INDEX i_id_unique_include_value ON public.i1001_1 USING btree (id) INCLUDE (value);
CREATE INDEX i_id_include_value ON public.i1001_1 USING btree (id) INCLUDE (value);

Задача: поиск похожих индексов (очень похожих, минимальные упрощения).

Скрытый текст

WITH 
idx AS (
SELECT
    c.relname as table_name,  -- наименование отношения
    ic.relname as index_name, -- наименование индекса
    ic.oid,
    i.indisunique,
    i.indrelid,
    pg_get_indexdef(ic.oid) AS object_definition,
    regexp_replace( -- ' DESC,'
    regexp_replace( -- ' DESC\)'
    regexp_replace( -- ' NULLS LAST,'
    regexp_replace( -- ' NULLS LAST\)'
    regexp_replace( -- ' NULLS FIRST,'
    regexp_replace( -- ' NULLS FIRST\)'
    regexp_replace( -- ' INDEX .* ON '
    		pg_get_indexdef(ic.oid), ' INDEX .* ON ', ' INDEX ON '),
    		' NULLS FIRST\)', ')'),
    		' NULLS FIRST,', ','),
    		' NULLS LAST\)', ')'),
    		' NULLS LAST,', ','),
    		' DESC\)', ')'),
    		' DESC,', ',')
    	 AS simplified_object_definition,
    (SELECT string_agg(format('%I', c.conname), ',') FROM pg_catalog.pg_constraint AS c WHERE c.conindid = ic.oid)
        AS used_in_constraint
FROM pg_catalog.pg_index AS i
    INNER JOIN pg_catalog.pg_class AS ic ON i.indexrelid = ic.oid
    INNER JOIN pg_catalog.pg_class AS c ON i.indrelid = c.oid    
)
SELECT  
  i1.table_name,  -- наименование отношения
  i1.index_name as i1_index_name,  -- наименование индекса i1
  i2.index_name as i2_index_name,  -- наименование индекса i2
  i1.simplified_object_definition as simplified_index_definition,
  i1.object_definition as i1_index_definition,
  i2.object_definition as i2_index_definition,
  i1.used_in_constraint as i1_used_in_constraint,
  i2.used_in_constraint as i2_used_in_constraint 
FROM idx as i1
    INNER JOIN idx AS i2 ON i1.oid < i2.oid AND i1.indrelid = i2.indrelid 
        AND i1.simplified_object_definition = i2.simplified_object_definition    
ORDER BY 1, 2

Задача: поиск похожих индексов, когда один из них уникальный, а второй - нет.

Скрытый текст

WITH 
idx AS (
SELECT
    c.relname as table_name,  -- наименование отношения
    ic.relname as index_name, -- наименование индекса
    ic.oid,
    i.indisunique,
    i.indrelid,
    pg_get_indexdef(ic.oid) AS object_definition,
    regexp_replace( -- ' DESC,'
    regexp_replace( -- ' DESC\)'
    regexp_replace( -- ' NULLS LAST,'
    regexp_replace( -- ' NULLS LAST\)'
    regexp_replace( -- ' NULLS FIRST,'
    regexp_replace( -- ' NULLS FIRST\)'
    regexp_replace( -- ' INDEX .* ON '
    		pg_get_indexdef(ic.oid), ' INDEX .* ON ', ' INDEX ON '),
    		' NULLS FIRST\)', ')'),
    		' NULLS FIRST,', ','),
    		' NULLS LAST\)', ')'),
    		' NULLS LAST,', ','),
    		' DESC\)', ')'),
    		' DESC,', ',')
    	 AS simplified_object_definition,
    (SELECT string_agg(format('%I', c.conname), ',') FROM pg_catalog.pg_constraint AS c WHERE c.conindid = ic.oid)
        AS used_in_constraint
FROM pg_catalog.pg_index AS i
    INNER JOIN pg_catalog.pg_class AS ic ON i.indexrelid = ic.oid
    INNER JOIN pg_catalog.pg_class AS c ON i.indrelid = c.oid    
)
SELECT  
  i1.table_name,  -- наименование отношения
  i1.index_name as i1_unique_index_name,  -- наименование индекса i1
  i2.index_name as i2_index_name,  -- наименование индекса i2
  i1.object_definition as i1_unique_index_definition,
  i2.object_definition as i2_index_definition,
  i1.used_in_constraint as i1_used_in_constraint,
  i2.used_in_constraint as i2_used_in_constraint 
FROM idx as i1
    INNER JOIN idx AS i2 ON i1.indrelid = i2.indrelid 
        AND i1.indisunique AND NOT i2.indisunique
        AND replace(i1.simplified_object_definition, ' UNIQUE ', ' ') = i2.simplified_object_definition        
ORDER BY 1, 2

Задача: грубый поиск похожих индексов.

Индексы могут быть уникальными, частичными, содержать дополнительные поля и другие дополнительные свойства, такие как табличные пространства и правила сортировки [1]. Отбрасывая часть этих свойств можно выполнить более грубое сравнение. Это помогает выявить кандидатов для последующей ручной проверки.

Скрытый текст

WITH 
idx AS (
SELECT
    c.relname as table_name,  -- наименование отношения
    ic.relname as index_name, -- наименование индекса
    ic.oid,
    i.indisunique,
    i.indrelid,
    pg_get_indexdef(ic.oid) AS object_definition,
    replace(        -- ' UNIQUE '
    regexp_replace( -- ' INCLUDE'
    regexp_replace( -- ' WHERE'    
    regexp_replace( -- ' DESC,'
    regexp_replace( -- ' DESC\)'
    regexp_replace( -- ' NULLS LAST,'
    regexp_replace( -- ' NULLS LAST\)'
    regexp_replace( -- ' NULLS FIRST,'
    regexp_replace( -- ' NULLS FIRST\)'
    regexp_replace( -- ' INDEX .* ON '
    		pg_get_indexdef(ic.oid), ' INDEX .* ON ', ' INDEX ON '),
    		' NULLS FIRST\)', ')'),
    		' NULLS FIRST,', ','),
    		' NULLS LAST\)', ')'),
    		' NULLS LAST,', ','),
    		' DESC\)', ')'),
    		' DESC,', ','), 
    		' WHERE .*', ''),
            ' INCLUDE .*', ''),
            ' UNIQUE ', ' ')
    	 AS simplified_object_definition,
    (SELECT string_agg(format('%I', c.conname), ',') FROM pg_catalog.pg_constraint AS c WHERE c.conindid = ic.oid)
        AS used_in_constraint
FROM pg_catalog.pg_index AS i
    INNER JOIN pg_catalog.pg_class AS ic ON i.indexrelid = ic.oid
    INNER JOIN pg_catalog.pg_class AS c ON i.indrelid = c.oid    
)
SELECT  
  i1.table_name,  -- наименование отношения
  i1.index_name as i1_index_name,  -- наименование индекса i1
  i2.index_name as i2_index_name,  -- наименование индекса i2
  i1.simplified_object_definition as simplified_index_definition,
  i1.object_definition as i1_index_definition,
  i2.object_definition as i2_index_definition,
  i1.used_in_constraint as i1_used_in_constraint,
  i2.used_in_constraint as i2_used_in_constraint 
FROM idx as i1
    INNER JOIN idx AS i2 ON i1.oid < i2.oid AND i1.indrelid = i2.indrelid 
        AND i1.simplified_object_definition = i2.simplified_object_definition    
ORDER BY 1, 2

Почему сравнение индексов в примерах реализовано через pg_get_indexdef()

Индекс имеет большое количество свойств [1]. Восстановление некоторых свойств достаточно трудоемкая задача. Например, indexprs и indpred из pg_index[3].

Функция pg_get_indexdef [4] генерирует полную команду создания индекса на основе внутреннего описания объекта в базе данных. Обработка строк значительно более наглядная операция, проще в понимании, отладке и модификации под конкретные условия.

Примеры ситуаций, когда появляются дубли или очень похожие индексы

В процессе развития проекта был создан не уникальный индекс. Позже появилось требование контроля уникальности значений. Добавляется ограничение уникальности, а старый индекс не удаляется.

На раннем этапе развития проекта в соответствии с требованиями был создан уникальный индекс. Поменялась команда или инструмент описания структуры базы данных из-за чего существующий уникальный индекс "теряется" (не учитывается как действующая реализация контроля уникальности). Для выполнения требования уникальности добавляется новое ограничение (CONSTRAINT), которое не переиспользует существующий индекс, а создает новый. Возможность использовать уже существующие индексы при создании ограничений (CONSTRAINT) существует достаточно давно.

Ссылки

[1] https://postgrespro.ru/docs CREATE INDEX

[2] https://postgrespro.ru/docs INDEXES TYPES

[3] https://postgrespro.ru/docs pg_index

[4] https://postgrespro.ru/docs FUNCTIONS-INFO-CATALOG (см. pg_get_indexdef)

Рассмотренные выше проверки можно найти в репозитории https://github.com/sdblist/db_verifier

Статический анализ структуры базы данных (часть 3)