Estudo de Caso I – Parte 3

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, voltamos a falar do EXPLAIN ANALYSE, vamos entender melhor os custos da operação.

— 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

Como são definidos os custos

Dentro do arquivo de configuração postgresql.conf é o local que definimos diversas configurações para o postgres, e uma dessas configurações são as de custos. São eles:.

  • seq_page_cost – É o tempo gasto para varrer uma página em disco sequencialmente .
  • random_page_cost – É o tempo gasto para varrer uma página não sequencial, normalmente este valor é de 4 vezes o do seq_page_cost, mas em casos onde o disco é SSD, pode-se colocar o valor igual ao seq_page_cost. Sistemas com muito cache ou todo em memória também entram nesse contexto.
  • cpu_tuple_cost – Custo estimado de processamento por linha de consulta.
  • cpu_index_tuple_cost – Custo estimado de processamento por entrada de índice.
  • cpu_operator_cost – Custo estimado de processamento por operador ou função executada durante consulta.

Dentro do psql, temos tabelas administrativas para controle do DBA. Temos a tabela pg_settings com os valores dos campos acima.

SELECT name, setting FROM pg_settings WHERE name IN(‘seq_page_cost’, ‘random_page_cost’, ‘cpu_tuple_cost’, ‘cpu_index_tuple_cost’, ‘cpu_operator_cost’);

name | setting
———————-+———
cpu_index_tuple_cost | 0.005
cpu_operator_cost | 0.0025
cpu_tuple_cost | 0.01
random_page_cost | 4
seq_page_cost | 1

Como são feitos os cálculos de custo

Sabemos onde são definidos as configurações de custos inicias, e agora usaremos a tabela administrativa pg_class para pegar o total de tuplas e páginas da tabela pessoas.

SELECT reltuples::int, relpages FROM pg_class WHERE relname = ‘pessoas’;
reltuples | relpages
———–+———-
1001000 | 7650

Agora basta multiplicar o valor total de tuplas pelo valor cpu_tuple_cost e somar o resultado com a multiplicação de seq_page_cost e o total de páginas

vale=# SELECT (7650 * 1) + (1001000 * 0.01);
?column?
———-
17660.00
(1 row)

vale=# explain select * from pessoas;
QUERY PLAN
——————————————————————
Seq Scan on pessoas (cost=0.00..17660.00 rows=1001000 width=31)

Com isso, entendemos melhor como é feito o cálculo de custo, e percebemos que o rows é a quantidade de tuplas e o width é o tamanho médio (byte) de rows.

Fazendo uma consulta mais avançada

Para quem trabalha diretamente no SGDB, é sempre interessante saber o custo total das tabelas. Para isso podemos fazer uma consulta um pouco mais bem elaborada e usando a cláusula WHERE, podemos selecionar as tabelas desejadas.

SELECT relname, relpages, reltuples, current_setting(‘seq_page_cost’) as seq_page_cost, current_setting(‘cpu_tuple_cost’) as cpu_tuple_cost, (relpages * current_setting(‘seq_page_cost’)::numeric) + (reltuples * current_setting(‘cpu_tuple_cost’)::numeric) as estimativa FROM pg_class WHERE relname = ‘pessoas’;

relname | relpages | reltuples | seq_page_cost | cpu_tuple_cost | estimativa
———+———-+———–+—————+—————-+————
pessoas | 7650 | 1001000 | 1 | 0.01 | 17660

Agora que já entendemos melhor como funciona o planejador, iremos criar nossos índices e analisar o ganho de performance. Mas isso fica para o próximo post.