[Из песочницы] PostrgreSQL: ускоряемся через intarray
Лет так 6 назад, когда слоник был только в 8.0, а я плотно сидел на MySql, часто слышал призывы сменить DB. Я помню как это было болезненно начать. Но после того, как решился, ни разу не жалел и на мускул уже вряд ли вернусь. Уж очень много тут плюсов, но пост не об этом.
Пришла задача: написать магазин, большой в перспективе. А-ля Фотос, Хотлайн. Ну и стандартная задача для таких площадок — это фильтр.
Как это делаются в обычных «движках»? Верно: параметры = фильтры. Ну все понятно, что это просто, но не всегда красиво, особенно когда нужны фильтры с диапазонами (например: диагональ 10"-11"). Да и тогда приходится думать о том, что фильтры — это независимая от параметров сущность. В моей версии фильтры «вяжутся» к параметрам. Не знаю, как это сделано на том же хотлайне (есть подозрение, что там фильтры вяжутся непосредственно на товары), но такой вариант требует много человеческого внимания. Не буду вдаваться в детали архитектуры, это тема другого поста. Но рассматривать будем именно упрощенный вариант, дабы не потеряться.
Проблема в таких фильтрах — это их построение. Просчитать количество товаров при выбранных (или не выбранных ещё) фильтрах.
Итак…
Создаем таблицу для товаров:
CREATE TABLE public.products (
id SERIAL,
title VARCHAR NOT NULL,
CONSTRAINT products_pkey PRIMARY KEY(id)
);
Таблицу фильтров:
CREATE TABLE public.filters (
id SERIAL,
title VARCHAR NOT NULL,
CONSTRAINT filters_pkey PRIMARY KEY(id)
);
Таблицу для связей между filters и products:
CREATE TABLE public.products_ref_filters (
id_product INTEGER NOT NULL,
id_filter INTEGER NOT NULL,
CONSTRAINT products_ref_filters_pkey PRIMARY KEY(id_product, id_filter),
CONSTRAINT products_ref_filters_fk FOREIGN KEY (id_product)
REFERENCES public.products(id)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT products_ref_filters_fk1 FOREIGN KEY (id_filter)
REFERENCES public.filters(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
И получается стандартный вариант. Тривиально.
Дальше…
В таблицу товаров добавляем поле filters:
ALTER TABLE public.products
ADD COLUMN filters INTEGER[] DEFAULT ARRAY[]::integer[] NOT NULL;
Как вы заметили, это массив чисел, тут будут дублироваться айдишки фильтров, да, это денормализация. Для целостности пишем процедуры и вешаем на триггера. Вставка:
CREATE OR REPLACE FUNCTION public.products_ref_filters__insert_tr ()
RETURNS trigger AS'
BEGIN
UPDATE products
SET filters = filters + NEW.id_filter --push element onto array
WHERE id = NEW.id_product;
RETURN NEW;
END;
'LANGUAGE 'plpgsql';
CREATE TRIGGER products_ref_filters__insert_tr
AFTER INSERT
ON public.products_ref_filters FOR EACH ROW
EXECUTE PROCEDURE public.products_ref_filters__insert_tr();
Удаление:
CREATE OR REPLACE FUNCTION public.products_ref_filters__delete_tr ()
RETURNS trigger AS'
BEGIN
UPDATE products
SET filters = filters - OLD.id_filter --remove entries matching right argument from array
WHERE id = OLD.id_product;
RETURN OLD;
END;
'LANGUAGE 'plpgsql';
Обновление:
CREATE OR REPLACE FUNCTION public.products_ref_filters__update_tr ()
RETURNS trigger AS'
BEGIN
UPDATE products
SET filters = filters - OLD.id_filter
WHERE id = OLD.id_product;
UPDATE products
SET filters = filters + NEW.id_filter
WHERE id = NEW.id_product;
RETURN NEW;
END;
'LANGUAGE 'plpgsql';
CREATE TRIGGER products_ref_filters__update_tr
AFTER UPDATE
ON public.products_ref_filters FOR EACH ROW
EXECUTE PROCEDURE public.products_ref_filters__update_tr();
Очистка:
CREATE OR REPLACE FUNCTION public.products_ref_filters__truncate_tr ()
RETURNS trigger AS'
BEGIN
UPDATE products SET filters = ARRAY[]::INTEGER[];
RETURN NULL;
END;
'LANGUAGE 'plpgsql';
CREATE TRIGGER products_ref_filters__truncate_tr
AFTER TRUNCATE
ON public.products_ref_filters FOR EACH STATEMENT
EXECUTE PROCEDURE public.products_ref_filters__truncate_tr();
Теперь, при вставке, обновлении, удалении, данных из таблицы связей будет записываться массив фильтров в таблицу товаров. И мы избавились от JOIN. Теперь не нужно клеить таблицы в запросе. Это дает много плюсов, легче строить запросы, они быстрее, меньше памяти и т.д. Но статья ведь о intarray? Да.
Устанавливаем расширение:
CREATE EXTENSION intarray;
После исполнения этой команды в базе появятся функции, индексы, операторы для работы с массивами типа INTEGER. Теперь работать будет намного быстрее и удобнее.
Наполняем нашу БД. Фильтров 10 000. Товаров 100 000. Каждому товару по 10 фильтров. Итого: промежуточная таблица 1 000 000 строк. Этот блок запросов у меня исполнялся 8 мин. Так что дождитесь.
INSERT INTO filters (title) SELECT 'filter_' || num FROM generate_series(1, 10000) as num;
INSERT INTO products (title) SELECT 'product_' || num FROM generate_series(1, 100000) as num;
DO $$
DECLARE
idp INTEGER;
BEGIN
FOR idp IN SELECT id FROM products
LOOP
INSERT INTO products_ref_filters
SELECT idp, f.id FROM filters f ORDER BY random() LIMIT 10;
END LOOP;
END$$;
Создаем индекс на массив чисел:
CREATE INDEX products_idx ON public.products
USING gin (filters public.gin__int_ops);
Итого, наша БД заполнена. Давайте посмотрим, что теперь с этим делать. Давайте найдем самые популярные фильтры и запомним их.
SELECT id_filter
FROM products_ref_filters
GROUP BY id_filter
ORDER BY count(*) DESC
LIMIT 10
Мой результат такой: 7267, 4889, 6364, 5376, 3556, 7292, 11188, 2643, 9005, 10235.
Найдем товары с определенным фильтром, пусть будет 7267:
-- обычный JOIN
SELECT t1.* FROM products t1, products_ref_filters t2
WHERE t1.id = t2.id_product
AND t2.id_filter = 7267
-- 140 rows returned (execution time: 125 ms; total time: 141 ms)
-- SUB SELECT
SELECT * FROM products
WHERE id IN ( SELECT id_product FROM products_ref_filters WHERE id_filter = 7267 )
-- 140 rows returned (execution time: 125 ms; total time: 141 ms)
-- поиск по массиву
SELECT * FROM products WHERE filters @> ARRAY[7267]
-- 140 rows returned (execution time: 0 ms; total time: 0 ms)
Один из фильтров
-- JOIN
SELECT DISTINCT t1.* FROM products t1, products_ref_filters t2
WHERE t1.id = t2.id_product
AND t2.id_filter IN (7267,4889,6364,5376,3556,7292,11188,2643,9005,10235)
-- 1347 rows returned (execution time: 297 ms; total time: 297 ms)
-- SUB SELECT
SELECT * FROM products
WHERE id IN ( SELECT id_product FROM products_ref_filters WHERE id_filter IN (7267,4889,6364,5376,3556,7292,11188,2643,9005,10235) )
-- 1347 rows returned (execution time: 234 ms; total time: 250 ms)
-- INTARRAY
SELECT * FROM products WHERE filters && ARRAY[7267,4889,6364,5376,3556,7292,11188,2643,9005,10235]
-- 1347 rows returned (execution time: 16 ms; total time: 16 ms)
Лень составлять другие запросы, уж простите. Но когда нужно найти товары с несколькими фильтрами, тут вообще этот подход оставляет джойны и сабселекты далеко позади.
-- JOIN
-- ЛЕНЬ, но тут отрыв ещё больше будет ;)
-- Товары содержащие оба фильтра
SELECT * FROM products WHERE filters @> ARRAY[9844,9957];
-- 1 rows returned (execution time: 0 ms; total time: 16 ms)
Оф. дока тут.
Обратите внимание на операторы, это просто сказка! Где-то читал, что даже есть патч, установив который, можно внешние ключи строить на массив, и база сама будет следить за целостностью. Еще где-то читал, что разработчики даже планируют это сделать без всяких патчей. Да, здорово. В свое время, когда я нашел (для себя) это решение, радости было…
