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

Ciclo de vida de uma consulta

Ciclo de vida de uma consulta

Ciclo de vida de uma consulta

Ciclo de vida de uma consulta

Ciclo de vida de uma consulta

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 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

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

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 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

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)

● 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

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)

● 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

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

Seq Scan on customers (cost=0.00..277012.00 rows=67800 width=155)

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

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

Seq Scan on customers (cost=0.00..277012.00 rows=1 width=155)

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