В прошлой статье я показал, как условие с парой однотипных неравенств, плохо поддающееся индексации с помощью 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
![Считаем чужие деньги Считаем чужие деньги](https://habrastorage.org/getpro/habr/upload_files/793/4de/3c9/7934de3c93ce80eb3da9f94ab12d6867.png)
Сформируем тестовые данные:
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мс:
![Использование btree-индекса Использование btree-индекса](https://habrastorage.org/getpro/habr/upload_files/69e/d27/137/69ed27137411a58491c43005e8c11a9a.png)
Подключаем "геометрию"
Но давайте взглянем на нашу задачу чуть под иным углом - геометрическим:
![Геометрический смысл пары интервалов Геометрический смысл пары интервалов](https://habrastorage.org/getpro/habr/upload_files/0a0/528/75e/0a052875eeb514d1db3f7016897fc59d.png)
Если мы расположим столбцы 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-индекса Использование gist-индекса](https://habrastorage.org/getpro/habr/upload_files/c6e/06d/674/c6e06d6748b09a3d41540e87889ed6cb.png)
Здесь преимущество GiST
-индекса заключается в возможности использования в запросе с помощью модуля btree_gist дополнительных скалярных "координат" - например, идентификатора магазина. Насколько это бывает полезно, я подробно разбирал в статье "PostgreSQL Antipatterns: работаем с отрезками в «кровавом энтерпрайзе»".
Но если такой задачи нет, мы можем воспользоваться более "геометрически-заточенным" индексом SP-GiST:
CREATE INDEX sales_point_spgist
ON sales USING spgist(
point(extract(EPOCH FROM dt), sum)
);
Это позволяет уменьшить объем чтений и ровно тот же запрос ускорить еще в 1.5 раза примерно:
![Использование spgist-индекса Использование spgist-индекса](https://habrastorage.org/getpro/habr/upload_files/0bc/d87/b32/0bcd87b3274c38b93265de26b09b2495.png)
Так что если для вас разница между 5.30мс и 0.37мс вдруг окажется значима при выполнении запроса и стоит небольшого его усложнения - вспомните про возможности gist/spgist
.
А еще "геометрический" поиск можно использовать для нетривиального решения задач типа префиксного FTS-поиска с релевантностью по дате.