Жизненный цикл запроса и планировщик

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

Amy McCarty

Instructor

Базовый жизненный цикл запроса

	Система	Шаги на стороне клиента	Процессы на стороне сервера
1	Парсер	Отправка запроса в базу данных	Проверяет синтаксис. Переводит SQL во внутреннее представление на основе системных правил.
2	Планировщик и оптимизатор	Оценка и оптимизация задач запроса	Использует статистику БД для построения плана. Вычисляет стоимость и выбирает оптимальный план.
3	Исполнитель	Возврат результатов запроса	Выполняет запрос согласно плану.

Реагирует на изменения структуры SQL

Строит деревья планов
- Узлы соответствуют шагам
- Визуализация с помощью EXPLAIN
Оценивает стоимость каждого дерева
- Статистика из pg_tables
- Оптимизация по времени

¹ Plan tree: https://www.postgresql.org/docs/current/querytree.html

SELECT * FROM pg_class
WHERE relname = 'mytable'

-- sample of output columns
| relname | relhasindex |

SELECT * FROM pg_stats
WHERE tablename = 'mytable'

-- sample of output columns
null_frac | avg_width | n_distinct |

EXPLAIN
SELECT * FROM cheeses

Seq Scan on cheeses 
(cost=0.00..10.50 rows=5725 width=296)

Seq Scan on cheeses (cost=0.00..10.50 rows=5725 width=296)

Безразмерная величина
Сравнивайте структуры с одинаковым выводом
- Не следует сравнивать запросы с разным выводом

Seq Scan on cheeses (cost=0.00..10.50 rows=5725 width=296)

Seq Scan on cheeses (cost=0.00..10.50 rows=5725 width=296)

EXPLAIN
SELECT * FROM cheeses WHERE species IN ('goat','sheep')

Seq Scan on cheeses (cost=0.00..378.90 rows=3 width=118)
 -> Filter: (species = ANY ('{"goat","sheep"}'::text[]))

EXPLAIN
SELECT * FROM cheeses WHERE species IN ('goat','sheep') -- index on species column

Bitmap Index Scan using species_idx on cheeses (cost=0.29..12.66 rows=3 width=118)
  Index Cond: (species = ANY ('{"goat","sheep"}'::text[]))

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