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.
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."
"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."