DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=PmB0Qq7vAFmzIzfSeCbymAGwSwuMiiHEkDACr1gl30+F3guKp3XIgRZ2edhnk4C4numvAVv+dfiiuKOk+8N5aQRa41x6Fm1pRBk09LrdEJGvvDUBbAQANwzNw8xq07cbRIe66RADHT61u/+iTfMWae1t64wflaRqbg/jk+WWGAI=
Message-ID: <1d4e0c10702260442m1fba9dc4r30ada1044f869a3b@mail.gmail.com>
Date: Mon, 26 Feb 2007 13:42:40 +0100
From: "Guillaume Smet" <guillaume.smet@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: pg_trgm performance
In-Reply-To: <20070224100737.GA13557@uio.no>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <82mz4k4mt7.fsf@mid.bfk.de> <20070115125834.GA28248@uio.no>
	<1d4e0c10702231509k7ad24e2ck3a149a2e3a2899f7@mail.gmail.com>
	<20070224003105.GA11010@uio.no>
	<1d4e0c10702231704y46270252o494b5d6180d7574f@mail.gmail.com>
	<20070224100737.GA13557@uio.no>

On 2/24/07, Steinar H. Gunderson <sgunderson@bigfoot.com> wrote:

Thanks for your time.

> GiN version, short:
>    ->  Bitmap Heap Scan on tags  (cost=3D8.64..151.79 rows=3D41 width=3D0=
) (actual time=3D5.555..30.157 rows=3D7 loops=3D1)
>          Filter: (title % 'foo'::text)
>          ->  Bitmap Index Scan on trgm_idx  (cost=3D0.00..8.63 rows=3D41 =
width=3D0) (actual time=3D2.857..2.857 rows=3D5555 loops=3D1)
>                Index Cond: (title % 'foo'::text)

This is currently the worst case in the gist - gin comparison because
in the index scan, gin version doesn't have the length of the indexed
string. So it returns a lot of rows which have every trigram of your
search string but has in fact a low similarity due to the length of
the indexed string (5555 rows -> 7 rows).
It cannot be fixed at the moment due to the way GIN indexes work.

> So, the GiN version seems to be a bit faster for long queries, but it's s=
till
> too slow -- in fact, _unindexed_ versions give 141ms, 342ms, 725ms for th=
ese
> three queries, so for the longer queries, the gain is only about a factor
> two. (By the way, I would like to stress that this is not my personal mus=
ic
> collection! :-P)

The fact is that pg_trgm is designed to index words and not to index
long sentences. I'm not that surprised it's slow in your case.

It's also my case but following the instructions in README.pg_trgm I
created a dictionary of words using tsearch2 (stat function) and I use
pg_trgm on this dictionary to find similar words in my dictionary.

For example, I rewrite the search:
auberge cevenes
as:
(auberge | auberges | aubberge | auberg) & (ceven | cene | cevenol | cevenn=
es)
using pg_trgm and my query can find Auberge des C=E9vennes (currently
it's limited to the 4th most similar words but I can change it
easily).

--
Guillaume