SQL HowTo: разные варианты работы с EAV

Соблазн использовать модель EAV ^[1] (Entity-Attribute-Value) при организации структуры БД весьма велик, особенно когда предметная область заранее плохо известна (или разработчик просто не хочет в нее углубляться). Это ведь так удобно - создать "универсальный" способ описания характеристик объектов, который больше не потребует доработок базы ни при появлении новых типов объектов, ни при возникновении новых атрибутов...

Однако, за любую универсальность приходится платить сложностью и производительностью запросов - так что json[b] может оказаться более эффективной заменой ^[2]. Но если уж такая модификация невозможна - давайте попробуем выжать максимум производительности из доставшегося нам 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);

Посмотрим, что у нас получится в плане ^[3]:

Сначала отбор по одной паре значений и сортировка по 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);

А в плане ^[4] теперь все получше - читаем ровно столько же, зато не пришлось тратить время на две сортировки: 301мск + 207 buffers.

GROUP BY

А можно как-то читать меньше данных? В принципе, да - ведь у нас дважды происходит чтение страниц данных по маске - сначала для одного условия, потом для другого. А избежать этого можно с помощью группировки, читая все за один раз:

SELECT
  e
FROM
  tst_eav
WHERE
  (a, v) IN ((1, 1), (2, 2))
GROUP BY
  e
HAVING
  count(*) = 2; -- присутствуют оба условия

Сэкономили "копеечку" ^[5]: 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);

И вот теперь наш план ^[6] для INTERSECT-варианта: 121мкс + 9 buffers.

А ведь чем меньше страниц данных (buffers) читается, тем меньше шансов сходить за ними на диск и потерять в скорости.

Напоминаю, что анализировать планы запросов и бороться за их производительность удобнее всего с помощью визуализаций на explain.tensor.ru ^[7].