Re: query with pg_trgm sometimes very slow

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: query with pg_trgm sometimes very slow
Дата
Msg-id CAMkU=1xN4M6UVFp39nceYEQE0JULFO42QiPcwkgtAFsey+5RLQ@mail.gmail.com
обсуждение исходный текст
Ответ на query with pg_trgm sometimes very slow  (Volker Böhm <volker@vboehm.de>)
Ответы Re: query with pg_trgm sometimes very slow  (Claudio Freire <klaussfreire@gmail.com>)
Список pgsql-performance
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?

...


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@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?

В списке pgsql-performance по дате отправления:

Предыдущее
От: Volker Böhm
Дата:
Сообщение: query with pg_trgm sometimes very slow
Следующее
От: Jean Cavallo
Дата:
Сообщение: Server slowing down over time