SQL HowTo: один индекс на два диапазона

В прошлой статье я показал, как условие с парой однотипных неравенств, плохо поддающееся индексации с помощью btree, можно переделать на эффективно gist-индексируемое в PostgreSQL условие относительно диапазонных типов, а наш сервис анализа планов запросов explain.tensor.ru подскажет, как именно это сделать.

Но что делать, если неравенств у нас не два, а целых четыре, да еще и с разными типами участвующих полей? Например, для целей бизнеса это может быть задачей вроде "найди мне все продажи за декабрь на сумму 10-20K", что на SQL будет выглядеть примерно так:

dt >= '2023-12-01'::date AND dt <= '2023-12-31'::date AND

sum >= 10000::numeric AND sum <= 20000::numeric

Сформируем тестовые данные:

CREATE TABLE sales AS
  SELECT
    id
  , now()::date - (random() * 1000)::integer dt
  , (random() * 1e6)::numeric(32,2) sum
  FROM
    generate_series(1, 1e6) id;

Сначала попробуем наивный подход с индексом по паре полей под условием:

CREATE INDEX ON sales(dt, sum);

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, SUMMARY OFF)
SELECT
  *
FROM
  sales
WHERE
  dt >= '2023-12-01' AND dt <= '2023-12-31' AND
  sum >= 10000::numeric AND sum <= 20000::numeric;

Кажется, что все выглядит более чем пристойно - прочитали 461 страницу данных (что важно, если они не находятся в оперативном кэше, и приходится медленно и грустно читать эти 3.6MB с диска) и заняло это 5.3мс:

Подключаем "геометрию"

Но давайте взглянем на нашу задачу чуть под иным углом - геометрическим:

Если мы расположим столбцы dt и sum по разным координатным осям, то искомые записи будут представлены точками с координатами значений этих столбцов, а пересечение условий интервалов их значений даст нам прямоугольник, углами которого будут пары минимальных/максимальных значений искомых интервалов.

То есть задача сводится к нахождению точек, находящихся внутри прямоугольника - а это задача как раз для GiST-индекса! Единственный нюанс - тип date нам придется привести к числовому значению с помощью функции extract:

-- индексируем "точки"
CREATE INDEX ON sales USING gist(point(extract(EPOCH FROM dt), sum));

Перепишем запрос, используя оператор включения (<@):

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, SUMMARY OFF)
SELECT
  *
FROM
  sales
WHERE
  point(extract(EPOCH FROM dt), sum) <@ box( -- "точка" принадлежит прямоугольнику
    point(extract(EPOCH FROM '2023-12-01'::date), 10000) -- min-угол
  , point(extract(EPOCH FROM '2023-12-31'::date), 20000) -- max-угол
  );

В результирующем плане изменились лишь условия и используемый индекс, но читать теперь нам пришлось на 10% меньше страниц, а время выполнения запроса сократилось в 8 раз:

Здесь преимущество GiST-индекса заключается в возможности использования в запросе с помощью модуля btree_gist дополнительных скалярных "координат" - например, идентификатора магазина. Насколько это бывает полезно, я подробно разбирал в статье "PostgreSQL Antipatterns: работаем с отрезками в «кровавом энтерпрайзе»".

Но если такой задачи нет, мы можем воспользоваться более "геометрически-заточенным" индексом SP-GiST:

CREATE INDEX sales_point_spgist
  ON sales USING spgist(
    point(extract(EPOCH FROM dt), sum)
  );

Это позволяет уменьшить объем чтений и ровно тот же запрос ускорить еще в 1.5 раза примерно:

Так что если для вас разница между 5.30мс и 0.37мс вдруг окажется значима при выполнении запроса и стоит небольшого его усложнения - вспомните про возможности gist/spgist.

А еще "геометрический" поиск можно использовать для нетривиального решения задач типа префиксного FTS-поиска с релевантностью по дате.