Почему PostgreSQL использует неоптимальный план выполнения для простого запроса?

Question

[yurasek]

Знакомлюсь с PostgreSQL 9.5 и меня уже много "простых" моментов начинают смущать. Вот один из них.
Создаю в базе таблицу params:

CREATE TABLE params(
counter_id integer NOT NULL, 
"time" bigint NOT NULL, 
value integer NOT NULL, 
param integer NOT NULL, 
param_id bigint NOT NULL, 
CONSTRAINT param_id 
PRIMARY KEY (param_id));

Для таблицы params создаю индекс:

CREATE INDEX counter_id_time ON params (counter_id, time);

Добавляю в таблицу params для экспериментов 320 млн записей.
Затем выполняю следующие запросы:

SELECT MAX(time) FROM params WHERE counter_id = 6001; -- 0,031 с

SELECT MAX(time) FROM params WHERE counter_id = 15998; -- 0,016 с

Затем выполняю такой запрос:

SELECT MAX(time) FROM params WHERE counter_id = 6001 OR counter_id = 15998; -- 139 с !??

Почему PostgreSQL выбирает такой тернистый вариант для такого "простого" запроса?
Вот какой план для выполнения запроса выдаёт БД:

"Aggregate  (cost=140975.88..140975.89 rows=1 width=8)"
"  ->  Bitmap Heap Scan on params  (cost=919.98..140878.74 rows=38853 width=8)"
"        Recheck Cond: ((counter_id = 6001) OR (counter_id = 15998))"
"        ->  BitmapOr  (cost=919.98..919.98 rows=38854 width=0)"
"              ->  Bitmap Index Scan on counter_id_time  (cost=0.00..450.27 rows=19427 width=0)"
"                    Index Cond: (counter_id = 6001)"
"              ->  Bitmap Index Scan on counter_id_time  (cost=0.00..450.27 rows=19427 width=0)"
"                    Index Cond: (counter_id = 15998)"

Мне пришлось "разжевать" для PostgreSQL запрос до такого:

SELECT MAX(m) FROM (SELECT MAX(time) m FROM params WHERE counter_id = 6001 UNION SELECT MAX(time) m FROM params WHERE counter_id = 15998) p; -- 0,016 c

Вот какой план для выполнения этого запроса выдаёт БД:

"Unique  (cost=9.23..9.24 rows=2 width=0)"
"  ->  Sort  (cost=9.23..9.24 rows=2 width=0)"
"        Sort Key: ($1)"
"        ->  Append  (cost=4.59..9.22 rows=2 width=0)"
"              ->  Result  (cost=4.59..4.60 rows=1 width=0)"
"                    InitPlan 2 (returns $1)"
"                      ->  Limit  (cost=0.57..4.59 rows=1 width=8)"
"                            ->  Index Only Scan Backward using counter_id_time on params params_1  (cost=0.57..78084.90 rows=19427 width=8)"
"                                  Index Cond: ((counter_id = 6001) AND ("time" IS NOT NULL))"
"              ->  Result  (cost=4.59..4.60 rows=1 width=0)"
"                    InitPlan 1 (returns $0)"
"                      ->  Limit  (cost=0.57..4.59 rows=1 width=8)"
"                            ->  Index Only Scan Backward using counter_id_time on params  (cost=0.57..78084.90 rows=19427 width=8)"
"                                  Index Cond: ((counter_id = 15998) AND ("time" IS NOT NULL))"

Например, в MySQL 5.7.10 и MS SQL 2014 SP1, проблемный для PostgreSQL запрос не является проблемным.
К чему я это всё: когда мне необходимо будет сделать выборку по нескольким counter_id, по условию количество которых будет ограничено, PostgreSQL всё равно будет пытаться сделать выборку по всем counter_id, а только в конце отсеет ненужные значения. Использование UNIOIN конечно же вариант, но я читаю его неоптимальным в большинстве случаев.
Что можно сделать, чтобы PostgreSQL "корректно" отрабатывал такие запросы?

Answer 1

[Melkij]

Обычно делают counter_id in (6001, 15998) а не толпу or.

explain analyze select max(time) from params where counter_id in (6001, 15998);

Aggregate (cost=18.52..18.53 rows=1 width=8) (actual time=1.586..1.586 rows=1 loops=1)
-> Index Only Scan using params_counter_id_time_idx on params (cost=0.56..18.51 rows=4 width=8) (actual time=0.769..1.566 rows=12 loops=1)
Index Cond: (counter_id = ANY ('{6001,15998}'::bigint[]))
Heap Fetches: 2
Planning time: 50.191 ms
Execution time: 1.933 ms

