Индексы

Андрей Аксенов

Contents

Обзор
Создание индексов
Просмотр списка индексов
Обслуживание индексов
Анализ использования индексов
Удаление индексов

В традиционных СУБД индексы могут значительно ускорить доступ к данным. В распределенных системах, таких как Greengage DB (на основе Greenplum), индексы следует применять более избирательно. Greengage DB выполняет последовательные сканирования параллельно — каждый сегмент сканирует только свой набор данных. Партиционирование может дополнительно уменьшить размер сканируемых данных.

Аналитические запросы обычно обрабатывают большие объемы данных, поэтому использование индексов может не давать эффекта. Прежде чем создавать индексы, протестируйте ваши запросы без них, чтобы определить базовый уровень производительности. Индексы могут помочь при OLTP-нагрузках или выборках с высокой селективностью, особенно для сжатых оптимизированных для добавления (AO) таблиц, где индексы позволяют выполнять распаковку только нужных строк.

Типы индексов PostgreSQL

Greengage DB поддерживает следующие типы индексов PostgreSQL:

B-дерево

Индексы B-дерево могут работать с запросами на равенство и проверку диапазона данных, которые можно отсортировать в определенном порядке. Планировщик запросов рассматривает возможность использования индекса B-дерево каждый раз, когда индексированный столбец участвует в сравнении с использованием одного из этих операторов: <, ⇐, =, >= и >.
GiST

Индексы GiST (Generalized Search Tree) предназначены для работы со сложными типами данных, такими как геометрические объекты, текст и массивы. Индексы GiST позволяют быстро искать пространственные, текстовые и иерархические данные.
SP-GiST

Индексы SP-GiST (space-partitioned GiST), как и индексы GiST, предлагают инфраструктуру, поддерживающую различные виды поиска. SP-GiST позволяет организовывать на диске разнообразные несбалансированные структуры данных, такие как деревья квадрантов, k-мерные и сжатые префиксные деревья.
GIN

Индексы GIN (Generalized Inverted Index) — это инвертированные индексы, подходящие для данных с несколькими ключами. Они используются для полнотекстового поиска, поиска в массивах, данных JSON и триграммах.

Подробное описание этих типов индексов смотрите в разделе Index Types документации PostgreSQL.

Помимо индексов PostgreSQL, Greengage DB также поддерживает bitmap-индексы, которые оптимизированы для аналитических нагрузок.

Bitmap-индексы лучше всего подходят для хранилищ с большим объемом данных, большим количеством произвольных запросов и редкими операциями модификации данных. Они могут занимать значительно меньше места по сравнению с другими типами индексов, обеспечивая при этом ту же функциональность.

Bitmap-индексы наиболее эффективны для SQL-запросов, которые содержат несколько условий в выражении WHERE. Они позволяют исключить из выборки строки, не соответствующие всем условиям, еще до обращения к таблице.

Bitmap-индексы эффективны на столбцах с количеством уникальных значений от 100 до 100000, особенно если по этому столбцу часто выполняются запросы вместе с другими индексированными столбцами. Однако они не подходят для уникальных столбцов или столбцов с высокой селективностью (например, имен клиентов или номеров телефонов), а также для OLTP-систем с большим числом параллельных операций модификации данных.

Чтобы создать индекс по указанному столбцу таблицы или материализованного представления, используйте команду CREATE INDEX. Чтобы изменить определение существующего индекса, используйте ALTER INDEX.

Ниже приведен упрощенный синтаксис команды CREATE INDEX:

CREATE [UNIQUE] INDEX [<name>] ON <table_name> [USING <method>]
    ( {<column_name> | (<expression>)} [, ...] )
    [ WHERE <predicate> ]

Основные выражения и аргументы для определения индекса следующие:

UNIQUE

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

ПРИМЕЧАНИЕ

Обратите внимание, что указание ограничения уникальности неявно создает уникальный индекс.
<name>

Имя создаваемого индекса. Индекс всегда создается в той же схеме, что и родительская таблица. Если имя не указано, Greengage DB выбирает подходящее имя на основе имени родительской таблицы и имени индексируемого столбца.
ON <table_name>

Имя (с возможным указанием схемы) таблицы или материализованного представления, для которых создается индекс.
USING <method>

Тип создаваемого индекса. Возможные значения: btree (по умолчанию), bitmap, gist, spgist и gin.
<column_name>

Имя столбца в таблице, по которому создается индекс. Можно указать несколько столбцов, если метод индекса поддерживает составные индексы. Смотрите раздел Создание составного индекса ниже.
<expression>

