Комментарии 19
Чтобы найти все заявки, связанные с конкретным пользователем, можно создать вспомогательную таблицу, связанную отношением «один ко многим»
Во-первых, как вы намерены поддерживать целостность? Триггерами? Нет, на показанном материале это нормально, но есть области, где такой оверхед будет неоправданно наклАдным. особенно если рассматриваемый запрос - нечастый/некритичный.
Во-вторых, зачем создавать ещё одну, дополнительную, таблицу, если исходную можно этой дополнительной таблицей заместить? А представление данных в формате, который предлагала исходная таблица, можно сделать несложным (извините за туфтологию) представлением.
Я, как древний программист, больше люблю читаемость. А вы предлагаете заменить неплохо читаемый OR на селект с тремя подзапросами. Под предлогом того, что у вас это сработало. Так это может и сработало, только на ВАШЕЙ базе, с вашей версией БД и с вашим тюнингом. И с вашими сценариями. Может внезапно так оказаться, что это database-specific поведение и в качестве общего совета ваш совет - так себе. То, что совет так себе с точки зрения поддержки кода - я уже не сомневаюсь.
На мой взгляд автор подсветил подводный камень, который надо учитывать при составлении запроса. Читаемость отходит на второй план, когда запрос вместо 100 мс отрабатывает минуту и знание о том, как работает OR может помочь решить эту проблему. Разумеется нет смысла жертвовать читаемостью ради выигрыша в 20-30 мс, но такого совета в статье не увидел
Всё эти OR перестают работать, когда вам нужно приджоинить справочник правил на какую-нибудь историю посещений + список юзеров, где правила позволяют много комбинаций и приходится городить такие этажи скриптов, порой даже с использованием временных таблиц.
И почему же они перестают работать?
Вот примерный вариант:
Есть таблица пользователей (id, name, current_cat_id, current_action) и посещений (id, user_id, created, cat_id, action), таблица категорий (id, ROOT_value, SUB_ROOT_value, CAT_calue, SUB_CAT_value). Также есть таблица правил для составления отчетов (id, name, cat_type, cat_id, last_cat_type, last_cat_id, action, last_action). Нужно взять правило, найти для этого правила пользователя и для этого пользователя найти последнее посещение, причем если указано ROOT, то мы используем ROOT_value и т.п. Также cat_id. last_cat_id, action, last_action может быть 0 тогда мы ищем без них.
Вот примерное решение на дипсике:
SQL код
-- Создание таблицы категорий
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
ROOT_value VARCHAR(50),
SUB_ROOT_value VARCHAR(50),
CAT_value VARCHAR(50),
SUB_CAT_value VARCHAR(50)
);
-- Создание таблицы пользователей
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
current_cat_id INTEGER REFERENCES categories(id),
current_action INTEGER
);
-- Создание таблицы посещений
CREATE TABLE visits (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id),
created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
cat_id INTEGER REFERENCES categories(id),
action INTEGER
);
-- Создание таблицы правил
CREATE TABLE rules (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
cat_type VARCHAR(20),
cat_id INTEGER,
last_cat_type VARCHAR(20),
last_cat_id INTEGER,
action INTEGER,
last_action INTEGER
);
-- Вставка тестовых данных в категории
INSERT INTO categories (id, ROOT_value, SUB_ROOT_value, CAT_value, SUB_CAT_value) VALUES
(1, 'Electronics', NULL, NULL, NULL),
(2, 'Electronics', 'Computers', NULL, NULL),
(3, 'Electronics', 'Computers', 'Laptops', NULL),
(4, 'Electronics', 'Computers', 'Laptops', 'Gaming'),
(5, 'Electronics', 'Computers', 'Laptops', 'Business'),
(6, 'Electronics', 'Phones', NULL, NULL),
(7, 'Electronics', 'Phones', 'Smartphones', NULL),
(8, 'Electronics', 'Phones', 'Smartphones', 'Android'),
(9, 'Electronics', 'Phones', 'Smartphones', 'iOS'),
(10, 'Home', NULL, NULL, NULL),
(11, 'Home', 'Kitchen', NULL, NULL),
(12, 'Home', 'Kitchen', 'Appliances', NULL),
(13, 'Home', 'Kitchen', 'Appliances', 'Refrigerators'),
(14, 'Home', 'Kitchen', 'Appliances', 'Ovens'),
(15, 'Home', 'Living Room', NULL, NULL),
(16, 'Home', 'Living Room', 'Furniture', NULL),
(17, 'Home', 'Living Room', 'Furniture', 'Sofas'),
(18, 'Home', 'Living Room', 'Furniture', 'Tables');
-- Вставка тестовых данных в пользователей
INSERT INTO users (id, name, current_cat_id, current_action) VALUES
(1, 'John Doe', 4, 1), -- Gaming Laptops, action=1
(2, 'Jane Smith', 8, 2), -- Android Phones, action=2
(3, 'Mike Johnson', 13, 1), -- Refrigerators, action=1
(4, 'Sarah Wilson', 17, 3), -- Sofas, action=3
(5, 'Tom Brown', 5, 2), -- Business Laptops, action=2
(6, 'Lisa Davis', 9, 1), -- iOS Phones, action=1
(7, 'Alex Miller', 14, 2), -- Ovens, action=2
(8, 'Emma Garcia', 18, 1); -- Tables, action=1
-- Вставка тестовых данных в посещения
INSERT INTO visits (id, user_id, created, cat_id, action) VALUES
-- Посещения для John Doe (user_id=1)
(1, 1, '2024-01-15 10:00:00', 3, 1), -- Laptops
(2, 1, '2024-01-16 14:30:00', 4, 2), -- Gaming Laptops
(3, 1, '2024-01-17 09:15:00', 2, 1), -- Computers
-- Посещения для Jane Smith (user_id=2)
(4, 2, '2024-01-14 11:20:00', 7, 2), -- Smartphones
(5, 2, '2024-01-15 16:45:00', 8, 1), -- Android Phones
(6, 2, '2024-01-16 13:10:00', 6, 2), -- Phones
-- Посещения для Mike Johnson (user_id=3)
(7, 3, '2024-01-13 08:30:00', 12, 1), -- Appliances
(8, 3, '2024-01-14 15:20:00', 13, 2), -- Refrigerators
(9, 3, '2024-01-15 11:45:00', 11, 1), -- Kitchen
-- Посещения для Sarah Wilson (user_id=4)
(10, 4, '2024-01-12 12:00:00', 16, 3), -- Furniture
(11, 4, '2024-01-13 14:15:00', 17, 1), -- Sofas
(12, 4, '2024-01-14 10:30:00', 15, 2), -- Living Room
-- Посещения для других пользователей
(13, 5, '2024-01-11 09:00:00', 5, 2), -- Business Laptops
(14, 6, '2024-01-10 13:20:00', 9, 1), -- iOS Phones
(15, 7, '2024-01-09 16:40:00', 14, 2), -- Ovens
(16, 8, '2024-01-08 08:15:00', 18, 1); -- Tables
-- Вставка тестовых правил
INSERT INTO rules (id, name, cat_type, cat_id, last_cat_type, last_cat_id, action, last_action) VALUES
-- Правило 1: Пользователи с ROOT категорией Electronics и последним посещением любой категории
(1, 'Electronics Users Any Last Visit', 'ROOT', 1, NULL, 0, 0, 0),
-- Правило 2: Пользователи с SUB_ROOT категорией Computers и последним посещением ROOT Electronics
(2, 'Computer Users Last Electronics', 'SUB_ROOT', 2, 'ROOT', 1, 0, 0),
-- Правило 3: Пользователи с CAT категорией Laptops и последним посещением SUB_ROOT Computers
(3, 'Laptop Users Last Computers', 'CAT', 3, 'SUB_ROOT', 2, 0, 0),
-- Правило 4: Пользователи с SUB_CAT категорией Gaming и последним посещением CAT Laptops
(4, 'Gaming Laptop Users Last Laptops', 'SUB_CAT', 4, 'CAT', 3, 0, 0),
-- Правило 5: Пользователи с действием=1 и последним действием=2
(5, 'Action 1 Users Last Action 2', NULL, 0, NULL, 0, 1, 2),
-- Правило 6: Пользователи с ROOT Home и последним посещением SUB_ROOT Kitchen
(6, 'Home Users Last Kitchen', 'ROOT', 10, 'SUB_ROOT', 11, 0, 0),
-- Правило 7: Пользователи с конкретной категорией 8 (Android) и последним посещением категории 7 (Smartphones)
(7, 'Android Users Last Smartphones', NULL, 8, NULL, 7, 0, 0),
-- Правило 8: Пользователи с любыми настройками (все пользователи)
(8, 'All Users Any Settings', NULL, 0, NULL, 0, 0, 0),
-- Правило 9: Пользователи с действием=2 и последним посещением ROOT Electronics
(9, 'Action 2 Users Last Electronics', NULL, 0, 'ROOT', 1, 2, 0),
-- Правило 10: Пользователи с SUB_CAT Business и последним действием=2
(10, 'Business Laptop Users Last Action 2', 'SUB_CAT', 5, NULL, 0, 0, 2);
-- Создание индексов для оптимизации
CREATE INDEX idx_users_current_cat_action ON users(current_cat_id, current_action);
CREATE INDEX idx_visits_user_created ON visits(user_id, created DESC);
CREATE INDEX idx_visits_cat_action ON visits(cat_id, action);
CREATE INDEX idx_categories_root_value ON categories(ROOT_value);
CREATE INDEX idx_categories_sub_root_value ON categories(SUB_ROOT_value);
CREATE INDEX idx_categories_cat_value ON categories(CAT_value);
CREATE INDEX idx_categories_sub_cat_value ON categories(SUB_CAT_value);
CREATE INDEX idx_rules_id ON rules(id);
-- Запрос для проверки правила (пример для правила 1)
SELECT DISTINCT ON (u.id)
u.id as user_id,
u.name,
u.current_cat_id as user_cat_id,
u.current_action as user_action,
v.id as visit_id,
v.created as visit_created,
v.cat_id as visit_cat_id,
v.action as visit_action,
r.name as rule_name
FROM rules r
CROSS JOIN users u
LEFT JOIN categories user_cat ON user_cat.id = u.current_cat_id
LEFT JOIN categories rule_cat ON rule_cat.id = r.cat_id
LEFT JOIN LATERAL (
SELECT v.*
FROM visits v
LEFT JOIN categories visit_cat ON visit_cat.id = v.cat_id
LEFT JOIN categories rule_last_cat ON rule_last_cat.id = r.last_cat_id
WHERE v.user_id = u.id
AND (r.last_action = 0 OR v.action = r.last_action)
AND (
r.last_cat_id = 0
OR (r.last_cat_type = 'ROOT' AND visit_cat.ROOT_value = rule_last_cat.ROOT_value)
OR (r.last_cat_type = 'SUB_ROOT' AND visit_cat.SUB_ROOT_value = rule_last_cat.SUB_ROOT_value)
OR (r.last_cat_type = 'CAT' AND visit_cat.CAT_value = rule_last_cat.CAT_value)
OR (r.last_cat_type = 'SUB_CAT' AND visit_cat.SUB_CAT_value = rule_last_cat.SUB_CAT_value)
OR (r.last_cat_type NOT IN ('ROOT', 'SUB_ROOT', 'CAT', 'SUB_CAT') AND v.cat_id = r.last_cat_id)
)
ORDER BY v.created DESC
LIMIT 1
) v ON true
LEFT JOIN categories visit_cat_final ON visit_cat_final.id = v.cat_id
LEFT JOIN categories rule_last_cat_final ON rule_last_cat_final.id = r.last_cat_id
WHERE 1 = 1
AND (r.action = 0 OR u.current_action = r.action)
AND (
r.cat_id = 0
OR (r.cat_type = 'ROOT' AND user_cat.ROOT_value = rule_cat.ROOT_value)
OR (r.cat_type = 'SUB_ROOT' AND user_cat.SUB_ROOT_value = rule_cat.SUB_ROOT_value)
OR (r.cat_type = 'CAT' AND user_cat.CAT_value = rule_cat.CAT_value)
OR (r.cat_type = 'SUB_CAT' AND user_cat.SUB_CAT_value = rule_cat.SUB_CAT_value)
OR (r.cat_type NOT IN ('ROOT', 'SUB_ROOT', 'CAT', 'SUB_CAT') AND u.current_cat_id = r.cat_id)
)
ORDER BY u.id, v.created DESC NULLS LAST;Скорее всего задача и код немного кривые, но в итоге при 4 типах правил + вариант "без правила" + action/без action должно было давать множитель 16 сырых данных. Т.е. таблица юзеров * таблица посещений * количество правил * 16 давало какие-то космические цифры в экспейне, которые тупо зависали =) Да, это про olap процессы.
А если добавить LIMIT 100 и сортировку по дате?
Хотя это не тот случай.
А вообще интересное решение.
Ну так оптимизатор PostgreSQL не совершенен. В MS-SQL всё проще.
Тоже хотел спросить. Название OR в SQL — это дорого. Речь про конкретно postgresql. Было бы интересно сравнить, что с MySQL, MS SQL, Oracle etc.
На аналитических запросах многие базы умудряются даже JOIN с OR нормально исполнять используя Hash join.
Запросы вроде
FROM a JOIN b ON a.p = b.q OR a.r = b.s.
А можно больше подробностей?
Например, в Spark не могу представить такого...
Google BigQuery скажем. Смотря на execution plan, они дублируют строчки слева и справа, хеширует так что каждая строка из a повторена дважды - один раз с hash(a.p), другой раз с hash(a.r), аналогично с b. Потом join по хешам, он может выдавать лишние значения, потом дополнительная проверка полного условия и устранение дубликатов. Для не распределенной базы данных наверное можно и проще сделать, а для распределенной, где данных могут быть терабайты, и главное избежать полного cross join - выглядит разумно.
Кстати, да. Некоторые СУБД сами понимают, что можно сделать UNION или сперва скан по одному столбцу, а потом фильтр. А тут под конкретный запрос менять схему хранения: выглядит ужасными костылями
MS SQL без columnstore индекса просто умирает на подобных запросах несколько позже, когда появляются фильтры по датам или иным аналитикам не по равенству, а по диапазону.
Автор - не оригинал. Висит шильдик - "перевод". Так что претензий ноль. Но приятно, что некоторые молодые и увлечённые спецы вот так наткнуться на эту статью и пойдут копать - где там индексы, зачем они вообще. Может, задумаются, как это физически организовано. Потом кому-то достанется эксвакатор, и он ещё глубже копнет ) а большинству будет пофиг, т.к. условие с OR простое и понятное. Если этот запрос выполняется один раз в неделю, то вообще пофиг,что он 100 мс длится. Если же 10000 rps, тогда другое дело )
Вы пишете про производительность, но в статье ни одного плана запроса. Собственно это ставит под сомнение ваши выводы.
Но стоит добавить, например, фильтр по дате в основной таблице, как оба приема превращаются в тыкву.
А вот columnstore index в MS SQL подобные проблемы решает. На PostgreSQL в подобных случаях приходится выкручиваться с помощью ClickHouse, храня там закрытые периоды.
В mysql c or такая же ситуация
вместо дополнительной таблицы может быть можно воспользоваться gin индексом
CREATE INDEX idx_application_users ON application USING GIN((array[submitter_id,reviewer_id]));
Ну и сам запрос для поиска
select count(*)
from application
where array[:user_id] && array[submitter_id,reviewer_id] Теперь для поиска будет использовать индекс, индекс поддерживается автоматически встроенными средствами.
Вот пример плана для такого запроса
Bitmap Heap Scan on ... (cost=12.05..33.87 rows=6 width=892) (actual time=0.207..0.245 rows=3 loops=1)
Recheck Cond: ('{360793003}'::numeric[] && ARRAY[idcreator, ideditor])
Heap Blocks: exact=2
-> Bitmap Index Scan on ... (cost=0.00..12.05 rows=6 width=0) (actual time=0.130..0.131 rows=3 loops=1)
Index Cond: (ARRAY[idcreator, ideditor] && '{360793003}'::numeric[])
Planning Time: 0.624 ms
Execution Time: 0.352 msПрименять или нет - все зависит от бизнес задачи, так как запись тоже похожа на заклинание (хоть и попроще). ORM - врятли готовы к такому трюку.
Эвристика: OR в SQL — это дорого