Why isn't Postgres using my index?

Question

Here is my table (actually a materialized view), in Postgres 9.4:

  Materialized view "public.vw_presentation_summary"
      Column       |         Type          | Modifiers
-------------------+-----------------------+-----------
 processing_date   | date                  |
 presentation_code | character varying(15) |
 items             | numeric               |
 cost              | double precision      |
Indexes:
    "vw_idx_presentation_summary" btree (presentation_code)

I have just run VACUUM ANALYZE vw_presentation_summary, so the query planner should be up to date.

Now if I run explain (analyse, buffers) select * from vw_presentation_summary where presentation_code LIKE '0205051I0BB%' this is what I see:

                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Seq Scan on vw_presentation_summary  (cost=0.00..23202.16 rows=182 width=32) (actual time=0.440..222.383 rows=224 loops=1)
   Filter: ((presentation_code)::text ~~ '0205051I0BB%'::text)
   Rows Removed by Filter: 1115229
   Buffers: shared hit=9259
 Planning time: 0.760 ms
 Execution time: 222.524 ms
(6 rows)

Explain link: http://explain.depesz.com/s/nTL4

Why is this running a Seq Scan not an index lookup?

Relevant (I would even say a duplicate): stackoverflow.com/q/27435980/251311 — zerkms
– zerkms, Commented Jul 27, 2015 at 23:49

Richard · Accepted Answer · 2015-07-28 07:36:23Z

2

The LIKE operator can not be implemented with a regular index except when using the "C" locale, hence you get a sequential scan over all rows. What you need is a varchar_pattern_ops index on your column "presentation_code". So you should have an index like so:

CREATE INDEX "vw_idx_presentation_summary_vcops"
  ON "vw_presentation_summary" (presentation_code varchar_pattern_ops);

You could also consider a trigram index, although that would not be strictly necessary here.

edited Jul 28, 2015 at 7:36

Richard

66.1k136 gold badges359 silver badges573 bronze badges

answered Jul 27, 2015 at 23:34

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Richard Over a year ago

Thanks! For some reason I thought that my database was using the C locale, but on checking it isn't.

Andrey B · Accepted Answer · 2015-07-28 00:28:55Z

-1

There are only two reasons for a database not to use an index:

It can't (index is not eligible)
It doesn't want to (optimizer estimates that full scan will be faster)

Correction: I initially thought that this was the case of #2, but as another answer has explained, it is in fact #1.

edited Jul 28, 2015 at 0:28

answered Jul 27, 2015 at 22:53

Andrey B

1094 bronze badges

2 Comments

Patrick Over a year ago

At an approximate 1.11 million rows in the table, your suggestion is impossible.

Andrey B Over a year ago

oops, my bad. I was looking at "rows=182", but that's the output size estimate. I missed the "Rows Removed by Filter: 1115229" part.

Collectives™ on Stack Overflow

Why isn't Postgres using my index?

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related