Боремся со сверхинтеллектом 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 не откажется от использования того или иного индекса в пользу двух-пяти других. Но пока изящного решения как бороться с этим я не нашел.

• Подсказки Планировщику
• Postgresql