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-поиска с релевантностью по дате.