Как стать автором
Обновить

Используем все возможности индексов в PostgreSQL

Время на прочтение 8 мин
Количество просмотров 23K
Всего голосов 32: ↑31 и ↓1 +30
Комментарии 3

Комментарии 3

Ох…

В PostgreSQL 11 появились покрывающие индексы.

Я затрудняюсь вспомнить, насколько древние index only scan в postgresql. Даже в далёком 9.2 их уже рефакторили, а не добавляли.

Index Only Scan — вещь очень старая. Что сделали в pg11 с include — разрешили добавлять в листья индекса дополнительные колонки, которые не участвуют в построении самого дерева, но могут быть из него прочитаны.

То есть вместо btree(active, email) — который годится для index only scan, но требует сортировать все email и корректно их размещать в дереве можно сделать btree(active) INCLUDE (email). Где сортируем только active, а email валяется дополнительным грузом.

«Index Only Scan» говорит нам, что запросу теперь достаточно одного лишь индекса, что помогает избегать всех дисковых операций ввода/вывода для чтения кучи таблицы.

Не совсем верно. Или вообще-то сказать неверно. Index Only Scan говорит, что база может пропустить чтение строки из heap. В худшем случае Index Only Scan идентичен Index Scan.
Зачем базе лезть в heap? Потому что только там хранятся поля xmin, xmax, cmin, cmax (4 поля по 4 байта), по которым машинерия MVCC может ответить, видна эта версия строки нашей транзакции или нет.
Index Only Scan может эту проверку пропустить, если visibility map скажет, что можешь не проверять видимость, эта строка видима всем транзакциям (слишком старая). О поддержке visibility map заботится вакуум и автовакуум, а любые изменения этой строки эту метку снимают.

Кстати, индекс по очевидно булевому полю — вообще странная вещь. Если у вас распределение данных около половины — индекс банально стоит дороже, чем пройти seqscan. Если у вас условный 1% строк нужен — то и сделайте частичный индекс на этот 1% btree(email) where active=0

+1
Только cmin/cmax — одно поле (:

хм, да, верно, моя ошибка. До 8.3 их 4 поля было
Зарегистрируйтесь на Хабре , чтобы оставить комментарий