Перейти к основному содержимому

Практика: Индексы в PostgreSQL


Онлайн редактор кода для PostgreSQL

Здесь вы можете попробовать свои силы в программировании на языке SQL для работы с PostgreSQL. Для этого мы предоставляем вам онлайн редактор кода, в котором вы можете написать свой код и запустить его.


❗️ При обновлении страницы код пропадёт, по этому, сохраните свой код куда-нибудь, если он важный.

Задание 1 — B-tree основы​

Задание 1: Создание и обоснование B-tree индексов

⏱️ Примерное время: 10-20 минут

Создай B-tree индексы (простые, составные, уникальные) и обоснуй выбор порядка колонок.


Требования:

  • минимум 2–3 индекса
  • хотя бы один составной
  • хотя бы один частичный (WHERE)
  • напиши CREATE INDEX + 1–2 примера запросов, которые должны использовать индекс
  • короткое пояснение (1–2 предложения), почему именно такой порядок колонок

Пример:

CREATE INDEX idx_orders_date_status 
ON orders (order_date, status)
WHERE status != 'canceled';

-- будет работать для:
SELECT * FROM orders 
WHERE order_date >= '2025-01-01' 
  AND status = 'delivered'
ORDER BY order_date;

Задание 2 — Частичные индексы + выражения​

Задание 2: Частичные индексы и индексы на выражения

⏱️ Примерное время: 20-30 минут

Создай индексы с WHERE-условием и//или на вычисляемые выражения.


Требования:

  • хотя бы один частичный индекс (WHERE)
  • хотя бы один индекс на выражение (LOWER(), DATE(), (col1 * col2))
  • покажи 1–2 запроса, которые используют созданный индекс
  • объясни, почему без частичного индекса было бы хуже

Пример:

CREATE INDEX idx_users_email_lower ON users (LOWER(email))
WHERE is_verified = true;

-- ускоряет:
SELECT * FROM users WHERE LOWER(email) = 'test@gmail.com';

Задание 3 — GIN для JSONB, массивов, полнотекста​

Задание 3: GIN-индексы (массивы, JSONB, tsvector)

⏱️ Примерное время: 25-40 минут

Создай GIN-индексы и напиши запросы, которые их используют.


Требования:

  • USING GIN
  • минимум 2 разных запроса (@>, &&, ?, @@, etc.)
  • покажи EXPLAIN (можно текстом) или объясни, почему GIN здесь лучше B-tree
  • (бонус) добавь fastupdate = off или gin_pending_list_limit и поясни

Пример:

CREATE INDEX idx_products_tags_gin ON products USING gin(tags);

SELECT * FROM products 
WHERE tags && ARRAY['sale', 'new-year'];

Задание 4 — BRIN для больших временных рядов​

Задание 4: BRIN индексы + сравнение размеров

⏱️ Примерное время: 20-35 минут

Работа с BRIN на большой таблице логов / заказов / событий.


Требования:

  • создай BRIN индекс
  • создай для сравнения обычный B-tree на ту же колонку
  • выведи размеры (pg_size_pretty(pg_relation_size(...)))
  • напиши 2–3 запроса по диапазону дат, которые выигрывают от BRIN
  • (бонус) измени pages_per_range и поясни эффект

Пример:

CREATE INDEX idx_logs_brin_time ON app_logs USING brin(logged_at);

SELECT pg_size_pretty(pg_relation_size('idx_logs_brin_time'));

Задание 5 — Итоговое: Оптимизация поисковой системы магазина​

Задание 5: Комплексная оптимизация (итоговое)

⏱️ Примерное время: 50-90 минут

Предложи набор индексов для интернет-магазина / блога / логов.


Требования:

  • минимум 6–8 индексов разных типов
  • обязательно должны быть: B-tree составной, частичный, GIN, BRIN
  • хотя бы один индекс на выражение
  • для каждого индекса — 1 типичный запрос + пояснение
  • таблица сравнения (какой тип индекса для какой задачи)
  • (желательно) 1–2 примера EXPLAIN ANALYZE (можно описать словами)

Это задание можно использовать как мини-проект / портфолио.

подсказка

Хороший набор индексов — это баланс между скоростью чтения, размером на диске и скоростью записи.
Самые частые ошибки: избыточные индексы, неправильный порядок в составных индексах, GIN там где хватило бы B-tree.