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.html for explanation).
Hope this helped.
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@simkorp.com.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 LtdaFone:
(51) 3366-7964
Celular:
(51) 8585-0796
В списке pgsql-general по дате отправления: