1

I am using Postgres 9.4. This is my table:

 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
 quantity          | 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_5283da53" btree (pct_id)
    "frontend_prescription_528f368c" btree (processing_date)
    "frontend_prescription_cost_by_pres_code" btree (presentation_code, pct_id, actual_cost)

There are about 450m rows in the table. I have just run VACUUM ANALYZE, so the query planner should be up to date.

Here is my query:

EXPLAIN (ANALYZE, BUFFERS)
  SELECT SUM(actual_cost) AS cost, 
    pct_id AS id, processing_date 
  FROM frontend_prescription 
  WHERE presentation_code='0501013B0AAAAAA' 
  GROUP BY pct_id, processing_date;

And here is the output:

 HashAggregate  (cost=1537066.69..1537066.87 rows=18 width=16) (actual time=189604.446..189608.532 rows=9725 loops=1)
   Group Key: pct_id, processing_date
   Buffers: shared hit=17 read=418783
   ->  Bitmap Heap Scan on frontend_prescription  (cost=14473.73..1533678.60 rows=451746 width=16) (actual time=543.825..187857.510 rows=416328 loops=1)
         Recheck Cond: ((presentation_code)::text = '0501013B0AAAAAA'::text)
         Heap Blocks: exact=416287
         Buffers: shared hit=17 read=418783
         ->  Bitmap Index Scan on frontend_prescription_cost_by_pres_code  (cost=0.00..14360.79 rows=451746 width=0) (actual time=317.102..317.102 rows=416328 loops=1)
               Index Cond: ((presentation_code)::text = '0501013B0AAAAAA'::text)
               Buffers: shared hit=2 read=2511
 Planning time: 0.329 ms
 Execution time: 189610.440 ms

Why does Postgres need to do a heap scan? Isn't all the information needed for the query in the composite frontend_prescription_cost_by_pres_code index?

Readable version of the output: http://explain.depesz.com/s/oV4

Improve this question
2
  • It's not doing an index scan, it's doing a bitmap index scan. Different thing. It scans the index, building a bitmap of pages that are likely to contain desired tuples. Then it scans the heap using that bitmap, fetching only the wanted pages, and re-checks each row on the fetched pages to filter out unrelated rows.
    – Craig Ringer
    Commented Jun 12, 2015 at 23:56
  • This is often a good choice where a seqscan would read too much data, but there are too many likely matches to make a pure index scan worthwhile due to random I/O costs.
    – Craig Ringer
    Commented Jun 12, 2015 at 23:56

1 Answer 1

Reset to default
2

processing_date is not in the index, so it cannot use an index only scan.

Create the index on (presentation_code, pct_id, processing_date, actual_cost) so that it can use an index only scan, and in that order so that it can efficiently use the sort order of the index to do the grouping.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.