RES: [GENERAL] Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?

[Edson Carlos Ericksson Richter]

Just discovered, I can’t use GIST over character varying...

 

Any other tips on how to optimize the query? Here are about 1,000,000 (one 
million) records in this table, table scan takes about 5 to 6 seconds on actual 
hardware (SAS, 1 Xeon, 2Gb memory on CentOS with all normal performance hacks).

 

select * from notafiscal where numeroctc like ‘POA%34345’;

 

 

Thanks,

 

Edson.

 

 

De: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] Em nome de Edson Carlos Ericksson 
Richter
Enviada em: segunda-feira, 26 de setembro de 2011 11:03
Para: pgsql-general@postgresql.org
Assunto: RES: [GENERAL] Does postgresql 9.0.4 use index on PREFIX%SUFFIX 
queries?

 

Thanks for the tip, unfortunately, split it does not work for me, since it’s a 
free text field, that users fill as they wish...

But looking most slow queries, this one pops up with more frequency...

 

So GIST would work; I’ll give a try.

 

Regards,

 

Edson.

 

 

 

De: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] Em nome de Gregg Jaskiewicz
Enviada em: segunda-feira, 26 de setembro de 2011 10:01
Para: Edson Carlos Ericksson Richter
Cc: pgsql-general@postgresql.org
Assunto: Re: [GENERAL] Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?

 

You can always store it divided in the database into two columns.  

Gist could also work for you. 

 

smime.p7s
Description: S/MIME cryptographic signature