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.html for explanation).
Hope this helped.
2011/9/26 Edson Carlos Ericksson Richter <richter@xxxxxxxxxxxxxx>
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 LtdaFone:
(51) 3366-7964
Celular:
(51) 8585-0796