From: | "Edson Carlos Ericksson Richter" <richter(at)simkorp(dot)com(dot)br> |
---|---|
To: | "'Edson Carlos Ericksson Richter'" <richter(at)simkorp(dot)com(dot)br>, <pgsql-general(at)postgresql(dot)org> |
Subject: | RES: Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries? |
Date: | 2011-09-26 15:15:13 |
Message-ID: | 007501cc7c5f$174e8160$45eb8420$@com.br |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] Em nome de Edson Carlos Ericksson Richter
Enviada em: segunda-feira, 26 de setembro de 2011 11:03
Para: pgsql-general(at)postgresql(dot)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-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)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(at)postgresql(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Gregg Jaskiewicz | 2011-09-26 15:27:37 | Re: Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries? |
Previous Message | Albe Laurenz | 2011-09-26 14:59:37 | Re: : PostgreSQL Online Backup |