Анализ использования индекса PostgreSQL

Ответ 1

Мне это нравится, чтобы найти отсутствующие индексы:

SELECT schemaname, relname, seq_scan-idx_scan AS too_much_seq, case when seq_scan-idx_scan>0 THEN 'Missing Index?' ELSE 'OK' END, pg_relation_size(format('%I.%I', schemaname, relname)::regclass) AS rel_size, seq_scan, idx_scan
 FROM pg_stat_user_tables
 WHERE pg_relation_size(format('%I.%I', schemaname, relname)::regclass)>80000 ORDER BY too_much_seq DESC;

Это проверяет, есть ли больше сканирования последовательностей, а затем сканирование индекса. Если таблица небольшая, она игнорируется, поскольку Postgres, похоже, предпочитает сканирование последовательности для них.

Вышеуказанный запрос обнаруживает отсутствующие индексы.

Следующим шагом будет обнаружение отсутствующих комбинированных индексов. Думаю, это непросто, но выполнимо. Возможно, анализируя медленные запросы... Я слышал, что pg_stat_statements может помочь...

Ответ 2

Проверьте статистику. pg_stat_user_tables и pg_stat_user_indexes - это те, которые начинаются с.

См. " Сборщик статистики".

Ответ 3

Об определении недостающих индексов подход.... Нет. Но есть некоторые планы сделать это проще в будущем выпуске, например, псевдоиндексах и машиносчитываемых EXPLAIN.

В настоящее время вам потребуется EXPLAIN ANALYZE плохо выполняющие запросы, а затем вручную определить лучший маршрут. Некоторые лог-анализаторы, такие как pgFouine, могут помочь определить запросы.

Что касается неиспользуемого индекса, вы можете использовать для его идентификации примерно следующее:

select * from pg_stat_all_indexes where schemaname <> 'pg_catalog';

Это поможет определить считывание, сканирование, извлечение кортежей.

Ответ 4

Еще один новый и интересный инструмент для анализа PostgreSQL - PgHero. Он больше ориентирован на настройку базы данных и делает многочисленные анализы и предложения.

Ответ 5

Существует несколько ссылок на скрипты, которые помогут вам найти неиспользуемые индексы в PostgreSQL wiki. Основной метод - смотреть pg_stat_user_indexes и искать те, где idx_scan, количество раз, сколько раз этот индекс использовался для ответа на запросы, равно нулю или, по крайней мере, очень низкому. Если приложение изменилось и ранее используемый индекс, вероятно, не сейчас, вам иногда нужно запустить pg_stat_reset(), чтобы вернуть все данные в 0 и затем собрать новые данные; вы можете сохранить текущие значения для всего и вычислить дельта вместо этого, чтобы понять это.

Пока нет доступных хороших инструментов, чтобы предлагать отсутствующие индексы. Один из подходов заключается в регистрации выполняемых запросов и анализе того, какие из них занимают много времени, с использованием инструмента анализа журнала запросов, такого как pgFouine или pqa. См. "" Запуск сложных запросов" для получения дополнительной информации.

Другим подходом является просмотр pg_stat_user_tables и поиск таблиц с большим количеством последовательных сканирований против них, где seq_tup_fetch велико. Когда используется индекс, вместо этого увеличивается счетчик idx_fetch_tup. Это может вас подсказать, если таблица недостаточно проиндексирована, чтобы отвечать на запросы против нее.

Собственно выясняя, какие столбцы вам следует индексировать? Это обычно снова возвращает данные анализа журнала запросов.

Ответ 6

Вы можете использовать нижеприведенный запрос для поиска использования индекса и размера индекса:

Ссылка взята из этого блога.

SELECT
    pt.tablename AS TableName
    ,t.indexname AS IndexName
    ,pc.reltuples AS TotalRows
    ,pg_size_pretty(pg_relation_size(quote_ident(pt.tablename)::text)) AS TableSize
    ,pg_size_pretty(pg_relation_size(quote_ident(t.indexrelname)::text)) AS IndexSize
    ,t.idx_scan AS TotalNumberOfScan
    ,t.idx_tup_read AS TotalTupleRead
    ,t.idx_tup_fetch AS TotalTupleFetched
FROM pg_tables AS pt
LEFT OUTER JOIN pg_class AS pc 
    ON pt.tablename=pc.relname
LEFT OUTER JOIN
( 
    SELECT 
        pc.relname AS TableName
        ,pc2.relname AS IndexName
        ,psai.idx_scan
        ,psai.idx_tup_read
        ,psai.idx_tup_fetch
        ,psai.indexrelname 
    FROM pg_index AS pi
    JOIN pg_class AS pc 
        ON pc.oid = pi.indrelid
    JOIN pg_class AS pc2 
        ON pc2.oid = pi.indexrelid
    JOIN pg_stat_all_indexes AS psai 
        ON pi.indexrelid = psai.indexrelid 
)AS T
    ON pt.tablename = T.TableName
WHERE pt.schemaname='public'
ORDER BY 1;

Ответ 7

PoWA кажется интересным инструментом для PostgreSQL 9.4+. Он собирает статистику, визуализирует их и предлагает индексы. Он использует расширение pg_stat_statements.

PoWA - PostgreSQL Workload Analyzer, который собирает статистику производительности и предоставляет графики и графики в реальном времени, которые помогают контролировать и настраивать ваши серверы PostgreSQL. Он похож на Oracle AWR или SQL Server MDW.

Ответ 8

Это должно помочь: Анализ Pratical Query