> -----Mensagem original----- > De: [email protected] [mailto:pgsql-general- > [email protected]] Em nome de Marti Raudsepp > Enviada em: segunda-feira, 26 de setembro de 2011 17:42 > Para: Edson Carlos Ericksson Richter > Cc: [email protected] > Assunto: Re: [GENERAL] Does postgresql 9.0.4 use index on PREFIX%SUFFIX > queries? > > On Mon, Sep 26, 2011 at 15:16, Edson Carlos Ericksson Richter > <[email protected]> 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);
I got the following error:
ERROR: function reverse(text) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to
add explicit type casts.
Character: 29
>
> 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
>
> --
> Sent via pgsql-general mailing list ([email protected]) To make
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
smime.p7s
Description: S/MIME cryptographic signature
