CODEWITHSUNDEEP
sqlpostgresql
Richard
Richard

Reputation: 65530

Postgres seems to be using the wrong index?

I am using Postgres 9.4. This is my table:

                                       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
 total_items       | integer                 | not null
 processing_date   | date                    | not null
 practice_id       | character varying(6)    | not null
Indexes:
    "frontend_prescription_pkey" PRIMARY KEY, btree (id)
    "frontend_prescription_6ea07fe3" btree (practice_id)
    "frontend_prescription_by_practice" btree (presentation_code, practice_id)
    "frontend_prescription_by_practice_and_code" btree (practice_id, presentation_code varchar_pattern_ops)
    "frontend_prescription_idx_date_and_code" btree (processing_date, presentation_code)

This is my query:

EXPLAIN (analyse, verbose) 
SELECT SUM(total_items) AS items, SUM(total_items) AS numerator 
FROM frontend_prescription 
WHERE ((presentation_code LIKE '0601012Z0%') OR (presentation_code LIKE '0601012X0%') OR (presentation_code LIKE '0601012V0%'))  
AND (practice_id='A81001')  
AND (processing_date='2016-01-01')

This is the output:

 Aggregate  (cost=12.26..12.27 rows=1 width=4) (actual time=16898.277..16898.277 rows=1 loops=1)
   Output: sum(total_items), sum(total_items)
   ->  Index Scan using frontend_prescription_idx_date_and_code on public.frontend_prescription  (cost=0.57..12.26 rows=1 width=4) (actual time=9220.091..16898.251 rows=6 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
         Index Cond: (frontend_prescription.processing_date = '2016-01-01'::date)
         Filter: (((frontend_prescription.practice_id)::text = 'A81001'::text) AND (((frontend_prescription.presentation_code)::text ~~ '0601012Z0%'::text) OR ((frontend_prescription.presentation_code)::text ~~ '0601012X0%'::text) OR ((frontend_prescription.presentation_code)::text ~~ '0601012V0%'::text)))
         Rows Removed by Filter: 10036400
 Planning time: 6.054 ms
 Execution time: 16898.366 ms

Here is the link to the explain.

Could anyone suggest how I might speed these queries up? I don't really understand why Postgres is using the frontend_prescription_idx_date_and_code index, when the frontend_prescription_by_practice_and_code index has a varchar option which should make things faster.

Perhaps it would help if I made a three-column index?

Upvotes: 1

Views: 553

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

For this query:

SELECT SUM(total_items) AS items, SUM(total_items) AS numerator 
FROM frontend_prescription 
WHERE ((presentation_code LIKE '0601012Z0%') OR (presentation_code LIKE '0601012X0%') OR (presentation_code LIKE '0601012V0%')
       ) AND
       (practice_id = 'A81001')  AND
       (processing_date = '2016-01-01');

The best index is a composite index on frontend_prescription(practice_id, processing_date, presentation_code, total_items). The last column is not strictly necessary. It makes the index a covering index for the query; in other words, all the information needed for the query is in the index, so the data pages are not necessary.

The first two columns can be in either order because the where clause uses equality.

Upvotes: 1

Related Questions