Тюнинг производительности запросов в PostgreSQL

[erogov]

Для тех, кто тоже не сразу врубился в перевод:

Стоимость — это комбинация из 5 рабочих компонентов, используемых для оценки требуемой работы: последовательная выборка, непоследовательная (случайная) выборка, обработка строки, оператор (функция) обработки и запись индекса обработки.

Тут речь о том, что есть пять параметров конфигурации, которые определяют стоимость отдельных операций: последовательной выборки (seq_page_cost), случайной выборки (random_page_cost), обработки табличной строки (cpu_tuple_cost), выполнения операции (cpu_operator_cost) и обработки индексной строки (cpu_index_tuple_cost). Итоговая стоимость любого узла плана оценивается как комбинация из энного количества этих примитивных операций.

[puyol_dev2]

Спасибо за разъяснение!

[IvanVakhrushev]

Спасибо за отсутствующие индексы, взял на вооружение.

Везде предлагают один и тот же достаточно примитивный запрос на неиспользуемые индексы. В жизни его применять практически нереально.
Так, например, нет смысла удалять индексы на внешние ключи, даже если они не используются в данный момент.
Чтение данных может выполняться на репликах, тогда на мастере индекс использоваться не будет, то есть проверку нужно делать на всех хостах.
Если индекс использовался раньше, а сейчас перестал совсем, запросом его, увы, не найти.

Мой вариант запроса

with forein_key_indexes as (
  select i.indexrelid
    from pg_constraint c
    join lateral unnest(c.conkey) with ordinality as u(attnum, attposition) on true
    join pg_index i on i.indrelid = c.conrelid and (c.conkey::int[] <@ indkey::int[])
    where c.contype = 'f'
)
select
  psui.relname as table_name,
  psui.indexrelname as index_name,
  pg_relation_size(i.indexrelid) as index_size,
  pg_size_pretty(pg_relation_size(i.indexrelid)) as index_size_pretty,
  psui.idx_scan as index_scans
from pg_stat_user_indexes psui
  join pg_index i on psui.indexrelid = i.indexrelid
where
      psui.schemaname = 'public'::text and
      not i.indisunique and
      i.indexrelid not in (select * from forein_key_indexes) and -- retain indexes on foreign keys
      psui.idx_scan < 50 and
      pg_relation_size(psui.relid) >= 5 * 8192 -- skip small tables
	  and pg_relation_size(psui.indexrelid) >= 5 * 8192 -- skip small indexes
order by psui.relname, pg_relation_size(i.indexrelid) desc

[danSamara]

Используйте Common Table Expressions и временные таблицы, когда вам нужно выполнить цепочечные запросы.

Только не забывайте, что до версии PG 12, CTE материализуются.

[DefineS]

Оптимизирую запросы при помощи профилировщика, входящего в состав dbForge Studio for PostgreSQL и не знаю забот. Во-первых, оптимизация происходит в визуальном режиме, все что требуется от меня — жмакнуть кнопку, после чего получаю исчерпывающую статистику по запросу, включая детальный план волнения с процентным соотношением траты ресуров каждым компонентом запроса. Запрос можно также модифицировать прямо в профилировщике и сравнивать результаты. Рекомендую тулзу.

[oxff]

Эта "студия" только под винду, нет исходников, и конечно же хочет денег.

[coregabe]

Лично для меня очень ценная статья. Автор молодец, всё так толково описал.

Сразу полез на сервер всё менять. Хотя я с Базами на ты