Полнотекстовый поиск в PostgreSQL: почему поиск такой медленный?

У меня есть небольшая база данных PostgreSQL (~~3000 строк).

Я пытаюсь настроить полнотекстовый поиск в одном из его текстовых полей («тело»).

Проблема в том, что любой запрос очень медленный (35+ секунд!!!).

Я предполагаю, что проблема связана с тем, что БД выбирает режим последовательного сканирования...

Это мой запрос:

    SELECT
        ts_rank_cd(to_tsvector('italian', body), query),
        ts_headline('italian', body, to_tsquery('torino')),
        title,
        location,
        id_author
    FROM
        fulltextsearch.documents, to_tsquery('torino') as query
    WHERE
        (body_tsvector @@ query)
    OFFSET
        0

Это ОБЪЯСНЕНИЕ АНАЛИЗ:

                                      QUERY PLAN                                    
----------------------------------------------------------------------------------------------------------------------------
Limit  (cost=0.00..1129.81 rows=19 width=468) (actual time=74.059..13630.114 rows=863 loops=1)
->  Nested Loop  (cost=0.00..1129.81 rows=19 width=468) (actual time=74.056..13629.342 rows=863 loops=1)
     Join Filter: (documents.body_tsvector @@ query.query)
     ->  Function Scan on to_tsquery query  (cost=0.00..0.01 rows=1 width=32) (actual time=4.606..4.608 rows=1 loops=1)
     ->  Seq Scan on documents  (cost=0.00..1082.09 rows=3809 width=591) (actual time=0.045..48.072 rows=3809 loops=1)
Total runtime: 13630.720 ms

Это моя таблица:

mydb=# \d+ fulltextsearch.documents;
                                              Table "fulltextsearch.documents"
    Column     |       Type        |                               Modifiers                               | Storage  | Description
---------------+-------------------+-----------------------------------------------------------------------+----------+-------------
 id            | integer           | not null default nextval('fulltextsearch.documents_id_seq'::regclass) | plain    |
 id_author     | integer           |                                                                       | plain    |
 body          | character varying |                                                                       | extended |
 title         | character varying |                                                                       | extended |
 location      | character varying |                                                                       | extended |
 date_creation | date              |                                                                       | plain    |
 body_tsvector | tsvector          |                                                                       | extended |
Indexes:
    "fulltextsearch_documents_tsvector_idx" gin (to_tsvector('italian'::regconfig,     COALESCE(body, ''::character varying)::text))
    "id_idx" btree (id)
Triggers:
    body_tsvectorupdate BEFORE INSERT OR UPDATE ON fulltextsearch.documents FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger('body_tsvector', 'pg_catalog.italian', 'body')
Has OIDs: no

Я уверен, что упускаю что-то очевидное....

Любые подсказки?

.

.

.

=== ОБНОВЛЕНИЕ ======================================= =================================

Благодаря вашим предложениям я придумал этот (лучший) запрос:

SELECT
    ts_rank(body_tsvector, query),
    ts_headline('italian', body, query),
    title,
    location
FROM
    fulltextsearch.documents, to_tsquery('italian', 'torino') as query
WHERE
    to_tsvector('italian', coalesce(body,'')) @@ query

что намного лучше, но всегда очень медленно (13+ секунд...).

Я заметил, что закомментировав строку "ts_headline()", запрос выполняется молниеносно.

Это EXPLAIN ANALYZE, который, наконец, использует индекс, но мне это мало помогает...:

EXPLAIN ANALYZE SELECT
clock_timestamp() - statement_timestamp() as elapsed_time,
    ts_rank(body_tsvector, query),
    ts_headline('italian', body, query),
    title,
    location
FROM
    fulltextsearch.documents, to_tsquery('italian', 'torino') as query
