Создайте уникальный индекс для неуникального столбца

Не уверен, что это возможно в PostgreSQL 9.3+, но я хотел бы создать уникальный индекс для неуникального столбца. Для таблицы типа:

CREATE TABLE data (
  id SERIAL
  , day DATE
  , val NUMERIC
);
CREATE INDEX data_day_val_idx ON data (day, val);

Я хотел бы иметь возможность [быстро] запрашивать только отдельные дни. Я знаю, что могу использовать data_day_val_idx для облегчения поиска отдельных элементов, но кажется, что это добавляет дополнительные накладные расходы, если количество уникальных значений существенно меньше, чем количество строк в покрытиях индекса. В моем случае примерно 1 раз в 30 дней.

Является ли мой единственный вариант создать реляционную таблицу для отслеживания только уникальных записей? Мышление:

CREATE TABLE days (
  day DATE PRIMARY KEY
);

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


postgresql sql postgresql-9.3 unique-index
person Justin    schedule 20.03.2015    source источник

Ответы (1)


arrow_upward
4
arrow_downward

Индекс может индексировать только фактические строки, а не агрегированные строки. Итак, да, что касается желаемого индекса, создание таблицы с уникальными значениями, как вы упомянули, является вашим единственным вариантом. Обеспечьте ссылочную целостность с помощью ограничения внешнего ключа от data.day до days.day. Это может быть лучшим для производительности, в зависимости от полной ситуации.

Однако, поскольку речь идет о производительности, есть альтернативное решение: вы можете использовать рекурсивный CTE для эмуляции сканирования свободного индекса:

WITH RECURSIVE cte AS (
   (  -- parentheses required
   SELECT day FROM data ORDER BY 1 LIMIT 1
   )
   UNION ALL
   SELECT (SELECT day FROM data WHERE day > c.day ORDER BY 1 LIMIT 1)
   FROM   cte  c
   WHERE  c.day IS NOT NULL  -- exit condition
   )
SELECT day FROM cte;

Круглые скобки вокруг первого SELECT необходимы из-за прикрепленных предложений ORDER BY и LIMIT. Видеть:

Для этого нужен только простой индекс на day.

Существуют различные варианты, в зависимости от ваших реальных запросов:

Подробнее в моем ответе на ваш последующий вопрос:

person Erwin Brandstetter    schedule 20.03.2015
comment
Мне нужно будет сравнить это с DISTINCT, который я уже использую, и упоминаемой таблицей, но это надежно. Опять же, ты да человек! - person Justin; 20.03.2015
comment
@Justin: Были бы заинтересованы в результатах. Может быть, вы могли бы оставить комментарий здесь позже? Или даже ответ с подробностями вашего дела... - person Erwin Brandstetter; 20.03.2015
comment
Возникли проблемы с правильной работой рекурсивного запроса... не уверен, что полностью понимаю, как он повторяется. Выполняет ли cte рекурсию при вызове FROM до или после соединения с данными? Опубликовал два других запроса в моем вопросе. - person Justin; 21.03.2015
comment
@Justin: Я предлагаю вам поместить все это в новый вопрос (и вернуть редактирование к текущему вопросу), чтобы сохранить его в чистоте. Вы всегда можете сослаться на это для справки. Добавленное условие WHERE является проблемой здесь ... - person Erwin Brandstetter; 21.03.2015
comment
Справедливо. Я думаю, что мне нужно прочитать о рекурсивном cte, прежде чем публиковать другой вопрос. Суть этого вопроса заключалась в том, чтобы убедиться, что я не упустил что-то легкое, и вы, я думаю, на данный момент указали в правильном направлении. Спасибо! - person Justin; 21.03.2015
comment
@Justin: Читать никогда не бывает неправильно. У меня уже есть идея или две для вас, если вы опубликуете случай как новый вопрос. - person Erwin Brandstetter; 21.03.2015
comment
Опубликовано stackoverflow.com/questions/29178280/ - person Justin; 21.03.2015
comment
@Justin: Нашел ошибку производительности. Рассмотрим обновление. Подробнее читайте в ответе на ваш дополнительный вопрос. - person Erwin Brandstetter; 22.03.2015