Лучший способ использовать ранжирование полнотекстового поиска PostgreSQL

Следуя этому ответу, я хочу знать, как лучше всего использовать встроенный полнотекстовый поиск PostgreSQL, если я хочу для сортировки по рангу и ограничиваются только совпадающими запросами.

Предположим, очень простая таблица.

CREATE TABLE pictures (
  id SERIAL PRIMARY KEY,
  title varchar(300),
  ...
)

или что-то еще. Теперь я хочу выполнить поиск в поле title. Сначала создаю индекс:

CREATE INDEX pictures_title ON pictures 
  USING gin(to_tsvector('english', title));

Теперь я хочу найти 'small dog'. Это работает:

SELECT pictures.id, 
       ts_rank_cd(
         to_tsvector('english', pictures.title), 'small dog'
       ) AS score
FROM pictures
ORDER BY score DESC

Но на самом деле я хочу вот что:

SELECT pictures.id, 
       ts_rank_cd(
         to_tsvector('english', pictures.title), to_tsquery('small dog')
       ) AS score
FROM pictures 
WHERE to_tsvector('english', pictures.title) @@ to_tsquery('small dog')
ORDER BY score DESC

Или, альтернативно, это (что не работает - нельзя использовать score в предложении WHERE):

SELECT pictures.id, 
       ts_rank_cd(
         to_tsvector('english', pictures.title), to_tsquery('small dog')
       ) AS score
FROM pictures WHERE score > 0
ORDER BY score DESC

Как лучше всего это сделать? У меня много вопросов:

  1. Если я использую версию с повторяющимся to_tsvector(...), она вызовет это дважды или достаточно умен, чтобы каким-то образом кэшировать результаты?
  2. Есть ли способ сделать это без повторения вызовов функций to_ts...?
  3. Есть ли вообще способ использовать score в предложении WHERE?
  4. Если есть, что лучше: отфильтровать по score > 0 или использовать @@?

postgresql sql full-text-search ranking
person Timmmm    schedule 17.10.2012    source источник
comment
В принятом ответе индекс не используется   -  person isapir    schedule 05.07.2018

Ответы (3)


arrow_upward
23
arrow_downward

Использование оператора @@ будет использовать полнотекстовый индекс GIN, в то время как тест для score > 0 не будет.

Я создал таблицу, как в вопросе, но добавил столбец с именем title_tsv:

CREATE TABLE test_pictures (
  id        BIGSERIAL,
  title     text,
  title_tsv tsvector
);

CREATE INDEX ix_pictures_title_tsv ON test_pictures 
    USING gin(title_tsv);

Я заполнил таблицу некоторыми тестовыми данными:

INSERT INTO test_pictures(title, title_tsv)
SELECT T.data, to_tsvector(T.data) 
FROM   some_table T;

Затем я запустил ранее принятый ответ с explain analyze:

EXPLAIN ANALYZE 
SELECT  score, id, title
FROM (
    SELECT ts_rank_cd(P.title_tsv, to_tsquery('address & shipping')) AS score
        ,P.id        
        ,P.title
    FROM test_pictures as P
) S
WHERE score > 0
ORDER BY score DESC;

И получил следующее. Обратите внимание на время выполнения 5015 мс.

QUERY PLAN                                                                                                                                    |
----------------------------------------------------------------------------------------------------------------------------------------------|
Gather Merge  (cost=274895.48..323298.03 rows=414850 width=60) (actual time=5010.844..5011.330 rows=1477 loops=1)                             |
  Workers Planned: 2                                                                                                                          |
  Workers Launched: 2                                                                                                                         |
  ->  Sort  (cost=273895.46..274414.02 rows=207425 width=60) (actual time=4994.539..4994.555 rows=492 loops=3)                                |
        Sort Key: (ts_rank_cd(p.title_tsv, to_tsquery('address & shipping'::text))) DESC                                                      |
        Sort Method: quicksort  Memory: 131kB                                                                                                 |
        ->  Parallel Seq Scan on test_pictures p  (cost=0.00..247776.02 rows=207425 width=60) (actual time=17.672..4993.997 rows=492 loops=3) |
              Filter: (ts_rank_cd(title_tsv, to_tsquery('address & shipping'::text)) > '0'::double precision)                                 |
              Rows Removed by Filter: 497296                                                                                                  |
