text_pattern_ops index not being used for prefix query

From: Alexander Staubo <alex(at)purefiction(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: text_pattern_ops index not being used for prefix query
Date: 2016-10-20 21:42:34
Message-ID: 8C473FB8-9487-496D-9747-B4964CB3BD4C@purefiction.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Indexing on a text column:

create index index_documents_on_id_with_pattern_ops (id text_pattern_ops);

This works fine:

> explain select id from documents where id like 'dingbat%';
Index Only Scan using index_documents_on_id_with_pattern_ops on documents (cost=0.56..8.58 rows=736 width=19)
Index Cond: ((id >= 'dingbat'::text) AND (id < 'dingbau'::text))
Filter: (id ~~ 'dingbat%'::text)

But for some reason, if an underscore character appears in my search string, it falls back to a disasterously slow seqscan:

> explain select id from documents where id like '_dingbat%';
Seq Scan on documents (cost=0.00..779238.28 rows=736 width=19)
Filter: (id ~~ '_dingbat%'::text)

Is this because of PostgreSQL’s collation system? Using “C” doesn’t work either.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Maxim Boguk 2016-10-21 03:37:14 Re: text_pattern_ops index not being used for prefix query
Previous Message David G. Johnston 2016-10-20 21:37:55 Re: Showing matrix with single query