Re: index refuses to build [DEFINITELY SOLVED :-]

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>