Уникальный индекс по полям, которые могут иметь null'евые значение¶

Частенько разработчики и администраторы PostgreSQL сталкиваются с вроде бы ошибкой PostgreSQL - на таблицу создан уникальный индекс, который "не работает". То есть в таблице есть строки, которые должны содержать некоторый набор полей, значение которых должно быть уникальным для всей таблице, но это условие не выполняется. Более подробный анализ дублей показывает, что все проблемные строки содержат хотя бы одно null'евое значение в одном из полей. А null != null (см. статью Сравнение с NULL). Отсюда и все "проблемы".

Пример описанной проблемы

Есть таблица test_func_index и уникальный индекс по полям name, cdate

CREATE TABLE test_func_index(
  id serial PRIMARY KEY,
  name varchar,
  cdate date
);

CREATE UNIQUE INDEX i_test_func_index_unique
  ON test_func_index
  USING btree(name, cdate);

Проверим работу уникального индекса.

Инициализация таблицы начальными значениями

denis=# INSERT INTO test_func_index(name, cdate) VALUES('name1', '2012-01-01');
insert 0 1

denis=# INSERT INTO test_func_index(name, cdate) VALUES('name1', '2012-01-02');
insert 0 1

denis=# INSERT INTO test_func_index(name, cdate) VALUES('name1', '2012-01-03');
insert 0 1

denis=# INSERT INTO test_func_index(name, cdate) VALUES('name1', null);
insert 0 1

Проверяю работу уникального индекса с ненулевыми значениями. Индекс работает

denis=# INSERT INTO test_func_index(name, cdate) VALUES('name1', '2012-01-03');
ERROR:  duplicate key value violates unique constraint "i_test_func_index_unique"
DETAIL:  key (name, cdate)=(name1, 2012-01-03) already exists.

Проверяю работу уникального индекса с NULL'евыми значениями. Индекс не работает

denis=# INSERT INTO test_func_index(name, cdate) VALUES('name1', null);
insert 0 1

Проблема уникальности присутствует.

На данный момент есть три самых популярных решений описанной проблемы:

Применение функционального индекса
Использование частичного индекса
Применение суррогатного уникального индекса

Функциональный индекс¶

Каждое поле уникального индекса оборачивается функцией COALESCE, в итоге NULL'евые значения полей в индексе заменяются специальными предопределенными значениями.

В данном примере нулевая строка заменяется пустой строкой, а нулевая дата - бесконечной датой

CREATE UNIQUE INDEX i_test_func_index_func_unique
  ON test_func_index
  USING btree(COALESCE(name, ''), COALESCE(cdate, 'infinity'));

Проверим работу индекса.

Инициализация таблицы

denis=# INSERT INTO test_func_index(name, cdate) VALUES('name1', '2012-01-01');
INSERT 0 1

denis=# INSERT INTO test_func_index(name, cdate) VALUES('name1', '2012-01-02');
INSERT 0 1

denis=# INSERT INTO test_func_index(name, cdate) VALUES('name1', null);
INSERT 0 1

Проверка работы уникального индекса для нулевых значений. Индекс работает.

denis=# INSERT INTO test_func_index(name, cdate) VALUES('name1', null);
ERROR:  duplicate key value violates unique constraint "i_test_func_index_func_unique"
DETAIL:  Key ((COALESCE(name, ''::character varying)), (COALESCE(cdate, 'infinity'::date)))=(name1, infinity) already exists.

Работа индекса в обычных запросах. Индекс не работает.

denis=# EXPLAIN SELECT * FROM test_func_index WHERE cdate < now();
                                      QUERY PLAN
