Запрос PostgreSQL выполняется быстрее при сканировании индекса, но движок выбирает хеш-соединение.

Запрос:

SELECT "replays_game".*
FROM "replays_game"
INNER JOIN
 "replays_playeringame" ON "replays_game"."id" = "replays_playeringame"."game_id"
WHERE "replays_playeringame"."player_id" = 50027

Если я устанавливаю SET enable_seqscan = off, то это происходит быстро, а именно:

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..27349.80 rows=3395 width=72) (actual time=28.726..65.056 rows=3398 loops=1)
   ->  Index Scan using replays_playeringame_player_id on replays_playeringame  (cost=0.00..8934.43 rows=3395 width=4) (actual time=0.019..2.412 rows=3398 loops=1)
         Index Cond: (player_id = 50027)
   ->  Index Scan using replays_game_pkey on replays_game  (cost=0.00..5.41 rows=1 width=72) (actual time=0.017..0.017 rows=1 loops=3398)
         Index Cond: (id = replays_playeringame.game_id)
 Total runtime: 65.437 ms

Но без ужасного enable_seqscan, он предпочитает работать медленнее:

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=7330.18..18145.24 rows=3395 width=72) (actual time=92.380..535.422 rows=3398 loops=1)
   Hash Cond: (replays_playeringame.game_id = replays_game.id)
   ->  Index Scan using replays_playeringame_player_id on replays_playeringame  (cost=0.00..8934.43 rows=3395 width=4) (actual time=0.020..2.899 rows=3398 loops=1)
         Index Cond: (player_id = 50027)
   ->  Hash  (cost=3668.08..3668.08 rows=151208 width=72) (actual time=90.842..90.842 rows=151208 loops=1)
         Buckets: 1024  Batches: 32 (originally 16)  Memory Usage: 1025kB
         ->  Seq Scan on replays_game  (cost=0.00..3668.08 rows=151208 width=72) (actual time=0.020..29.061 rows=151208 loops=1)
 Total runtime: 535.821 ms

Вот соответствующие индексы:

Index "public.replays_game_pkey"
 Column |  Type   | Definition
--------+---------+------------
 id     | integer | id
primary key, btree, for table "public.replays_game"

Index "public.replays_playeringame_player_id"
  Column   |  Type   | Definition
-----------+---------+------------
 player_id | integer | player_id
btree, for table "public.replays_playeringame"

Итак, мой вопрос: что я делаю не так, что Postgres неверно оценивает относительную стоимость двух способов соединения? ? Я вижу в оценках стоимости, что он думает, что хеш-соединение будет быстрее. И его оценка стоимости соединения индекса отличается в 500 раз.

Как я могу дать Postgres больше информации? Я запустил ВАКУУМНЫЙ АНАЛИЗнепосредственно перед запуском всего вышеперечисленного.

Интересно, что если я запускаю этот запрос для игрока с меньшим количеством игр, Postgres выбирает сканирование индекса + вложенный цикл. Так что что-то в большом количестве игр вызывает нежелательное поведение, когда относительная оценочная стоимость не соответствует фактической оценочной стоимости.

Наконец, стоит ли вообще использовать Postgres? Я не хочу становиться экспертом в настройке баз данных, поэтому я ищу базу данных, которая будет работать достаточно хорошо при уровне внимания добросовестного разработчика, а не преданного администратора баз данных.Я боюсь, что если я останусь с Postgres, у меня будет постоянный поток подобных проблем, которые заставят меня стать экспертом по Postgres, и, возможно, другая БД будет более снисходительна к более небрежному подходу.

Эксперт по Postgres (RhodiumToad) просмотрел мои полные настройки базы данных (http://pastebin.com/77QuiQSp) и рекомендовал установить cpu_tuple_cost = 0,1. Это дало резкое ускорение: http://pastebin.com/nTHvSHVd

Кроме того, переход на MySQL также довольно хорошо решил проблему. У меня есть установка MySQL и Postgres по умолчанию на моем компьютере с OS X, и MySQL работает в 2 раза быстрее, сравнивая запросы, которые «разогреваются» повторным выполнением запроса. На «холодных» запросах, то есть при первом выполнении данного запроса, MySQL работает в 5–150 раз быстрее. Производительность холодных запросов очень важна для моего конкретного приложения.

Большой вопрос, на мой взгляд, все еще остается нерешенным — потребует ли Postgres больше возни и настройки для хорошей работы, чем MySQL? Например, учтите, что ни одно из предложений, предложенных здесь комментаторами, не сработало.