title: Speeding up a Postgres query on millions of rows?
body: <p>I am using Postgres 9.4. I have a large table. This is the structure of my table: </p>
  
  <pre><code> processing_date | date                 |
   practice_id     | character varying(6) |
   chemical_id     | character varying(9) |
   items           | bigint               |
   cost            | double precision     |
  Indexes:
      "vw_idx_chem_by_practice_chem_id" btree (chemical_id)
      "vw_idx_chem_by_practice_chem_id_vc" btree (chemical_id varchar_pattern_ops)
      "vw_idx_chem_by_practice_joint_id" btree (practice_id, chemical_id)
  </code></pre>
  
  <p>Now I want to run a LIKE query on the table. This is my query:</p>
  
  <pre><code>EXPLAIN (ANALYSE, BUFFERS) SELECT sum(pr.cost) as actual_cost, 
        sum(pr.items) as items, pr.practice_id as row_id, 
        pc.name as row_name, pr.processing_date as date 
  FROM vw_chemical_summary_by_practice pr 
  JOIN frontend_practice pc ON pr.practice_id=pc.code 
  WHERE (pr.chemical_id LIKE '0401%' ) 
  GROUP BY pr.practice_id, pc.code, date 
  ORDER BY date, pr.practice_id;
  </code></pre>
  
  <p>And this is the result of an EXPLAIN: <a href="http://explain.depesz.com/s/lYRT" rel="nofollow">http://explain.depesz.com/s/lYRT</a></p>
  
  <p>As you can see, it's slow partly because it's running a bitmap heap scan on nearly 4 million rows. (The subsequent sort is also slow.)</p>
  
  <p>Is there anything I can do to speed this up? </p>
  
  <p>I'm wondering if I should create a further materialized view, or whether a multicolumn index would help, so that Postgres can look in the index rather than disk. </p>
  
  <p>Is there some way I can also make the sort more efficient?</p>
  
  <p>UPDATE: Here's the definition of the materialized view:</p>
  
  <pre><code>    CREATE MATERIALIZED VIEW vw_chemical_summary_by_practice
      AS SELECT processing_date, practice_id, chemical_id, 
      SUM(total_items) AS items, SUM(actual_cost) AS cost
      FROM frontend_prescription
      GROUP BY processing_date, practice_id, chemical_id
  </code></pre>
  
  <p>And the underlying table:</p>
  
  <pre><code>id                | integer                 | not null default nextval('frontend_prescription_id_seq'::regclass)
   presentation_code | character varying(15)   | not null
   total_items       | integer                 | not null
   actual_cost       | double precision        | not null
   processing_date   | date                    | not null
   practice_id       | character varying(6)    | not null
  Indexes:
      "frontend_prescription_pkey" PRIMARY KEY, btree (id)
      "frontend_prescription_528f368c" btree (processing_date)
      "frontend_prescription_6ea07fe3" btree (practice_id)
      "frontend_prescription_idx_code" btree (presentation_code varchar_pattern_ops)
      "frontend_prescription_idx_date_and_code" btree (processing_date, presentation_code)
  </code></pre>
  
  <p>UPDATE 2: In case it wasn't clear, I need to get total spending and items by practice and by month, across all chemical_ids that start with `0401. </p>