# Занятие 05: Доступ к данным / Типы индексов > Исходная страница в нативном Markdown: Доступ к данным / Типы индексов --- Индекс LLMS: [llms.txt](/ru/llms.txt) --- > [!NOTE] > Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. > **HTML source:** [qpt_05_am_indexes.html](https://edu.postgrespro.ru/16/qpt-16/qpt_05_am_indexes.html) > **Archive:** [QPT-handouts-16.zip](https://edu.postgrespro.ru/16/QPT-handouts-16.zip) > **Страниц PDF:** 26 ## Page 1: Доступ к данным / Типы индексов {#source-page-001} ### Авторские права © Postgres Professional, 2019–2024 Авторы: Егор Рогов, Павел Лузанов, Павел Толмачев, Илья Баштанов Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал) ### Использование материалов курса Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса. ### Обратная связь Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru ### Отказ от ответственности Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения. ## Page 2: Темы {#source-page-002} - Хеш-индекс - GiST - Класс операторов - SP-GiST - GIN - BRIN ## Page 3: Идея хеширования {#source-page-003} ![Source slide page 3: Идея хеширования](images/page-003.png){#source-figure-003 caption="Source slide page 3: Идея хеширования"} ```sql CREATE INDEX ON t USING hash(title); SELECT * FROM t WHERE title = 'Abbey Road'; ``` ### Комментарии преподавателя Идея хеширования состоит в том, что значения любого типа данных равномерно распределяются по ограниченному количеству корзин хеш-таблицы с помощью функции хеширования. Если хеш-таблица имеет достаточный размер, чтобы в одну корзину в среднем попадало одно значение (хеш-код), поиск значения в хеш-таблице выполняется за константное время. Для этого: 1) вычисляется хеш-функция от заданного значения; 2) по нескольким битам полученного хеш-кода определяется номер корзины; 3) корзина просматривается в поисках хеш-кода. Если данные распределены неравномерно, в одну корзину могут попасть много значений. В этом случае эффективность поиска будет страдать. По сути, хеш-индекс представляет собой хеш-таблицу, которая хранится на диске. ## Page 4: Хеш-индекс {#source-page-004} **Хранит только хеш-коды, но не исходные значения** - размер не зависит от ключа индексирования - невозможно сканирование только индекса - Индекс увеличивается динамически - скачкообразный рост - Поиск только по условию равенства ### Комментарии преподавателя Хеш-индекс хранит только значения хеш-функции и ссылки на версии строк; само индексируемое значение не сохраняется. Поэтому размер хеш-индекса не зависит от размера ключа индексирования, но теряется возможность сканирования только индекса — значение можно прочитать только из таблицы. Размер хеш-индекса увеличивается динамически при добавлении новых значений. При увеличении количество корзин удваивается, поэтому рост размера происходит скачкообразно. В отличие от B-дерева, хеш-индексы имеют много ограничений, в частности: ● единственная поддерживаемая операция — поиск по условию равенства, поскольку хеш-функция не сохраняет порядок следования значений; ● не поддерживается ограничение уникальности; ● нельзя создать многоколоночный индекс и добавить к индексу дополнительные include-столбцы. Поэтому хеш-индексы не получили широкого распространения. Однако за счет меньшего размера и фиксированного времени поиска хеш-индекс может в ряде случаев работать быстрее, чем индекс на основе B-дерева. ## Page 5: Хеш-индекс {#source-page-005} Посмотрим на план запроса, получающего определенное место в салонах всех самолетов: ```sql => EXPLAIN (costs off) SELECT * FROM seats WHERE seat_no = '31D'; ``` ```text QUERY PLAN ------------------------------------------- Seq Scan on seats Filter: ((seat_no)::text = '31D'::text) (2 rows) ``` Поскольку подходящего индекса нет, используется последовательное сканирование. Создадим хеш-индекс по полю seat\_no и повторим запрос: ```sql => CREATE INDEX ON seats USING hash(seat_no); ``` ```sql CREATE INDEX ``` ```sql => EXPLAIN (costs off) SELECT * FROM seats WHERE seat_no = '31D'; ``` ```text QUERY PLAN ----------------------------------------------------- Bitmap Heap Scan on seats Recheck Cond: ((seat_no)::text = '31D'::text) -> Bitmap Index Scan on seats_seat_no_idx Index Cond: ((seat_no)::text = '31D'::text) (4 rows) ``` Теперь планировщик использует хеш-индекс и строит битовую карту. Поменяем условие равенства на «больше»: ```sql => EXPLAIN (costs off) SELECT * FROM seats WHERE seat_no > '31D'; ``` ```text QUERY PLAN ------------------------------------------- Seq Scan on seats Filter: ((seat_no)::text > '31D'::text) (2 rows) ``` С неравенствами хеш-индекс использоваться не может. ## Page 6: Пример индекса GiST {#source-page-006} ![Source slide page 6: Пример индекса GiST](images/page-006.png){#source-figure-006 caption="Source slide page 6: Пример индекса GiST"} ### Комментарии преподавателя GiST расшифровывается как generalized search tree — обобщенное дерево поиска. Идею работы GiST-индексов рассмотрим на примере точек на плоскости. Плоскость разбивается на несколько прямоугольников, которые в сумме покрывают все индексируемые точки. Эти прямоугольники составляют верхний уровень дерева. Как видно на рисунке, прямоугольники могут пересекаться (хотя это и уменьшает эффективность поиска). ## Page 7: Пример индекса GiST {#source-page-007} ![Source slide page 7: Пример индекса GiST](images/page-007.png){#source-figure-007 caption="Source slide page 7: Пример индекса GiST"} ### Комментарии преподавателя На следующем уровне дерева каждый из больших прямоугольников распадается на прямоугольники меньшего размера. ## Page 8: Пример индекса GiST {#source-page-008} ![Source slide page 8: Пример индекса GiST](images/page-008.png){#source-figure-008 caption="Source slide page 8: Пример индекса GiST"} ### Комментарии преподавателя На последнем уровне дерева каждый ограничивающий прямоугольник будет содержать столько точек, сколько помещается на одну индексную страницу. Общее условие разбиения таково: прямоугольник родительской вершины охватывает все прямоугольники соответствующего поддерева. Это позволяет, например, быстро находить точки, лежащие внутри определенной области: 1) находим прямоугольники, пересекающиеся с заданной областью, на верхнем уровне индекса; 2) спускаемся в выбранные поддеревья и повторяем поиск в них. Такой алгоритм индексирования называется R-деревом. ## Page 9: Индекс GiST {#source-page-009} **Сбалансированное дерево поиска** - рассчитано на произвольные типы данных - не требуется упорядоченность - Типичные поддерживаемые операции - вхождение в область - нахождение слева, справа, сверху, снизу от области - Поиск ближайших соседей - первые k значений, ближайших к заданному ### Комментарии преподавателя B-дерево представляет собой сбалансированное дерево, значения в котором упорядочены в соответствии с операциями «больше» и «меньше». Индекс GiST также образует сбалансированное дерево, но значения в нем распределяются по другому принципу, например, на основе взаимного расположения точек на плоскости. Поэтому GiST можно применять к тем типам данных, для которых не имеют смысла операции «больше» и «меньше», и ускорять другие, более важные для этих типов, операции. Например, для геометрических фигур на плоскости индекс GiST может ускорять поиск вхождения значения в определенную область или поиск значений, находящихся в определенной стороне от заданной области. Другая важная особенность GiST-индекса — поддержка поиска ближайших соседей. Индекс позволяет быстро получить несколько значений, ближайших к заданному. ## Page 10: Индекс GiST {#source-page-010} Для демонстрации работы GiST-индекса обратимся к таблице airports\_data (на этой таблице построено представление airports). В таблице есть поле coordinates типа point, по нему и будем строить GiST-индекс. Но сначала выполним следующий запрос — найдем все аэропорты, находящиеся недалеко от Москвы: ```sql => EXPLAIN (costs off) SELECT airport_code FROM airports_data WHERE coordinates <@ '<(37.622513,55.753220),1.0>'::circle; ``` ```text QUERY PLAN --------------------------------------------------------------- Seq Scan on airports_data Filter: (coordinates <@ '<(37.622513,55.75322),1>'::circle) (2 rows) ``` Без индекса просматривается вся таблица. Создадим GiST-индекс: ```sql => CREATE INDEX airports_gist_idx ON airports_data USING gist(coordinates); ``` ```sql CREATE INDEX ``` Таблица airports\_data невелика, поэтому планировщик все равно будет использовать последовательное сканирование. Временно отключим этот метод доступа: ```text => SET enable_seqscan = off; ``` ```text SET ``` Повторим запрос: ```sql => EXPLAIN (costs off) SELECT airport_code FROM airports_data WHERE coordinates <@ '<(37.622513,55.753220),1.0>'::circle; ``` ```text QUERY PLAN ------------------------------------------------------------------- Index Scan using airports_gist_idx on airports_data Index Cond: (coordinates <@ '<(37.622513,55.75322),1>'::circle) (2 rows) ``` Теперь планировщик получает нужные строки, обращаясь к индексу airports\_gist\_idx. Удалим созданный индекс: ```sql => DROP INDEX airports_gist_idx; ``` ```sql DROP INDEX ``` ## Page 11: Класс операторов {#source-page-011} ![Source slide page 11: Класс операторов](images/page-011.png){#source-figure-011 caption="Source slide page 11: Класс операторов"} **Посредник между индексным методом и типом данных** - Может включать значительную часть логики индексирования ### Комментарии преподавателя Чтобы индексные методы доступа могли работать с разными типами данных (которые в PostgreSQL могут подключаться на лету), между индексами и типами данных есть посредник — класс операторов, в который входят необходимые операторы и вспомогательные функции. Такие индексы, как B-деревья и хеш-индексы, тоже используют классы операторов, но они довольно простые: содержат такие операторы, как «равно», «больше» и «меньше». Классы операторов для GiST-индексов включают в себя значительную часть логики индексирования и определяют правила, по которым значения добавляются в индекс и затем ищутся в нем. Поэтому для разных типов данных GiST может ускорять разные операции. Например, с помощью GiST можно индексировать значения диапазонных типов (таких, как int4range или tstzrange). Такой индекс позволяет находить диапазоны, включенные в заданный диапазон, пересекающиеся с заданным диапазоном, примыкающие к заданному диапазону и т. п. GiST можно считать каркасом, на основе которого строятся произвольные схемы индексации (не только R-дерево), реализуя нужным образом класс операторов. Это гораздо проще, чем создать с нуля новый тип индекса, что весьма трудоемко и требует очень высокой квалификация разработчика. ## Page 12: Пример индекса SP-GiST {#source-page-012} ![Source slide page 12: Пример индекса SP-GiST](images/page-012.png){#source-figure-012 caption="Source slide page 12: Пример индекса SP-GiST"} ### Комментарии преподавателя SP-GiST расшифровывается как space partitioning GiST. Это тоже обобщенное дерево поиска, но оно строится путем разбиения пространства поиска на непересекающиеся области. Рассмотрим пример индекса SP-GiST для точек на плоскости. Один из вариантов — дерево квадрантов. Корневой узел разбивает плоскость на четыре части (квадранта) по отношению к выбранной точке-центроиду. ## Page 13: Пример индекса SP-GiST {#source-page-013} ![Source slide page 13: Пример индекса SP-GiST](images/page-013.png){#source-figure-013 caption="Source slide page 13: Пример индекса SP-GiST"} ### Комментарии преподавателя Далее каждый из четырех квадрантов делится на собственные квадранты. ## Page 14: Пример индекса SP-GiST {#source-page-014} ![Source slide page 14: Пример индекса SP-GiST](images/page-014.png){#source-figure-014 caption="Source slide page 14: Пример индекса SP-GiST"} ### Комментарии преподавателя Разбиение продолжится, пока все точки в квадранте не станут помещаться на одну индексную страницу. ## Page 15: Индекс SP-GiST {#source-page-015} **Несбалансированное дерево поиска** - слабо ветвящееся дерево с большой глубиной - рассчитано на произвольные типы данных - Операции аналогичны GiST - вхождение в область - нахождение слева, справа, сверху, снизу от области - поиск ближайших соседей ### Комментарии преподавателя SP-GiST, как и GiST, является каркасом, на основе которого, реализуя классы операторов, можно строить произвольные схемы индексации. Например, дерево квадрантов для точек реализуется классом операторов point\_ops. Другой способ деления плоскости — на две, а не на четыре части. Такой способ называется k-мерным деревом и реализуется другим классом операторов — kd\_point\_ops. Разбиение плоскости на непересекающиеся области порождает несбалансированные деревья, которые обычно слабо ветвятся и имеют большую глубину. Как правило, индексы SP-GiST предоставляют поддержку тех же типов данных и операторов, что и GiST. Но из-за другой структуры индекса они могут оказаться как более, так и менее эффективными, чем GiST. ## Page 16: Индекс SP-GiST {#source-page-016} Создадим индекс SP-GiST по полю coordinates в таблице airports\_data. Для точек есть два класса операторов. По умолчанию используется point\_ops (дерево квадрантов), а мы для примера укажем kd\_point\_ops (k-мерное дерево): ```sql => CREATE INDEX airports_spgist_idx ON airports_data USING spgist(coordinates kd_point_ops); ``` ```sql CREATE INDEX ``` Попробуем найти все аэропорты, расположенные выше (севернее) Надыма: ```sql => EXPLAIN (costs off) SELECT airport_code FROM airports_data WHERE coordinates >^ '(72.69889831542969,65.48090362548828)'::point; ``` ```text QUERY PLAN ------------------------------------------------------------------------------------- Bitmap Heap Scan on airports_data Recheck Cond: (coordinates >^ '(72.69889831542969,65.48090362548828)'::point) -> Bitmap Index Scan on airports_spgist_idx Index Cond: (coordinates >^ '(72.69889831542969,65.48090362548828)'::point) (4 rows) ``` Теперь таблица сканируется по битовой карте, построенной на основе SP-GiST-индекса airports\_spgist\_idx. ## Page 17: Идея GIN-индекса {#source-page-017} ![Source slide page 17: Идея GIN-индекса](images/page-017.png){#source-figure-017 caption="Source slide page 17: Идея GIN-индекса"} ### Комментарии преподавателя GIN — generalized inverted index, обобщенный инвертированный индекс. Идею этого индексного метода проще всего понять на примере предметного указателя в обычной книге. На страницах книги встречаются термины, а в предметном указателе перечислены в алфавитном порядке все термины с указанием номеров страниц, на которых они присутствуют. Индекс GIN в первую очередь используется для индексации документов с целью ускорения полнотекстового поиска. По сути, это обычное B-дерево, но в него помещены не сами документы, а составляющие их слова. GIN-индекс оптимизирован с учетом того, что каждое слово может встречаться во многих документах. Если «список страниц» очень велик, он может храниться не в самой индексной странице, а в отдельном B-дереве. ## Page 18: Индекс GIN {#source-page-018} **Инвертированный список** - для типов данных, значения которых (документы) состоят из элементов - индексируются элементы, а не документы - Типичная поддерживаемая операция - проверка соответствия документа поисковому запросу - проверка вхождения элемента в массив - поиск документов JSON по ключам или значениям ### Комментарии преподавателя GIN работает с типами данных, значения которых состоят из элементов, а не являются атомарными. При этом индексируются не сами значения, а их элементы. Как и GiST и SP-GiST, метод GIN является каркасом, который можно настроить не только на работу с текстом (состоящим из слов), но и с другими типами данных: с массивами (состоящими из элементов), с документами JSON (состоящими из ключей и значений). Для этого создается класс операторов, реализующий разбиение документа на элементы и проверку соответствия документа поисковому запросу. ## Page 19: Индекс GIN {#source-page-019} В столбце days\_of\_week представления routes хранится массив номеров дней недели, по которым выполняется рейс: ```sql => SELECT flight_no, days_of_week FROM routes LIMIT 5; ``` ```text flight_no | days_of_week -----------+-------------- PG0001 | {6} PG0002 | {7} PG0003 | {2,6} PG0004 | {3,7} PG0005 | {2,5,7} (5 rows) ``` Для представления нельзя построить индекс, поэтому сохраним его строки в отдельной таблице: ```sql => CREATE TABLE routes_tbl AS SELECT * FROM routes; ``` ```sql SELECT 710 ``` Теперь создадим GIN-индекс: ```sql => CREATE INDEX routestbl_gin_idx ON routes_tbl USING gin(days_of_week); ``` ```sql CREATE INDEX ``` С помощью GIN-индекса можно, например, отобрать рейсы, отправляющиеся только по средам и субботам: ```sql => EXPLAIN (costs off) SELECT flight_no, departure_airport_name AS departure, arrival_airport_name AS arrival, days_of_week FROM routes_tbl WHERE days_of_week = ARRAY[3,6]; ``` ```text QUERY PLAN --------------------------------------------------------- Bitmap Heap Scan on routes_tbl Recheck Cond: (days_of_week = '{3,6}'::integer[]) -> Bitmap Index Scan on routestbl_gin_idx Index Cond: (days_of_week = '{3,6}'::integer[]) (4 rows) ``` Созданный GIN-индекс содержит всего семь элементов: целые числа от 1 до 7, представляющие дни недели. Для каждого из них в индексе хранятся ссылки на рейсы, выполняющиеся в этот день. ## Page 20: Пример индекса BRIN {#source-page-020} ![Source slide page 20: Пример индекса BRIN](images/page-020.png){#source-figure-020 caption="Source slide page 20: Пример индекса BRIN"} ```sql SELECT * FROM t WHERE temperature BETWEEN 20 AND 30; ``` ### Комментарии преподавателя BRIN — block range index, «индекс зон блоков». Таблица разбивается на зоны определенной (настраиваемой) длины, каждая из которых охватывает набор последовательно расположенных страниц. Например, на слайде показан индекс с зоной размером 128 страниц. По каждой зоне собирается сводная информация, например, минимум и максимум значений индексируемого столбца. При выполнении запроса можно пропустить все зоны, значения в которых гарантированно не попадают под условие. В примере на слайде только две зоны могут содержать значения температуры, удовлетворяющие условию. Индекс BRIN не хранит идентификаторы версий строк, как все другие индексы, поэтому в выбранных зонах необходимо просмотреть все версии строк. В некотором смысле BRIN можно рассматривать как ускоритель последовательного сканирования. ## Page 21: Индекс BRIN {#source-page-021} **Список зон со сводной информацией** - зона охватывает группу последовательно расположенных страниц - сводная информация: минимум, максимум и т. п. - требуется корреляция с физическим расположением строк - Не хранит ссылки на версии строк - только сканирование по битовой карте - Предназначен для очень больших таблиц - небольшой размер - настраиваемое соотношение размера и точности ### Комментарии преподавателя Используя классы операторов, можно выбирать сводную информацию, хранимую в индексе для каждой зоны. Это может быть как просто минимум и максимум, так и несколько диапазонов значений, а для геометрических типов данных можно хранить охватывающий прямоугольник (как в GiST). В любом случае для эффективной работы BRIN необходима корреляция между значениями столбца и физическим расположением строк, чтобы в одну зону попадали значения со сходной сводной информацией. Обновления данных нарушают корреляцию и могут сказаться на эффективности индекса. Поскольку BRIN не хранит ссылки на версии строк, он возвращает перечень страниц зоны в виде неточной битовой карты. Обычное индексное сканирование (и сканирование только индекса) невозможны. Зато BRIN-индекс имеет очень небольшой размер, который, к тому же, может настраиваться за счет указания размера зоны. Чем больше зона, тем меньше индекс, но меньше и точность. Благодаря этому BRIN идеально подходит для очень больших таблиц, характерных для хранилищ данных. ## Page 22: Индекс BRIN {#source-page-022} Для примера построим индекс BRIN по самой большой таблице: ```sql => CREATE INDEX tflights_brin_idx ON ticket_flights USING brin(flight_id); ``` ```sql CREATE INDEX ``` Индексы BRIN дают ощутимый эффект только для очень больших таблиц. Тем не менее, планировщик использует построенный индекс, поскольку по сводной информации (минимум и максимум) можно отсечь зоны, не содержащие требуемых значений: ```sql => EXPLAIN (analyze, costs off, timing off) SELECT * FROM ticket_flights WHERE flight_id BETWEEN 3000 AND 4000; ``` ```text QUERY PLAN --------------------------------------------------------------------------------- Gather (actual rows=46357 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Bitmap Heap Scan on ticket_flights (actual rows=15452 loops=3) Recheck Cond: ((flight_id >= 3000) AND (flight_id <= 4000)) Rows Removed by Index Recheck: 2377352 Heap Blocks: lossy=20149 -> Bitmap Index Scan on tflights_brin_idx (actual rows=598210 loops=1) Index Cond: ((flight_id >= 3000) AND (flight_id <= 4000)) Planning Time: 77.935 ms Execution Time: 4364.705 ms (11 rows) ``` Поскольку BRIN не хранит ссылки на версии строк, единственный возможный способ доступа — сканирование по неточной (lossy) битовой карте. ## Page 23: Итоги {#source-page-023} **Помимо B-дерева, существуют и другие, более специфичные типы индексов** - Хеш-индекс для поиска по равенству - GiST и SP-GiST для несортируемых типов данных - GIN для документов - BRIN для очень больших таблиц ## Page 24: Практика {#source-page-024} 1. Сравните размеры и время построения хеш-индекса по полям разного размера (book\_ref и contact\_data) таблицы билетов tickets. Повторите то же для индекса на основе B-дерева. 2. Воспользуйтесь GIN-индексом и расширением pg\_trgm для поиска пассажиров, номер телефона которых содержит последовательность цифр 1234. Можно ли ускорить такой запрос с помощью B-дерева? ### Комментарии преподавателя 1. Для оценки размера индекса можно использовать функцию pg\_total\_relation\_size('имя-индекса'). 2. Создайте GIN-индекс по выражению contact\_data->>'phone', используя класс операторов gin\_trgm\_ops из расширения pg\_trgm. Страница документации: ## Page 25: 1. Сравнение размера и времени создания индексов {#source-page-025} Посмотрим структуру таблицы tickets: ```sql => \d tickets ``` ```text Table "bookings.tickets" Column | Type | Collation | Nullable | Default ----------------+-----------------------+-----------+----------+--------- ticket_no | character(13) | | not null | book_ref | character(6) | | not null | passenger_id | character varying(20) | | not null | passenger_name | text | | not null | contact_data | jsonb | | | Indexes: "tickets_pkey" PRIMARY KEY, btree (ticket_no) Foreign-key constraints: "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref) Referenced by: TABLE "ticket_flights" CONSTRAINT "ticket_flights_ticket_no_fkey" FOREIGN KEY (ticket_no) REFERENCES tickets(ticket_no) ``` Поле book\_ref имеет фиксированный размер, а поле contact\_data имеет тип jsonb. Включим подсчет времени выполнения запросов: ```text => \timing on ``` ```text Timing is on. ``` Создадим hash-индексы... ```sql => CREATE INDEX tickets_hash_br ON tickets USING hash(book_ref); ``` ```sql CREATE INDEX Time: 12494,426 ms (00:12,494) ``` ```sql => CREATE INDEX tickets_hash_cd ON tickets USING hash(contact_data); ``` ```sql CREATE INDEX Time: 12111,018 ms (00:12,111) ``` ...и индексы B-tree: ```sql => CREATE INDEX tickets_btree_br ON tickets(book_ref); ``` ```sql CREATE INDEX Time: 27089,915 ms (00:27,090) ``` ```sql => CREATE INDEX tickets_btree_cd ON tickets(contact_data); ``` ```sql CREATE INDEX Time: 72766,842 ms (01:12,767) ``` Время создания хеш-индексов примерно одинаково, а время создания btree-индексов зависит от размера индексируемого поля. ```text => \timing off ``` ```text Timing is off. ``` Теперь проверим размеры полученных индексов: ```sql => SELECT pg_size_pretty(pg_total_relation_size('tickets_hash_br')) "hash book_ref", pg_size_pretty(pg_total_relation_size('tickets_hash_cd')) "hash contact_data", pg_size_pretty(pg_total_relation_size('tickets_btree_br')) "btree book_ref", pg_size_pretty(pg_total_relation_size('tickets_btree_cd')) "btree contact_data" \gx ``` ```text -[ RECORD 1 ]------+------- hash book_ref | 81 MB hash contact_data | 80 MB btree book_ref | 59 MB btree contact_data | 226 MB ``` Размер hash-индексов тоже примерно одинаковый. А размер индексов B-tree (как и время их построения) зависит от размера индексируемого поля, поскольку такие индексы хранят индексируемые значения. ## Page 26: 2. Расширение pg_trgm {#source-page-026} Выполним запрос: ```sql => EXPLAIN (analyze, buffers, costs off, timing off) SELECT * FROM tickets WHERE contact_data->>'phone' LIKE '%1234%'; ``` ```text QUERY PLAN ---------------------------------------------------------------------- Gather (actual rows=1801 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=128 read=49287 -> Parallel Seq Scan on tickets (actual rows=600 loops=3) Filter: ((contact_data ->> 'phone'::text) ~~ '%1234%'::text) Rows Removed by Filter: 982685 Buffers: shared hit=128 read=49287 Planning: Buffers: shared hit=24 read=9 dirtied=1 Planning Time: 4.555 ms Execution Time: 2995.486 ms (12 rows) ``` Обратите внимание на количество прочитанных страниц (Buffers) — их почти пятьдесят тысяч. Добавим расширение pg\_trgm: ```sql => CREATE EXTENSION pg_trgm; ``` ```sql CREATE EXTENSION ``` Создадим GIN-индекс с классом операторов gin\_trgm\_ops: ```sql => CREATE INDEX tickets_gin ON tickets USING GIN ((contact_data->>'phone') gin_trgm_ops); ``` ```sql CREATE INDEX ``` Такой класс операторов ускоряет поиск по шаблону, который начинается на знак процента, и даже по регулярным выражениям. Индекс на основе B-дерева этого не позволяет. Повторим запрос: ```sql => EXPLAIN (analyze, buffers, costs off, timing off) SELECT * FROM tickets WHERE contact_data->>'phone' LIKE '%1234%'; ``` ```text QUERY PLAN -------------------------------------------------------------------------- Bitmap Heap Scan on tickets (actual rows=1801 loops=1) Recheck Cond: ((contact_data ->> 'phone'::text) ~~ '%1234%'::text) Rows Removed by Index Recheck: 46 Heap Blocks: exact=1820 Buffers: shared hit=23 read=1818 written=30 -> Bitmap Index Scan on tickets_gin (actual rows=1847 loops=1) Index Cond: ((contact_data ->> 'phone'::text) ~~ '%1234%'::text) Buffers: shared hit=21 Planning: Buffers: shared hit=26 read=1 dirtied=1 Planning Time: 0.561 ms Execution Time: 308.370 ms (12 rows) ``` Количество прочитанных страниц сократилось более чем на порядок, время выполнения существенно уменьшилось.