Re: index refuses to build [finally SOLVED, but still some questions]

On Fri, 30 Dec 2011 00:10:01 +0100
"Jean-Yves F. Barbier" <12ukwn(at)gmail(dot)com> wrote:

Sooo, for those who are interested, this is how I did it:

I build my own function as:

CREATE FUNCTION erpunaccent(text) RETURNS text AS $$
SELECT unaccent($1);
$$ LANGUAGE sql IMMUTABLE;

indexed my test table with:

CREATE INDEX tst1m_name_lu_ix ON tst1m(lower(erpunaccent(name));

picked a known row:

SELECT * FROM tst1m WHERE id=33;
id | name | note
----+-------------------+-------------------------------------------
33 | oGvvÀtÖiÉsWMtWqma | CËÁANfKaáMàÑaiLd TtBGsCpwÉCKJrFëöTyPiTmèU
(1 ligne)

and launched the query:

SELECT * FROM tst1m WHERE lower(erpunaccent(name)) = 'ogvvatoieswmtwqma';
id | name | note
----+-------------------+-------------------------------------------
33 | oGvvÀtÖiÉsWMtWqma | CËÁANfKaáMàÑaiLd TtBGsCpwÉCKJrFëöTyPiTmèU
(1 ligne)

However, you must be very careful to use your function and not the
original one, otherwise the index' not used (it feels weird, as the
result's exactly the same! Tooom, whhhyyy??).

My function plan:

EXPLAIN ANALYZE SELECT * FROM tst1m WHERE lower(erpunaccent(name)) = 'ogvvatoieswmtwqma';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tst1m (cost=16.40..1210.37 rows=500 width=100) (actual time=0.093..0.094 rows=1 loops=1)
Recheck Cond: (lower(erpunaccent((name)::text)) = 'ogvvatoieswmtwqma'::text)
-> Bitmap Index Scan on tst1m_name_lu_key (cost=0.00..16.27 rows=500 width=0) (actual time=0.074..0.074 rows=1 loops=1)
Index Cond: (lower(erpunaccent((name)::text)) = 'ogvvatoieswmtwqma'::text)
Total runtime: 0.177 ms
(5 lignes)

Original function plan:

EXPLAIN ANALYZE SELECT * FROM tst1m WHERE lower(unaccent(name)) = 'ogvvatoieswmtwqma';
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Seq Scan on tst1m (cost=0.00..3367.02 rows=500 width=100) (actual time=0.466..1162.568 rows=1 loops=1)
Filter: (lower(unaccent((name)::text)) = 'ogvvatoieswmtwqma'::text)
Total runtime: 1162.656 ms
(3 lignes)

There's a drawback though: a research with LIKE is much slower on this
index than on the regular index despite the fact it uses the new
index and I can't understand why (!??)
Except if the index doesn't contain data but is recalculated on the fly?

LIKE research using new index:

EXPLAIN ANALYZE SELECT * FROM tst1m WHERE lower(erpunaccent(name)) LIKE 'ogvvatoies%';
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Seq Scan on tst1m (cost=0.00..28117.27 rows=500 width=100) (actual time=1.284..3569.742 rows=1 loops=1)
Filter: (lower(erpunaccent((name)::text)) ~~ 'ogvvatoies%'::text)
Total runtime: 3569.815 ms
(3 lignes)

LIKE research using normal index:

EXPLAIN ANALYZE SELECT * FROM tst1m WHERE name LIKE 'oGvvÀtÖiÉ%';
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Seq Scan on tst1m (cost=0.00..2867.01 rows=10 width=100) (actual time=0.071..140.336 rows=1 loops=1)
Filter: ((name)::text ~~ 'oGvvÀtÖiÉ%'::text)
Total runtime: 140.418 ms
(3 lignes)

If some are interested in file unaccent.rules I can post it here (*nix UTF-8)

JY
--
Sometimes you get an almost irresistible urge to go on living.