Тут на порядок поменьше данных, это просто виртуалка и pg ещё 9.4

or две минуты исполнения тоже не даёт:

Aggregate (cost=25.20..25.21 rows=1 width=8) (actual time=38.797..38.797 rows=1 loops=1)
-> Bitmap Heap Scan on params (cost=9.16..25.19 rows=4 width=8) (actual time=0.362..38.749 rows=12 loops=1)
Recheck Cond: ((counter_id = 6001) OR (counter_id = 15998))
Heap Blocks: exact=12
-> BitmapOr (cost=9.16..9.16 rows=4 width=0) (actual time=0.042..0.042 rows=0 loops=1)
-> Bitmap Index Scan on params_counter_id_time_idx (cost=0.00..4.58 rows=2 width=0) (actual time=0.031..0.031 rows=6 loops=1)
Index Cond: (counter_id = 6001)
-> Bitmap Index Scan on params_counter_id_time_idx (cost=0.00..4.58 rows=2 width=0) (actual time=0.009..0.009 rows=6 loops=1)
Index Cond: (counter_id = 15998)
Planning time: 0.247 ms
Execution time: 39.408 ms

Попробуйте сделать analyze params;
Может, у планировщика статистика старая.

Comments

[yurasek]

С конструкцией IN (6001, 15998) запрос действительно лучше и наглядней, спасибо.
Без analyze params запрос:

SELECT MAX(time) FROM params WHERE counter_id IN (6001, 15998);

выполнился за 125 секунд.
После выполнения analyze params этот запрос выполнился на 0,3 с и закэшировался, но любой последующий запрос с использованием двух произвольных чисел counter_id выполняется где-то за 125 секунд, после чего кэшируется и повторно выполняется за 0,3 с. Таким образом в моём случае analyze params хотя бы помогает кэшировать результаты запроса, но всё равно пока ситуация в моём случае остаётся странной.

[yurasek]

Всё оказывается ещё страннее, т.к. запрос

SELECT MAX(time) FROM params WHERE counter_id IN (4444);

и

SELECT MAX(time) FROM params WHERE counter_id IN (4444, 4444);

выполняются разное время! Первый за 0,034 с, а второй за 80 с!

[Melkij]

Покажите explain (analyze,buffers) select ... для этих запросов.
На чём база запущена? Что-то происходит очень странное, а что - пока не понимаю.

[yurasek]

База работает на Windows 8.1 x64. Процессор Intel i7-4820, оперативной памяти 16 Гб, диск на 3 Тб.
Сама база сейчас занимает 37 Гб (321 млн записей + индекс по полю counter_id и time).
Это тестовая база, но имитирует боевую базу на MySQL 5.6.10, которую я планирую оптимизировать или заменить на PostgreSQL, если я приду к пониманию, что PostgreSQL справляется поставленной задачей лучше.
В таблице params используется ровно 16384 уникальных значений counter_id, которые были добавлены в рэндомном порядке в базу, но с равномерным количеством записей (321000000/16384 ~ 19592). После чего был создан индекс по полям counter_id и time.

Вот результат запроса explain (analyze, buffers) select max(time) from params where counter_id in (9876, 6789);

"Aggregate  (cost=139934.30..139934.31 rows=1 width=8) (actual time=132113.630..132113.630 rows=1 loops=1)"
"  Buffers: shared hit=112 read=39422"
"  ->  Bitmap Heap Scan on params  (cost=908.12..139837.85 rows=38578 width=8) (actual time=111.489..132081.067 rows=39693 loops=1)"
"        Recheck Cond: (counter_id = ANY ('{9876,6789}'::integer[]))"
"        Heap Blocks: exact=39373"
"        Buffers: shared hit=112 read=39422"
"        ->  Bitmap Index Scan on counter_id_time  (cost=0.00..898.48 rows=38578 width=0) (actual time=93.849..93.849 rows=39693 loops=1)"
"              Index Cond: (counter_id = ANY ('{9876,6789}'::integer[]))"
"              Buffers: shared hit=2 read=159"
"Planning time: 0.238 ms"
"Execution time: 132113.965 ms"

[yurasek]

explain (analyze, buffers) SELECT MAX(time) FROM params WHERE counter_id IN (8888);

