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