Planning time: 0.159 ms                                                                                                                       |
Execution time: 5015.664 ms                                                                                                                   |

Теперь сравните это с оператором @@:

EXPLAIN ANALYZE
SELECT ts_rank_cd(to_tsvector(P.title), to_tsquery('address & shipping')) AS score
    ,P.id
    ,P.title
FROM    test_pictures as P
WHERE P.title_tsv @@ to_tsquery('address & shipping')
ORDER BY score DESC;

И результаты приходят с временем выполнения около 29 мс:

QUERY PLAN                                                                                                                                       |
-------------------------------------------------------------------------------------------------------------------------------------------------|
Gather Merge  (cost=13884.42..14288.35 rows=3462 width=60) (actual time=26.472..26.942 rows=1477 loops=1)                                        |
  Workers Planned: 2                                                                                                                             |
  Workers Launched: 2                                                                                                                            |
  ->  Sort  (cost=12884.40..12888.73 rows=1731 width=60) (actual time=17.507..17.524 rows=492 loops=3)                                           |
        Sort Key: (ts_rank_cd(to_tsvector(title), to_tsquery('address & shipping'::text))) DESC                                                  |
        Sort Method: quicksort  Memory: 171kB                                                                                                    |
        ->  Parallel Bitmap Heap Scan on test_pictures p  (cost=72.45..12791.29 rows=1731 width=60) (actual time=1.781..17.268 rows=492 loops=3) |
              Recheck Cond: (title_tsv @@ to_tsquery('address & shipping'::text))                                                                |
              Heap Blocks: exact=625                                                                                                             |
              ->  Bitmap Index Scan on ix_pictures_title_tsv  (cost=0.00..71.41 rows=4155 width=0) (actual time=3.765..3.765 rows=1477 loops=1)  |
                    Index Cond: (title_tsv @@ to_tsquery('address & shipping'::text))                                                            |
Planning time: 0.214 ms                                                                                                                          |
Execution time: 28.995 ms                                                                                                                        |

Как вы можете видеть в плане выполнения, индекс ix_pictures_title_tsv использовался во втором запросе, но не в первом, что сделало запрос с оператором @@ в 172 раза быстрее!

person isapir    schedule 05.07.2018

arrow_upward
4
arrow_downward

Если я использую версию с повторяющимся to_tsvector (...), она вызовет это дважды или достаточно ли умен, чтобы каким-то образом кэшировать результаты?

Лучший способ заметить эти вещи - дать простые объяснения, хотя их бывает трудно прочитать.

Короче говоря, да, PostgreSQL достаточно умен, чтобы повторно использовать вычисленные результаты.

Есть ли способ сделать это, не повторяя вызовы функции to_ts ...?

Обычно я добавляю столбец tsv, который является вектором текстового поиска. Если вы сделаете это автоматическое обновление с помощью триггеров, оно сразу же предоставит вам легко доступный вектор, но также позволит вам выборочно обновлять поисковый индекс, сделав триггер выборочным.

Есть ли вообще способ использовать счет в предложении WHERE?

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

Если есть, что лучше: отфильтровать по баллу> 0 или использовать элемент @@?

Самая простая версия, о которой я могу думать, такова:

SELECT *
FROM pictures
WHERE 'small dog' @@ text_search_vector

Очевидно, text_search_vector можно заменить чем-то вроде to_tsvector('english', pictures.title)

person Wolph    schedule 19.10.2012
comment
В этом ответе игнорируется самая важная часть вопроса: рейтинг. - person steviesh; 23.06.2017

arrow_upward

arrow_downward

person    schedule
comment
Этот запрос не использует индекс. Кроме того, to_tsquery('small dog') выдает ошибку. Вы, наверное, имели в виду to_tsquery('small & dog'). - person isapir; 05.07.2018