Re: Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?
От
Filip Rembiałkowski
Тема
Re: Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?
Дата
Msg-id
CAP_rww=LDnszcuie2eqSab2F4x+W+P0HZeL6AVncBzOXY9A5ew@mail.gmail.com
Ответ на
Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries? (Edson Carlos Ericksson Richter)
Список
Дерево обсуждения
Re: Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries? Harald Fuchs <hari.fuchs@gmail.com>
RES: Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries? "Edson Carlos Ericksson Richter" <richter@simkorp.com.br>
Re: Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries? Marti Raudsepp <marti@juffo.org>
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 по дате отправления
От: Guillaume Lelarge
Дата:
