Глава 31. Как PostgreSQL выбирает индекс: operator classes · PostgreSQL

31.1 Индекс работает для операторов, а не для колонок

Привычная фраза «есть индекс на колонке» неточна. Точнее: есть индекс, который умеет отвечать на определённые операторы над колонкой. B-tree-индекс на flight_id отвечает на flight_id = 7, flight_id > 40, flight_id BETWEEN 1 AND 10. Но на flight_id % 2 = 0 он бесполезен - оператора «остаток от деления» в его наборе нет.

Связь «тип данных + оператор + метод доступа» задаётся через operator class. Это объект каталога, который говорит: «для типа int4 в методе btree оператор < означает стратегию "меньше", = - "равно"» и так далее. Когда планировщик видит условие, он проверяет: есть ли индекс, чей operator class поддерживает этот оператор для этого типа? Если да - индекс кандидат. Если нет - индекс не рассматривается, будет Seq Scan.

31.2 Карта каталогов

Четыре системные таблицы описывают всю систему индексных операторов:

Каталог	Что хранит
`pg_am`	методы доступа: btree, hash, gist, gin, spgist, brin
`pg_opclass`	operator class: тип + AM + набор операторов
`pg_opfamily`	семейство связанных opclass (например, все целочисленные)
`pg_amop`	какие операторы поддерживает семейство и под какой стратегией

sql

SELECT amname FROM pg_am;

-- btree, hash, gist, gin, spgist, brin

Логика чтения: от оператора (pg_amop.amopopr) через семейство (pg_opfamily) к методу доступа (pg_am). Пройдя этот путь, ты получаешь ответ «какой AM знает этот оператор» без всякого заучивания. Подробно - в operator-classes.

31.3 Кто поддерживает оператор: запрос к каталогу

Вот запрос, отвечающий на главный вопрос главы - какие методы доступа знают данный оператор:

sql

SELECT DISTINCT am.amname

FROM pg_amop aop

JOIN pg_operator op ON op.oid = aop.amopopr

JOIN pg_opfamily f ON f.oid = aop.amopfamily

JOIN pg_am am ON am.oid = f.opfmethod

WHERE op.oprname = '@>';

Для @> (содержит) ответ - gin и gist, но не btree. Значит, условие tags @> '{sale}' ускорит GIN-индекс, а B-tree - нет, сколько его ни создавай. Поменяй оператор на < - и в ответе будет btree. Это и есть способ выбирать тип индекса: не по памяти, а по каталогу.

31.4 Стратегии: что значит номер оператора

Внутри operator class операторы пронумерованы стратегиями. Для B-tree это пять чисел с фиксированным смыслом:

Стратегия	Оператор	Смысл
1	`<`	меньше
2	`<=`	меньше или равно
3	`=`	равно
4	`>=`	больше или равно
5	`>`	больше

Эта нумерация - контракт между AM и операторами. B-tree знает, что стратегия 1 - это «меньше», и умеет по ней спускаться по дереву. У GiST стратегий больше и они другие (пересечение, содержание, расстояние) - потому что дерево решает другие задачи. Стратегия - это язык, на котором AM понимает, что от него хотят.

31.5 Несколько operator class для одного типа

Один тип данных может иметь несколько operator class под один AM, и выбор между ними меняет, какие запросы ускорятся. Классика - текст и поиск по префиксу.

sql

-- обычный индекс: для = и сравнений по умолчанию

CREATE INDEX ON t (email);

-- индекс под LIKE 'prefix%'

CREATE INDEX ON t (email text_pattern_ops);

Обычный B-tree на тексте использует операторы сравнения текущей локали. Для LIKE 'abc%' он часто не подходит, потому что локаль-сортировка не совпадает с побайтовой. Operator class text_pattern_ops сравнивает побайтово (как локаль C) - и тогда LIKE 'abc%' ускоряется индексом. Это типичная причина «индекс на email есть, а LIKE по нему не работает»: нужен другой operator class.

31.5.1 Подводный камень: оператор поддержан, но условие не sargable

Даже если AM знает оператор, индекс не сработает, когда колонка спрятана под функцией. WHERE lower(email) = 'a@b.c' использует =, который B-tree знает, но слева не колонка email, а lower(email) - выражение. Индекс по email к нему не подходит.

