fullstack.org.ua

Індекси для Full Text Search · fullstack.org.ua

Середній11 хв· Fullstack Team

Індекси для Full Text Search

Налаштуєте індексацію повнотекстового пошуку через tsvector і підберете відповідний GIN або GiST індекс.

Навіщо індексувати повнотекстовий пошук

Повнотекстовий пошук у PostgreSQL працює з двома основними типами даних:

tsvector — нормалізований документ, представлений набором лексем;
tsquery — пошуковий запит, також представлений у формі, придатній для порівняння.

Оператор @@ перевіряє, чи відповідає tsvector заданому tsquery:

sql

search_vector @@ search_query

Без індексу PostgreSQL мусить обчислити tsvector і перевірити кожен рядок таблиці. На великій таблиці це призводить до повного сканування.

Індекс на tsvector дає змогу швидко знаходити документи, які відповідають пошуковому запиту.

sql

CREATE TABLE articles (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title text NOT NULL,
    body text NOT NULL,
    search_vector tsvector GENERATED ALWAYS AS (
        setweight(
            to_tsvector('simple'::regconfig, coalesce(title, '')),
            'A'
        ) ||
        setweight(
            to_tsvector('simple'::regconfig, coalesce(body, '')),
            'B'
        )
    ) STORED
);

sql

INSERT INTO articles (title, body)
VALUES
    (
        'Індекси в PostgreSQL',
        'GIN індекс прискорює повнотекстовий пошук у великих таблицях.'
    ),
    (
        'Транзакції бази даних',
        'Транзакції забезпечують узгодженість і надійність операцій.'
    ),
    (
        'Пошук у документах',
        'Повнотекстовий пошук використовує tsvector і tsquery.'
    );

sql

SELECT
    id,
    title,
    ts_rank(
        search_vector,
        websearch_to_tsquery('simple'::regconfig, 'GIN пошук')
    ) AS rank
FROM articles
WHERE search_vector @@ websearch_to_tsquery(
    'simple'::regconfig,
    'GIN пошук'
)
ORDER BY rank DESC, id;

sql

CREATE TABLE documents (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title text NOT NULL,
    body text NOT NULL
);

CREATE INDEX documents_search_gin_idx
ON documents
USING GIN (
    to_tsvector(
        'simple'::regconfig,
        coalesce(title, '') || ' ' || coalesce(body, '')
    )
);

sql

SELECT id, title
FROM documents
WHERE to_tsvector(
    'simple'::regconfig,
    coalesce(title, '') || ' ' || coalesce(body, '')
) @@ websearch_to_tsquery(
    'simple'::regconfig,
    'індекси'
);

sql

SELECT
    id,
    title,
    ts_rank_cd(
        search_vector,
        websearch_to_tsquery('simple'::regconfig, 'PostgreSQL')
    ) AS rank
FROM articles
WHERE search_vector @@ websearch_to_tsquery(
    'simple'::regconfig,
    'PostgreSQL'
)
ORDER BY rank DESC;