Why postgresql does not use index in this query

Question

Here is the complete table definition:

CREATE TABLE search.tablename
(
  id integer NOT NULL,
  name character varying(300) NOT NULL,
  CONSTRAINT tablename_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

CREATE INDEX tablename_name_idx
  ON search.tablename
  USING btree
  (name COLLATE pg_catalog."default");

It has ~73k rows and was just vacuumed/analyzed by manual run.

What I cannot understand is why this trivial query

SELECT * 
  FROM "tablename" AS "arn" 
 WHERE arn.name LIKE 'foo%'

does not use the corresponding index.

The execution plan for this query is

Seq Scan on tablename arn  (cost=0.00..1626.96 rows=7 width=47) (actual time=8.682..8.682 rows=0 loops=1)
  Filter: ((name)::text ~~ 'foo%'::text)
  Rows Removed by Filter: 73197
Total runtime: 8.703 ms

Could anyone point to what I am missing?

UPD: changing column name type to text changes nothing.

UPD 2: name = 'foo' predicate expectedly uses index

Interesting. Can you try EXPLAIN ANALYZE for SELECT * FROM search.tablename WHERE name LIKE 'foo%' — Jayadevan
– Jayadevan, Commented Dec 12, 2014 at 3:31
@Jayadevan it's identical (it would surprise me even more if it wouldn't :-) — zerkms
– zerkms, Commented Dec 12, 2014 at 3:33
In one case you are specifying the schema name and in the other, you are not. I just wondered if the query was going against a different table altogether. — Jayadevan
– Jayadevan, Commented Dec 12, 2014 at 3:44
@Jayadevan I'm sure you will be curious to see the answer below :-) — zerkms
– zerkms, Commented Dec 12, 2014 at 4:00

Frank Heikens · Accepted Answer · 2014-12-12 08:28:49Z

3

Try adding varchar_pattern_ops to your index

CREATE INDEX tablename_name_idx
  ON search.tablename
  USING btree
  (name COLLATE pg_catalog."default" varchar_pattern_ops);

The operator classes text_pattern_ops, varchar_pattern_ops, bpchar_pattern_ops, and name_pattern_ops support B-tree indexes on the types text, varchar, char, and name, respectively. The difference from the default operator classes is that the values are compared strictly character by character rather than according to the locale-specific collation rules. This makes these operator classes suitable for use by queries involving pattern matching expressions (LIKE or POSIX regular expressions) when the server does not use the standard "C" locale.

edited Dec 12, 2014 at 8:28

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

answered Dec 12, 2014 at 3:47

FuzzyTree

32.4k3 gold badges58 silver badges87 bronze badges

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

6 Comments

zerkms Over a year ago

That's it. One of the chapters or postgresql manual that I skipped due to "I will never do such a weird things". Does it mean one must always use LC_COLLATE and LC_CTYPE = C? I've tried to make a research on them several times but failed to get a clear understanding.

FuzzyTree Over a year ago

I'm not sure what the best practice is regarding using the C locale. Personally I use en_US.UTF-8 along with the _pattern_ops classes

user533832 Over a year ago

@zerkms definitely don't change your locale for this - use _pattern_ops as FuzzyTree says

user533832 Over a year ago

@zerkms I was confusing database encoding with locale - and you are right, Erwin is great. Please read this entire thread and make up your own mind: postgresql.nabble.com/… but basically it comes down to the question of what sorting you prefer to see by default.

user533832 Over a year ago

@zerkms please see Erwin's reply to my comment on the question you linked to: dba.stackexchange.com/questions/46408/… FuzzyTree sorry to spam you.

|

Collectives™ on Stack Overflow

Why postgresql does not use index in this query

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related