Полнотекстовый поиск PostgreSQL (PostgreSQL Full-Text Search)

Введение

В этой лабораторной работе вы узнаете, как реализовать полнотекстовый поиск в PostgreSQL с использованием TSVECTOR. Вы начнете с создания таблицы articles и добавления образцов данных. Затем вы добавите столбец search_vector и создадите индекс TSVECTOR для него. Наконец, вы настроите триггер для автоматического обновления столбца search_vector всякий раз, когда изменяются столбцы title или content. Это позволит вам выполнять эффективные полнотекстовые поиски.

Skills Graph

Настройка таблицы Articles (Статьи)

На этом шаге вы создадите таблицу articles и вставите в нее несколько образцов данных. Эта таблица будет использоваться для демонстрации возможностей полнотекстового поиска в PostgreSQL.

Сначала подключитесь к базе данных PostgreSQL от имени пользователя postgres, используя следующую команду в вашем терминале:

sudo -u postgres psql

Теперь создайте таблицу articles с помощью следующей SQL-команды:

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255),
    content TEXT
);

Эта команда создает таблицу с именем articles с тремя столбцами: id, title и content. Столбец id является автоматически увеличивающимся первичным ключом (auto-incrementing primary key).

Далее вставьте несколько образцов данных в таблицу articles:

INSERT INTO articles (title, content) VALUES
('PostgreSQL Tutorial', 'This is a comprehensive tutorial on PostgreSQL.'),
('Full Text Search in PostgreSQL', 'Learn how to implement full text search using TSVECTOR in PostgreSQL.'),
('PostgreSQL Performance Tuning', 'Tips and tricks to improve the performance of your PostgreSQL database.');

Эта команда вставляет три строки в таблицу articles, каждая с разным заголовком (title) и содержимым (content).

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

SELECT * FROM articles;

Вы должны увидеть три строки, которые вы только что вставили.

 id |             title              |                                       content
----+----------------------------------+-----------------------------------------------------------------------
  1 | PostgreSQL Tutorial            | This is a comprehensive tutorial on PostgreSQL.
  2 | Full Text Search in PostgreSQL | Learn how to implement full text search using TSVECTOR in PostgreSQL.
  3 | PostgreSQL Performance Tuning  | Tips and tricks to improve the performance of your PostgreSQL database.
(3 rows)

Добавление столбца search_vector и создание GIN-индекса

На этом шаге вы добавите столбец search_vector в таблицу articles и создадите GIN-индекс (GIN index) для него. Столбец search_vector будет хранить представление TSVECTOR столбцов title и content, а GIN-индекс ускорит полнотекстовый поиск.

Сначала добавьте столбец search_vector в таблицу articles:

ALTER TABLE articles ADD COLUMN search_vector TSVECTOR;

Затем обновите столбец search_vector представлением TSVECTOR столбцов title и content. Вы можете использовать функцию to_tsvector для преобразования текста в TSVECTOR.

UPDATE articles SET search_vector = to_tsvector('english', title || ' ' || content);

В приведенной выше команде 'english' указывает языковую конфигурацию, используемую для обработки текста. Оператор || объединяет столбцы title и content с пробелом между ними.

Теперь создайте GIN-индекс для столбца search_vector:

CREATE INDEX articles_search_vector_idx ON articles USING GIN (search_vector);

GIN (Generalized Inverted Index) — это тип индекса, который хорошо подходит для индексации столбцов TSVECTOR. Этот индекс позволит PostgreSQL быстро находить документы, соответствующие заданному поисковому запросу.

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

\d articles

Вы должны увидеть индекс articles_search_vector_idx, перечисленный в выходных данных.

                                     Table "public.articles"
      Column      |          Type          | Collation | Nullable |                 Default
------------------+------------------------+-----------+----------+-----------------------------------------
 id               | integer                |           | not null | nextval('articles_id_seq'::regclass)
 title            | character varying(255) |           |          |
 content          | text                   |           |          |
 search_vector    | tsvector               |           |          |
Indexes:
    "articles_pkey" PRIMARY KEY, btree (id)
    "articles_search_vector_idx" gin (search_vector)

