Postgresql: промышленная разработка баз данных. лекция 5

PostgreSQL: промышленная разработка баз данных Лекция 5 Механизм выполнения запроса. План запроса, виды операций.  Оптимизация запросов. Оператор EXPLAIN. Типы индексов.

Устройство PostgreSQL

PostgreSQL: промышленная разработка баз данных. Лекция 5

Обработка запроса в PostgreSQL  Parser (синтаксический анализатор)  Planner (выбор оптимального пути)  Executor (непосредственное выполнение) SQL – декларативный язык. СУБД решает, как  именно будет выполняться запрос.

PostgreSQL: промышленная разработка баз данных. Лекция 5

EXPLAIN 

План запроса – дерево



Узлы – действия







−

соединения (join)

−

сортировка

−

просмотр таблицы

Выполнение происходит от листьев к корню Оценка «ширины» данных, количества строк и  стоимости Это только оценка, реальную стоимость покажет  EXPLAIN ANALYZE

PostgreSQL: промышленная разработка баз данных. Лекция 5

Пример EXPLAIN test=# explain select * from pg_proc order by proname;                             QUERY PLAN ­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­  Sort  (cost=190.00..195.35 rows=2143 width=339)    Sort Key: proname    ­>  Seq Scan on pg_proc  (cost=0.00..71.43 rows=2143  width=339) (3 rows)

PostgreSQL: промышленная разработка баз данных. Лекция 5

EXPLAIN: width  test=# explain select * from pg_proc order by proname;                             QUERY PLAN ­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­  Sort  (cost=191.67..197.08 rows=2166 width=299)    Sort Key: proname    ­>  Seq Scan on pg_proc  (cost=0.00..71.66 rows=2166 width=299) (3 rows)

Показывает примерное среднее  количество байт в одной  строке результата 

«Ширины» типов данных int2 int4 int8 boolean varchar(n) / char(n) timestamp / timestamptz

PostgreSQL: промышленная разработка баз данных. Лекция 5

2 4 8 1 n + 4 8

EXPLAIN: rows test=# explain select * from pg_proc order by proname;                             QUERY PLAN ­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­  Sort  (cost=191.67..197.08 rows=2166 width=299)    Sort Key: proname    ­>  Seq Scan on pg_proc  (cost=0.00..71.66 rows=2166 width=299) (3 rows)

Показывает примерное количество строк результата (в т.ч.,  промежуточного)  Большое отклонение от реальности => необходим VACUUM &  ANALYZE!  Можно использовать для вывода примерного количества строк  результата конечному пользователю 

PostgreSQL: промышленная разработка баз данных. Лекция 5

EXPLAIN: cost test=# explain select * from pg_proc order by proname;                             QUERY PLAN ­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­  Sort  (cost=191.67..197.08 rows=2166 width=299)    Sort Key: proname    ­>  Seq Scan on pg_proc  (cost=0.00..71.66 rows=2166 width=299) (3 rows)

Абстрактная величина (ввода­вывода, CPU)  Именно это даёт возможность планнеру выбрать один план из  нескольких  2 значения: startup & total  Это всего лишь оценка! 

PostgreSQL: промышленная разработка баз данных. Лекция 5

EXPLAIN ANALYZE test=# explain analyze select * from pg_proc order by proname;                           QUERY PLAN  ­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­  Sort  (cost=190.00..195.35 rows=2143 width=339) (actual  time=423.508..426.069 rows=2143 loops=1)    Sort Key: proname    Sort Method:  quicksort  Memory: 626kB    ­>  Seq Scan on pg_proc  (cost=0.00..71.43 rows=2143 width=339)  (actual time=0.015..418.299 rows=2143 loops=1)  Total runtime: 428.757 ms (5 rows)

Фактическая информация  Время в миллисекундах  Добавляется общее время запроса  loops – количество «проходов» (необходимо умножить время  на loops, чтобы получить общее время) 

PostgreSQL: промышленная разработка баз данных. Лекция 5

Виды операций 





Способы просмотра таблицы −

Seq Scan

−

Index Scan

Способы подготовки данных −

Sort

−

Hash

Способы соединения (join) −

Nested Loop

−

Merge Join

−

Hash Join

PostgreSQL: промышленная разработка баз данных. Лекция 5

Управление планировщиком 

enable_seqscan



enable_indexscan



enable_sort



enable_nestloop



enable_hashjoin



enable_mergejoin



enable_hashagg

