Manutenção
- Query rodando
SELECT
pid,
now() – pg_stat_activity.query_start AS duration,
query
FROM pg_stat_activity
WHERE state <> ‘idle’
AND pid<>pg_backend_pid()
ORDER BY duration desc
;
- Query travada
select pid,
usename,
pg_blocking_pids(pid) as blocked_by,
query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0
;
- Parar uma query
SELECT pg_cancel_backend(PID); — Aguarde +ou- 30s
SELECT pg_terminate_backend(PID);
Index
- Uso de índice
SELECT relname,
CASE WHEN (seq_scan + idx_scan) != 0
THEN 100.0 * idx_scan / (seq_scan + idx_scan)
ELSE 0
END AS percent_of_times_index_used,
n_live_tup AS rows_in_table
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;
- Índice corrompido
select c.relname , i.relname , x.indisvalid
FROM pg_class c
JOIN pg_index x ON c.oid = x.indrelid
JOIN pg_class i ON i.oid = x.indexrelid
where
x.indisvalid = false;
- Densidade da árvore de índice
SELECT avg_leaf_density FROM pgstatindex('INDEX_NAME');
- Verificação de indice na query
Usar com https://tatiyants.com/pev/ ou o https://www.pgadmin.org/1 EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
- Verificando seletividade
SELECT attname, inherited, n_distinct, array_to_string(most_common_vals, E’, ‘) as most_common_vals
FROM pg_stats;
- Densidade com seletividade
WITH seletividade AS (
SELECT ‘retry’ campo,COUNT(DISTINCT retry)::NUMERIC(10,4)/COUNT(retry) AS seletividade
from ordernotification ordernotif0_
UNION all
SELECT ‘type’ campo,COUNT(DISTINCT type)::NUMERIC(10,4)/COUNT(type) AS seletividade
from ordernotification ordernotif0_
)
, densidade AS (
SELECT ‘retry’ campo, 1.00 / (
COUNT(DISTINCT retry)::NUMERIC(10,4)/COUNT(retry)
) AS densidade
FROM ordernotification ordernotif0_
UNION ALL
SELECT ‘type’ campo, 1.00 / (
COUNT(DISTINCT type)::NUMERIC(10,4)/COUNT(type)
) AS densidade
FROM ordernotification ordernotif0_
)
SELECT s.campo, s.seletividade, d.densidade FROM seletividade s
JOIN densidade d ON d.campo::text = s.campo::TEXT
— maior seletividade
— menor densidade
;
Volume
- Tamanho de todos os bancos
SELECT d.datname as Name, pg_catalog.pg_get_userbyid(d.datdba) as Owner,
CASE WHEN pg_catalog.has_database_privilege(d.datname, ‘CONNECT’)
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
ELSE ‘No Access’
END as Size
FROM pg_catalog.pg_database d
order by
CASE WHEN pg_catalog.has_database_privilege(d.datname, ‘CONNECT’)
THEN pg_catalog.pg_database_size(d.datname)
ELSE NULL
END desc — nulls first
LIMIT 20;
- Tamanho das tabelas
SELECT table_name, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS index
, pg_size_pretty(table_bytes) AS table
FROM (
SELECT *, total_bytes-index_bytes-coalesce(toast_bytes,0) AS table_bytes FROM (
SELECT c.oid,nspname AS table_schema, relname AS table_name
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
, pg_indexes_size(c.oid) AS index_bytes
, pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = ‘r’
) a
) a
ORDER BY total_bytes desc
;
Referências
https://gist.github.com/rgreenjr/3637525 – Connect to preview
https://espigah.medium.com/index-melhoria-de-desempenho-a9100b7dc75f
https://wiki.postgresql.org/wiki/Index_Maintenance
https://www.postgresql.org/docs/9.1/row-estimation-examples.html
Fabricio Gonçalves 