Выхода два: индекс по выражению (CREATE INDEX ON t (lower(email))) или хранить уже нормализованное значение. Это свойство называется sargability, и оно ортогонально operator class: сначала условие должно быть sargable (колонка не обёрнута), потом оператор должен быть в operator class. Разбор - в sargability.

31.6 Свойства индекса в каталоге

У каждого AM есть свойства, которые планировщик учитывает: умеет ли он возвращать данные в отсортированном порядке, поддерживает ли Index-Only Scan, работает ли с многоколоночными индексами. Их видно через функции свойств:

sql

SELECT amname,

       pg_indexam_has_property(a.oid, 'can_order')   AS ordered,

       pg_indexam_has_property(a.oid, 'can_unique')  AS unique

FROM pg_am a WHERE amname IN ('btree','hash','gin','brin');

B-tree умеет упорядочивать (can_order) - поэтому только он ускоряет ORDER BY. Hash, GIN, BRIN порядок не дают. Эти свойства - причина, по которой разные AM не взаимозаменяемы: дело не только в поддержке операторов, но и в том, что AM умеет отдавать. Дальше в части мы разберём устройство каждого AM, начиная с B-tree.

Уроки в sandbox

lab-31.1. Найти нужный метод доступа через каталог

Возьмём JSONB-оператор, который btree игнорирует, и через каталог определим, что нужен GIN. Перед запросом к каталогу предскажи, какие AM поддержат оператор.

Создай таблицу с JSONB: CREATE TABLE doc (id serial, body jsonb); INSERT INTO doc(body) SELECT jsonb_build_object('tag', 'sale', 'n', g) FROM generate_series(1,50000) g;.
Попробуй btree: CREATE INDEX ON doc (body); EXPLAIN SELECT * FROM doc WHERE body @> '{"tag":"sale"}'; - индекс не используется, Seq Scan.
Спроси каталог, кто поддерживает @>: запрос из раздела 31.3. Предскажи и проверь: gin и gist, не btree.
Создай правильный индекс: CREATE INDEX ON doc USING gin (body); ANALYZE doc;.
Повтори EXPLAIN того же запроса - теперь оператор @> появился в Index Cond, план использует GIN (Bitmap Index Scan).

sandbox с автопроверкой - открыть в песочнице

Резюме

Индекс работает не «на колонке», а для конкретных операторов над ней; связь тип+оператор+AM задаёт operator class.
Каталоги pg_am, pg_opclass, pg_opfamily, pg_amop описывают всю систему: от оператора через семейство к методу доступа.
Запросом к pg_amop можно узнать, какой AM поддерживает оператор: @> знают gin и gist, но не btree - поэтому btree бесполезен для JSONB-содержания.
Операторы внутри opclass пронумерованы стратегиями; для B-tree это <, <=, =, >=, > (стратегии 1-5) с фиксированным смыслом.
У типа бывает несколько opclass: text_pattern_ops ускоряет LIKE 'prefix%' побайтовым сравнением, чего обычный текстовый opclass не делает.
Поддержка оператора не спасёт, если условие не sargable (колонка под функцией) - нужен индекс по выражению.
Свойства AM (can_order, can_unique) определяют, что он умеет отдавать: только B-tree упорядочивает, поэтому только он ускоряет ORDER BY.

Контрольные вопросы

Создал B-tree-индекс на JSONB-колонке, а запрос с `@>` его не использует. Почему?
Показать ответ
Потому что оператор @> (содержит) не входит в operator class B-tree. B-tree знает только операторы сравнения порядка (<, <=, =,

=, >), а @> - это операция содержания, для которой нужна другая структура. Запрос к pg_amop показывает, что @> поддерживают gin и gist, но не btree. Поэтому сколько ни создавай B-tree на JSONB, он не ускорит body @> '...' - нужен GIN-индекс.
Зачем нужен operator class `text_pattern_ops`, если обычный индекс на тексте уже есть?
Показать ответ
Обычный текстовый operator class использует сравнения текущей локали, и их порядок не совпадает с побайтовым. Для LIKE 'abc%' (поиск по префиксу) индекс по локаль-сортировке обычно не подходит. text_pattern_ops сравнивает побайтово (как локаль C), и тогда префиксный LIKE ускоряется индексом. Это типичная причина «индекс на колонке есть, а LIKE по нему всё равно Seq Scan»: для такого запроса нужен индекс с opclass text_pattern_ops.
Как, не заучивая таблицы, узнать, какой тип индекса нужен для данного оператора?
Показать ответ
Спросить каталог. Оператор лежит в pg_operator, его поддержка индексами - в pg_amop (через семейство pg_opfamily, привязанное к методу доступа в pg_am). Запрос, соединяющий pg_amop → pg_operator → pg_opfamily → pg_am с фильтром по имени оператора, возвращает список методов доступа, которые этот оператор знают. Это model-first подход: тип индекса выводится из каталога, а не из памяти.
Что такое стратегия в operator class?
Показать ответ
Стратегия - это номер, под которым AM понимает смысл оператора. Для B-tree пять стратегий с фиксированным значением: 1 - «меньше», 2 - «меньше или равно», 3 - «равно», 4 - «больше или равно», 5 - «больше». AM не работает с конкретными операторами напрямую - он работает со стратегиями, а operator class сопоставляет оператор типа нужной стратегии. У GiST стратегий больше и другие (пересечение, содержание, расстояние), потому что дерево решает другие задачи.
Почему ORDER BY ускоряет только B-tree, а не GIN или hash?
Показать ответ
Потому что у методов доступа разные свойства. B-tree хранит ключи в отсортированном порядке и умеет отдавать строки уже упорядоченными (свойство can_order) - это позволяет обслужить ORDER BY без отдельной сортировки. Hash хранит значения по хешу, GIN - как инвертированный список, BRIN - как сводки по диапазонам блоков; ни один из них не выдаёт данные в порядке значений. Поэтому для ORDER BY годится только B-tree, и это видно по свойству can_order в каталоге.

31.1 Индекс работает для операторов, а не для колонок

31.2 Карта каталогов

Четыре системные таблицы описывают всю систему индексных операторов:

Каталог	Что хранит
`pg_am`	методы доступа: btree, hash, gist, gin, spgist, brin
`pg_opclass`	operator class: тип + AM + набор операторов
`pg_opfamily`	семейство связанных opclass (например, все целочисленные)
`pg_amop`	какие операторы поддерживает семейство и под какой стратегией

sql

SELECT amname FROM pg_am;

-- btree, hash, gist, gin, spgist, brin

31.3 Кто поддерживает оператор: запрос к каталогу

Вот запрос, отвечающий на главный вопрос главы - какие методы доступа знают данный оператор:

sql

SELECT DISTINCT am.amname

FROM pg_amop aop

JOIN pg_operator op ON op.oid = aop.amopopr

JOIN pg_opfamily f ON f.oid = aop.amopfamily

JOIN pg_am am ON am.oid = f.opfmethod

WHERE op.oprname = '@>';

31.4 Стратегии: что значит номер оператора

Внутри operator class операторы пронумерованы стратегиями. Для B-tree это пять чисел с фиксированным смыслом:

Стратегия	Оператор	Смысл
1	`<`	меньше
2	`<=`	меньше или равно
3	`=`	равно
4	`>=`	больше или равно
5	`>`	больше

31.5 Несколько operator class для одного типа

sql

-- обычный индекс: для = и сравнений по умолчанию

CREATE INDEX ON t (email);

-- индекс под LIKE 'prefix%'

CREATE INDEX ON t (email text_pattern_ops);

31.5.1 Подводный камень: оператор поддержан, но условие не sargable

31.6 Свойства индекса в каталоге

sql

SELECT amname,

       pg_indexam_has_property(a.oid, 'can_order')   AS ordered,

       pg_indexam_has_property(a.oid, 'can_unique')  AS unique

FROM pg_am a WHERE amname IN ('btree','hash','gin','brin');

Уроки в sandbox

lab-31.1. Найти нужный метод доступа через каталог

