Thread: text_pattern_ops index not being used for prefix query

text_pattern_ops index not being used for prefix query

From
Alexander Staubo
Date:
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.



Re: text_pattern_ops index not being used for prefix query

From
Maxim Boguk
Date:


On Fri, Oct 21, 2016 at 8:42 AM, Alexander Staubo <alex@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.boguk@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."


Re: text_pattern_ops index not being used for prefix query

From
Alexander Staubo
Date:
On 20 Oct 2016, at 23:37, Maxim Boguk <maxim.boguk@gmail.com> wrote:
> ​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
respectivecharacter in pattern must be preceded by the escape character.” 

Oops, how embarrassing. For some reason I thought PostgreSQL only had "%". Thanks for clearing that up.