Estudo de Caso I – Parte 2

Siga o passa-a-passo corretamente, não altere os nomes aqui definidos, pois, futuramente serão usados para outros exemplos, ou estudos de caso. Se você alterá-los esteja ciente disto.

Continuando nosso estudo de caso, vamos dar uma carga de 1 milhão de registros aleatórios. Feito isso, iremos usar o EXPLAIN ANALYSE para entendermos qual o plano será executado.

Dando carga de dados

Chamamos a função criada, no post anterior, ins_dados_pessoas no banco de dados vale.

SELECT ins_dados_pessoas(1000000);
Preparando as consultas

Depois de inserido os dados, planejamos nossas consultas. No temos os campos nome, sexo, e data de nascimento. Então, faço uma combinação de condições para podermos fazer a análise. As condições são:

  • sexo = ‘M’ AND dt_nasc BETWEEN (now() – interval ’28 YEARS’) and now()
  • (nome like ‘%VALE%’ AND sexo = ‘M’ AND dt_nasc < (now() – interval ’28 YEARS’) )

Antes de fazermos as consultas, vamos de fato entender melhor os comandos EXPLAIN e EXPLAIN ANALYSE. Em resumo:

  • EXPLAIN – Explica de qual forma o postgres irá fazer a consulta, em outras palavras, ele te mostra se usará índices, hash e etc. De forma, que é possível melhorar o tempo de suas consultas.
  • EXPLAIN ANALYSE – O mesmo do EXPLAIN, com acréscimo de que irá executar realmente a consulta SQL, a fim de mostrar o tempo gasto na consulta.

Entendido os comandos, vamos vê-los em ação. Executando o comando EXPLAIN ANALYSE, é possível ver o plano de ação do postgres.

— Primeira Condição
EXPLAIN ANALYSE SELECT nome, dt_nasc, sexo FROM pessoas WHERE sexo = ‘M’ AND dt_nasc BETWEEN (now() – interval ’28 YEARS’) and now();
QUERY PLAN
——————————————————————————————————————
Seq Scan on pessoas (cost=0.00..32675.00 rows=164615 width=27) (actual time=0.032..568.373 rows=175420 loops=1)
Filter: ((sexo = ‘M’::bpchar) AND (dt_nasc <= now()) AND (dt_nasc >= (now() – ’28 years’::interval)))
Total runtime: 577.328 ms

— Segunda Condição

EXPLAIN ANALYSE SELECT nome, dt_nasc, sexo FROM pessoas WHERE (nome like ‘%VALE%’ AND sexo = ‘M’ AND dt_nasc < (now() – interval ’28 YEARS’) );
QUERY PLAN
———————————————————————————————————————-
Seq Scan on pessoas (cost=0.00..30172.50 rows=33 width=27) (actual time=15.001..235.484 rows=5 loops=1)
Filter: (((nome)::text ~~ ‘%VALE%’::text) AND (sexo = ‘M’::bpchar) AND (dt_nasc < (now() – ’28 years’::interval)))
Total runtime: 235.531 ms

Entendendo o Planejador

Podemos observar em negrito, o Seq Scan, este é um dos métodos que o postgres usa para encontrar um registro, sempre que possível evite deixar o planejador usá-lo, nestes casos, crie índices.

O Seq Scan faz uma busca sequencial em todos os registros. Tabelas com muitos registros, tornam o uso do Seq Scan muito custoso. Imagine como seria custoso uma busca em disco em um milhão de registros? Por isso, sempre crie índices quando necessário, mas antes faça avaliação.

Em alguns casos, o uso do Seq Scan é permitido e aconselhado. Sempre existem tabelas que chamamos de tabelas de apoio, que são as tabelas criadas apenas para categorizar. Dessa forma, essas tabelas tendem a serem pequenas, com registros que vão de 1 a 1.000. Tabelas com estes tamanhos, não precisam de índices, pois a busca sequencial é mais rápida que o tempo que o planejador leva, para escolher o melhor caminho.

No próximo post, entenderemos melhor os valores em negrito e criaremos os índices.