Подписка на RSS

Форсируем использование цикла в запросе

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

Один отзыв на «Форсируем использование цикла в запросе»

  1. test

Leave a Reply

Your email address will not be published. Required fields are marked *