Uso de estatísticas pelo postgre sql

Uso de Estatísticas pelo PostgreSQL

José ArthurLocaweb

Por que é importante reduzir I/O?

Latency Numbers Every Programmer Should Know

● L1 cache reference: 1ns

● Main memory reference: 100ns

● SSD random read: 16.000ns ≈ 16μs

● Disk seek: 4.000.000ns ≈ 4ms

Porque I/O é uma operação demorada.

(fonte: http://www.eecs.berkeley.edu/~rcs/research/interactive_latency.html)

Por que é importante reduzir I/O?

Um banco de dados faz muitas operações no disco, reduzir este número, bem como acessar o disco de forma ordenada levam a um melhor tempo de resposta.

Usando estatísticas para o acesso, conseguimos reduzir o número de operações no disco e atingimos o objetivo de melhorar a perfomance.

"customers_pkey" PRIMARY KEY, btree (customerid)"ix_cust_user_password" UNIQUE, btree (username, password)"ix_cust_username" UNIQUE, btree (username)"ix_cust_email" btree (email) WHERE email IS NOT NULL

Ciclo de vida de uma consulta

Atualização das estatísticas

● Pelo custo da operação, as estatísticas não são atualizadas a cada modificação

● O processo de atualização das estatísticas é papel do vacuum (autovacuum)– autovacuum_vacuum_threshold (50 tuplas)

– autovacuum_analyze_scale_factor (10% da tabela)

● Para criar as estatísticas, o banco lê uma AMOSTRA ALEATÓRIA dos dados– Executar duas vezes seguidas um analyze pode resultar em

estatísticas diferentes

postgresql.conf

● Baseado em uma escala arbitrária● Configurável no postgresql.conf

– seq_page_cost

– random_page_cost

– cpu_tuple_cost

– cpu_index_tuple_cost

– cpu_operator_cost

– effective_cache_size

Onde estão as estatísticas

● Estatísticas da tabela– pg_class

● relpages: páginas da tabela (estimado)● reltuples: tuplas (linhas) da tabela

– pg_statistic● Dados estatísticos por coluna

– pg_stats● Visão mais simples de entender● Garante as permissões

pg_class

ds2=# SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname in ('customers', 'customers_pkey', 'ix_cust_user_password', 'ix_cust_username', 'ix_cust_email'); relname | relkind | reltuples | relpages -----------------------+---------+-------------+---------- customers_pkey | i | 6e+06 | 19972 ix_cust_email | i | 5.39869e+06 | 26768 ix_cust_user_password | i | 6e+06 | 37765 ix_cust_username | i | 6e+06 | 28061 customers | r | 6e+06 | 153079(5 rows)

pg_statistic

Nome Descrição

starelid O oid do objeto (tabela ou índice)

statattnum Coluna

statinherit Herança de tabela

stanullfrac Fração de nulos

stawidth Média em bytes do tamanho

stadistinct Elementos distintosmenor que zero: multiplicador do número de registrosmaior que zero: número de distintos

stakindN Tipo da estatística

staopN Operação da estatística (=, <)

stanumbersN Depende do tipo

stavaluesN Depende do tipo

pg_stats

Nome Descrição

schemaname Nome do schema

tablename Nome da tabela

attname Nome da coluna

inherited True se inclui estatísticas das filhas

null_frac Fração de nulos

avg_width Média em bytes do tamanho

n_distinct Elementos distintosmenor que zero: multiplicador do número de registrosmaior que zero: número de distintos

most_common_vals Lista dos valores mais comuns

most_common_freqs Frequencia dos itens acima

histogram_bounds Limites do histograma

correlation Ordem no disco

most_common_elements Valores mais comuns contando arrays

most_common_elements_freq Frequencia dos itens acima

element_count_histogram Histograma do count dos elementos

Tipos de estatísticas

● Most Common Values: útil para operações de igualdade (=)

● Histogram: operações de comparação (<)● Correlation: correlação entre ordem física e lógica● Most Common Elements: semelhante ao MCV, mas para

vetores e matrizes● Distinct Elements Count Histogram● Length Histogram● Bounds Histogram

Histograma

Histograma (wikipedia): uma representação gráfica na qual um conjunto de dados é agrupado em classes uniformes, representado por um retângulo cuja base horizontal são as classes e seu intervalo e a altura vertical representa a frequência com que os valores desta classe estão presente no conjunto de dados

Histograma

0-5 2

6-10 3

11-15 10

16-20 15

21-25 12

26-30 7

30 ou mais 30

● Distribuição de idades

0-5 6-10 11-15 16-20 21-25 26-30 30 ou mais0

5

10

15

20

25

30

35

Distribuição de Idades

Histograma nas estatísticas do PostgreSQL

● A tabela é dividida em default_statistics_target (100) partes, todas de mesmo tamanho

Custo de uma consulta

explain SELECT * FROM customers WHERE customerid < 10;

Seq Scan on customers (cost=0.00..220060.00 rows=9 width=153)

Filter: (customerid < 10)

● Custo inicial: custo para começar a retornar registros

● Custo máximo: custo máximo do nó

● Rows: estimativa de tuplas a retornar

● Width: tamanho médio da linha em bytes





● Custo inicial: custo para começar a retornar registros

Neste caso, é zero por ser direto nos dados (Seq Scan)





● Custo máximo: custo máximo do nó.

– Custo = (páginas lidas * custo páginas) + (tuplas * custo tupla) + (tuplas * custo operação)

– (relpages * seq_page_cost) + (reltuples * cpu_page_cost) + (reltuples * cpu_operator_cost)

– (145050 * 1) + (6000000 * 0,04) + (6000000 * 0,0025)

– 220060





● Rows: estimativa de tuplas a retornar

– Total de tuplas * Selectividade

– Selectividade: (bucks + (val – bucket[min])/bucket[max] – bucket[min]))/total buckets