"Result  (cost=4.59..4.60 rows=1 width=0) (actual time=36.492..36.492 rows=1 loops=1)"
"  Buffers: shared hit=1 read=4"
"  InitPlan 1 (returns $0)"
"    ->  Limit  (cost=0.57..4.59 rows=1 width=8) (actual time=36.487..36.487 rows=1 loops=1)"
"          Buffers: shared hit=1 read=4"
"          ->  Index Only Scan Backward using counter_id_time on params  (cost=0.57..77534.35 rows=19289 width=8) (actual time=36.486..36.486 rows=1 loops=1)"
"                Index Cond: ((counter_id = 8888) AND ("time" IS NOT NULL))"
"                Heap Fetches: 1"
"                Buffers: shared hit=1 read=4"
"Planning time: 0.198 ms"
"Execution time: 36.525 ms"

explain (analyze, buffers) SELECT MAX(time) FROM params WHERE counter_id IN (8888, 8888);

"Aggregate  (cost=139934.30..139934.31 rows=1 width=8) (actual time=81536.479..81536.479 rows=1 loops=1)"
"  Buffers: shared hit=222 read=19201"
"  ->  Bitmap Heap Scan on params  (cost=908.12..139837.85 rows=38578 width=8) (actual time=50.128..81522.183 rows=19420 loops=1)"
"        Recheck Cond: (counter_id = ANY ('{8888,8888}'::integer[]))"
"        Heap Blocks: exact=19343"
"        Buffers: shared hit=222 read=19201"
"        ->  Bitmap Index Scan on counter_id_time  (cost=0.00..898.48 rows=38578 width=0) (actual time=47.687..47.687 rows=19420 loops=1)"
"              Index Cond: (counter_id = ANY ('{8888,8888}'::integer[]))"
"              Buffers: shared hit=4 read=76"
"Planning time: 0.202 ms"
"Execution time: 81536.674 ms"

[Melkij]

А, винда. Проверьте на нормальной системе.
Как говорят опытные ДБА, postgresql - это только linux или freebsd.

На сколько помню по прошлой pgday'15, где этот вопрос вскользь звучал, на винде не работает shared memory - а это краеугольный камень всей архитектуры postgresql. Какими-то граблями шаренная память под виндой эмулируется чтобы хоть как-то запуститься, о производительности там речи не идёт.

[yurasek]

Следуя Вашему совету решил провести эксперимент в Ubuntu 14.04 LTS x64 правда в виртуальной среде. Процессор был полностью проброшен, выделено 4 Гб ОЗУ, база размером 37 Гб была скопирована на выделенный раздел, таким образом фрагментированность файлов базы исключена. Пришлось повозиться с локалью базы, т.к. клиент pgAdmin ни в какую не хотел подключаться к базе, ссылаясь на неизвестную локаль Russian_Russia.1251. Она была присвоена при создании базы в Windows , а в Ubuntu такой нет. Наверное разработчикам очень сложно было сделать что-то универсальное, но да ладно. Я пробовал найти штатный способ смены локали, но как оказалось таковой нет. Наверное, это опять же сложная задача для разработчиков. Найденный в интернете способ резервного копирования и восстановления базы с новой локалью меня немного озадачил своими временными рамками, да к тому же у меня нет текстовых полей. Пришлось провести небольшой эксперимент: при помощи поиска был найден в папке global файл, содержащий название локали Russian_Russia.1251. Локаль Russian_Russia.1251 была заменена в hex-редакторе на ru_RU.utf8, а оставшиеся символы затёрты нулями. После этого подключение pgAdmin прошло корректно.
Дальше я решил проверить скорость выполнения запроса вида
SELECT MAX(time) FROM params WHERE counter_id IN (123, 345);
Увы, но запрос выполнился за 116 секунд!

"Aggregate  (cost=139934.30..139934.31 rows=1 width=8)"
"  ->  Bitmap Heap Scan on params  (cost=908.12..139837.85 rows=38578 width=8)"
"        Recheck Cond: (counter_id = ANY ('{123,345}'::integer[]))"
"        ->  Bitmap Index Scan on counter_id_time  (cost=0.00..898.48 rows=38578 width=0)"
"              Index Cond: (counter_id = ANY ('{123,345}'::integer[]))"

Таким образом дело не в Windows и shared_buffers, но в чём же тогда?

[yurasek]

Только после того, как я отметил индекс как кластерный и выполнил команду CLUSTER для упорядочивания данных таблицы, запрос начал выполняться с адекватной скоростью: за 0,066 с.