Индексы в Postgresql
Общие сведения
Индексы позволяют достичь производительность при выполнении различных запросов
Элемент индекса формируется на основе записи в определенной строке, определенной таблицы
Индексы могут формировать на основе одного или нескольких полей, которые могут комбинироваться и преобразовываться
Элемент индекса содержит указатель на определенную строку
Элементы в индексе отсортированы и упорядочены, что облегчает поиск элемента в индексе
СУБД осуществляет поиск в индексе и переходит по прямой ссылке к соответствующей строке
Подсистема СУБД, так называемый планировщик, проверяет наличие индексов, которые соответствуют атрибутам определенной таблицы из запроса и если такой индекс присутствует, он будет использоваться как наиболее короткий путь к запрашиваемым данным.
Таким образом поиск осуществляется не методом перебора всех значений в таблице, а в первую очередь возможностью просто перейти по ссылке к запрашиваемым данным.
Создание индексов требует накладных расходов на их создание и содержание, поэтому это невсегда целесообразно.
При создании первичного ключа, СУБД автоматически создает индекс который поддерживает соответствующие ограничения для этого ключа, быстро осуществляя проверку на дублирование значений соответствующего атрибута
Подобным образом создается индекс для уникальности UNIQUE
Для таких атрибутов создается специальные постфиксы: (первичный ключ) и key(UNIQUE). Их наличие можно увидеть с помощью комады \d для конкретной таблицы
Кроме префикса команда покажет тип индекса (например btree), список столбцов по которым создан индекс
demo=# \d boarding_passes
Table "bookings.boarding_passes"
Column | Type | Collation | Nullable | Default
-------------+----------------------+-----------+----------+---------
ticket_no | character(13) | | not null |
flight_id | integer | | not null |
boarding_no | integer | | not null |
seat_no | character varying(4) | | not null |
Indexes:
"boarding_passes_pkey" PRIMARY KEY, btree (ticket_no, flight_id)
"boarding_passes_flight_id_boarding_no_key" UNIQUE CONSTRAINT, btree (flight_id, boarding_no)
"boarding_passes_flight_id_seat_no_key" UNIQUE CONSTRAINT, btree (flight_id, seat_no)
Foreign-key constraints:
"boarding_passes_ticket_no_fkey" FOREIGN KEY (ticket_no, flight_id) REFERENCES ticket_flights(ticket_no, flight_id)
Индексы целесообразно создавать в соответствии с наиболее часто используемыми данными выборкиМетоды доступа
Метод доступа - извлечение только тех строк, которые соответствуют критерию отбора.
Таблицы и индексы считываются с диска в память в виде страниц по 8 Кб
Последовательный просмотр - обращение к индексам не происходит, строки извлекаются в соответствии с критерием отбора, соответственно если критерий отбора отсутствует, тогда строки извлекаются полностью ( низкая селективность )
При низкой селективности нет смысла использовать индекс
Просмотр на основе индекса - обращение по созданному индексу
Просмотр исключительно на основе индекса - все данные которые нужно получить с помощью запроса, находятся в индексеМетод доступа - извлечение только тех строк, которые соответствуют критерию отбор
В индексе нет информации о видимости строк, нет гарантии, что данные полученные из индекса, доступны в текущей транзакции, поэтому сначала выполняется обращение к карте видимости, которая существует для каждой таблицы.
В карте видимости одним битом отмечена только те страницы, которые видны всем транзакциям. Если полученная из индекса версия строки находится на такой странице, эта строка будет видна текущей транзакции и обращаться к самой таблице не нужно, однако строка может быть не доступна для текущей транзакции, поэтому будет выполняться лишняя операция.
Просмотр только на основе индекса будет эффективен, когда выбираемые данные изменяются редко, может применяться когда в SELECT указаны имена столбцов, на основе которых создан индекс
Просмотр на основе бтовой карты - модификация просмотра на основе индекса. Здесь сначала формируется поиск на основе индекса, формируются так называемые битовые карты, в которых указывается где в памяти находится страница, при этом обращение к странице происходит один раз.
Создание индекса
Для создания индекса:
demo=# CREATE INDEX name_index
ON airports_data ( airport_name ) ;
CREATE INDEX
demo=# \d airports_data
Table "bookings.airports_data"
Column | Type | Collation | Nullable | Default
--------------+--------------+-----------+----------+---------
airport_code | character(3) | | not null |
airport_name | jsonb | | not null |
city | jsonb | | not null |
coordinates | point | | not null |
timezone | text | | not null |
Indexes:
"airports_data_pkey" PRIMARY KEY, btree (airport_code)
"name_index" btree (airport_name)
Referenced by:
TABLE "flights" CONSTRAINT "flights_arrival_airport_fkey" FOREIGN KEY (arrival_airport) REFERENCES airports_data(airport_code)
TABLE "flights" CONSTRAINT "flights_departure_airport_fkey" FOREIGN KEY (departure_airport) REFERENCES airports_data(airport_code)
timing
В Postgresql есть секундомер, который позволяет определить скорость обработки запроса
demo=# \timing on
Timing is on.
demo=# SELECT * FROM tickets ;
Time: 1992,277 ms (00:01,992)
demo=# SELECT count( * ) FROM tickets
demo-# WHERE passenger_name = 'IVAN IVANOV' ;
count
-------
1507
(1 row)
Time: 121,369 ms
demo=#
demo=# \timing off
Timing is off.
demo=#
Создать индекс для той же таблицы и сверить время выполнения тем же запросом:
demo=# CREATE INDEX passenger_name
ON tickets ( passenger_name );
CREATE INDEX
Time: 4804,817 ms (00:04,805)
demo=# SELECT count( * ) FROM tickets
WHERE passenger_name = 'IVAN IVANOV' ;
count
-------
1507
(1 row)
Time: 13,573 ms
demo=#
Запрос выполняется в 10 раз быстрее
Просмотр всех индексов в БД
demo=# \di
List of relations
Schema | Name | Type | Owner | Table
----------+-------------------------------------------+-------+----------+-----------------
bookings | aircrafts_pkey | index | postgres | aircrafts_data
bookings | airports_data_pkey | index | postgres | airports_data
bookings | boarding_passes_flight_id_boarding_no_key | index | postgres | boarding_passes
bookings | boarding_passes_flight_id_seat_no_key | index | postgres | boarding_passes
bookings | boarding_passes_pkey | index | postgres | boarding_passes
bookings | bookings_pkey | index | postgres | bookings
bookings | flights_flight_no_scheduled_departure_key | index | postgres | flights
bookings | flights_pkey | index | postgres | flights
bookings | name_index | index | postgres | airports_data
bookings | passenger_name | index | postgres | tickets
bookings | seats_pkey | index | postgres | seats
bookings | ticket_flights_pkey | index | postgres | ticket_flights
bookings | tickets_pkey | index | postgres | tickets
(13 rows)
demo=#
Удаление индекса
demo=# DROP INDEX passenger_name ;
DROP INDEX
Time: 20,331 ms
demo=#
Создание индексов по нескольким столбцам
Например первичный ключ созданный по двум столбцам, использует индекс созданный по двум столбцам
Создавая индексы необходимо учитывать селективность - кол-во строк для перебора, выбираемых при выполнении запроса, в котором предпологается использовать индекс, т.к. явная сортировка может оказаться быстрее чем использование такого индекса
При низкой селективности( если строк достаточно много ) возможно нет смысла использовать индекс. Таким образом индексы будут полезны, если выбиратся небольшая доля строк, т.е. при высокой селективности
В случае если не используется индекс:
demo=# SELECT * FROM tickets ORDER BY book_ref LIMIT 5;
ticket_no | book_ref | passenger_id | passenger_name | contact_data
---------------+----------+--------------+--------------------+---------------------------------------------------------------------------------
0005434683522 | 000004 | 4711 084094 | PETR MAKAROV | {"email": "p_makarov.091967@postgrespro.ru", "phone": "+70750296011"}
0005435838975 | 00000F | 1708 262537 | ANNA ANTONOVA | {"email": "annaantonova-19021973@postgrespro.ru", "phone": "+70938049942"}
0005432295359 | 000010 | 5722 837257 | ALEKSANDR SOKOLOV | {"email": "sokolov-aleksandr.1972@postgrespro.ru", "phone": "+70163874352"}
0005432295360 | 000010 | 0564 044306 | LYUDMILA BOGDANOVA | {"email": "bogdanova_lyudmila09081973@postgrespro.ru", "phone": "+70509943013"}
0005432527326 | 000012 | 9091 269355 | TAMARA ZAYCEVA | {"email": "tamarazayceva-1971@postgrespro.ru", "phone": "+70749401734"}
(5 rows)
Time: 179,297 ms
Время выполнения запроса = 179,297 ms
Теперь добавим индекс для данного атрибута:
demo=# CREATE INDEX test_index_book_ref ON tickets ( book_ref ) ;
CREATE INDEX
Time: 4906,055 ms (00:04,906)
Если повторить тот же запрос снова:
demo=# SELECT * FROM tickets ORDER BY book_ref LIMIT 5;
ticket_no | book_ref | passenger_id | passenger_name | contact_data
---------------+----------+--------------+--------------------+---------------------------------------------------------------------------------
0005434683522 | 000004 | 4711 084094 | PETR MAKAROV | {"email": "p_makarov.091967@postgrespro.ru", "phone": "+70750296011"}
0005435838975 | 00000F | 1708 262537 | ANNA ANTONOVA | {"email": "annaantonova-19021973@postgrespro.ru", "phone": "+70938049942"}
0005432295359 | 000010 | 5722 837257 | ALEKSANDR SOKOLOV | {"email": "sokolov-aleksandr.1972@postgrespro.ru", "phone": "+70163874352"}
0005432295360 | 000010 | 0564 044306 | LYUDMILA BOGDANOVA | {"email": "bogdanova_lyudmila09081973@postgrespro.ru", "phone": "+70509943013"}
0005432527326 | 000012 | 9091 269355 | TAMARA ZAYCEVA | {"email": "tamarazayceva-1971@postgrespro.ru", "phone": "+70749401734"}
(5 rows)
Time: 1,099 ms
То можно увидеть колосальную разницу, объясняется это тем, что без индекса ORDER BY в комбинации с LIMIT n , потребует обработки всех строк, чтобы определить первые n строк. Но если есть индекс для тех же столбцов, по которым производится сортировка ORDER BY, тогда первые n строк будут извлечены без сканирования всей таблицы.
При создании индекса в индексируемом столбце может использоваться возрастающий( по умолчанию) или убывающий порядок. Поведение по умолчанию можно изменить с помощью ключевых слов:
ASC( возрастающий )
DESC( убывающий )
NULLS FIRST( значения идут первыми )
NULLS LAST( значения идут последними )
CREATE INDEX имя-индекса
ON имя-таблицы ( имя-столбца NULLS FIRST, ... );
CREATE INDEX имя-индекса
ON имя-таблицы ( имя-столбца DESC NULLS LAST, ... );
Уникальный индекс
CREATE UNIQUE INDEX aircrafts_unique_model_key
ON aircrafts ( model );
В данном случае не получится ввести уже существующее значение для соответствующего атрибута, можно было бы использовать вместо уникального индекса просто ключевое слово UNIQUE
Разница в том, что для атрибута с уникальным индексом допускается значение NULL
Если уникальный индекс будет создан по нескольким атрибутам, тогда уникальными строками будут строки с одинаковыми значениями в обоих столбцах
Индексы на основе выражений
CREATE UNIQUE INDEX aircrafts_unique_model_key
ON aircrafts ( lower( model ) );
При попытке вставить значение не соответствующее условию индекса, выдаст ошибку:
INSERT INTO aircrafts
VALUES ( '123', 'Cessna 208 CARAVAN', 1300);
ОШИБКА: повторяющееся значение ключа нарушает ограничение уникальности
"aircrafts_unique_model_key"
ПОДРОБНОСТИ: Ключ "(lower(model))=(cessna 208 caravan)" уже существует.
Частичные индексы
Частичный индекс, где book_date не участвует в формировании индекса, в предикате индекса будет использоваться атрибут total_amountCREATE INDEX bookings_book_date_part_key
ON bookings ( book_date )
WHERE total_amount > 1000000;
Запрос для которого целесообразно использовать созданный индекс:
SELECT *
FROM bookings
WHERE total_amount > 1100000 ;
В условии меньше 1000000 не будет использоваться созданный индекс