● (0 + (10 – 1)/(60000 – 1)/100) = 0,000001500250004166736– 6000000 * 0,000001500250004166736

– 9.000150002500041600000000





● Width: tamanho médio da linha em bytes

– Não é usado diretamente, mas sim entra no cálculo de quantas páginas serão retornadas e estima a quantidade de memória que a consulta irá usar, optando por tabela temporária em disco caso seja maior que a work_mem

Most Common Values

ds2=# explain select * from customers where state = 'DC';

QUERY PLAN

--------------------------------------------------------------------


Filter: ((state)::text = 'DC'::text)

6000000 * 0.0113 = 67800

Most common values = {"",DC,RI,NM,...

Most common freqs = {0.494867,0.0113,0.0111667,0.0109667...

Most Common Values

ds2=# explain select * from customers where state = 'SP'

QUERY PLAN

----------------------------------------------------------------


Filter: ((state)::text = 'SP'::text)

● No caso do valor não estar, retiramos todos os valores mais comuns e fazemos a conta com o número de distintos

● Selectivity = (1 - sum(mvf))/(num_distinct – num_mcv)

● (1 – 1.0000003200000003)/(52 – 100)

● Rows = total rows * selectivity

● 6000000 * 6.6666666724094865e-09

● 0.040000000034456917

Mais de uma condição

ds2=# explain SELECT * FROM customers WHERE customerid < 10 and state = 'DC';

QUERY PLAN

-----------------------------------------------------------------------------------

Index Scan using customers_pkey on customers (cost=0.43..39.35 rows=1 width=155)

Index Cond: (customerid < 10)

Filter: ((state)::text = 'DC'::text)

(3 rows)

● Quando temos mais de uma condição, as seletividades das duas condições são multiplicadas:

– 6000000 * 0.0113 * 0,000001500250004166736

JOIN Tables

ds2=# explain select * from customers, orders where customers.customerid = orders.customerid and customers.customerid < 9;

Nested Loop (cost=5.03..823.45 rows=4 width=185)

-> Index Scan using customers_pkey on customers (cost=0.43..35.90 rows=9 width=155)

Index Cond: (customerid < 9)

-> Bitmap Heap Scan on orders (cost=4.59..87.30 rows=21 width=30)

Recheck Cond: (customerid = customers.customerid)

-> Bitmap Index Scan on ix_order_custid (cost=0.00..4.59 rows=21 width=0)

Index Cond: (customerid = customers.customerid)

● Selectivity =(1 - null_frac1) * (1 - null_frac2) * min(1/num_distinct1, 1/num_distinct2)– (1 – 0) * (1 – 0,161867) * min(1/6000000, 1/0,161867) = 0.000000166666666666666667

● Rows = (outer_cardinality * inner_cardinality) * selectivity– (9 * 3.59648e+060) * 0.000000166666666666666667 = 4

Documentação PostgreSQL

● http://www.postgresql.org/docs/9.3/static/using-explain.html

● http://www.postgresql.org/docs/9.3/static/planner-stats.html

● http://www.postgresql.org/docs/9.3/static/planner-stats-details.html

GIT

● src/backend/optimizer/util/plancat.c

● src/backend/optimizer/path/clausesel.c

● src/backend/utils/adt/selfuncs.c

● src/include/catalog/pg_statistic.h

Uso de estatísticas pelo postgre sql

Technology

Transcript of Uso de estatísticas pelo postgre sql