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

Оптимизация запросов в PostgreSQL

Amy McCarty

Instructor

Обзор индексов

Что такое индекс

  • Метод создания отсортированных ключей столбца для ускорения поиска
  • Аналог предметного указателя в книге
  • Ссылка на местоположение данных

Зачем нужен

  • Ускоряет запросы

Где применять

  • Часто используемые столбцы фильтрации
  • Первичный ключ
Оптимизация запросов в PostgreSQL

Пример индекса

ingredient recipe
tomatoes spaghetti & meatballs
green onions fried rice
eggs fried rice
ground beef spaghetti & meatballs
pasta spaghetti & meatballs
rice fried rice
soy sauce fried rice 
SELECT *
FROM cookbook
WHERE recipe = 'fried rice'
Оптимизация запросов в PostgreSQL

Индекс как ключ и указатель

Две таблицы. Таблица индекса содержит столбец recipe и столбец указателей со значениями от _12 до _18 последовательно. Таблица с индексом — та же таблица слева, но с дополнительным столбцом указателей. Значения в этом столбце совпадают со значениями в столбце указателей таблицы индекса. Стрелки показывают, как четыре значения fried rice в таблице индекса соответствуют четырём значениям fried rice в таблице с индексом.

Оптимизация запросов в PostgreSQL

Поиск существующих индексов

pg_tables
  • Аналог information_schema
    • специфично для Postgres
  • Метаданные о базе данных
Оптимизация запросов в PostgreSQL

Поиск существующих индексов

pg_tables
  • Аналог information_schema
    • специфично для Postgres
  • Метаданные о базе данных

 

SELECT * FROM pg_indexes
schemaname tablename indexname tablespace indexdef
food dinner recipe_index null CREATE INDEX recipe_index ...
Оптимизация запросов в PostgreSQL

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

Таблица dinner со столбцами ingredient, recipe и serving size. tomatoes — spaghetti & meatballs — 4. green onions — fried rice — 2. eggs — fried rice — 2. ground beef — spaghetti & meatballs — 4. pasta — spaghetti & meatballs — 4. rice — fried rice — 2. soy sauce — fried rice — 2.

CREATE INDEX recipe_index 
ON cookbook (recipe);

CREATE INDEX CONCURRENTLY recipe_index
ON cookbook (recipe, serving_size);
Оптимизация запросов в PostgreSQL

Использовать или нет

Когда использовать индекс

  • Большие таблицы
  • Часто используемые условия фильтрации
  • Первичный ключ

Когда избегать индекса

  • Небольшие таблицы
  • Столбцы с большим числом NULL
  • Часто обновляемые таблицы
    • Индекс фрагментируется
    • Данные записываются в двух местах
Оптимизация запросов в PostgreSQL

Часто обновляемые таблицы

Две таблицы. Таблица индекса содержит столбец recipe и столбец указателей со значениями от _12 до _19 последовательно. Таблица с индексом — та же таблица слева с дополнительным столбцом указателей. Значения в этом столбце совпадают со значениями в столбце указателей таблицы индекса. Стрелки показывают, как четыре значения spaghetti and meatballs в таблице индекса соответствуют четырём значениям spaghetti and meatballs в таблице с индексом. Три значения сгруппированы в верхней части таблицы индекса. Одно значение spaghetti and meatballs находится в нижней части таблицы индекса и соответствует новой записи basil в таблице с индексом.

Оптимизация запросов в PostgreSQL

Оценка запроса с индексом

Планировщик запросов

Несколько поваров у большого кулинарного котла 

EXPLAIN
SELECT * 
FROM cookbook

 

План запроса

Seq scan on cookbook (cost=0.00...22.70
  rows = 1270 width = 36)
  • Оценки стоимости (времени)
Оптимизация запросов в PostgreSQL

Давайте потренируемся!

Оптимизация запросов в PostgreSQL

Preparing Video For Download...