Re: query with pg_trgm sometimes very slow

On Thu, Sep 3, 2015 at 6:19 PM, Claudio Freire <klaussfreire@gmail.com> wrote:
> On Wed, Sep 2, 2015 at 4:29 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
>> On Wed, Sep 2, 2015 at 7:00 AM, Volker Böhm <volker@vboehm.de> wrote:
>>>
>>>
>>>
>>> CREATE INDEX trgm_adresse ON adressen.adresse USING gist
>>> (normalize_string((btrim((((((((normalize_string((((COALESCE((vorname)::text,
>>> ''::text) || ' '::text) ||   (name1)::text))::character varying,
>>> (-1)))::text || ' '::text) || (normalize_string((COALESCE((strasse)::text,
>>> ''::text))::character varying, (-2)))::text) || ' '::text) || (plz)::text)
>>> || ' '::text) || (normalize_string((COALESCE((ort)::text,
>>> ''::text))::character varying, (-3)))::text)))::character varying)
>>> gist_trgm_ops);
>>
>>
>>
>> You might have better luck with gin_trgm_ops than gist_trgm_ops.  Have you
>> tried that?
>
>
> I just had the exact same problem, and indeed gin fares much better.

Also, with 9.5 we will see much better worst case performance from gin
via Jeff's patch:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=97f3014647a5bd570032abd2b809d3233003f13f

(I had to previously abandon pg_tgrm for a previous project and go
with solr; had this patch been in place that would not have happened)

merlin