如何在postgresql上执行快速连接

Question

在ubuntu-15.10上使用postgres-9.4

对索引列上的两个表执行联接并获取Seq Scan

表a：~700万行，包含char()&varchar()&float，在date char(11)上有聚集的b-树索引.每个日期1~2500行。

表b：~6k行，包含2个cols date char(11) and feature float[]也有集群b树索引在date char(11)上.每个日期只有一个记录。

我有一个查询，cols中的select太多了，太丑了，所以我把它写得很短：

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

它需要大约500000毫秒，并使用EXPLAIN ANALYZE VERBOSE，我的结果如下：

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)
   ->  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
   ->  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
     ->  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)

我试过了PostgreSQL query runs faster with index scan, but engine chooses hash join的建议

但set random_page_cost = 2和set work_mem = '2048MB'都不能产生任何效果:计划和时间保持不变。

我看到一些文章说位图索引扫描可以做得更好，但我不知道如何创建这样的索引: postgres似乎是决定在查询某些内容时是否应该生成它的人。

其他信息：

关于资源使用情况：

查询只需要一个逻辑核心就可以工作，我有足够的内存(32 6GiB，当数据库大小<6GiB时)

Answer 1

此查询将不使用索引，因为查询将使用public.a的所有700万行。只有当您做了一个限制性更强的过滤器，只需要在public.a中的一小部分行来运行查询时，您才会看到索引被使用了。

如果您查看查询的实际运行时间，就会注意到顺序扫描花费不到1秒(准确地说是967.771毫秒)，而连接则需要大约48秒。