Postgres Pro
Downloads
Home   >   mailing lists

Re: Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries? - Mailing list pgsql-general

From Filip Rembiałkowski
Subject Re: Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?
Date
Msg-id CAP_rww=LDnszcuie2eqSab2F4x+W+P0HZeL6AVncBzOXY9A5ew@mail.gmail.com
Whole thread Raw
In response to Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?  ("Edson Carlos Ericksson Richter" <richter@simkorp.com.br>)
List pgsql-general
Tree view
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.



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 Ltda

Fone:

(51) 3366-7964

Celular:

(51) 8585-0796

Embedded Image

www.simkorp.com.br

 


pgsql-general by date:

Previous
From: Guillaume Lelarge
Date:
Subject: Re: "all" not inclusive of "replication" in pg_hba.conf
Next
From: Merlin Moncure
Date:
Subject: Re: "all" not inclusive of "replication" in pg_hba.conf