Re: Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?

On Mon, Sep 26, 2011 at 15:16, Edson Carlos Ericksson Richter
<richter@simkorp.com.br> wrote:
> select * from notafiscal where numeroctc like ‘POA%34345’;
>
> Prefix is normally 3 characters, suffix varyies.
>
> Is Postgresql 9.0.4 able to use an BTREE index on notafiscal.numeroctc to execute this query?

As mentioned by other posters, you should use a btree index with
text_pattern_ops opclass to speed up this query.

For queries like these, it's often faster to match the text in
*reverse*. You can create two indexes like this:

create index on foobar (txt text_pattern_ops);
create index on foobar (reverse(txt) text_pattern_ops);

And then write your queries like this:
SELECT * FROM foobar
WHERE txt like 'POA%34345'
  AND reverse(txt) like reverse('POA%34345');

PostgreSQL will automatically choose one or both indexes for executing
this query.

Regards,
Marti