Выполнение полнотекстового поиска с помощью TSQUERY

Теперь, когда у вас настроены столбец search_vector и GIN-индекс, вы можете выполнять полнотекстовый поиск с использованием операторов TSQUERY.

PostgreSQL предоставляет функцию to_tsquery для преобразования текстовой строки в объект TSQUERY. Затем вы можете использовать оператор @@ для сопоставления TSQUERY со столбцом search_vector.

Например, чтобы найти статьи, которые содержат как "PostgreSQL", так и "tutorial", вы можете использовать следующий запрос:

SELECT * FROM articles WHERE search_vector @@ to_tsquery('english', 'PostgreSQL & tutorial');

Оператор & в функции to_tsquery указывает, что оба термина должны присутствовать в search_vector.

Вы также можете использовать оператор | для поиска статей, которые содержат либо "PostgreSQL", либо "tutorial":

SELECT * FROM articles WHERE search_vector @@ to_tsquery('english', 'PostgreSQL | tutorial');

Чтобы найти статьи, которые содержат фразу "full text search" (полнотекстовый поиск), вы можете использовать следующий запрос:

SELECT * FROM articles WHERE search_vector @@ to_tsquery('english', '''full text search''');

Заключение фразы в одинарные кавычки указывает to_tsquery рассматривать ее как единую фразу.

Попробуйте поэкспериментировать с различными поисковыми запросами, чтобы увидеть, как работают операторы TSQUERY.

Ранжирование результатов по релевантности (Relevance)

PostgreSQL предоставляет функцию ts_rank для ранжирования результатов поиска по релевантности. Функция ts_rank принимает search_vector и TSQUERY в качестве входных данных и возвращает значение ранга.

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

SELECT title, ts_rank(search_vector, to_tsquery('english', 'PostgreSQL')) AS rank
FROM articles
WHERE search_vector @@ to_tsquery('english', 'PostgreSQL')
ORDER BY rank DESC;

Этот запрос ищет статьи, которые содержат "PostgreSQL", вычисляет ранг каждой статьи с помощью функции ts_rank, а затем упорядочивает результаты по рангу в порядке убывания.

Столбец rank в выходных данных показывает оценку релевантности для каждой статьи. Статьи с более высокими оценками релевантности перечисляются первыми.

Поддержание актуальности столбца search_vector с помощью триггера (Trigger)

Чтобы обеспечить постоянную актуальность столбца search_vector, вы можете создать триггер, который автоматически обновляет столбец всякий раз, когда изменяются столбцы title или content.

Сначала создайте функцию триггера, которая обновляет столбец search_vector:

CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS TRIGGER AS $$
BEGIN
  NEW.search_vector := to_tsvector('english', NEW.title || ' ' || NEW.content);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Эта функция принимает новые значения столбцов title и content и обновляет столбец search_vector представлением TSVECTOR объединенных значений.

Затем создайте триггер, который вызывает функцию update_search_vector всякий раз, когда строка вставляется или обновляется в таблице articles:

CREATE TRIGGER articles_update_search_vector
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW
EXECUTE FUNCTION update_search_vector();

Этот триггер будет автоматически обновлять столбец search_vector всякий раз, когда изменяются столбцы title или content.

Чтобы протестировать триггер, обновите содержимое существующей статьи:

UPDATE articles SET content = 'This is an updated comprehensive tutorial on PostgreSQL with advanced features.' WHERE id = 1;

Теперь найдите статьи, которые содержат слово "features" (функции):

SELECT * FROM articles WHERE search_vector @@ to_tsquery('english', 'features');

Вы должны увидеть обновленную статью в результатах поиска.

Итог (Summary)

В этой лабораторной работе вы узнали, как реализовать полнотекстовый поиск (full-text search) в PostgreSQL с использованием TSVECTOR. Вы создали таблицу articles, добавили столбец search_vector, создали GIN-индекс для столбца search_vector и настроили триггер (trigger) для автоматического обновления столбца search_vector всякий раз, когда изменяются столбцы title или content. Вы также узнали, как выполнять полнотекстовый поиск с использованием операторов TSQUERY и как ранжировать результаты поиска по релевантности (relevance).