Como localizar instruções SQL com maior consumo de recursos - ApsaraDB RDS: Central de Documentação do Alibaba Cloud

Quando sua instância do ApsaraDB RDS for PostgreSQL está sob carga, nem todas as instruções SQL contribuem igualmente para o consumo de recursos. A extensão pg_stat_statements rastreia estatísticas de execução de cada instrução da instância e permite classificar consultas por tempo de E/S, tempo de execução, variação de resposta, uso de memória compartilhada ou espaço temporário. Assim, você concentra os esforços de otimização onde terão maior impacto.

Este tópico aborda como instalar a extensão, executar consultas de diagnóstico para cada dimensão de recurso e redefinir as estatísticas acumuladas.

Nota

O recurso SQL Explorer and Audit oferece uma forma alternativa de analisar a execução de consultas. Ele registra instruções SQL no nível do kernel do banco de dados — incluindo detalhes de execução, contas executoras e endereços IP — sem afetar o desempenho da instância. Para mais detalhes, consulte Usar o recurso SQL Explorer and Audit.

Instalar a extensão

Execute a seguinte instrução para ativar a extensão pg_stat_statements na sua instância:

CREATE EXTENSION pg_stat_statements;

Após a instalação, a extensão começa automaticamente a coletar estatísticas de execução de todas as instruções SQL.

Como funciona o pg_stat_statements

A extensão pg_stat_statements normaliza instruções SQL ao substituir valores literais de filtros por variáveis e agrupa padrões de instrução idênticos. Por exemplo, WHERE id = 1 e WHERE id = 2 são mapeados para a mesma entrada. Isso evita entradas duplicadas para consultas que diferem apenas nos valores dos parâmetros.

A extensão expõe seus dados por meio de uma view com o mesmo nome. Essa view registra:

Estatísticas de execução: contagem de chamadas; tempo total, mínimo, máximo e médio de execução (em milissegundos); e desvio padrão do tempo de execução. O desvio padrão (stddev_time) reflete a variação de resposta.
Uso de buffer compartilhado: acertos de cache, leituras (misses), blocos sujos gerados e blocos sujos gravados.
Uso de buffer local: acertos de cache, leituras, blocos sujos gerados e blocos sujos gravados.
Uso de buffer temporário: blocos temporários lidos e gravados.
Durações de E/S de blocos: tempo gasto na leitura e gravação de blocos. Requer track_io_timing ativado; caso contrário, o valor retornado é zero.

Colunas da view

A tabela a seguir descreve todas as colunas da view pg_stat_statements.

Coluna	Tipo	Descrição
`userid`	oid	Identificador de objeto (OID) do usuário que executou a instrução. Faz referência a `pg_authid.oid`.
`dbid`	oid	OID do banco de dados no qual a instrução foi executada. Faz referência a `pg_database.oid`.
`queryid`	bigint	Código hash interno calculado a partir da árvore de análise sintática da instrução.
`query`	text	Texto de uma instrução representativa.
`calls`	bigint	Número de execuções da instrução.
`total_time`	double precision	Tempo total de execução em todas as chamadas, em milissegundos.
`min_time`	double precision	Tempo mínimo de execução para uma única chamada, em milissegundos.
`max_time`	double precision	Tempo máximo de execução para uma única chamada, em milissegundos.
`mean_time`	double precision	Tempo médio de execução por chamada, em milissegundos.
`stddev_time`	double precision	Desvio padrão populacional do tempo de execução, em milissegundos.
`rows`	bigint	Número total de linhas recuperadas ou afetadas pela instrução.
`shared_blks_hit`	bigint	Total de acertos de cache de blocos compartilhados.
`shared_blks_read`	bigint	Total de blocos compartilhados lidos do disco.
`shared_blks_dirtied`	bigint	Total de blocos compartilhados marcados como sujos.
`shared_blks_written`	bigint	Total de blocos compartilhados gravados.
`local_blks_hit`	bigint	Total de acertos de cache de blocos locais.
`local_blks_read`	bigint	Total de blocos locais lidos do disco.
`local_blks_dirtied`	bigint	Total de blocos locais marcados como sujos.
`local_blks_written`	bigint	Total de blocos locais gravados.
`temp_blks_read`	bigint	Total de blocos temporários lidos.
`temp_blks_written`	bigint	Total de blocos temporários gravados.
`blk_read_time`	double precision	Tempo total gasto na leitura de blocos, em milissegundos. Requer `track_io_timing`.
`blk_write_time`	double precision	Tempo total gasto na gravação de blocos, em milissegundos. Requer `track_io_timing`.

Encontrar as consultas que mais consomem recursos

Cada consulta abaixo seleciona dados de pg_stat_statements e retorna as cinco principais instruções classificadas por uma métrica específica. Execute-as para identificar candidatas à otimização.

Maior consumo de E/S

Tempo elevado de E/S geralmente indica índices ausentes, varreduras completas de tabelas ou consultas que recuperam muito mais dados do que o necessário.

As 5 principais por tempo médio de E/S por chamada (ideal para encontrar consultas custosas em cada execução):

SELECT userid::regrole, dbid, query
FROM pg_stat_statements
ORDER BY (blk_read_time + blk_write_time) / calls DESC
LIMIT 5;

As 5 principais por tempo total de E/S em todas as chamadas (ideal para encontrar consultas com maior impacto cumulativo):

SELECT userid::regrole, dbid, query
FROM pg_stat_statements
ORDER BY (blk_read_time + blk_write_time) DESC
LIMIT 5;

Tempo de execução mais lento

Tempo médio de execução alto aponta para consultas individualmente lentas. Essas são boas candidatas para análise de plano de execução com EXPLAIN ANALYZE.

As 5 principais por tempo médio de execução por chamada:

SELECT userid::regrole, dbid, query
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 5;

As 5 principais por tempo total de execução em todas as chamadas (útil para identificar consultas frequentes com impacto acumulado):

SELECT userid::regrole, dbid, query
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

Maior variação de resposta

Desvio padrão elevado no tempo de execução (stddev_time) indica tempos de resposta imprevisíveis. Isso frequentemente sinaliza contenção de locks, pressão de evicção de cache ou competição intermitente por recursos.

SELECT userid::regrole, dbid, query
FROM pg_stat_statements
ORDER BY stddev_time DESC
LIMIT 5;

Maior uso de memória compartilhada

Consultas com alto consumo de buffer compartilhado ocupam grandes porções do pool de buffers. Isso pode prejudicar outras consultas e aumentar os misses de cache em toda a instância.

SELECT userid::regrole, dbid, query
FROM pg_stat_statements
ORDER BY (shared_blks_hit + shared_blks_dirtied) DESC
LIMIT 5;

Maior uso de espaço temporário

Consultas que gravam blocos temporários realizam ordenações ou hash joins que excedem o limite de work_mem. Aumentar o valor de work_mem no nível da sessão ou reescrever a consulta para reduzir o tamanho do conjunto de resultados pode eliminar o uso de disco temporário.

SELECT userid::regrole, dbid, query
FROM pg_stat_statements
ORDER BY temp_blks_written DESC
LIMIT 5;

Redefinir estatísticas

A extensão pg_stat_statements acumula estatísticas desde a instalação ou desde a última redefinição. Para estabelecer uma linha de base limpa — por exemplo, após implantar uma otimização de consulta — redefina os contadores:

SELECT pg_stat_statements_reset();

Execute esse comando periodicamente para evitar que dados históricos ocultem os padrões atuais de desempenho.

Referências

Documentação do PostgreSQL 9.6.2 - F.29. pg_stat_statements