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

Edson,

1. PostgreSQL IS able to use btree index to execute this query.
More generally, it is able to use btree index for all PREFIX search.

2. You will need a special (NOT spatial) index for it
CREATE INDEX notafiscal_numeroctc_tpo_idx ON notafiscal (numeroctc
text_pattern_ops);
( see http://www.postgresql.org/docs/9.0/interactive/indexes-opclass.htmlfor
explanation).

Hope this helped.

2011/9/26 Edson Carlos Ericksson Richter <richter(at)simkorp(dot)com(dot)br>

> Dear experts,****
>
> ** **
>
> I have the following query:****
>
> ** **
>
> 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?****
>
> ** **
>
> Should I create GIST index or something else to speed up the query?****
>
> ** **
>
> ** **
>
> Thanks,****
>
> ** **
>
> *Edson Carlos Ericksson Richter*
> *SimKorp Infomática Ltda *****
>
> Fone:****
>
> (51) 3366-7964 ****
>
> Celular:****
>
> (51) 8585-0796****
>
> [image: Embedded Image]****
>
> www.simkorp.com.br****
>
> ** **
>