Выражение на основе одного или нескольких столбцов таблицы. Смотрите раздел Использование индексов по выражениям ниже.
WHERE <predicate>

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

В таблице приведены ограничения поддерживаемых в Greengage DB типов индексов.

Тип индекса	Составные индексы	Уникальные индексы	Поддержка GPORCA
B-дерево	да	да (только heap-таблицы)	да
Bitmap	нет	нет	да
GiST	да	нет	да
SP-GiST	нет	нет	нет
GIN	да	нет	да

Тип индекса

Составные индексы

Уникальные индексы

Поддержка GPORCA

B-дерево

да

да (только heap-таблицы)

да

Bitmap

нет

да

GiST

да

нет

да

SP-GiST

нет

GIN

да

нет

да

GPORCA не использует индексы, созданные с использованием неподдерживаемых методов. Следующие возможности не поддерживаются при включенном GPORCA:

Частичные индексы
Индексы по выражениям

Учитывайте приведенные ниже пункты при создании индексов. Смотрите также: Анализ использования индексов.

Характер ваших запросов.

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

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

Если создать индекс на часто обновляемом столбце, то при изменении этого столбца увеличится число операций записи.

Создавайте селективные индексы B-дерево.

Селективность индекса — это количество уникальных значений в столбце, деленное на общее число строк в таблице. Например, если в таблице 1000 строк и в одном из столбцов содержится 800 уникальных значений, селективность индекса составляет 0.8, что считается хорошим показателем. Уникальные индексы всегда имеют селективность 1.0 — это наилучший возможный вариант.
Используйте bitmap-индексы для столбцов с низкой селективностью.

Смотрите раздел Bitmap-индексы.
Избегайте перекрывающихся индексов.

Индексы с одинаковым первым столбцом избыточны. Например, если есть индекс по (category, product), отдельный индекс по (category) не требуется.

Индексация столбцов, используемых в соединениях.

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

Столбцы, которые часто используются в выражениях WHERE, зачастую подходят для индексации. Это относится не только к запросам SELECT, но и к UPDATE и DELETE, использующим такие выражения.

Обслуживание индексов

Удаляйте индексы перед массовой загрузкой данных.

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

Кластеризация таблицы по индексу упорядочивает записи на диске в соответствии с индексом. Когда данные хранятся последовательно (например, по дате), запросы, выполняющие сканирование диапазона, могут извлекать данные эффективнее за счет быстрого последовательного чтения. Смотрите раздел Кластеризация таблицы по индексу ниже.

Предварительные требования

Для выполнения команд, описанных в следующих разделах, подключитесь к мастер-хосту Greengage DB с помощью psql, как описано в статье Подключение к Greengage DB с использованием psql. Создайте новую базу данных и подключитесь к ней:

CREATE DATABASE media_library;
\c media_library

Затем создайте две таблицы и заполните их данными:

Создание таблиц

AO-таблица

CREATE TABLE movies
(
    id     INTEGER,
    title  TEXT NOT NULL,
    genre  TEXT NOT NULL,
    year   INT,
    rating NUMERIC(3, 1)
)
    WITH (appendoptimized = true, orientation = row)
    DISTRIBUTED BY (id);

INSERT INTO movies (id, title, genre, year, rating)
SELECT gs                                        AS id,
       'Movie ' || gs                            AS title,
       genres[(random() * 4)::int + 1],
       (1925 + (random() * 100)::int),
       round((7.0 + random() * 3.0)::numeric, 1) AS rating
FROM generate_series(1, 100000) gs,
     LATERAL(VALUES(ARRAY ['Sci-Fi', 'Crime', 'Romance', 'Action', 'Drama'])) AS g(genres);

Heap-таблица

CREATE TABLE actors
(
    id   INTEGER,
    name TEXT NOT NULL
)
    DISTRIBUTED BY (id);

INSERT INTO actors (id, name)
SELECT gs                                                                                 AS actor_id,
       first_names[(random() * 4)::int + 1] || ' ' || last_names[(random() * 4)::int + 1] AS name
FROM generate_series(1, 1000) gs,
     LATERAL(VALUES(ARRAY ['John','Emily','Michael','Sarah','David'])) AS f(first_names),
     LATERAL(VALUES(ARRAY ['Smith','Johnson','Williams','Brown','Davis'])) AS l(last_names);

Следующая команда создает индекс B-дерево по столбцу title в таблице movies:

CREATE INDEX movies_title_idx ON movies (title);

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

SELECT *
FROM movies
WHERE title = 'Movie 12345';

План запроса

                       QUERY PLAN
