On Fri, 30 Dec 2011 07:34:28 +0100
"Jean-Yves F. Barbier" <12ukwn@gmail.com> wrote:
Ok, I found the answer on postgresql.fr forum and here the mod'op:
* Delete my old function that used TEXT for I/O,
* Recreate it using VARCHAR for I/O,
(not mandatory, as explain talks about ::text and test show the
same results w/ either TEXT or VARCHAR),
* Delete the index,
* Recreate it with some specialization salt:
CREATE INDEX tst1m_name_lu_key ON tst1m(lower(jyunaccent(name)) varchar_pattern_ops);
* Retest:
EXPLAIN ANALYZE SELECT * FROM tst1m WHERE lower(erpunaccent(name)) LIKE 'ogvvatoie%';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tst1m (cost=17.90..1211.87 rows=500 width=100) (actual time=0.254..0.256 rows=1 loops=1)
Filter: (lower((erpunaccent(name))::text) ~~ 'ogvvatoie%'::text)
-> Bitmap Index Scan on tst1m_name_lu_key (cost=0.00..17.78 rows=500 width=0) (actual time=0.054..0.054 rows=1
loops=1)
Index Cond: ((lower((erpunaccent(name))::text) ~>=~ 'ogvvatoie'::text) AND (lower((erpunaccent(name))::text)
~<~'ogvvatoif'::text))
Total runtime: 0.338 ms
(5 lignes)
Which isn't bad on a table w/10,000 rows and a column randomly filled length [14-32].
Hehe.
--
I don't think it's worth washing hogs over.
-- Larry Wall in <199710060253.TAA09723@wall.org>
