title: Postgres not using index when index scan is much better option
body: <p>I have a simple query to join two tables that's being really slow. I found out that the query plan does a seq scan on the large table <code>email_activities</code> (~10m rows) while I think using indexes doing nested loops will actually be faster.</p>
  
  <p>I rewrote the query using a subquery in an attempt to force the use of index, then noticed something interesting. If you look at the two query plans below, you will see that when I limit the result set of subquery to 43k, query plan does use index on email_activities while setting the limit in subquery to even 44k will cause query plan to use seq scan on <code>email_activities</code>. One is clearly more efficient than the other, but Postgres doesn't seem to care.</p>
  
  <p>What could cause this?  Does it have a configs somewhere that forces the use of hash join if one of the set is larger than certain size? </p>
  
  <pre><code>explain analyze SELECT COUNT(DISTINCT "email_activities"."email_recipient_id") FROM "email_activities" where email_recipient_id in (select "email_recipients"."id" from email_recipients WHERE "email_recipients"."email_campaign_id" = 1607 limit 43000);
                                                                                              QUERY PLAN
  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   Aggregate  (cost=118261.50..118261.50 rows=1 width=4) (actual time=224.556..224.556 rows=1 loops=1)
     -&gt;  Nested Loop  (cost=3699.03..118147.99 rows=227007 width=4) (actual time=32.586..209.076 rows=40789 loops=1)
           -&gt;  HashAggregate  (cost=3698.94..3827.94 rows=43000 width=4) (actual time=32.572..47.276 rows=43000 loops=1)
                 -&gt;  Limit  (cost=0.09..3548.44 rows=43000 width=4) (actual time=0.017..22.547 rows=43000 loops=1)
                       -&gt;  Index Scan using index_email_recipients_on_email_campaign_id on email_recipients  (cost=0.09..5422.47 rows=65710 width=4) (actual time=0.017..19.168 rows=43000 loops=1)
                             Index Cond: (email_campaign_id = 1607)
           -&gt;  Index Only Scan using index_email_activities_on_email_recipient_id on email_activities  (cost=0.09..2.64 rows=5 width=4) (actual time=0.003..0.003 rows=1 loops=43000)
                 Index Cond: (email_recipient_id = email_recipients.id)
                 Heap Fetches: 40789
   Total runtime: 224.675 ms
  </code></pre>
  
  <p>And:</p>
  
  <pre><code>explain analyze SELECT COUNT(DISTINCT "email_activities"."email_recipient_id") FROM "email_activities" where email_recipient_id in (select "email_recipients"."id" from email_recipients WHERE "email_recipients"."email_campaign_id" = 1607 limit 50000);
                                                                                              QUERY PLAN
  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   Aggregate  (cost=119306.25..119306.25 rows=1 width=4) (actual time=3050.612..3050.613 rows=1 loops=1)
     -&gt;  Hash Semi Join  (cost=4451.08..119174.27 rows=263962 width=4) (actual time=1831.673..3038.683 rows=47935 loops=1)
           Hash Cond: (email_activities.email_recipient_id = email_recipients.id)
           -&gt;  Seq Scan on email_activities  (cost=0.00..107490.96 rows=9359988 width=4) (actual time=0.003..751.988 rows=9360039 loops=1)
           -&gt;  Hash  (cost=4276.08..4276.08 rows=50000 width=4) (actual time=34.058..34.058 rows=50000 loops=1)
                 Buckets: 8192  Batches: 1  Memory Usage: 1758kB
                 -&gt;  Limit  (cost=0.09..4126.08 rows=50000 width=4) (actual time=0.016..27.302 rows=50000 loops=1)
                       -&gt;  Index Scan using index_email_recipients_on_email_campaign_id on email_recipients  (cost=0.09..5422.47 rows=65710 width=4) (actual time=0.016..22.244 rows=50000 loops=1)
                             Index Cond: (email_campaign_id = 1607)
   Total runtime: 3050.660 ms
  </code></pre>
  
  <ul>
  <li>Version: PostgreSQL 9.3.10 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit</li>
  <li>email_activities:  ~10m rows</li>
  <li>email_recipients:  ~11m rows</li>
  </ul>