Комментарии 59
Очень доступно и круто!
прочитал так заголовок
Спасибо за статью, ждем продолжение.
Спасибо за статью.
Скажите, а инструменты по визуализации содержимого индекса для постгреса есть какие нибудь (для btree, GIN, GIST индексов)? Иногда кажется полезным такой инструмент, в плане исследования как работают и как устроены те или иные индексы.
Есть pageinspect для btree, git и brin, есть gevel для gist и gin.
Про это все тоже будет, когда доживем до конкретных индексов.
Спасибо за статью, ждем продолжение. можете подсказать примерно когда можно будет публикация 2 часть?
Напишите пожалуйста, как правильно обслуживать btree-индексы, чтобы они не разрастались?
Если по месячной партиции количество UPDATE в 6 (может и более) раз превышает количество INSERT, то reindex в конце месяца практически всегда сокращает объём индексов на 75% от исходного. При этом пересоздание таблицы даёт чаще всего меньший выигрыш по объёму самой таблицы, не более 20 %, что указывает на то, что сам vacuum всё-таки успевает.
Что ещё интересно, reindex даёт наибольший выигрыш по объёму не на всех индексах: на трёх выигрыш около 5.3 раз, на трёх — от 3.5 до 4.2 раз, на двух — от 2.8 до 3.4 раз, при этом, ни один из индексов не содержит ни колонок, значения которых меняются в UPDATE, ни колонок переменной длины, а содержит в основном значения типов integer и timestamp.
Неужели единственный выход в периодическом пересоздании индексов?
ни колонок переменной длины, а содержит в основном значения типов integer и timestamp.
дело в движке:
- каждый индекс живет своей отдельной жизнью от данных
- каждое обновление строки не делает обновление по месту, а создает новую версию строки и для каждой новой версии появляется такая же новая запись в индексе.
обслуживание
обычно через concurrency создают такой же индекс и удаляют старый, были баги в 9.4 с репликами, когда новый индекс не подхватывался, сейчас устранено.
Проблема разрастания индексов есть, и связана она не только с очисткой (как в случае таблицы), но и с тем, что индексные страницы могут при необходимости разбиваться на две, но не умеют срастаться обратно.
Но если очистка у вас работает нормально, и если по прошествии месяца секция уже активно не обновляется, то однократное перестроение индексов (чтобы зазря не расходовать место) вроде бы не выглядит большой сложностью?
Проблема разрастания индексов есть, и связана она не только с очисткой (как в случае таблицы), но и с тем, что индексные страницы могут при необходимости разбиваться на две, но не умеют срастаться обратно.
Вообще-то в B-деревьях склеивание разряжённых блоков — дело обычное. Неужели это не реализовано в PostgreSQL?
src/backend/access/nbtree/README
We consider deleting an entire page from the btree only when it's become
completely empty of items. (Merging partly-full pages would allow better
space reuse, but it seems impractical to move existing data items left or
right to make this happen — a scan moving in the opposite direction
might miss the items if so.)
Метод доступа может возвращать TID-ы по одному, а может в виде битовой карты. Такая карта не содержит никакой информации о видимости, и для ее получения не нужно заглядывать в табличные страницы.
Если получать TID-ы по одному, то может оказаться, что в итоге мы будем обращаться к одной и той же табличной странице несколько раз: сначала за одной строкой, потом за другой… Каждое обращение — это поиск страницы в буферном кэше (и при необходимости вытеснение другой страницы и чтение нужной с диска), установка нужных блокировок, в общем — накладные расходы.
При сканировании битовой карты мы читаем каждую табличную страницу только один раз.
В частичный индекс попадают только строки, подходящие по явно указанному условию (никакой автоматики на обнаружение высокочастотных значений нет). В нашем примере: create index on t(c) where c;
таким условием является c
.
Если планировщик, глядя на условие обращения к таблице в запросе, понимает, что нужная информация содержится в частичном индексе, он будет рассматривать его как один из возможных способов доступа к данным.
А комбинирования доступа по индексу и сканирования таблицы никогда не происходит — либо так, либо иначе, но не оба вместе.
Вот другой пример для наглядности:
postgres=# create table test as select * from t;
SELECT 100000
postgres=# create index on test(a) where a < 1000;
CREATE INDEX
Вот так индекс будет использоваться:
postgres=# explain (costs off) select * from test where a < 1000;
QUERY PLAN
---------------------------------------
Bitmap Heap Scan on test
Recheck Cond: (a < 1000)
-> Bitmap Index Scan on test_a_idx
(3 rows)
И вот так планировщик догадается:
postgres=# explain (costs off) select * from test where a < 100;
QUERY PLAN
---------------------------------------
Bitmap Heap Scan on test
Recheck Cond: (a < 100)
-> Bitmap Index Scan on test_a_idx
Index Cond: (a < 100)
(4 rows)
А вот так — уже нельзя:
postgres=# explain (costs off) select * from test where a < 10000;
QUERY PLAN
-----------------------
Seq Scan on test
Filter: (a < 10000)
(2 rows)
И второй вопрос на эту же тему, стоит ли создавать(и если да то что нужно учитывать?) два разных индекса которые отличаются лишь порядком индексируемы столбцов?
Например индекc t(a,b) и t(b,a)
Если про B-дерево говорить, то конечно влияет. Потому что индексные записи будут отсортированы сначала по первому столбцу, а потом по второму. И если у вас в запросе условие на первый столбец и на второй, вы тут же находите нужное значение, просто спускаясь вниз по дереву. А если условие только на второй столбец, то вам придется просматривать весь индекс.
Но это мы вперед забегаем, про все расскажу, когда дойдем до btree.
Например индекc t(a,b)
AFAIK, индекc t(a,b) позволяет ускорить запросы вида:
- SELECT * FROM t WHERE a=… AND b=…
- SELECT * FROM t WHERE a=…
Но не такой:
- SELECT * FROM t WHERE b=…
P.S.: Т.е. индекс t(a1, a2, …, aN), считай, работает не только как t(a1, a2, …, aN), но и как t(a1), t(a1, a2), …, t(a1, a2, …, a(N-1)).
На самом деле и SELECT * FROM t WHERE b=...
тоже, особенно если вместо *
там несколько полей. Это может иметь смысл, если индекс покрывающий — может оказаться проще просмотреть индекс (пусть и весь), чем таблицу (тоже всю).
Все значения a
, конечно, придется перебрать. То есть, грубо говоря, индекс придется просмотреть весь или почти весь. Но если индекс по размеру меньше таблицы, то это все равно может оказаться эффективнее, чем просматривать всю таблицу. Речь, конечно, о случае, когда на таблице нет индекса t(b)
, который, естественно, будет значительно эффективнее.
Постгрес так умеет:
postgres=# \d t
Table "public.t"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
b | text |
c | boolean |
Indexes:
"t_a_b_idx" btree (a, b)
postgres=# set enable_seqscan = off;
SET
postgres=# explain select * from t where b = '!';
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on t (cost=1854.54..2310.04 rows=1000 width=7)
Recheck Cond: (b = '!'::text)
-> Bitmap Index Scan on t_a_b_idx (cost=0.00..1854.29 rows=1000 width=0)
Index Cond: (b = '!'::text)
(4 rows)
Однако, например, «WHERE a=7 AND b>=100» теоретически можно интерпретировать как «WHERE (a, b) BETWEEN (7, 100) AND (7, +INFINITY)», но не как «WHERE (b, a) BETWEEN … AND …» — поэтому в таком условии индекс t(a, b) явно более подходящ, чем t(b, a) — второй может быть использован лишь в той степени, в которой может быть использован t(b).
Заметим, что обновление полей таблицы, по которым не создавались индексы, не приводит к перестроению индексовВ нашумевшем переходе Uber с PostgreSQL на MySQL они жаловались на обновление всех индексов. Как я понимаю, вы намеренно акцентировали внимание на Heap-Only Tuples, чтобы показать, что проблема решена при миграции с 9.2 на 9.6? Или раньше?
HOT-то работает с незапамятных времен, с 8.3 начиная. Но работает он (что раньше, что сейчас) так: если какое-то поле есть хотя бы в одном индексе, то при его изменениях будут обновляться все индексы, независимо от того, есть в них это поле или нет. Как я понимаю, на это Убер и наступил.
А когда ждать продолжение про RUM и VODKA?
На здоровье.
Градус будем повышать постепенно, так что до крепких напитков доберемся не скоро. Но, надеюсь, по пути тоже будет интересно.
тогда смысл разбираться с ними сейчас?
Полноценный inmemory с колоночным хранением, когда появится, изменит ситуацию с индексированием. Но, во-первых, индексы это все равно не отменяет, во-вторых, inmemory — не панацея, а в-третьих, в ближайшем будущем нам это не грозит, не в 10-ке точно.
Сейчас коробочка приоткрылась.
Индексы в PostgreSQL — 1