Postgresql index not used in inner join
<p>I am using 9.1</p>

<p>Here is the query:</p>

<pre><code>select a.id,b.flag              
from a, b               
where b.starting_date&gt;='2002-01-01'::date               
and a.zip_code= b.zip_code              
and abs( a.date1-b.date1 ) &lt;=60                 
and abs( a.balance-b.balance ) &lt;=1000               
and abs( a.value-b.value ) &lt;=3000   
</code></pre>

<p>Where table a and b have every single field indexed (not multi-column index) using btree. </p>

<p>Here is <code>explain analyze verbose</code> results:</p>

<pre><code>Merge Join  (cost=0.00..448431434.24 rows=440104742 width=16) (actual time=384.268..15151912.652 rows=672144 loops=1)   
  Output: a.id, b.flag  
  Merge Cond: (a.zip_code = b.zip_code) 
  Join Filter: ((abs((a.date1 - b.date1)) &lt;= 60) AND (abs((a.balance - b.balance)) &lt;= 1000) AND (abs((a.value - b.value)) &lt;= 3000)) 
  -&gt;  Index Scan using indx_a_zip_code on a  (cost=0.00..950851.26 rows=6800857 width=32) (actual time=0.028..22292.274 rows=2080440 loops=1)   
        Output: a.id, a.zip_code, a.date1, a.balance, a.value   
  -&gt;  Materialize  (cost=0.00..1906889.40 rows=19744024 width=28) (actual time=0.032..6148075.701 rows=6472114362 loops=1)  
        Output: b.balance, b.date1, b.zip_code, b.starting_date, b.value,   
        -&gt;  Index Scan using indx_zip_code on  b  (cost=0.00..1857529.34 rows=19744024 width=28) (actual time=0.025..76893.104 rows=19078422 loops=1)   
              Output:b.balance, b.date1, b.zip_code, b.starting_date    
              Filter: (b.starting_date &gt;= '2002-01-01'::date)   
Total runtime: 15155983.643 ms  
</code></pre>

<p>It appears to me that the query planner is using index for zip_code but not other fields. I have 8 million rows in table a and 20 million rows in table b and it takes 3 hours to finish (The RAM is 64GB, and all critical postgresql server configurations have been tuned according to:</p>

<p><a href="https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server" rel="nofollow">https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server</a></p>

<p>Any advice/suggestion is appreciated. Thanks a million!</p>