On Sun, 18 Feb 2007, Guillaume Smet wrote:

Hi Oleg,

On 2/17/07, Oleg Bartunov <oleg@sai.msu.su> wrote:
3-gram is implemented as a contrib/pg_trgm. It currently uses GiST index,
but may be enhanced with the GiN.

As I'm facing the same problem, I've taken a look to pg_trgm. At the
moment, my opinion is quite mixed but perhaps I did something wrong.

I have a table (100k rows) with a location name in it generally
composed of several words but not that long. I created the index
directly on this column (ie I don't create a table with each word of
the location name). Then I tried a few queries.

Here is an example:

prod=# explain analyze select nomlieu from lieu where nomlieu ilike '%gaumont%';
                                           QUERY PLAN
Seq Scan on lieu  (cost=0.00..7230.20 rows=7 width=21) (actual
time=7.768..556.930 rows=39 loops=1)
 Filter: ((nomlieu)::text ~~* '%gaumont%'::text)
Total runtime: 557.066 ms
(3 rows)

_prod=# explain analyze select nomlieu from lieu where nomlieu % 'gaumont';
Bitmap Heap Scan on lieu  (cost=3.37..200.80 rows=106 width=21)
(actual time=689.799..690.035 rows=36 loops=1)
 Recheck Cond: ((nomlieu)::text % 'gaumont'::text)
 ->  Bitmap Index Scan on idx_lieu_nomlieu_trgm  (cost=0.00..3.37
rows=106 width=0) (actual time=689.749..689.749 rows=36 loops=1)
       Index Cond: ((nomlieu)::text % 'gaumont'::text)
Total runtime: 690.195 ms
(5 rows)

The trigram version is slower and doesn't return 3 results I should
have. The 3 results it doesn't return have the word gaumont in them at
the start of the string exactly like the others.

Is there anything I can do to improve the performances and investigate
why I don't have these 3 results?

pg_trgm was developed for spelling corrrection and there is a threshold of
similarity, which is 0.3 by default. Readme explains what does it means.

Similarity could be very low, since you didn't make separate column and length
of the full string is used to normalize similarity.

pg_trgm as is isn't well suited for wild card search, but the idea is there.

Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reply via email to