Re: query with pg_trgm sometimes very slow

On Wed, Sep 2, 2015 at 7:00 AM, Volker Böhm <volker(at)vboehm(dot)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?

...

> When such a slow query is running, 'top' shows nearly '100 % wait' and
> 'iotop' shows 3 - 8 MB/sec disk read by a process
> postgres: vb vb 10.128.96.25(60435) FETCH
>
> Also the postgres log, which was told to log every task longer than 5000
> ms, shows
>
> 2015-09-02 13:44:48 CEST [25237-1] vb(at)vb LOG: duration: 55817.191
> ms execute <unnamed>: FETCH FORWARD 4096 IN "py:0xa2d61f6c"
>
> Since I never used a FETCH command in my life, this must be used by
> pg_trgm or something inside it (gin, gist etc.)
>

The FETCH is probably being automatically added by whatever python library
you are use to talk to PostgreSQL. Are you using a named cursor in
python? In any event, that is not the cause of the problem.

Can you get the result of the indexed expression for a query that is slow?