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

From: "Edson Carlos Ericksson Richter" <richter(at)simkorp(dot)com(dot)br>
To: <pgsql-general(at)postgresql(dot)org>
Subject: RES: Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?
Date: 2011-09-26 22:43:50
Message-ID: 002701cc7c9d$c36be6c0$4a43b440$@com.br
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

> -----Mensagem original-----
> De: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] Em nome de Marti Raudsepp
> Enviada em: segunda-feira, 26 de setembro de 2011 17:42
> Para: Edson Carlos Ericksson Richter
> Cc: pgsql-general(at)postgresql(dot)org
> 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
> <richter(at)simkorp(dot)com(dot)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);

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 (pgsql-general(at)postgresql(dot)org) To make
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message richter 2011-09-26 22:52:17 RES: Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?
Previous Message Marti Raudsepp 2011-09-26 20:41:36 Re: Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?