---------------------------------------------------------
 Gather Motion 4:1  (slice1; segments: 4)
   ->  Bitmap Heap Scan on movies
         Recheck Cond: (title = 'Movie 12345'::text)
         ->  Bitmap Index Scan on movies_title_idx
               Index Cond: (title = 'Movie 12345'::text)
 Optimizer: Pivotal Optimizer (GPORCA)
(6 rows)

Смотрите также: Анализ использования индексов.

Следующий SQL-запрос создает bitmap-индекс по столбцу genre:

CREATE INDEX movies_genre_idx ON movies USING bitmap (genre);

Следующий SELECT-запрос будет использовать созданный индекс:

SELECT AVG(rating) AS avg_rating
FROM movies
WHERE genre = 'Sci-Fi';

План запроса

                           QUERY PLAN
----------------------------------------------------------------
 Aggregate
   ->  Gather Motion 4:1  (slice1; segments: 4)
         ->  Aggregate
               ->  Bitmap Heap Scan on movies
                     Recheck Cond: (genre = 'Sci-Fi'::text)
                     ->  Bitmap Index Scan on movies_genre_idx
                           Index Cond: (genre = 'Sci-Fi'::text)
 Optimizer: Pivotal Optimizer (GPORCA)
(8 rows)

Ниже приведена команда для создания индекса movies_genre_year_idx по столбцам genre и year:

CREATE INDEX movies_genre_year_idx ON movies (genre, year);

Созданный индекс должен ускорить выполнение запросов с фильтрацией по genre и year, например:

SELECT AVG(rating) AS avg_rating
FROM movies
WHERE genre = 'Sci-Fi'
  AND year = 2010;

План запроса

                                     QUERY PLAN
------------------------------------------------------------------------------------
 Aggregate
   ->  Gather Motion 4:1  (slice1; segments: 4)
         ->  Aggregate
               ->  Bitmap Heap Scan on movies
                     Recheck Cond: ((genre = 'Sci-Fi'::text) AND (year = 2010))
                     ->  Bitmap Index Scan on movies_genre_year_idx
                           Index Cond: ((genre = 'Sci-Fi'::text) AND (year = 2010))
 Optimizer: Pivotal Optimizer (GPORCA)
(8 rows)

Команда ниже создает уникальный индекс B-дерево по столбцу id в heap-таблице actors:

CREATE UNIQUE INDEX actors_id_idx ON actors (id);

Пример запроса, который использует индекс, выглядит следующим образом:

SELECT *
FROM actors
WHERE id = 123;

План запроса

                   QUERY PLAN
------------------------------------------------
 Gather Motion 1:1  (slice1; segments: 1)
   ->  Index Scan using actors_id_idx on actors
         Index Cond: (id = 123)
 Optimizer: Pivotal Optimizer (GPORCA)
(4 rows)

Частичные индексы поддерживаются только планировщиком Postgres. Чтобы отключить оптимизатор SQL-запросов GPORCA на уровне сессии, установите параметр optimizer в значение off:

SET optimizer = off;

Команда ниже создает частичный индекс по столбцу year для ускорения запросов, запрашивающих фильмы, вышедшие после 2000 года:

CREATE INDEX movies_year_post_2000_idx ON movies (year)
    WHERE year > 2000;

Пример запроса SELECT может выглядеть следующим образом:

SELECT AVG(rating) AS avg_rating
FROM movies
WHERE year = 2020;

План запроса

                               QUERY PLAN
------------------------------------------------------------------------
 Aggregate
   ->  Gather Motion 4:1  (slice1; segments: 4)
         ->  Aggregate
               ->  Bitmap Heap Scan on movies
                     Recheck Cond: (year = 2020)
                     ->  Bitmap Index Scan on movies_year_post_2000_idx
                           Index Cond: (year = 2020)
 Optimizer: Postgres query optimizer
(8 rows)

Индексы по выражениям поддерживаются только планировщиком Postgres.

Ниже приведен SQL-запрос для создания индекса, который можно использовать для выполнения регистронезависимых сравнений с помощью функции lower():

CREATE INDEX movies_title_lower_idx ON movies (lower(title));

Пример запроса с использованием этого индекса может выглядеть так:

SELECT *
FROM movies
WHERE lower(title) = lower('Movie 12345');

План запроса

                           QUERY PLAN
----------------------------------------------------------------
 Gather Motion 4:1  (slice1; segments: 4)
   ->  Bitmap Heap Scan on movies
         Recheck Cond: (lower(title) = 'movie 12345'::text)
         ->  Bitmap Index Scan on movies_title_lower_idx
               Index Cond: (lower(title) = 'movie 12345'::text)
 Optimizer: Postgres query optimizer
(6 rows)

Чтобы просмотреть список индексов и их размеры, используйте метакоманду \di+:

