Анализ производительности запроса

EXPLAIN ANALYZE, поиск неиспользуемых индексов и медленных запросов в PostgreSQL.

-- Реальный план выполнения с временем и буферами
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.username, COUNT(o.id) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at >= NOW() - INTERVAL '30 days'
GROUP BY u.username
ORDER BY orders DESC
LIMIT 20;

-- Самые медленные запросы (нужно расширение pg_stat_statements)
SELECT
    ROUND(total_exec_time::numeric, 1) AS total_ms,
    calls,
    ROUND(mean_exec_time::numeric, 2)  AS mean_ms,
    LEFT(query, 120)                   AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Индексы, которые никогда не использовались
SELECT relname AS table_name, indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Размер таблиц с индексами
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;