On Fri, Oct 21, 2016 at 8:42 AM, Alexander Staubo <a...@purefiction.net> wrote:
> 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. > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > Underscore in like pattern have a special meaning of "any symbol". >From documentation on the https://www.postgresql.org/docs/9.4/static/functions-matching.html : "An underscore (_) in pattern stands for (matches) any single character;" This could be useful as well: "To match a literal underscore or percent sign without matching other characters, the respective character in pattern must be preceded by the escape character." -- Maxim Boguk Senior Postgresql DBA http://www.postgresql-consulting.com/ Phone RU: +7 910 405 4718 Phone AU: +61 45 218 5678 LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b Skype: maxim.boguk Jabber: maxim.bo...@gmail.com "People problems are solved with people. If people cannot solve the problem, try technology. People will then wish they'd listened at the first stage."