\di+

Результат должен выглядеть так:

                                   List of relations
 Schema |           Name            | Type  |  Owner  | Table  |  Size   | Description
--------+---------------------------+-------+---------+--------+---------+-------------
 public | actors_id_idx             | index | gpadmin | actors | 288 kB  |
 public | movies_genre_idx          | index | gpadmin | movies | 960 kB  |
 public | movies_genre_year_idx     | index | gpadmin | movies | 3360 kB |
 public | movies_title_idx          | index | gpadmin | movies | 3360 kB |
 public | movies_title_lower_idx    | index | gpadmin | movies | 3360 kB |
 public | movies_year_post_2000_idx | index | gpadmin | movies | 928 kB  |
(6 rows)

Вы также можете выполнить запрос к системному представлению pg_indexes, чтобы получить информацию об индексах:

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'movies';

Столбец indexdef содержит определение каждого индекса:

         indexname         |                                            indexdef
---------------------------+------------------------------------------------------------------------------------------------
 movies_title_idx          | CREATE INDEX movies_title_idx ON public.movies USING btree (title)
 movies_genre_idx          | CREATE INDEX movies_genre_idx ON public.movies USING bitmap (genre)
 movies_genre_year_idx     | CREATE INDEX movies_genre_year_idx ON public.movies USING btree (genre, year)
 movies_year_post_2000_idx | CREATE INDEX movies_year_post_2000_idx ON public.movies USING btree (year) WHERE (year > 2000)
 movies_title_lower_idx    | CREATE INDEX movies_title_lower_idx ON public.movies USING btree (lower(title))
(5 rows)

Обслуживание индексов

После создания индексов необходимо собрать статистику с помощью ANALYZE:

ANALYZE;

Это помогает оптимизатору запросов оценивать количество строк и выбирать более эффективные планы выполнения запросов. Подробнее о сборе статистики рассказывается в статье Сбор статистики с помощью ANALYZE.

В Greengage DB представления pg_stat_*_indexes отображают статистику доступа к индексам только на мастере. Чтобы получить статистику по индексам, объединяющую данные с мастера и сегментов, создайте следующие представления:

Создайте представление, предоставляющее статистику доступа для всех индексов:

CREATE VIEW pg_stat_all_indexes_ggdb AS
SELECT s.relid,
       s.indexrelid,
       s.schemaname,
       s.relname,
       s.indexrelname,
       m.idx_scan,
       m.idx_tup_read,
       m.idx_tup_fetch
FROM (SELECT relid,
             indexrelid,
             schemaname,
             relname,
             indexrelname,
             sum(idx_scan)      as idx_scan,
             sum(idx_tup_read)  as idx_tup_read,
             sum(idx_tup_fetch) as idx_tup_fetch
      FROM gp_dist_random('pg_stat_all_indexes')
      WHERE relid >= 16384
      GROUP BY relid, indexrelid, schemaname, relname, indexrelname
      UNION ALL
      SELECT *
      FROM pg_stat_all_indexes
      WHERE relid < 16384) m,
     pg_stat_all_indexes s
WHERE m.relid = s.relid
  AND m.indexrelid = s.indexrelid;

Создайте представление, предоставляющее статистику для системных индексов:

CREATE VIEW pg_stat_sys_indexes_ggdb AS
SELECT *
FROM pg_stat_all_indexes_ggdb
WHERE schemaname IN ('pg_catalog', 'information_schema')
   OR schemaname ~ '^pg_toast';

Создайте представление, предоставляющее статистику доступа для пользовательских индексов:

CREATE VIEW pg_stat_user_indexes_ggdb AS
SELECT *
FROM pg_stat_all_indexes_ggdb
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
  AND schemaname !~ '^pg_toast';

Затем вы можете выполнить запрос к представлению pg_stat_user_indexes_ggdb, чтобы получить статистику использования индексов, созданных в разделе Создание индексов:

SELECT *
FROM pg_stat_user_indexes_ggdb
WHERE relname IN ('movies', 'actors');

Результат должен выглядеть так:

 relid  | indexrelid | schemaname | relname |       indexrelname        | idx_scan | idx_tup_read | idx_tup_fetch
--------+------------+------------+---------+---------------------------+----------+--------------+---------------
 450662 |     450686 | public     | movies  | movies_genre_year_idx     |        4 |          131 |             0
 450672 |     450687 | public     | actors  | actors_id_idx             |        1 |            1 |             1
 450662 |     450688 | public     | movies  | movies_year_post_2000_idx |        4 |          997 |             0
 450662 |     450689 | public     | movies  | movies_title_lower_idx    |        4 |            1 |             0
 450662 |     450682 | public     | movies  | movies_genre_idx          |        0 |            4 |             0
 450662 |     450678 | public     | movies  | movies_title_idx          |        4 |            1 |             0
