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

Соблазн использовать модель 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.