Соблазн использовать модель EAV (Entity-Attribute-Value) при организации структуры БД весьма велик, особенно когда предметная область заранее плохо известна (или разработчик просто не хочет в нее углубляться). Это ведь так удобно - создать "универсальный" способ описания характеристик объектов, который больше не потребует доработок базы ни при появлении новых типов объектов, ни при возникновении новых атрибутов...
Однако, за любую универсальность приходится платить сложностью и производительностью запросов - так что json[b] может оказаться более эффективной заменой. Но если уж такая модификация невозможна - давайте попробуем выжать максимум производительности из доставшегося нам legacy на самом простом примере.
Ограничимся работой с единственной таблицей значений:
CREATE TABLE tst_eav AS
SELECT
(random() * 1e4)::integer e -- 10k объектов
, (random() * 1e2)::integer a -- 100 характеристик
, (random() * 1e2)::integer v -- 100 вариантов значений
FROM
generate_series(1, 1e6); -- 1M записей о значениях
Попробуем найти такие объекты e
, для которых одновременно существуют записи с (a, v) = (1, 1)
и (a, v) = (2, 2)
- это типичный вариант множественного фильтра в любом интернет-магазине: "смартфоны с экраном 6" и памятью 64GB".
JOIN
Самым первым вариантом решения, пришедшим в голову разработчика уровня "я уже освоил SQL!", наверняка, будет соединение:
SELECT
e
FROM
tst_eav r1
JOIN
tst_eav r2
USING(e)
WHERE
(r1.a, r1.v) = (1, 1) AND
(r2.a, r2.v) = (2, 2);
Очевидно, для этого нам понадобится, как минимум, индекс по (a, v)
:
CREATE INDEX eav_idx1 ON tst_eav(a, v);
Посмотрим, что у нас получится в плане:
Сначала отбор по одной паре значений и сортировка по e
, потом - по второй паре и сортировка, а потом уже - слияние двух отсортированных наборов.
Этот вариант станет для нас отправной точкой: 432мкс + 207 buffers.
Неплохо для отбора из миллиона записей, но можно лучше!
INTERSECT
Ведь в предыдущем запросе мы искали вовсе не соединение, а пересечение множеств - так давайте его и попробуем использовать:
SELECT
e
FROM
tst_eav
WHERE
(a, v) = (1, 1)
INTERSECT
SELECT
e
FROM
tst_eav
WHERE
(a, v) = (2, 2);
А в плане теперь все получше - читаем ровно столько же, зато не пришлось тратить время на две сортировки: 301мск + 207 buffers.
GROUP BY
А можно как-то читать меньше данных? В принципе, да - ведь у нас дважды происходит чтение страниц данных по маске - сначала для одного условия, потом для другого. А избежать этого можно с помощью группировки, читая все за один раз:
SELECT
e
FROM
tst_eav
WHERE
(a, v) IN ((1, 1), (2, 2))
GROUP BY
e
HAVING
count(*) = 2; -- присутствуют оба условия
Сэкономили "копеечку": 296мкс + 202 buffers.
Конечно, тут мы пошли на допущение, что каждая пара (a, v)
внутри одного объекта заведомо уникальна. Потому что если это не так, запрос станет существенно сложнее:
SELECT
e
FROM
tst_eav T
WHERE
(a, v) IN ((1, 1), (2, 2))
GROUP BY
e
HAVING
array_length(array_agg(DISTINCT T), 1) = 2; -- оба уникальных условия
INCLUDE
Но всегда терзает мысль - может быть, можно сделать запрос еще быстрее?.. Оказывается, в нашем случае - можно!
Заметим, что львиная доля времени уходит на Bitmap Heap Scan
- то есть вычитку страниц таблицы ради получения значения e
, ведь его нет в нашем индексе, иначе мы могли бы обойтись Index Only Scan
.
Но ведь еще с PostgreSQL 11 есть способ добавить неключевые поля в индекс:
CREATE INDEX eav_idx2 ON tst_eav(a, v) INCLUDE(e);
И вот теперь наш план для INTERSECT
-варианта: 121мкс + 9 buffers.
А ведь чем меньше страниц данных (buffers) читается, тем меньше шансов сходить за ними на диск и потерять в скорости.
Напоминаю, что анализировать планы запросов и бороться за их производительность удобнее всего с помощью визуализаций на explain.tensor.ru.
Автор: Боровиков Кирилл