Оптимизация запроса сходства postgres (pg_trgm + индекс джина)

Я определил следующий индекс:

CREATE INDEX
    users_search_idx
ON
    auth_user
USING
    gin(
        username gin_trgm_ops,
        first_name gin_trgm_ops,
        last_name gin_trgm_ops
    );

Я выполняю следующий запрос:

PREPARE user_search (TEXT, INT) AS
    SELECT
        username,
        email,
        first_name,
        last_name,
        ( -- would probably do per-field weightings here
            s_username + s_first_name + s_last_name
        ) rank
    FROM
        auth_user,
        similarity(username, $1) s_username,
        similarity(first_name, $1) s_first_name,
        similarity(last_name, $1) s_last_name
    WHERE
        username % $1 OR
        first_name % $1 OR
        last_name % $1
    ORDER BY
        rank DESC
    LIMIT $2;

auth_user Таблица имеет 6,2 миллиона строк.

Скорость запроса, по-видимому, очень сильно зависит от количества результатов, потенциально возвращаемых similarity запрос.

Увеличение порога подобия с помощью set_limit помогает, но снижает полезность результатов, устраняя частичные совпадения.

Некоторые поиски возвращаются через 200 мс, другие занимают ~ 10 секунд.

У нас есть существующая реализация этой функции, использующая Elasticsearch, которая возвращает < 200 мс для любого запроса, в то же время выполняя более сложное (лучшее) ранжирование.

Я хотел бы знать, есть ли способ улучшить это, чтобы получить более стабильную производительность?

Насколько я понимаю, индекс GIN (инвертированный индекс) - это тот же базовый подход, который используется Elasticsearch, поэтому я бы подумал, что возможна некоторая оптимизация.

EXPLAIN ANALYZE EXECUTE user_search('mel', 20) показывает:

Limit  (cost=54099.81..54099.86 rows=20 width=52) (actual time=10302.092..10302.104 rows=20 loops=1)
  ->  Sort  (cost=54099.81..54146.66 rows=18739 width=52) (actual time=10302.091..10302.095 rows=20 loops=1)
        Sort Key: (((s_username.s_username + s_first_name.s_first_name) + s_last_name.s_last_name)) DESC
        Sort Method: top-N heapsort  Memory: 26kB
        ->  Nested Loop  (cost=382.74..53601.17 rows=18739 width=52) (actual time=118.164..10293.765 rows=8380 loops=1)
              ->  Nested Loop  (cost=382.74..53132.69 rows=18739 width=56) (actual time=118.150..10262.804 rows=8380 loops=1)
                    ->  Nested Loop  (cost=382.74..52757.91 rows=18739 width=52) (actual time=118.142..10233.990 rows=8380 loops=1)
                          ->  Bitmap Heap Scan on auth_user  (cost=382.74..52383.13 rows=18739 width=48) (actual time=118.128..10186.816 rows=8380loops=1)"
                                Recheck Cond: (((username)::text % 'mel'::text) OR ((first_name)::text % 'mel'::text) OR ((last_name)::text %'mel'::text))"
                                Rows Removed by Index Recheck: 2434523
                                Heap Blocks: exact=49337 lossy=53104
                                ->  BitmapOr  (cost=382.74..382.74 rows=18757 width=0) (actual time=107.436..107.436 rows=0 loops=1)
                                      ->  Bitmap Index Scan on users_search_idx  (cost=0.00..122.89 rows=6252 width=0) (actual time=40.200..40.200rows=88908 loops=1)"
                                            Index Cond: ((username)::text % 'mel'::text)
                                      ->  Bitmap Index Scan on users_search_idx  (cost=0.00..122.89 rows=6252 width=0) (actual time=43.847..43.847rows=102028 loops=1)"
                                            Index Cond: ((first_name)::text % 'mel'::text)
                                      ->  Bitmap Index Scan on users_search_idx  (cost=0.00..122.89 rows=6252 width=0) (actual time=23.387..23.387rows=58740 loops=1)"
                                            Index Cond: ((last_name)::text % 'mel'::text)
                          ->  Function Scan on similarity s_username  (cost=0.00..0.01 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=8380)
                    ->  Function Scan on similarity s_first_name  (cost=0.00..0.01 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=8380)
              ->  Function Scan on similarity s_last_name  (cost=0.00..0.01 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=8380)
