Skip site navigation (1) Skip section navigation (2)

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

From: "Jean-Yves F(dot) Barbier" <12ukwn(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: index refuses to build [DEFINITELY SOLVED :-]
Date: 2011-12-30 16:06:13
Message-ID: 20111230170613.71f77850@anubis.defcon1 (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-novice
On Fri, 30 Dec 2011 07:34:28 +0100
"Jean-Yves F. Barbier" <12ukwn(at)gmail(dot)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(dot)TAA09723(at)wall(dot)org>

In response to

pgsql-novice by date

Next:From: Jean-Yves F. BarbierDate: 2011-12-30 18:20:27
Subject: strange order by
Previous:From: raghu ramDate: 2011-12-30 15:40:35
Subject: Re: thanks

pgsql-hackers by date

Next:From: Tom LaneDate: 2011-12-30 16:23:14
Subject: Re: spinlocks on powerpc
Previous:From: Aidan Van DykDate: 2011-12-30 14:44:09
Subject: Re: 16-bit page checksums for 9.2

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group