Боремся со сверхинтеллектом Postgresql средствами Postgresql

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

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

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

Рассмотрим простейший пример. Допустим Вы выбираете список из одной таблицы по пересечению условий 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 не откажется от использования того или иного индекса в пользу двух-пяти других. Но пока изящного решения как бороться с этим я не нашел.

Автор: Обухов Дмитрий Эдуардович

Источник