Execution time: 10302.559 ms

Сервер Postgres 9.6.1 работает на Amazon RDS

Обновить

1.

Вскоре после публикации вопроса я нашел эту информацию: https://www.postgresql.org/message-id/464F3C5D.2000700@enterprisedb.com

Так я попробовал

-> SHOW work_mem;
4MB
-> SET work_mem='12MB';
-> EXECUTE user_search('mel', 20);
(results returned in ~1.5s)

Это сделало большое улучшение (ранее> 10 с)!

1.5s все еще намного медленнее, чем ES для аналогичного запроса, поэтому я все же хотел бы услышать любые предложения по оптимизации запроса.

2.

В ответ на комментарии и после просмотра этого вопроса ( индекс gg Postgresql медленнее, чем GIST для pg_trgm), я попробовал точно такую ​​же настройку с индексом GIST вместо индекса GIN.

Пытаясь выполнить тот же поиск выше, он вернулся через ~3.5 с, используя значение по умолчанию work_mem='4MB', Увеличение work_mem не имеет значения.

Из этого я делаю вывод, что индекс GIST более эффективен для памяти (не затрагивал патологический случай, как это сделал GIN), но медленнее, чем GIN, когда GIN работает должным образом. Это соответствует тому, что описано в документации, рекомендующей индекс GIN.

3.

Я до сих пор не понимаю, почему так много времени тратится на:

 ->  Bitmap Heap Scan on auth_user  (cost=382.74..52383.13 rows=18739 width=48) (actual time=118.128..10186.816 rows=8380loops=1)"
     Recheck Cond: (((username)::text % 'mel'::text) OR ((first_name)::text % 'mel'::text) OR ((last_name)::text %'mel'::text))"
     Rows Removed by Index Recheck: 2434523
     Heap Blocks: exact=49337 lossy=53104

Я не понимаю, зачем нужен этот шаг или что он делает.

Есть три Bitmap Index Scan под ним для каждого из username % $1 пункты... эти результаты затем объединяются с BitmapOr шаг. Эти части все довольно быстро.

Но даже в том случае, когда у нас не заканчивается работа, мы все равно тратим почти целую секунду Bitmap Heap Scan,

Источник

1 ответ

Я ожидаю гораздо более быстрых результатов с таким подходом:

1.

Создайте индекс GiST с 1 столбцом, содержащим объединенные значения:

CREATE INDEX users_search_idx ON auth_user
USING gist((username || ' ' || first_name || ' ' || last_name) gist_trgm_ops);

Это предполагает, что все 3 столбца должны быть определены как NOT NULL (вы не указали). Иначе вам нужно сделать больше.
Почему бы не упростить с concat_ws()?

2.

Используйте правильный запрос ближайшего соседа, соответствующий вышеуказанному индексу:

SELECT username, email, first_name, last_name
     , similarity(username  , $1) AS s_username
     , similarity(first_name, $1) AS s_first_name
     , similarity(last_name , $1) AS s_last_name
     , row_number() OVER () AS rank  -- greatest similarity first
FROM   auth_user
WHERE     (username || ' ' || first_name || ' ' || last_name) %   $1  -- !!
ORDER  BY (username || ' ' || first_name || ' ' || last_name) <-> $1  -- !!
LIMIT  $2;

Выражения в WHERE а также ORDER BY должно соответствовать выражению индекса!

Особенно ORDER BY rank (как у вас это было) всегда будет плохо работать для маленького LIMIT выбор из гораздо большего пула подходящих строк, потому что он не может напрямую использовать индекс: сложное выражение, стоящее за rank должен быть рассчитан для каждой подходящей строки, затем все должны быть отсортированы, прежде чем будет возвращен небольшой выбор лучших совпадений. Это намного, намного дороже, чем настоящий запрос ближайшего соседа, который может получить лучшие результаты из индекса напрямую, даже не глядя на остальные.

row_number() с пустым определением окна просто отражает порядок, произведенный ORDER BY того же самого SELECT,

Связанные ответы:

Что касается вашего товара 3.Я добавил ответ на вопрос, на который вы ссылаетесь, который должен это объяснить:

Источник
Другие вопросы по тегам