Как правило, помогает при разработке, но вредит на  «боевых» серверах (другие объёмы данных => другая  статистика) PostgreSQL: промышленная разработка баз данных. Лекция 5

Индексы в PostgreSQL 

B­tree



Hash



R­tree – теперь тоже GiST



GiST (обобщенное поисковое дерево)



GIN (обратный индекс)

PostgreSQL: промышленная разработка баз данных. Лекция 5

Индексы в PostgreSQL 

B­tree



Hash



R­tree – теперь тоже GiST



GiST (обобщенное поисковое дерево)



GIN (обратный индекс)

PostgreSQL: промышленная разработка баз данных. Лекция 5

Фёдор Сигаев, Олег Бартунов

Cоздание индекса test=# \h CREATE INDEX Command:     CREATE INDEX Description: define a new index Syntax: CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ]     ( { column | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS  { FIRST | LAST } ] [, ...] )     [ WITH ( storage_parameter = value [, ... ] ) ]     [ TABLESPACE tablespace ]     [ WHERE predicate ]

PostgreSQL: промышленная разработка баз данных. Лекция 5

Общие возможности использования  индексов в PostgreSQL 





Частичные индексы  CREATE INDEX ... WHERE ... Функциональные индексы  −

CREATE INDEX ... USING btree(myfunc(a)) уникальные функциональные индексы

−

индексирование данных “экзотических” типов (XML, array, ...)

Многоколоночные индексы −

следим за правильным порядком столбцов

−

избегаем ненужных одноколоночных индексов



GIN­индексы для массивов



CLUSTER table USING indexname

PostgreSQL: промышленная разработка баз данных. Лекция 5

Общие возможности использования  индексов в PostgreSQL ●

CREATE INDEX CONCURRENTLY ...

●

CREATE INDEX ... WITH (fillfactor = ...)

●

Необходимость в перестроении индексов (read/fetch ratio):

select   indexrelname,   idx_tup_read,   idx_tup_fetch,   case      when idx_tup_fetch = 0 then 100      else idx_tup_read / idx_tup_fetch    end as ratio  from pg_stat_user_indexes  order by ratio desc;

PostgreSQL: промышленная разработка баз данных. Лекция 5

Оптимизация: общие рекомендации 

Сбор статистики, мониторинг, анализ логов (pgFouine)



Итерационное выявление медленных запросов









Иногда лучше перестроить запрос, а не создать N новых  индексов Следить за кардинальностью и селективностью  промежуточных результатов (SELECT * FROM a, b) Потенциальные источники проблем: LEFT JOIN, count(),  UNION вместо UNION ALL, DISTINCT, WHERE ... IN  (...), соединение 100 таблиц, неожиданное «выпрямление»  запросов с подзапросами, «вязанка» индексов вместо одного­ двух Не забывать об ANALYZE после массивных изменений!

PostgreSQL: промышленная разработка баз данных. Лекция 5

Оптимизация: общие рекомендации 









При решении проблемы убедитесь в том, что вам не  нужны VACUUM & ANALYZE Запускайте EXPLAIN ANALYZE не менее двух раз (не  забываем про кэш) Читайте план снизу вверх, ищите, где начинаются  замедления и/или ошибки планировщика При возможности, используйте реальные данные в  тестировании (Slony?) и оборудование, приближенное к  production Обращайтесь за помощью: pgsql­performance, IRC, sql.ru,  коммерческая поддержка

PostgreSQL: промышленная разработка баз данных. Лекция 5

Литература:  рекомендации для данной  лекции ●

PostgreSQL Reference Manual ●

11. Indexes

14. Performance Tips ● 43.5. Planner/Optimizer ● 55. How the Planner Uses Statistics Bruce Momjian, PostgreSQL Hardware Performance Tuning  http://www.postgresql.org/files/documentation/books/aw_pgsql/hw_performance/ ●

●

●

●

●

Дейт, К.. Введение в системы баз данных — Глава 17. Кузнецов, С. Д. Основы современных баз данных — 9.2. Индексы  http://citforum.ru/database/osbd/glava_39.shtml#_4_1_2  Гарсиа­Молина, Г., Ульман, Дж., Уидом, Дж. Системы баз данных. Полный  курс. — Главы 13­16

PostgreSQL: промышленная разработка баз данных. Лекция 5

Контакты ●

[email protected]

●

Blog: http://nikolay.samokhvalov.com

●

XMPP/GTalk: [email protected]

●

Skype: samokhvalov & postgresmen

●

+7 905 783 9804

PostgreSQL: промышленная разработка баз данных. Лекция 5