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

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

Очень доступно и круто!

Спасибо, Дим. Дальше — больше.

Я правильно понимаю, что GiST и GIN в продолжении (которое следует)?
Спасибо, хорошая статья.

Совершенно верно.

А там будет про работу с json? Насколько помню, этот тип данных налагает некоторые ограничения на использования индексов.

Не планировал, но подумаю. Может, какой-то пример приведу.
По уму про json надо отдельную большую статью писать, равно как и про полнотекстовый поиск.

+1 про полнотекстовый поиск
Индексы в PostgreSQL «минус» 1

прочитал так заголовок

Если это единственное замечание к статье, то я удовлетворен (:

Мало. Вкусно. Интересно.

Оставайтесь с нами!

Очень интересно, спасибо!

Всегда пожалуйста. Продолжение следует.

Спасибо за статью, ждем продолжение.

На здоровье!

Спасибо за статью.


Скажите, а инструменты по визуализации содержимого индекса для постгреса есть какие нибудь (для btree, GIN, GIST индексов)? Иногда кажется полезным такой инструмент, в плане исследования как работают и как устроены те или иные индексы.

Есть pageinspect для btree, git и brin, есть gevel для gist и gin.
Про это все тоже будет, когда доживем до конкретных индексов.

Спасибо за статью, ждем продолжение. можете подсказать примерно когда можно будет публикация 2 часть?

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

Безумно крутая статья. Конкретно и доступно. С нетерпением жду продолжение. Будут ли еще статьи по Postgre не связанные с индексами?

Спасибо, рад, что понравилось. Статьи мы писали, пишем и останавливаться не собираемся (:

Спасибо, интересное чтиво.

На здоровье, хотя «чтиво» и звучит несколько пренебрежительно.

Спасибо за интересную статью.

Напишите пожалуйста, как правильно обслуживать 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-ы по одному, а может в виде битовой карты. Такая карта не содержит никакой информации о видимости, и для ее получения не нужно заглядывать в табличные страницы.


Если получать 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)
Вопрос к сообществу, есть ли аналог процесса очистки в MS SQL Server?

И ещё один вопрос, влияет ли порядок индексируемых столбцов по которым создан индекс, на выбор оптимизатора при создании плана выполнения запроса?
И второй вопрос на эту же тему, стоит ли создавать(и если да то что нужно учитывать?) два разных индекса которые отличаются лишь порядком индексируемы столбцов?
Например индек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, b) позволяет ускорить поиск по b? (При условии, что a могут принимать разные значения.) Я не то, чтобы спорю, просто не в курсе.

Все значения 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)
«То есть, грубо говоря, индекс придется просмотреть весь или почти весь. Но если индекс по размеру меньше таблицы, то это все равно может оказаться эффективнее, чем просматривать всю таблицу.»
Спасибо.
Поэтому, предполагая условия только с равенствами, индексов, например, t(a, b) и t(b) более чем достаточно: они покроют случаи и «a=…», и «a=… AND b=…», и «b=…» — индексы t(a) и t(b, a) были бы (при условии наличия первых двух) уже избыточны.

Однако, например, «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 начиная. Но работает он (что раньше, что сейчас) так: если какое-то поле есть хотя бы в одном индексе, то при его изменениях будут обновляться все индексы, независимо от того, есть в них это поле или нет. Как я понимаю, на это Убер и наступил.

Значит я неверно вас понял. Прочел как «индексы, не включающие изменяемое поле, не перестраиваются». Наверное, сбило с толку то, что изначальный смысл в общем-то ожидаем по дефолту. Спасибо.
Хм, интересно, а какие плюсы есть в том чтобы обновлять все индексы?

Тут скорее не в плюсах дело, а в особенностях реализации. Если интересно, можно заглянуть в слайды и демонстрацию темы № 3 нашего курса DBA2, там все нарисовано подробно.

Спасибо за статью.

А когда ждать продолжение про RUM и VODKA?

На здоровье.
Градус будем повышать постепенно, так что до крепких напитков доберемся не скоро. Но, надеюсь, по пути тоже будет интересно.

Базовые индексы и механизмы у вас очень хорошо рассказаны в DBA2 курсе. А вот про «новые», Бартунов и Коротков постоянно рассказывают на конференциях, прямо как затравки. Каждый раз думаешь «вау, как круто», но так чтобы понять, а главное приспособить в реальной жизни что-то кроме Btree или Gin даже и не понятно с какой стороны смотреть толком…
Если в PostgreSQL 10 будет inmemory, то индексы уберут?
тогда смысл разбираться с ними сейчас?
Даже наличие опциональной поддержки inmemory как отменяет использование индексов?? Причём как в памяти, так и для оставшейся на диске части? Некоторые базы, вроде Exasol, вообще делают их автоматически в памяти, как рассказывал tinkoff, большинству же остальных так или иначе они всё равно нужны от DBA.

Полноценный inmemory с колоночным хранением, когда появится, изменит ситуацию с индексированием. Но, во-первых, индексы это все равно не отменяет, во-вторых, inmemory — не панацея, а в-третьих, в ближайшем будущем нам это не грозит, не в 10-ке точно.

изменит и кардинально. inmemory с колоночным хранением на видеокарте решат вопрос с производительность.
благо на видеокартах уже сейчас много памяти и подключение винчестеров через M2 уже почти стандарт.
для дурака поясните — а при чём тут видеокарты (и подключение винчестеров через M2)?
Спасибо.
Будучи аналитиком, всегда смотрел на индексы как на колдунство.
Сейчас коробочка приоткрылась.

Как известно, достаточно развитая технология неотличима от колдунства (:

Зарегистрируйтесь на Хабре , чтобы оставить комментарий