Сразу скажу, что в этой статье нет универсального совета на все случаи, а рассмотрен случай оптимизации лишь небольшого класса запросов. Тем не менее такие запросы могут встречаться во многих проектах.
Сформулируем задачу
Рассмотрим такую схему. У нас есть две таблички
content— документы.content_keyword_ref— ключевые слова, которые присутствуют в документе.
CREATE TABLE content ( id integer NOT NULL DEFAULT nextval('content_id_seq'::regclass), some_data character varying(1000) NOT NULL, CONSTRAINT content_pkey PRIMARY KEY (id), ); CREATE TABLE content_keyword_ref ( keyword_id integer NOT NULL, content_id integer NOT NULL, CONSTRAINT content_keyword_ref_pkey PRIMARY KEY (keyword_id, content_id), CONSTRAINT content_keyword_ref_content_id_foreign FOREIGN KEY (content_id) REFERENCES content (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE, CONSTRAINT content_keyword_ref_keyword_id_foreign FOREIGN KEY (keyword_id) REFERENCES keywords (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE ); CREATE INDEX content_keyword_ref_content_id_index ON content_keyword_ref USING btree (content_id); CREATE INDEX content_keyword_ref_keyword_id_index ON content_keyword_ref USING btree (keyword_id);
Документов у меня локальной в базе примерно 2 млн, а связей с ключевыми словами примерно 15 млн.
Выберем документы, содержащие одно из перечисленных ключевых слов.
Решение классическое
Для этого нам придется написать примерно такой запрос (я сразу буду добавлять EXPLAIN ANALYZE и выводить план):
EXPLAIN ANALYSE SELECT c.id FROM content c JOIN content_keyword_ref r ON r.content_id = c.id AND r.keyword_id IN (4713, 5951) GROUP BY c.id LIMIT 1000 GROUP BY нам приходится использовать только для того, чтобы в выводе не дублировались документы для каждого найденного ключевого слова. Что мы видим в плане выполнения запроса:
Limit (cost=21454.94..34933.16 rows=1000 width=4) (actual time=6.777..199.735 rows=1000 loops=1) -> Group (cost=21454.94..100235.11 rows=5845 width=4) (actual time=6.775..199.641 rows=1000 loops=1) Group Key: c.id -> Merge Join (cost=21454.94..100220.49 rows=5845 width=4) (actual time=6.774..199.389 rows=1141 loops=1) Merge Cond: (c.id = r.content_id) -> Index Only Scan using content_pkey on content c (cost=0.43..73221.47 rows=2182736 width=4) (actual time=0.013..131.942 rows=1339506 loops=1) Heap Fetches: 0 -> Sort (cost=21454.51..21469.13 rows=5845 width=4) (actual time=6.662..6.792 rows=1141 loops=1) Sort Key: r.content_id Sort Method: quicksort Memory: 143kB -> Bitmap Heap Scan on content_keyword_ref r (cost=118.16..21088.82 rows=5845 width=4) (actual time=0.470..6.273 rows=2007 loops=1) Recheck Cond: (keyword_id = ANY ('{4713,5951}'::integer[])) Heap Blocks: exact=1781 -> Bitmap Index Scan on content_keyword_ref_keyword_content_idx (cost=0.00..116.70 rows=5845 width=0) (actual time=0.239..0.239 rows=2007 loops=1) Index Cond: (keyword_id = ANY ('{4713,5951}'::integer[])) Planning time: 0.277 ms Execution time: 199.805 ms
Аналогичный результат мы получили бы, использовав DISTINCT вместо GROUP BY:
EXPLAIN ANALYSE SELECT DISTINCT c.id FROM content c JOIN content_keyword_ref r ON r.content_id = c.id AND r.keyword_id IN (4713, 5951) LIMIT 1000Получаем:
Limit (cost=21454.94..34933.16 rows=1000 width=4) (actual time=2.824..187.619 rows=1000 loops=1) -> Unique (cost=21454.94..100235.11 rows=5845 width=4) (actual time=2.824..187.519 rows=1000 loops=1) -> Merge Join (cost=21454.94..100220.49 rows=5845 width=4) (actual time=2.823..187.351 rows=1141 loops=1) Merge Cond: (c.id = r.content_id) -> Index Only Scan using content_pkey on content c (cost=0.43..73221.47 rows=2182736 width=4) (actual time=0.011..120.481 rows=1339506 loops=1) Heap Fetches: 0 -> Sort (cost=21454.51..21469.13 rows=5845 width=4) (actual time=2.693..2.805 rows=1141 loops=1) Sort Key: r.content_id Sort Method: quicksort Memory: 143kB -> Bitmap Heap Scan on content_keyword_ref r (cost=118.16..21088.82 rows=5845 width=4) (actual time=0.463..2.321 rows=2007 loops=1) Recheck Cond: (keyword_id = ANY ('{4713,5951}'::integer[])) Heap Blocks: exact=1781 -> Bitmap Index Scan on content_keyword_ref_keyword_content_idx (cost=0.00..116.70 rows=5845 width=0) (actual time=0.235..0.235 rows=2007 loops=1) Index Cond: (keyword_id = ANY ('{4713,5951}'::integer[])) Planning time: 0.264 ms Execution time: 187.727 ms
Как видно, группировка приводит к сортировкам и другим накладным расходам. На некоторых данных время выполнения достигает нескольких секунд!
Как быть?
Оптимизация
Мои идеи, как ускорить запрос при имеющейся схеме, закончились. Попробуем перестроить схему. Табличку content оставляем. А вот связи с ключевыми словами будем хранить в массиве. Чтобы быстро выбирать данные по условиям на массиве, создаем также GiST индекс. О том, какие операторы для работы с массивами поддерживаются индексами, можно посмотреть в документации PostgreSQL.
CREATE TABLE document ( content_id integer NOT NULL, -- Наш массив ключевых слов, взамен таблицы content_keyword_ref keyword_ids integer[] NOT NULL ); -- Наш GiST индекс CREATE INDEX document_keyword_ids_index ON document USING GiST(keyword_ids gist__intbig_ops); CREATE INDEX document_content_id_index ON public.document USING btree (content_id); -- Копипастим имеющиеся данные INSERT INTO document (content_id, keyword_ids) SELECT c.id, ARRAY( SELECT r.keyword_id FROM content_keyword_ref r WHERE r.content_id = c.id ) FROM content c GROUP BY c.id;
Теперь попробуем построить запрос, который будет возвращать такие же данные как и в вариантах выше:
EXPLAIN ANALYZE SELECT c.id FROM content c JOIN document d ON d.content_id = c.id AND d.keyword_ids && ARRAY[4713, 5951] limit 1000 Смотрим план:
Limit (cost=387.80..7540.27 rows=1000 width=4) (actual time=8.799..12.935 rows=1000 loops=1) -> Nested Loop (cost=387.80..14177.77 rows=1928 width=4) (actual time=8.799..12.880 rows=1000 loops=1) -> Bitmap Heap Scan on document d (cost=387.37..6246.79 rows=1930 width=4) (actual time=8.786..10.599 rows=1000 loops=1) Recheck Cond: (keyword_ids && '{4713,5951}'::integer[]) Rows Removed by Index Recheck: 107 Heap Blocks: exact=1008 -> Bitmap Index Scan on document_keyword_ids_index (cost=0.00..386.89 rows=1930 width=0) (actual time=8.560..8.560 rows=1977 loops=1) Index Cond: (keyword_ids && '{4713,5951}'::integer[]) -> Index Only Scan using content_pkey on content c (cost=0.43..4.10 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1000) Index Cond: (id = d.content_id) Heap Fetches: 0 Planning time: 0.184 ms Execution time: 12.994 ms
Выгода есть, причем заметная. На выбранных данных оптимизированный вариант запроса выполняется примерно в 14 раз быстрее. Текст запроса остался примерно таким же понятным. Давайте посмотрим, какие еще выгоды мы получили.
Бонус
Допустим, надо выводить найденные документы на странице с пагинацией. Как в этом случае посчитать количество записей в выборке в «классическом» варианте? Вот несколько вариантов:
Считаем количество записей в подзапросе с GROUP BY:
SELECT COUNT(1) FROM ( SELECT c.id FROM content c JOIN content_keyword_ref r ON r.content_id = c.id AND r.keyword_id IN (4713, 5951) GROUP BY c.id ) t; Считаем количество записей в подзапросе с DISTINCT:
SELECT COUNT(1) FROM ( SELECT DISTINCT(c.id) FROM content c JOIN content_keyword_ref r ON r.content_id = c.id AND r.keyword_id IN (4713, 5951) ) t; Считаем количество записей без подзапроса, но с помощью COUNT (DISTINCT columns):
SELECT COUNT(DISTINCT c.id) FROM content c JOIN content_keyword_ref r ON r.content_id = c.id AND r.keyword_id IN (4713, 5951)Или даже так:
SELECT COUNT(1) OVER() FROM content c JOIN content_keyword_ref r ON r.content_id = c.id AND r.keyword_id IN (4713, 5951) GROUP BY c.id LIMIT 1 Во всех этих вариантах минус не только в производительности. Будет ли модуль пагинации в вашем фреймворке автоматически делать один из этих вариантов? Laravel, например, нет. Вместо этого он выберет все записи и посчитает их количество с помощью count() уже на PHP. Поэтому скорее всего вам придется переопределять метод расчета количества записей, чтобы каждый раз не вычитывалась из базы вся выборка.
Как мы посчитаем количество записей в оптимизированном варианте запроса:
SELECT COUNT(1) FROM document d WHERE d.keyword_ids && ARRAY[4713, 5951]Гораздо лаконичнее и нет проблем с пагинатором.
Еще один бонус
Мы выбирали документы, содержащие хотя бы одно из указанных слов. Что если надо выбрать документы, в которых содержатся все интересующие ключевые слова? В классическом варианте запрос можно построить примерно так:
SELECT c.id FROM content c JOIN content_keyword_ref r1 ON r1.content_id = c.id AND r1.keyword_id = 5388 JOIN content_keyword_ref r2 ON r2.content_id = c.id AND r2.keyword_id = 5951 LIMIT 1000 То есть сколько ключевых слов ищем, столько JOIN-ов и делаем. Если мы фильтруем записи по массиву, то можем использовать оператор @>. Тогда запрос выглядит более аккуратно:
SELECT c.id FROM content c JOIN document d ON d.content_id = c.id AND d.keyword_ids @> ARRAY[5388, 5951] LIMIT 1000Да и план выполнения у него оказывается лучше.
В документации по ссылке, которую я оставил выше, можно найти описание и других полезных операторов, поддерживаемых индексами.
Резюме
Я поэкспериментировал с разными данными. Как правило, оптимизированный вариант дает выигрыш в скорости от 2 до 10 раз. Но мне удалось-таки подобрать примеры, когда запросы на вычисление количества записей в выдаче работают в 1.5-2 раза медленнее в случае «оптимизированного» варианта.
То есть в целом эксперимент можно назвать удачным. Но если решитесь на подобные хитрости, то перед запуском изменений в продакшн стоит проверить эффективность на ваших данных.
ссылка на оригинал статьи https://habrahabr.ru/post/317980/
Добавить комментарий