title: PostgreSQL query runs faster with index scan, but engine chooses hash join
body: <p>The query:</p>
  
  <pre><code>SELECT "replays_game".*
  FROM "replays_game"
  INNER JOIN
   "replays_playeringame" ON "replays_game"."id" = "replays_playeringame"."game_id"
  WHERE "replays_playeringame"."player_id" = 50027
  </code></pre>
  
  <p>If I set <code>SET enable_seqscan = off</code>, then it does the fast thing, which is:</p>
  
  <pre><code>QUERY PLAN
  --------------------------------------------------------------------------------------------------------------------------------------------------------------------
   Nested Loop  (cost=0.00..27349.80 rows=3395 width=72) (actual time=28.726..65.056 rows=3398 loops=1)
     -&gt;  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)
     -&gt;  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
  </code></pre>
  
  <p>But without the dreaded enable_seqscan, it chooses to do a slower thing:</p>
  
  <pre><code>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)
     -&gt;  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)
     -&gt;  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
           -&gt;  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
  </code></pre>
  
  <p>Here are the relevant indexes:</p>
  
  <pre><code>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"
  </code></pre>
  
  <p>So my question is, what am I doing wrong that Postgres is mis-estimating the relative costs of the two ways of joining? I see in the cost estimates that it <em>thinks</em> the hash-join will be faster. And its estimate of the cost of the index-join is off by a factor of 500.</p>
  
  <p>How can I give Postgres more of a clue? I did run a <code>VACUUM ANALYZE</code> immediately before running all of the above.</p>
  
  <p>Interestingly, if I run this query for a player with a smaller # of games, Postgres chooses to do the index-scan + nested-loop. So something about the large # of games tickles this undesired behavior where relative estimated cost is out of line with actual estimated cost.</p>
  
  <p>Finally, should I be using Postgres at all?  I don't wish to become an expert in database tuning, so I'm looking for a database that will perform reasonably well with a conscientious developer's level of attention, as opposed to a dedicated DBA. I am afraid that if I stick with Postgres I will have a steady stream of issues like this that will force me to become a Postgres expert, and perhaps another DB will be more forgiving of a more casual approach.</p>
  
  <hr>
  
  <p>A Postgres expert (RhodiumToad) reviewed my full database settings (<a href="http://pastebin.com/77QuiQSp" rel="noreferrer">http://pastebin.com/77QuiQSp</a>) and recommended <code>set cpu_tuple_cost = 0.1</code>. That gave a dramatic speedup: <a href="http://pastebin.com/nTHvSHVd" rel="noreferrer">http://pastebin.com/nTHvSHVd</a></p>
  
  <p>Alternatively, switching to MySQL also solved the problem pretty nicely. I have a default installation of MySQL and Postgres on my OS X box, and MySQL is 2x faster, comparing queries that are "warmed up" by repeatedly executing the query. On "cold" queries, i.e. the first time a given query is executed, MySQL is 5 to 150 times faster. The performance of cold queries is pretty important for my particular application.</p>
  
  <p>The big question, as far as I'm concerned, is still outstanding -- will Postgres require more fiddling and configuration to run well than MySQL?  For example, consider that none of the suggestions offered by the commenters here worked.</p>