Postgres как поисковый движок

Накину ссылку, которая очень мне помогла:

Речь о том, как делать гибридный поиск в Посгресе. Это когда документы ищутся по разным критериям, ранжируются, а потом объединяются в финальный набор. Как раз то, над чем я работаю в текущем проекте.

Статья полезна вот чем: до нее я не понимал, как объединять выборки с удалением дублей. Бывает, один и тот же документ оказывается в разных выборках, и нужно оставить ту, у которой больше ранг. Пытался сделать это при помощи UNION, но из-за ранга дубликаты не удалялись. Чистить их вторым проходом тяжело, усложняется план.

Так вот: автор предлагает объединение выборок при помощи full outer join и coalesce среди айдишек прошлых результатов. Звучит непонятно, но если разобраться, то получается как в примере ниже.

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

SELECT aggregate FROM some_aggregates
    WHERE
        NOT ((aggregate #>> ARRAY['state']) = ':deleted')
        AND (((aggregate #>> ARRAY['attrs', 'code']) = 'hello')
            OR (aggregate @@ '$.attrs."code-name" == "hello"')
            OR ((aggregate #>> ARRAY['attrs', 'code']) ILIKE '%hello%')
            OR ((aggregate #>> ARRAY['attrs', 'code-name']) ILIKE '%hello%'))

    ORDER BY CASE
        WHEN (aggregate #>> ARRAY['attrs', 'code']) = 'hello' THEN 0
        WHEN aggregate @@ '$.attrs."code-name" == "hello"' THEN 1
        WHEN (aggregate #>> ARRAY['attrs', 'code']) ILIKE '%hello%' THEN 2
        WHEN (aggregate #>> ARRAY['attrs', 'code-name']) ILIKE '%hello%' THEN 3
        ELSE 999
    END ASC

    LIMIT 51 OFFSET 0

На таблице с 1.5 миллионами записей метрики такие: execution=450ms, cost=90000, что довольно много.

А вот то же самое, но с подходом, который предлагает автор:

with sub1 as (
    select
        id,
        0 as rank
    from
        some_aggregates
    where
        NOT ((aggregate #>> ARRAY['state']) = ':deleted')
        and ((aggregate #>> ARRAY['attrs', 'code']) = 'hello')
    limit 51
),

sub2 as (
    select
        id,
        1 as rank
    from
        some_aggregates
    where
        NOT ((aggregate #>> ARRAY['state']) = ':deleted')
        and (aggregate @@ '$.attrs."code-name" == "hello"')
    limit 51
),

sub3 as (
    select
        id,
        2 as rank
    from
        some_aggregates
    where
        NOT ((aggregate #>> ARRAY['state']) = ':deleted')
        and ((aggregate #>> ARRAY['attrs', 'code']) ILIKE '%hello%')
    limit 51
),

sub4 as (
    select
        id,
        3 as rank
    from
        some_aggregates
    where
        NOT ((aggregate #>> ARRAY['state']) = ':deleted')
        and ((aggregate #>> ARRAY['attrs', 'code-name']) ILIKE '%hello%')
    limit 51
)

select
    aggs.id,
 aggs.aggregate #>> ARRAY['attrs', 'code'],
 aggs.aggregate #>> ARRAY['attrs', 'code-name'],
 sub1.rank,
 sub2.rank,
 sub3.rank,
 sub4.rank,
 aggs.aggregate
from
    sub1

full outer join sub2 on coalesce(sub1.id) = sub2.id
full outer join sub3 on coalesce(sub1.id, sub2.id) = sub3.id
full outer join sub4 on coalesce(sub1.id, sub2.id, sub3.id) = sub4.id

join some_aggregates aggs
    on coalesce(sub1.id, sub2.id, sub3.id, sub4.id) = aggs.id

order by
    sub1.rank, sub2.rank, sub3.rank, sub3.rank asc

limit 51

Хоть он и выглядит длинно, содержит CTE и джоины, но метрики такие: execution=7ms, cost=15000. Гораздо быстрее первого варианта.

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

Статья на Хабре – перевод вот этого блога: https://anyblockers.com/posts/postgres-as-a-search-engine

В свою очередь, автор взял идею из блога Supabase: https://supabase.com/docs/guides/ai/hybrid-search

Тем, кто ковыряеся с Посгресом, будет очень полезно изучить ссылки.

← Настоящий гей

Телефонные боты →