WHERE
    to_tsvector('italian', coalesce(body,'')) @@ query

 Nested Loop  (cost=16.15..85.04 rows=19 width=605) (actual time=102.290..13392.161 rows=863 loops=1)
   ->  Function Scan on query  (cost=0.00..0.01 rows=1 width=32) (actual time=0.008..0.009 rows=1 loops=1)
   ->  Bitmap Heap Scan on documents  (cost=16.15..84.65 rows=19 width=573) (actual time=0.381..4.236 rows=863 loops=1)
         Recheck Cond: (to_tsvector('italian'::regconfig, (COALESCE(body, ''::character varying))::text) @@ query.query)
         ->  Bitmap Index Scan on fulltextsearch_documents_tsvector_idx  (cost=0.00..16.15 rows=19 width=0) (actual time=0.312..0.312 rows=863 loops=1)
               Index Cond: (to_tsvector('italian'::regconfig, (COALESCE(body, ''::character varying))::text) @@ query.query)
 Total runtime: 13392.717 ms

postgresql full-text-search full-text-indexing
person MarcoS    schedule 18.04.2013    source источник

Ответы (3)


arrow_upward
5
arrow_downward

Вам не хватает двух (достаточно очевидных) вещей:

1 Вы установили 'italian' в своем to_tsvector(), но не указали его в to_tsquery()

Держите оба последовательными.

2 Вы проиндексировали COALESCE(body, ...), но это не то, что вы ищете.

Планировщик не волшебство — вы можете использовать индекс, только если это то, что вы ищете.

person Richard Huxton    schedule 18.04.2013
comment
Планировщик прост и консервативен в выборе индекса. Он не будет искать язык tsearch2 по умолчанию, чтобы узнать, является ли он italian, а затем обрабатывать версию без указания языка так же, как и версию с указанием языка как итальянского, необходимо, чтобы оба вызова функций были одинаковыми. На самом деле, в этом отношении планировщик может быть немного тусклым — в прошлый раз, когда я проверял, было довольно много простых вариантов написания выражения — например, избыточные скобки — из-за которых планировщик не распознавал, что оно соответствует индексу. . - person Craig Ringer; 19.04.2013
comment
@ Ричард Хакстон Спасибо за ваш ответ. Под поиском вы имеете в виду предложение FROM? Если да, не могли бы вы опубликовать образец правильного предложения FROM для моей ситуации? Извините, я еще совсем запутался... - person MarcoS; 19.04.2013
comment
postgresql.org/docs/current/static/ ts_headline использует исходный документ, а не сводку tsvector, поэтому он может быть медленным и его следует использовать с осторожностью... - person Richard Huxton; 19.04.2013

arrow_upward
2
arrow_downward

Наконец, с помощью ваших ответов и комментариев, а также с некоторыми поисками в Google, я решил, запустив ts_headline() (очень тяжелая функция, я полагаю) на подмножестве полного набора результатов (интересующая меня страница результатов в):

    SELECT
        id,
        ts_headline('italian', body, to_tsquery('italian', 'torino')) as headline,
        rank,
        title,
        location
    FROM (
        SELECT
            id,
            body,
            title,
            location,
            ts_rank(body_tsvector, query) as rank
        FROM
            fulltextsearch.documents, to_tsquery('italian', 'torino') as query
        WHERE
            to_tsvector('italian', coalesce(body,'')) @@ query
        LIMIT 10
        OFFSET 0
    ) as s
person MarcoS    schedule 19.04.2013

arrow_upward
0
arrow_downward

Я решил проблему, предварительно вычислив ts_rank_cd и сохранив его в таблице популярных терминов (высоких вхождений) в корпусе. Поиск просматривает эту таблицу, чтобы получить отсортированный рейтинг документа для термина запроса. если его нет (для менее популярных терминов), по умолчанию будет создан ts_rank_cd на лету.

Пожалуйста, взгляните на этот пост.

https://dba.stackexchange.com/a/149701

person Pari Rajaram    schedule 15.09.2016