title: Postgres: speed up bitmap heap scan?
body: <p>I'm using Postgres 9.3 and wondering if there is a way to speed up a particular query on a large table. These are my tables:</p>
  
  <pre><code>                                      Table "public.frontend_prescription"
        Column       |          Type           |                             Modifiers
  -------------------+-------------------------+--------------------------------------------------------------------
   id                | integer                 | not null default nextval('frontend_prescription_id_seq'::regclass)
   presentation_code | character varying(15)   | not null
   actual_cost       | double precision        | not null
   processing_date   | date                    | not null
   pct_id            | character varying(3)    | not null
  Indexes:
      "frontend_prescription_pkey" PRIMARY KEY, btree (id)
      "frontend_prescription_4e2e609b" btree (pct_id)
      "frontend_prescription_528f368c" btree (processing_date)
      "frontend_prescription_b9b2c7ab" btree (presentation_code)
      "frontend_prescription_cost_by_pres_code" btree (presentation_code, pct_id, actual_cost)
      "frontend_prescription_presentation_code_69403ee04fda6522_like" btree (presentation_code varchar_pattern_ops)
      "frontend_prescription_presentation_code_varchar_pattern_ops_idx" btree (presentation_code varchar_pattern_ops)
  
             Table "public.frontend_pct"
        Column       |          Type           | Modifiers
  -------------------+-------------------------+-----------
   code              | character varying(3)    | not null
   name              | character varying(200)  |
   org_type          | character varying(9)    | not null
  </code></pre>
  
  <p>And this is my query to get spending by all CCG organisations by month, on a particular <code>presentation_code</code>:</p>
  
  <pre><code>SELECT sum(frontend_prescription.actual_cost) as val, 
         frontend_prescription.pct_id as row_id, 
         frontend_prescription.processing_date as date, 
         frontend_pct.name as row_name 
  FROM frontend_prescription, frontend_pct 
  WHERE (presentation_code='0407041T0BBACAC') 
  AND frontend_prescription.pct_id=frontend_pct.code 
  AND frontend_pct.org_type='CCG' 
  GROUP BY frontend_prescription.pct_id, frontend_pct.code, date 
  ORDER BY date, row_id
  </code></pre>
  
  <p>Here are the results of an <code>EXPLAIN (ANALYSE, BUFFERS)</code> on this query: <a href="http://explain.depesz.com/s/YrR5" rel="nofollow">http://explain.depesz.com/s/YrR5</a></p>
  
  <p>It looks like the slow part is the bitmap heap scan on <code>frontend_prescription</code>. Is there any way to make this faster? In particular, I notice that it's looping 211 times (once for each <code>pct</code> found in the data).  </p>
  
  <p>The table has many millions of rows, so I suspect there isn't, but just wanted to check to see if there was anything obvious I could do.  </p>