--------------------------------------------------------------------------------
 Seq Scan on test_func_index  (cost=10000000000.00..10000000027.40 rows=387 widt
   Filter: (cdate < now())
(2 rows)

Работа индекса при использовании функции COALESCE, но с другими константами. Не работает

denis=# EXPLAIN SELECT * FROM test_func_index WHERE COALESCE(cdate, now()) < now();
                                      QUERY PLAN
--------------------------------------------------------------------------------
 Seq Scan on test_func_index  (cost=10000000000.00..10000000033.20 rows=387 widt
   Filter: (COALESCE((cdate)::timestamp with time zone, now()) < now())
(2 rows)

Работа индекса при использовании функции COALESCE и теми же параметрами. Работает.

denis=# EXPLAIN SELECT * FROM test_func_index WHERE COALESCE(cdate, 'infinity') < now();
                                          QUERY PLAN
--------------------------------------------------------------------------------
 Bitmap Heap Scan on test_func_index  (cost=17.05..32.85 rows=387 width=40)
   Recheck Cond: (COALESCE(cdate, 'infinity'::date) < now())
   ->  Bitmap Index Scan on i_test_func_index_func_unique  (cost=0.00..16.95 row
         Index Cond: (COALESCE(cdate, 'infinity'::date) < now())
(4 rows)

Плюсы

Уникальность достигается без дополнительных ухищрений в момент внесения данных в таблицу
Индекс работает не только как уникальный, но и годен для фильтрации данных

Минусы

Специальные значения, которыми заменяются NULL'евые, должны быть вне допустимого диапазона ненулевых значений
Фильтрация работает только при использовании COALESCE с константами, которые были определены в индексе

Частичный индекс¶

Для каждого NULL'евого поля и/или комбинации NULL'евых полей создаётся свой индекс. Это необходимо, чтобы индексы не перекрывали друг друга (меньше нагрузки при вставке данных и меньше места для хранения индекс)

CREATE UNIQUE INDEX i_test_func_index_unique_1
  ON test_func_index
  USING btree(name, cdate)
  WHERE name IS NOT NULL AND cdate IS NOT NULL;

CREATE UNIQUE INDEX i_test_func_index_unique_2
  ON test_func_index
  USING btree(name, cdate)
  WHERE name IS NOT NULL AND cdate IS NULL;

CREATE UNIQUE INDEX i_test_func_index_unique_3
  ON test_func_index
  USING btree(name, cdate)
  WHERE name IS NULL AND cdate IS NOT NULL;

CREATE UNIQUE INDEX i_test_func_index_unique_4
  ON test_func_index
  USING btree(name, cdate)
  WHERE name IS NULL AND cdate IS NULL; -- Этот индекс "хранит" одну запись

Индексы не перекрываются, но при этом поддерживают уникальность по полям name и cdate

Плюсы

Никаких искусственно созданных ограничений при работе со значениями полей.
Фильтрация работает без ограничений
Никакого оверхеда при хранении

Минусы

Сложность создания индексов. Количество индексов должно быть количество_полей в степени двойки (для трех полей - 8, для 4 - 16)

"Суррогатный" индекс¶

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

Функция склеивания полей

CREATE OR REPLACE FUNCTION uniq(_name varchar, _cdate date) RETURNS varchar AS
$$
  SELECT (COALESCE(_name, '') || '-' || COALESCE(_cdate::varchar, 'NULL'))
$$ LANGUAGE sql IMMUTABLE;

Функциональный индекс

CREATE UNIQUE INDEX i_test_func_index_unique
  ON test_func_index
  USING btree(uniq(name, cdate));

Заполнение таблицы

denis=# INSERT INTO test_func_index(name, cdate) VALUES('name1', '2012-01-01');
INSERT 0 1

denis=# INSERT INTO test_func_index(name, cdate) VALUES('name1', NULL);
INSERT 0 1

Проверка работы индекса

denis=# INSERT INTO test_func_index(name, cdate) VALUES('name1', NULL);
ERROR:  duplicate key value violates unique constraint "i_test_func_index_unique"
DETAIL:  Key (uniq(name, cdate))=(name1-NULL) already exists.

denis=# INSERT INTO test_func_index(name, cdate) VALUES('name1', '2012-01-01');
ERROR:  duplicate key value violates unique constraint "i_test_func_index_unique"
DETAIL:  Key (uniq(name, cdate))=(name1-2012-01-01) already exists.

Индекс работает как и ожидалось.

Плюсы

Поддерживает уникальность

Минусы

Требуется создавать склеивающую поля функцию
Индекс работает только на уникальность, на фильтрацию - не работает.

Резюме¶

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

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

Третий способ с суррогатным индексом можно применять только, если необходимо только поддержка уникальности, так как большего он предоставить не может.

P.S.¶

Хорошим компромиссом может стать синергия обычного уникального индекса и суррогатного индекса для нулевых полей. Для вышеописанной таблицы это будет выглядеть так.

Обычный уникальный индекс, обеспечивающий уникальность для ненулевых полей и полноценную фильтрацию

CREATE UNIQUE INDEX i_test_func_index_unique
  ON test_func_index
  USING btree(name, cdate);

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

CREATE UNIQUE INDEX i_test_func_index_unique_1
  ON test_func_index
  USING btree(uniq(name, cdate))
  WHERE name IS NULL OR cdate IS NULL;

Да, индексы в некоторых случаях дублируют друг друга, но это компенсируется их простотой (как в архитектуре, так и поддержке / использовании)

Дополнительные материалы¶

PostgreSQL Documentation: CREATE INDEX

PostgreSQL Documentation: Unique Indexes

comments powered by Disqus

Содержание

Предыдущий раздел

Следующий раздел

Уникальный индекс по полям, которые могут иметь null'евые значение¶

Функциональный индекс¶

Частичный индекс¶

"Суррогатный" индекс¶

Резюме¶

P.S.¶

Дополнительные материалы¶

Просмотр

Содержание

Предыдущий раздел

Следующий раздел

Быстрый поиск

Уникальный индекс по полям, которые могут иметь null'евые значение¶

Функциональный индекс¶

Частичный индекс¶

"Суррогатный" индекс¶

Резюме¶

P.S.¶

Дополнительные материалы¶

Просмотр