Використання та створення індексів

Покращення продуктивності запитів у 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 послідовно. «Таблиця з індексом» — це та сама таблиця, що ліворуч, з додатковим стовпцем покажчика. Значення в цьому стовпці збігаються зі стовпцем покажчика таблиці Index. Стрілки показують, як чотири значення для fried rice в таблиці Index відповідають чотирьом значенням для 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 послідовно. «Таблиця з індексом» — це та сама таблиця, що ліворуч, з додатковим стовпцем покажчика. Значення в цьому стовпці збігаються зі стовпцем покажчика таблиці Index. Стрілки показують, як чотири значення для spaghetti and meatballs у таблиці Index відповідають чотирьом значенням для spaghetti and meatballs у «Таблиці з індексом». Три значення згруповані зверху таблиці Index. Одне значення 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...