HighLoad.org – блог о высоких нагрузках
HighLoad.org > Форсируем использование цикла в запросе

Форсируем использование цикла в запросе
2016-06-14 03:08 my_fess 
postgresql Рассмотрим ситуацию как заставить планировщик PostgreSQL использовать цикл и заставить вместо seq scan использовать несколько раз поиск по одному и тому же индексу.
Допустим есть таблица документов у которых указана категория и автор.
CREATE TABLE documents
(
id serial NOT NULL,
category integer,
author integer
);
Есть индекс по обеим колонкам:
CREATE INDEX index_category_author ON documents
USING btree (category, author);
Заполним данными с 10 различными категориями и 1000 авторов:
INSERT INTO documents(category, author)
SELECT round(random()*10), round(random()*1000)
FROM generate_series(1, 1000000)
Допустим мы хотим посчитать количсетво документов определенного набора категорий от одного автора:
WITH
cats(cat_id) AS (
SELECT *
FROM UNNEST(ARRAY[1, 3, 5, 7, 9])
)

SELECT count(*)
FROM documents
WHERE
category = ANY(SELECT cat_id FROM cats)
AND author = 50
explain1 Мы получили seq scan. Это очевидная ошибка планировщика, и чтобы заставить использовать существующий индекс можно переписать запрос следующим образом:
WITH
cats(cat_id) AS (
SELECT *
FROM UNNEST(ARRAY[1, 3, 5, 7, 9])
),

tmp(cnt) AS (
SELECT UNNEST(ARRAY(
SELECT count(*)
FROM documents
WHERE
category = cats.cat_id
AND author = 50
))
FROM cats
)

SELECT sum(cnt)
FROM tmp
explain2 По сути таким образом мы заставляем планировщик выполнить цикл foreach для каждой записи в подтаблице cats, и для каждой записи из cats выполнить SELECT из documents с использованием индекса. Этот метод является универсальным и прямо заставляет планировщик сделать то, что мы хотим, но иногда получается и переписать запрос таким способом что postgresql сам поймёт, что лучше использовать индекс, например:
WITH
cats(cat_id) AS (
SELECT *
FROM UNNEST(ARRAY[1, 3, 5, 7 ,9])
)

SELECT count(*)
FROM documents
WHERE
category = ANY(ARRAY(SELECT cat_id FROM cats))
AND author = 50

комментарии [0]  | комментировать

  © 2010-2018 HIGHLOAD