Добавить ограничение даты и времени к частичному индексу PostgreSQL с несколькими столбцами

Question

У меня есть таблица PostgreSQL под названием queries_query, который имеет много столбцов.

Два из этих столбцов, created а также user_sid, часто используются вместе в SQL-запросах моим приложением для определения количества запросов, выполненных данным пользователем за последние 30 дней. Очень, очень редко я запрашиваю эту статистику в любое время старше, чем последние 30 дней.

Вот мой вопрос:

В настоящее время я создал свой многостолбцовый индекс для этих двух столбцов, выполнив:

CREATE INDEX CONCURRENTLY some_index_name ON queries_query (user_sid, created)

Но я бы хотел еще больше ограничить индекс тем, чтобы заботиться только о тех запросах, в которых дата создания находится в течение последних 30 дней. Я пытался сделать следующее:

CREATE INDEX CONCURRENTLY some_index_name ON queries_query (user_sid, created)
WHERE created >= NOW() - '30 days'::INTERVAL`

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

Мне бы хотелось, чтобы это работало так, чтобы я мог оптимизировать свой индекс и сократить ресурсы, необходимые Postgres для выполнения этих повторных запросов.

Answer 1

Вы получаете исключение при попытке использовать now() потому что функция не IMMUTABLE (очевидно), и я цитирую руководство здесь:

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

Я вижу два способа использовать (гораздо более эффективный) частичный индекс здесь:

1. Частичный индекс с условием с использованием постоянной даты:

CREATE INDEX queries_recent_idx ON queries_query (user_sid, created)
WHERE created > '2013-01-07 00:00'::timestamp;

Если предположить, created на самом деле определяется как timestamp, Это не будет работать, чтобы обеспечить timestamp постоянная для timestamptz столбец (timestamp with time zone). Актерский состав из timestamp в timestamptz (или наоборот) зависит от текущей настройки часового пояса и не является неизменной. Используйте константу соответствующего типа данных. Понимать основы временных отметок с / без часового пояса:

• Полное игнорирование часовых поясов в Rails и PostgreSQL

Удалите и воссоздайте этот индекс в часы с небольшим трафиком, возможно, с работой cron на ежедневной или еженедельной основе (или с тем, что вам достаточно). Создание индекса довольно быстрое, особенно частичный, сравнительно небольшой. Это решение также не нужно ничего добавлять к таблице.

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

CREATE OR REPLACE FUNCTION f_index_recreate()
  RETURNS void AS
$func$
BEGIN
   DROP INDEX IF EXISTS queries_recent_idx;
   EXECUTE format('
      CREATE INDEX queries_recent_idx
      ON queries_query (user_sid, created)
      WHERE created > %L::timestamp'
    , LOCALTIMESTAMP - interval '30 days');  -- timestamp constant
--  , now() - interval '30 days');           -- alternative for timestamptz
END
$func$  LANGUAGE plpgsql;

Вызов:

SELECT f_index_recreate();

now() (как у вас) является эквивалентом CURRENT_TIMESTAMP и возвращается timestamptz, Приведение к timestamp с now()::timestamp или использовать LOCALTIMESTAMP вместо.

• Выберите только сегодняшние (с полуночи) временные метки

Протестировано с Postgres 9.2 - 9.4.
SQL Fiddle.

---

Если вам приходится иметь дело с одновременным доступом, используйте CREATE INDEX CONCURRENTLY, Но вы не можете заключить эту команду в функцию, потому что согласно документации:

... обычный CREATE INDEX Команда может быть выполнена внутри блока транзакции, но CREATE INDEX CONCURRENTLY не могу.

Итак, с двумя отдельными транзакциями:

CREATE INDEX CONCURRENTLY queries_recent_idx2 ON queries_query (user_sid, created)
WHERE  created > '2013-01-07 00:00'::timestamp;  -- your new condition

Затем:

DROP INDEX CONCURRENTLY IF EXISTS queries_recent_idx;

При желании переименуйте в старое имя:

ALTER INDEX queries_recent_idx2 RENAME TO queries_recent_idx;

2. Частичный индекс с условием на "заархивированный" тег

Добавить archived отметьте к своей таблице:

ALTER queries_query ADD COLUMN archived boolean NOT NULL DEFAULT FALSE;

UPDATE столбец с интервалом по вашему выбору, чтобы "удалить" старые строки и создать индекс, как:

CREATE INDEX some_index_name ON queries_query (user_sid, created)
WHERE NOT archived;

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

Вам не нужно удалять и воссоздавать индекс, но UPDATE на столе может быть дороже, чем индекс отдыха, и стол становится немного больше.

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

Оба решения сохраняют свою полезность с течением времени, производительность медленно ухудшается, поскольку в индекс включается больше устаревших строк.