From: | Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> |
---|---|
To: | Alexander Staubo <alex(at)purefiction(dot)net> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: text_pattern_ops index not being used for prefix query |
Date: | 2016-10-21 03:37:14 |
Message-ID: | CAK-MWwSpwRRQF_vFsYhFOnCmWkZ7CeGNcsuSmjM3LP-Zrqa5yg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, Oct 21, 2016 at 8:42 AM, Alexander Staubo <alex(at)purefiction(dot)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(at)postgresql(dot)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(dot)boguk(at)gmail(dot)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."
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Staubo | 2016-10-21 04:11:01 | Re: text_pattern_ops index not being used for prefix query |
Previous Message | Alexander Staubo | 2016-10-20 21:42:34 | text_pattern_ops index not being used for prefix query |