Боремся со сверхинтеллектом Postgresql средствами Postgresql02.12.2017 13:33

PostgreSQL — отличнейшая БД, планировщик которой достаточно интеллектуален.
Однако в ряде случаев мощь интеллекта планировщика вырастает настолько, что он превращается в сверх-интеллект, ну и как всякий сверх-интеллект — объявляет войну своему создателю, а прежде всего начинает с войны с проектом в котором живет.


gazeazri-h3xbnqzojqmw8hou_m.jpeg


Образумливать взбунтовавшийся интеллект иногда очень сложно. Поделюсь недавней «находкой» в этой области.


Предположим что у Вас есть вебсайт, на котором есть несколько страничек, которые выводят выборки из БД (в общем-то типовой случай).


Рассмотрим простейший пример. Допустим Вы выбираете список из одной таблицы по пересечению условий AND: 


/* Запрос № 1*/
SELECT
    *
FROM
    "table1"
WHERE
        "a" = 1
    AND "b" = 2
    AND "c" = 3


При разработке приложения, разработчику очевидно что для данного запроса
неплохо иметь индекс: 



CREATE INDEX CONCURRENTLY "table_index1" ON "table1"("a","b","c");


Или даже частичный, если какое-то из полей — константа: 



CREATE INDEX CONCURRENTLY "table_index1" ON "table1"("a","b")
    WHERE "c" = 3;


На другой странице у Вас может быть выборка по «c» и «e»: 


/* Запрос №2 */
SELECT
    *
FROM
    "table1"
WHERE
    "c" = 456
ORDER BY
    "e"
LIMIT
    10


соответственно для другой страницы у Вас будет индекс: 



CREATE INDEX CONCURRENTLY "table_index2" ON "table1"("c","e");


Ну и если проект у Вас большой, то вполне вероятно наличие третьего индекса: 



CREATE INDEX CONCURRENTLY "table_index3" ON "table1"("a","b","e");


Теперь Ваш сайт работает нормально, таблицы (в данном примере — одна штука) понемногу наполняются.
Сайт развивается. Разработчики иногда добавляют индексы для важных выборок.


В какой-то момент случается факап. Вы заходите pg_dump и видите сотни запросов №1. Вы начинаете исследовать EXPLAIN этого запроса и обнаруживаете нечто вроде следующего: 


 Bitmap Heap Scan on table1  (cost=43482.22..54652.36 rows=2806 width=2494) (actual time=2544.520..2602.755 rows=337 loops=1)
   Recheck Cond: ((a = 1) AND (b = 2) AND (c = 3))
   Heap Blocks: exact=86917
   ->  BitmapAnd  (cost=43482.22..43482.22 rows=2806 width=0) (actual time=2516.333..2516.333 rows=0 loops=1)
         ->  Bitmap Index Scan on table_index2  (cost=0.00..7643.88 rows=150331 width=0) (actual time=1791.934..1791.934 rows=3982643 loops=1)
               Index Cond: (c = 3)
         ->  Bitmap Index Scan on table_index3  (cost=0.00..35836.69 rows=1258378 width=0) (actual time=91.829..91.829 rows=377222 loops=1)
               Index Cond: ((a = 1) AND (b = 2))
 Planning time: 2.706 ms
 Execution time: 2612.418 ms
(10 строк)


То есть начиная с какого-то размера таблицы постгрис решил что вместо того чтобы выбрать 337 записей напрямую из предназначенного для этого запроса (возможно частичного!) индекса, он лучше сделает выборку на 4+ млн записей, потом перемножит ее с выборкой на 0.3+ млн записей и вернет Вам результат из 337 записей.


Как бороться с этим?


Гугл выдает много ссылок с аналогичными вопросами, но мало ответов.


Есть ссылки на разные решения, в том числе и тут на хабре. Однако большинство решений требуют при апгрейдах Pg заниматься вопросом и их апгрейда, требуют нестандартного запуска Pg итп.


В общем нам, смигрировавшим за последние пару лет между 9.0 → 9.1 → 9.3 → 9.5, планирующим апгрейд на 10.x, такой способ не очень подходит.


Заставить PostgreSQL использовать Ваш индекс можно и штатным способом. Для этого нам потребуется фейковая функция: 


CREATE FUNCTION "selindex" ("name" TEXT)
    RETURNS BOOLEAN
AS $$
    BEGIN
        RETURN TRUE;
    END;
$$
LANGUAGE plpgsql
IMMUTABLE
;


Функция ничего не делает, но будет использоваться для того чтобы сбить сверхинтеллект PostgreSQL с толку в нужное нам русло.


Далее перестраиваем индексы примерно таким образом: 



CREATE INDEX CONCURRENTLY "table_index1" ON "table1"("a","b","c");
    WHERE "selindex"('table_index1')


Если секция WHERE уже имеется — добавляем в нее соответствующее условие AND: 



CREATE INDEX CONCURRENTLY "table_index1" ON "table1"("a","b")
    WHERE "c" = 3 AND "selindex"('table_index1');


Ну, а в запросы дописываем AND "selindex"('table_index1'): 


SELECT
    *
FROM
    "table1"
WHERE
        "a" = 1
    AND "b" = 2
    AND "c" = 3

    AND "selindex"('table_index1')


PS: Честно говоря я подустал разгребать факапы связанные со «сверхинтеллектом». Отладка, интеграционные тесты, нагрузочные тесты Вам не гарантируют что в перспективе PostgreSQL не откажется от использования того или иного индекса в пользу двух-пяти других. Но пока изящного решения как бороться с этим я не нашел.

© Habrahabr.ru