(6 rows)

Используйте команду REINDEX для перестроения индексов с низкой производительностью. REINDEX пересоздает индекс на основе данных таблицы, заменяя старую копию индекса.

Ниже приведен SQL-запрос, который пересоздает все индексы таблицы movies:

REINDEX TABLE movies;

Следующий запрос пересоздает указанный индекс:

REINDEX INDEX movies_genre_idx;

После выполнения REINDEX статистика по индексу обнуляется. Чтобы актуализировать статистику, выполните ANALYZE для таблицы:

ANALYZE movies;

При массовой загрузке данных часто быстрее удалить индексы до загрузки и восстановить их после, чем обновлять индексы во время загрузки данных:

DROP INDEX movies_genre_idx;
CREATE INDEX movies_genre_idx ON movies USING bitmap (genre);

Если данные таблицы хранятся в том же порядке, что и индекс, запросы с диапазонными условиями работают быстрее. Когда записи упорядочены на диске, диапазонные запросы могут использовать быстрое последовательное чтение. Для этого нужно создать промежуточную таблицу и загрузить в нее данные в нужном порядке:

Создайте новую таблицу:

CREATE TABLE actors_new
(
    LIKE actors
)
    DISTRIBUTED BY (id);

Загрузите данные из исходной таблицы в новую в нужном порядке:
```
INSERT INTO actors_new
SELECT *
FROM actors
ORDER BY id;
```
Удалите исходную таблицу:
```
DROP TABLE actors;
```
Переименуйте новую таблицу, присвоив ей имя исходной таблицы:
```
ALTER TABLE actors_new
    RENAME TO actors;
```

Пересоздайте индекс:

CREATE UNIQUE INDEX actors_id_idx ON actors (id);

Выполните VACUUM ANALYZE для новой таблицы:
```
VACUUM ANALYZE actors;
```

Используйте команду EXPLAIN для анализа использования индекса SQL-запросом. Следующий запрос показывает план SQL-запроса SELECT из раздела Создание bitmap-индекса:

EXPLAIN (COSTS OFF)
SELECT AVG(rating) AS avg_rating
FROM movies
WHERE genre = 'Sci-Fi';

В результате должны использоваться Bitmap Heap Scan по таблице movies и Bitmap Index Scan по индексу movies_genre_idx:

                           QUERY PLAN
----------------------------------------------------------------
 Aggregate
   ->  Gather Motion 4:1  (slice1; segments: 4)
         ->  Aggregate
               ->  Bitmap Heap Scan on movies
                     Recheck Cond: (genre = 'Sci-Fi'::text)
                     ->  Bitmap Index Scan on movies_genre_idx
                           Index Cond: (genre = 'Sci-Fi'::text)
 Optimizer: Pivotal Optimizer (GPORCA)
(8 rows)

Учитывайте следующие моменты при тестировании индексов:

По возможности тестируйте на реальных данных. Индексы, хорошо работающие на тестовых данных, могут быть неэффективны в реальной эксплуатации.
Избегайте слишком маленьких тестовых наборов — результаты могут быть некорректны.
Внимательно подходите к проектированию тестовых данных. Одинаковые, случайные или отсортированные значения могут исказить статистику и не показать, как все будет работать на практике.
Для проверки работы индекса можно принудительно отключать определенные типы планов сканирования (например, используя параметры конфигурации enable_seqscan и enable_nestloop).
Сравнивайте производительность с индексами и без них с помощью команды EXPLAIN ANALYZE.

Чтобы удалить индекс, используйте команду DROP INDEX:

DROP INDEX movies_genre_idx;

Обратите внимание, что команда DROP TABLE удаляет все существующие индексы для указанной таблицы.

Последовательности

Представления и материализованные представления

Индексы

Обзор

Типы индексов

Типы индексов PostgreSQL

Bitmap-индексы

Обзор синтаксиса

Ограничения

Рекомендации по созданию индексов

Общий подход к созданию индексов

Тип индекса

Индексирование с учетом характера запросов

Обслуживание индексов

Создание индексов

Создание индекса B-дерево

Создание bitmap-индекса

Создание составного индекса

Создание уникального индекса

Создание частичного индекса

Использование индексов по выражениям

Просмотр списка индексов

Обслуживание индексов

Сбор статистики

Просмотр статистики по индексам

Перестроение индексов

Кластеризация таблицы по индексу

Анализ использования индексов

Удаление индексов