Индексы в PostgreSQL

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

Индексы B-tree являются наиболее распространенным типом индексов и будут использоваться по умолчанию, если вы создаете индекс и не указываете тип. Индексы B-дерева отлично подходят для универсального индексирования информации, которую вы часто запрашиваете.

Индексы BRIN — это индексы блочного диапазона, специально предназначенные для очень больших наборов данных, где данные, которые вы ищете, находятся в блоках, таких как временные метки и диапазоны дат. Известно, что они очень производительны и компактны.

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

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

EXPLAIN ANALYZE

Вы почти никогда не говорите об индексации Postgres, не упоминая функцию EXPLAIN. Это всего лишь один из тех швейцарских армейских ножей Postgres, которые нужно всегда иметь в кармане. EXPLAIN ANALYZE предоставит вам такую информацию, как план запроса, время выполнения и другую полезную информацию для любого запроса. Поэтому, когда вы работаете с индексами, вы будете проверять индексы, используя EXPLAIN ANALYZE, чтобы просмотреть путь запроса и время запроса.

Вы увидите, что в плане запроса указано «Seq scan» или последовательное сканирование. Это означает, что он просматривает каждую строку данных в таблице, чтобы увидеть, соответствует ли она условию запроса. Вы можете догадаться, что для больших таблиц последовательное сканирование может занять довольно много времени, поэтому индекс экономит нагрузку на вашу базу данных.

Seq Scan on weather (cost=0.00..168.00 rows=496 width=102) (actual time=0.011..0.181 rows=100 loops=1)

Если вы используете индекс, вы увидите сканирование индекса в результатах EXPLAIN.

Bitmap Index Scan on idx_weather_type  (cost=0.00..8.00 rows=496 width=0) (actual time=0.027..0.027 rows=496 loops=1

B-Tree

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

Запрос

SELECT * FROM weather where event_type='Winter Storm'

EXPLAIN ANALYZE, без индекса 

Seq Scan on weather  (cost=0.00..9204.64 rows=3158 width=853) (actual time=0.008..27.619 rows=3182 loops=1)
Execution Time: 27.778 ms

Индекс

CREATE INDEX idx_weather_type ON weather(event_type);

EXPLAIN ANALYZE

Bitmap Index Scan on idx_weather_type  (cost=0.00..35.98 rows=3158 width=0) (actual time=0.247..0.247 rows=3182 loops=1)
Execution Time: 3.005 ms

Как вы можете заметить время запроса существенно уменьшилось.

Многоколоночные B-tree

Запрос

SELECT * FROM weather WHERE event_type='Winter Storm' AND damage_crops > '0'

EXPLAIN ANALYZE, без индекса 

Seq Scan on weather  (cost=0.00..9402.36 rows=2586 width=853) (actual time=0.007..67.365 rows=2896 loops=1)
Execution Time: 67.499 ms

Индекс

CREATE INDEX idx_storm_crop ON weather(event_type,damage_crops);

EXPLAIN ANALYZE

Bitmap Index Scan on idx_storm_crop  (cost=0.00..38.15 rows=2586 width=0) (actual time=0.339..0.339 rows=2896 loops=1)
Execution Time: 2.204 ms

И снова мы сократили время запроса. Круто!

И если вы заблудитесь со всеми своими созданными индексами и вам нужно посмотреть, что у вас есть, это покажет вам все индексы в конкретной таблице:

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

Удаление индекса:

DROP INDEX idx_storm_crop;

BRIN

BRIN часто бывает очень полезен при использовании больших наборов данных, особенно временных рядов или данных с отметками времени. Для этого я использовал образец из набора данных IoT с тысячами строк данных в день.

Запрос

SELECT device, humidity FROM iot WHERE ts between '2020-07-13 0:00' AND '2020-07-14 0:00'

EXPLAIN ANALYZE, без индекса

Parallel Seq Scan on iot  (cost=0.00..10363.95 rows=896 width=28) (actual time=12.710..42.080 rows=16707 loops=3)
Execution Time: 67.851 ms

Индекс

CREATE INDEX iot_time ON iot USING brin(ts);

EXPLAIN ANALYZE

Bitmap Index Scan on iot_time  (cost=0.00..12.26 rows=54025 width=0) (actual time=0.046..0.047 rows=10240 loops=1)
Execution Time: 10.513 ms

Вы часто будете слышать, что индексы BRIN очень эффективно используют пространство. Поэтому, когда вы работаете с индексами, вы можете запросить размер фактического индекса. Например:

pg_size_pretty(pg_relation_size('iot_time'));

Пространственный индекс GIST

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

Запрос

SELECT count(blocks.blkid)
FROM nyc_census_blocks blocks
JOIN nyc_subway_stations subways
ON ST_Contains(blocks.geom, subways.geom)
 WHERE subways.name LIKE 'B%';

EXPLAIN ANALYZE, без индекса

Timing: Generation 4.364 ms, Inlining 360.628 ms, Optimization 615.663 ms, Emission 559.573 ms, Total 1540.227 ms
Execution Time: 1467.916 ms

Индекс

CREATE INDEX nyc_census_blocks_geom_idx
  ON nyc_census_blocks
  USING GIST (geom);

EXPLAIN ANALYZE (время)

Execution Time: 7.575 ms

GIN для JSON

Данные JSON довольно широко используются пользователями Postgres, а основной проект Postgres использует типы данных JSON с широкими возможностями. Есть несколько типов индексации, которые действительно могут помочь, если у вас есть данные, перечисляющие несколько объектов внутри каждого поля. Для этого обычно используется тип индекса GIN. В этом примере я использовал файл json от NASA, в котором есть информация о местоположении метеора.

Запрос

SELECT data -> 'name' as name FROM meteors WHERE data @> '{"mass": "100"}';

EXPLAIN ANALYZE, без индекса

Parallel Seq Scan on meteors  (cost=0.00..23926.28 rows=4245 width=32) (actual time=0.065..114.114 rows=1024 loops=3)
Execution Time: 123.698 ms

Индекс

CREATE INDEX gin_test ON meteors USING gin(data)

EXPLAIN ANALYZE

Bitmap Index Scan on gin_test  (cost=0.00..116.40 rows=10187 width=0) (actual time=12.164..12.164 rows=3072 loops=1)
Execution Time: 22.017 ms