有什么办法来加快这一Postgres的位图堆扫描？(Is there any way to spee

数据库新手在这里。这是我的查询，我使用的是Postgres 9.3.5：

=# explain analyse SELECT SUM(actual_cost) as cost, SUM(total_items) 
   as num_items, processing_date FROM frontend_items 
   WHERE chemical_id='0501013B0' GROUP BY processing_date;

这是查询计划：

HashAggregate  (cost=1648624.91..1648624.92 rows=1 width=16) (actual time=12591.844..12591.848 rows=17 loops=1)
   ->  Bitmap Heap Scan on frontend_items  (cost=14520.24..1643821.35 rows=640474 width=16) (actual time=254.841..12317.746 rows=724242 loops=1)
         Recheck Cond: ((chemical_id)::text = '0501013B0'::text)
         ->  Bitmap Index Scan on frontend_items_chemical_id_varchar_pattern_ops_idx  (cost=0.00..14360.12 rows=640474 width=0) (actual time=209.538..209.538 rows=724242 loops=1)
               Index Cond: ((chemical_id)::text = '0501013B0'::text)
 Total runtime: 12592.499 ms

正如你所看到的，它的Bitmap Heap Scan ，占用了大部分时间。有什么办法来加快这个吗？

如果需要，我可以创造更多的索引：我的数据几乎只读（它更新每月一次）。

我猜没有太多我可以做的，因为我想多个属性，除了报酬足够的内存来保存在内存中的整个数据库，但建议将非常赞赏。

这是可能的，我可以只查找这些属性之一的时间，是否会加快速度。

注：我与16GB的RAM一台Macbook，和SSD运行此。我已经设置shared_buffers到4GB和work_mem为40MB。我最终将使用服务器与32GB的RAM和SSD。

UPDATE：表架构如下：

     Column       |          Type           |                             Modifiers
-------------------+-------------------------+--------------------------------------------------------------------
 id                | integer                 | not null default nextval('frontend_items_id_seq'::regclass)
 presentation_code | character varying(15)   | not null
 presentation_name | character varying(1000) | not null
 total_items       | integer                 | not null
 net_cost          | double precision        | not null
 actual_cost       | double precision        | not null
 quantity          | double precision        | not null
 processing_date   | date                    | not null
 price_per_unit    | double precision        | not null
 chemical_id       | character varying(9)    | not null
 pct_id            | character varying(3)    | not null
 practice_id       | character varying(6)    | not null
 sha_id            | character varying(3)    | not null
Indexes:
    "frontend_items_pkey" PRIMARY KEY, btree (id)
    "frontend_items_45fff4c7" btree (sha_id)
    "frontend_items_4e2e609b" btree (pct_id)
    "frontend_items_528f368c" btree (processing_date)
    "frontend_items_6ea07fe3" btree (practice_id)
    "frontend_items_a69d813a" btree (chemical_id)
    "frontend_items_b9b2c7ab" btree (presentation_code)
    "frontend_items_chemical_id_varchar_pattern_ops_idx" btree (chemical_id varchar_pattern_ops)
    "frontend_items_pct_code_id_488a8bbfb2bddc6d_like" btree (pct_id varchar_pattern_ops)
    "frontend_items_practice_id_bbbafffdb2c2bf1_like" btree (practice_id varchar_pattern_ops)
    "frontend_items_presentation_code_69403ee04fda6522_like" btree (presentation_code varchar_pattern_ops)
    "frontend_items_presentation_code_varchar_pattern_ops_idx" btree (presentation_code varchar_pattern_ops)
Foreign-key constraints:
    "front_chemical_id_4619f68f65c49a8_fk_frontend_chemical_bnf_code" FOREIGN KEY (chemical_id) REFERENCES frontend_chemical(bnf_code) DEFERRABLE INITIALLY DEFERRED
    "frontend__practice_id_bbbafffdb2c2bf1_fk_frontend_practice_code" FOREIGN KEY (practice_id) REFERENCES frontend_practice(code) DEFERRABLE INITIALLY DEFERRED
    "frontend_items_pct_id_30c06df242c3d1ba_fk_frontend_pct_code" FOREIGN KEY (pct_id) REFERENCES frontend_pct(code) DEFERRABLE INITIALLY DEFERRED
    "frontend_items_sha_id_4fa0ca3c3b9b67f_fk_frontend_sha_code" FOREIGN KEY (sha_id) REFERENCES frontend_sha(code) DEFERRABLE INITIALLY DEFERRED

而这里的的详细解释输出：

# explain (verbose, buffers, analyse) SELECT SUM(actual_cost) as cost, SUM(total_items) as num_items, processing_date FROM frontend_items WHERE chemical_id='0501012G0' GROUP BY processing_date;



    QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=1415349.73..1415349.74 rows=1 width=16) (actual time=3048.551..3048.556 rows=17 loops=1)
   Output: sum(actual_cost), sum(total_items), processing_date
   Buffers: shared hit=141958 read=12725
   ->  Bitmap Heap Scan on public.frontend_items  (cost=11797.55..1411446.84 rows=520385 width=16) (actual time=213.889..2834.911 rows=524644 loops=1)
         Output: id, presentation_code, presentation_name, total_items, net_cost, actual_cost, quantity, processing_date, price_per_unit, chemical_id, pct_id, practice_id, sha_id
         Recheck Cond: ((frontend_items.chemical_id)::text = '0501012G0'::text)
         Buffers: shared hit=141958 read=12725
         ->  Bitmap Index Scan on frontend_items_chemical_id_varchar_pattern_ops_idx  (cost=0.00..11667.46 rows=520385 width=0) (actual time=172.574..172.574 rows=524644 loops=1)
               Index Cond: ((frontend_items.chemical_id)::text = '0501012G0'::text)
               Buffers: shared hit=2 read=2012
 Total runtime: 3049.177 ms

你有724242行和查询采用12592.499毫秒。这是每行0.017387毫秒，即每秒57514行。你在抱怨？我觉得您的查询是足够快。普通的HDD支持的65个率 - 每秒200行，通过使用索引扫描，尽管位图索引/堆扫描速度更快。我想你会发现这是PostgreSQL使用您的具体情况最好的查询计划。

如果再次执行查询，它得到更快？缓存是热的话，那么重复执行可能会更快。如果没有得到更快的话，那是不可能的，更多的内存会有帮助。 PostgreSQL的数据页大小为8KB，所以你访问最多724242 * 8 KB = 5.5 GB的数据，即数据应该适合你的RAM。

编辑：关于这个问题的编辑版本中提到的第二个查询显示每秒172000行的表现。因此，它是可能的，如果数据在RAM缓存这样的查询变得更快。我会选择安装在RAM中的整个数据集的方法。 RAM是便宜，但开发时间是昂贵的。