How to perform fast join on postgresql
<p>using postgres-9.4 on ubuntu-15.10</p>

<p>Performing join on two tables on indexed column  and get <code>Seq Scan</code></p>

<p>table <code>a</code> : ~7 million rows, contains <code>char()&amp;varchar()&amp;float</code>, have clustered b-tree index on <code>date char(11)</code>. 1~2500 rows on each date.</p>

<p>table <code>b</code> : ~6k rows,contains 2 cols <code>date char(11) and feature float[]</code> also have clustered b-tree index on <code>date char(11)</code>. There is only 1 record on each date.</p>

<p>I have the following query, <code>cols</code> in <code>select</code> are too many and too ugly so I make it short:</p>

<pre><code>SELECT
  6 regexp_split_to_array(a.char_cols),
  ARRAY[ a.all_float] || b.feature
FROM
  a JOIN b ON
a.date = b.date;
</code></pre>

<p>It takes ~50000ms and use <code>EXPLAIN ANALYZE VERBOSE</code> I have following result:</p>

<pre><code>QUERY PLAN
-----------------------------------------------------------------------------
 Hash Join  (cost=501.67..790703.48 rows=7154354 width=856) (actual time=3.216..49223.885 rows=7154589 loops=1)
   Output: A_LOT_MESS
   Hash Cond: (a.date = b.date)
   -&gt;  Seq Scan on public.a  (cost=0.00..521913.54 rows=7154354 width=440) (actual time=0.001..967.771 rows=7154589 loops=1)
     Output: EVERY_COLS_OF_A
   -&gt;  Hash  (cost=424.63..424.63 rows=6163 width=428) (actual time=3.157..3.157 rows=6163 loops=1)
     Output: b.feature, b.date
     Buckets: 1024  Batches: 1  Memory Usage: 2769kB
     -&gt;  Seq Scan on public.b  (cost=0.00..424.63 rows=6163 width=428) (actual time=0.003..1.156 rows=6163 loops=1)
           Output: b.feature, b.date
 Planning time: 1.041 ms
 Execution time: 49396.419 ms
(12 rows)
</code></pre>

<p>I tried advices from <a href="https://stackoverflow.com/questions/10643215/postgresql-query-runs-faster-with-index-scan-but-engine-chooses-hash-join">PostgreSQL query runs faster with index scan, but engine chooses hash join</a> , </p>

<p>but neither <code>set random_page_cost = 2</code> nor <code>set work_mem = '2048MB'</code> can have any effect: plan and times remains the same.</p>

<p>I see some article says that bitmap index scan could do better, but I don't know how to create such index: It seems that postgres is the one who decide if it should be  generated when querying something.</p>

<p>Additional information:</p>

<p>about resource usage:</p>

<p>query takes only one logical core to work, and I have enough memory(32GiB, when database size &lt;6GiB)</p>