lesson ── postgres-labs ── ~22 мин ── 4 шагов

btree проваливается на LIKE, GIN спасает полнотекст

B-tree бессилен против поиска подстроки LIKE '%x%' и полнотекста. Увидим это, потом построим GIN по tsvector и сравним. Запусти psql во вкладке client.

интерактивный sandbox

Поднимется контейнер postgreslab/postgres-base с PostgreSQL 17 и psql. В браузере откроется терминал, база lab уже настроена. Каждый шаг проверяется автоматически. Сеть air-gapped, наружу контейнер не ходит.

запустить sandbox →

stack ── PostgreSQL 17 · psql · 1 GB RAM · air-gapped · самоуничтожается через 45 мин простоя

Шаги

01
Создай таблицу текстов
sql
CREATE TABLE docs AS
SELECT g id,
'рейс ' || g || ' статус ' ||
(CASE WHEN g % 3 = 0 THEN 'задержка' ELSE 'вовремя' END) AS body
FROM generate_series(1, 200000) g;
ANALYZE docs;
подсказка
У каждого документа несколько слов - это случай для инвертированного индекса.
✓ Таблица текстов готова.
02
btree не берёт подстроку
sql
CREATE INDEX docs_btree ON docs (body);
EXPLAIN SELECT * FROM docs WHERE body LIKE '%задержка%';
Подстрока в середине - начало неизвестно, B-tree не строит диапазон. План Seq Scan. Предскажи это до запуска.
подсказка
LIKE 'abc%' (префикс) btree берёт, '%abc%' (подстрока) - нет.
✓ Seq Scan - btree бессилен против подстроки.
03
Построй GIN по tsvector
sql
CREATE INDEX docs_fts ON docs USING gin (to_tsvector('simple', body));
ANALYZE docs;
GIN строит инвертированный индекс: слово → список документов.
подсказка
to_tsvector разбирает текст на слова, GIN индексирует каждое.
✓ GIN-индекс по tsvector построен.
04
Полнотекстовый поиск через GIN
sql
EXPLAIN SELECT * FROM docs
WHERE to_tsvector('simple', body) @@ to_tsquery('simple', 'задержка');
Тот же оператор-выражение, что в индексе - план идёт по GIN (Bitmap Index Scan). Сравни время с LIKE через EXPLAIN ANALYZE.
подсказка
Выражение в запросе должно совпасть с выражением индекса.
✓ Полнотекст ушёл в GIN - быстро вместо Seq Scan.

Что ты узнал

B-tree не ускоряет LIKE '%x%' (подстрока без известного начала) и полнотекст. GIN - инвертированный индекс «элемент → список строк» - решает это: по tsvector с оператором @@ поиск по словам идёт через индекс. GIN быстр на чтение и дорог на запись (pending list, fastupdate).

команды

CREATE INDEX ON t USING gin (to_tsvector('simple', body));GIN для полнотекста
WHERE to_tsvector('simple', body) @@ to_tsquery('simple','x')поиск по словам

концепции

· btree берёт префикс LIKE 'abc%', но не подстроку '%abc%'
· GIN хранит элемент → posting list строк
· выражение запроса должно совпасть с выражением индекса
· GIN дорог на запись - уместен где читают чаще, чем пишут