Ciclo da consulta e o planner

Melhorando o Desempenho de Consultas no PostgreSQL

Amy McCarty

Instructor

Ciclo básico da consulta

Sistema Etapas no front-end Processos no back-end
1 Parser Envia a consulta ao banco Verifica a sintaxe. Traduz SQL para uma sintaxe mais amigável ao computador com base em regras do sistema.
2 Planner & Optimizer Avalia e otimiza tarefas da consulta Usa estatísticas do banco para criar o plano. Calcula custos e escolhe o melhor plano.
3 Executor Retorna os resultados Segue o plano para executar a consulta.
Melhorando o Desempenho de Consultas no PostgreSQL

Planner e otimizador da consulta

Responde a mudanças na estrutura SQL

  • Gera árvores de plano
    • Nós correspondem a etapas
    • Visualize com EXPLAIN
  • Estima o custo de cada árvore
    • Estatísticas de pg_tables
    • Otimização baseada em tempo
1 Plan tree: https://www.postgresql.org/docs/current/querytree.html
Melhorando o Desempenho de Consultas no PostgreSQL

Estatísticas de pg_tables

SELECT * FROM pg_class
WHERE relname = 'mytable'

-- sample of output columns
| relname | relhasindex |

SELECT * FROM pg_stats
WHERE tablename = 'mytable'

-- sample of output columns
null_frac | avg_width | n_distinct |
  • Índices de coluna
  • Contagem de valores nulos
  • Largura da coluna
  • Valores distintos
Melhorando o Desempenho de Consultas no PostgreSQL

EXPLAIN

 

  • Janela para o plano de consulta
  • Etapas e estimativas de custo
    • Não executa a consulta

 

  • Varredura sequencial da tabela cheeses
  • Estimativas de custo e tamanho

 

EXPLAIN
SELECT * FROM cheeses

 

Seq Scan on cheeses 
(cost=0.00..10.50 rows=5725 width=296)
Melhorando o Desempenho de Consultas no PostgreSQL

EXPLAIN: Scan

 

  • Etapa do plano
  • Retorna linhas

 

Seq Scan em cheeses (cost=0.00..10.50 rows=5725 width=296)

 

 

 

 

  • Seq Scan: varredura de todas as linhas da tabela
Melhorando o Desempenho de Consultas no PostgreSQL

EXPLAIN: Custo

 

  • Adimensional
  • Compare estruturas com mesma saída
    • Não compare consultas com saídas diferentes

 

Seq Scan em cheeses (cost=0.00..10.50 rows=5725 width=296)

 

 

 

 

 

  • 0.00.. : tempo de inicialização

  • ..10.50 : tempo total

  • tempo total = inicialização + execução

Melhorando o Desempenho de Consultas no PostgreSQL

EXPLAIN: Tamanho

 

  • Estimativas de tamanho

 

Seq Scan em cheeses (cost=0.00..10.50 rows=5725 width=296)

 

 

 

  • rows: linhas que a consulta precisa examinar
  • width: largura em bytes das linhas
Melhorando o Desempenho de Consultas no PostgreSQL

EXPLAIN com cláusula WHERE

EXPLAIN
SELECT * FROM cheeses WHERE species IN ('goat','sheep') 

Seq Scan on cheeses (cost=0.00..378.90 rows=3 width=118)
 -> Filter: (species = ANY ('{"goat","sheep"}'::text[]))
  • De baixo para cima
    • Etapa 1: Filtro
    • Etapa 2: Varredura sequencial
  • Cláusula WHERE
    • Reduz linhas a varrer e aumenta o custo total
Melhorando o Desempenho de Consultas no PostgreSQL

EXPLAIN com índice

EXPLAIN
SELECT * FROM cheeses WHERE species IN ('goat','sheep') -- index on species column

Bitmap Index Scan using species_idx on cheeses (cost=0.29..12.66 rows=3 width=118)
  Index Cond: (species = ANY ('{"goat","sheep"}'::text[]))
  • Etapa 1: Bitmap Index Scan
    • Index Cond explica a etapa de varredura
  • INDEX
    • Custo inicial aumentou de 0
    • Custo total caiu de 379
Melhorando o Desempenho de Consultas no PostgreSQL

Vamos praticar!

Melhorando o Desempenho de Consultas no PostgreSQL

Preparing Video For Download...