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