Создай таблицу с JSONB: CREATE TABLE doc (id serial, body jsonb); INSERT INTO doc(body) SELECT jsonb_build_object('tag', 'sale', 'n', g) FROM generate_series(1,50000) g;.
Попробуй btree: CREATE INDEX ON doc (body); EXPLAIN SELECT * FROM doc WHERE body @> '{"tag":"sale"}'; - индекс не используется, Seq Scan.
Спроси каталог, кто поддерживает @>: запрос из раздела 31.3. Предскажи и проверь: gin и gist, не btree.
Создай правильный индекс: CREATE INDEX ON doc USING gin (body); ANALYZE doc;.
Повтори EXPLAIN того же запроса - теперь оператор @> появился в Index Cond, план использует GIN (Bitmap Index Scan).

sandbox с автопроверкой - открыть в песочнице

Резюме

Индекс работает не «на колонке», а для конкретных операторов над ней; связь тип+оператор+AM задаёт operator class.
Каталоги pg_am, pg_opclass, pg_opfamily, pg_amop описывают всю систему: от оператора через семейство к методу доступа.
Запросом к pg_amop можно узнать, какой AM поддерживает оператор: @> знают gin и gist, но не btree - поэтому btree бесполезен для JSONB-содержания.
Операторы внутри opclass пронумерованы стратегиями; для B-tree это <, <=, =, >=, > (стратегии 1-5) с фиксированным смыслом.
У типа бывает несколько opclass: text_pattern_ops ускоряет LIKE 'prefix%' побайтовым сравнением, чего обычный текстовый opclass не делает.
Поддержка оператора не спасёт, если условие не sargable (колонка под функцией) - нужен индекс по выражению.
Свойства AM (can_order, can_unique) определяют, что он умеет отдавать: только B-tree упорядочивает, поэтому только он ускоряет ORDER BY.

Контрольные вопросы

Создал B-tree-индекс на JSONB-колонке, а запрос с `@>` его не использует. Почему?
Показать ответ
Потому что оператор @> (содержит) не входит в operator class B-tree. B-tree знает только операторы сравнения порядка (<, <=, =,

=, >), а @> - это операция содержания, для которой нужна другая структура. Запрос к pg_amop показывает, что @> поддерживают gin и gist, но не btree. Поэтому сколько ни создавай B-tree на JSONB, он не ускорит body @> '...' - нужен GIN-индекс.
Зачем нужен operator class `text_pattern_ops`, если обычный индекс на тексте уже есть?
Показать ответ
Обычный текстовый operator class использует сравнения текущей локали, и их порядок не совпадает с побайтовым. Для LIKE 'abc%' (поиск по префиксу) индекс по локаль-сортировке обычно не подходит. text_pattern_ops сравнивает побайтово (как локаль C), и тогда префиксный LIKE ускоряется индексом. Это типичная причина «индекс на колонке есть, а LIKE по нему всё равно Seq Scan»: для такого запроса нужен индекс с opclass text_pattern_ops.
Как, не заучивая таблицы, узнать, какой тип индекса нужен для данного оператора?
Показать ответ
Спросить каталог. Оператор лежит в pg_operator, его поддержка индексами - в pg_amop (через семейство pg_opfamily, привязанное к методу доступа в pg_am). Запрос, соединяющий pg_amop → pg_operator → pg_opfamily → pg_am с фильтром по имени оператора, возвращает список методов доступа, которые этот оператор знают. Это model-first подход: тип индекса выводится из каталога, а не из памяти.
Что такое стратегия в operator class?
Показать ответ
Стратегия - это номер, под которым AM понимает смысл оператора. Для B-tree пять стратегий с фиксированным значением: 1 - «меньше», 2 - «меньше или равно», 3 - «равно», 4 - «больше или равно», 5 - «больше». AM не работает с конкретными операторами напрямую - он работает со стратегиями, а operator class сопоставляет оператор типа нужной стратегии. У GiST стратегий больше и другие (пересечение, содержание, расстояние), потому что дерево решает другие задачи.
Почему ORDER BY ускоряет только B-tree, а не GIN или hash?
Показать ответ
Потому что у методов доступа разные свойства. B-tree хранит ключи в отсортированном порядке и умеет отдавать строки уже упорядоченными (свойство can_order) - это позволяет обслужить ORDER BY без отдельной сортировки. Hash хранит значения по хешу, GIN - как инвертированный список, BRIN - как сводки по диапазонам блоков; ни один из них не выдаёт данные в порядке значений. Поэтому для ORDER BY годится только B-tree, и это видно по свойству can_order в каталоге.