Upload
vinicius-schmidt
View
699
Download
2
Embed Size (px)
Citation preview
PgDay Campinas – 2015Índices no PostgreSQL – O que você precisa saber (Vinnix)!Dia 07/08/2015 - 10h35 - 11h35 - Auditório 1 / Unicamp
Agenda - Resumo
▪ Conceituação
▪ CREATE INDEX [CONCURRENTLY]
▪ FILLFACTOR
▪ ASC/DESC
▪ NULLS FIRST / NULLS LAST
▪ UNIQUE
▪ Múltiplas Colunas (composto)
▪ Parciais (where)
▪ Tablespaces
▪ Operadores e Funções
▪ Métodos de acesso
▪Btree▪ GiST (opt.: Buffering)
▪ GIN (opt.: FastUpdate)
▪ Hash (evite usar)
▪ SP-GiST
▪ BRIN (novidade)
▪ Vodka
▪ (Outros)
Agenda 2
▪ “Esta palestra demonstrará conceitos e práticas acerca da utilização dos índices (principalmente btree) com o PostgreSQL.
▪ Abordaremos a teoria básica por trás do mecanismo de índices b-tree.
▪ Além disso, veremos alguns casos práticos como índices compostos, índices parciais e quando utilizá-los para obter melhor performance.”
Conceitos: B-tree
http://patshaughnessy.net/2014/11/11/discovering-the-computer-science-behind-postgres-indexes
“What Does a B-Tree Index Look Like?Lehman and Yao’s paper explains an innovation they made to the B-Tree algorithm in 1981. I’ll discuss this a bit later. But they start with a simple introduction to the B-Tree data structure, which was actually invented 9 years earlier in 1972. One of their diagrams shows an example of a simple B-Tree:”
Muito mais que uma “árvore binária”
Nós à esquerda são menores que nós à direita
▪ (...)
Como isso se dá na prática? Observando com page-inspect contrib
teste_pgday2015=#
select
count(*),type
from (select gs.i , t.*
from (select generate_series(1,2744) as i) as gs,
bt_page_stats('stuff_g_idx',i) as t
) as c
group by type;
-- tabela com 1,000,000 de linhas
Count Type
Folha 2733 L
Intermediário 10 I
Raíz 1 R
Nos índices, são os itens dos nós-folha que apontam para as tuplas nas tabelas (via coluna interna de sistema ctid)
▪ Um nó está sempre ordenado
▪ Cada item de um nó folha aponta para o registro correspondente na tabela (ctid)
▪ Os itens dos nós intermediários apontam para os nós (à “esquerda” se forem menores, à “direita” se forem maiores).
▪ Sempre há um nó raiz com alguns itens, geralmente apontando para nós intermediários
Inside IndexTupleData
Vamos nos lembrar que o PostgreSQL é OpenSource?
▪ Como as coisas funcionam dependem do quanto você está disposto a conhecer os mecanismos que afetam o processo▪ complexidade▪ concorrência em i/o (r/w)▪ comunidade
▪ Saber inglês e C é básico?
▪ Aprender usando
Now, Let’s have some fun!
Duh!!!
▪ DBA, it is not your fault!
▪ Developers, let’s try not to blame each other, it’s not your fault either.
▪ We are in the same boat..
▪ Why not work together?
▪ Não dependa do DBA para indexar o seu banco!
▪ Slide from: http://www.slideshare.net/MarkusWinand/indexes-neglectedperformanceallrounder
It’s a dev task!
Queries• Sabem as regras
do negócio• Registram
regras e dados do negócio
• Acessam dados
Data Manipulation• How does PG
access your data?
• Deve ser eficiente e efetiva
Indexes• Considere os
dados nas tabelas
• Considere como são feitas as buscas
Tests & Monitoring• auto_explain• pgAnalytics ;-)• pgBadger• Teste em
produção?
Como vejo os índices no PostgreSQL?
teste_pgday2015=# \d+ stuffTable "public.stuff" Column | Type --------+---------+--- a | text | b | text | c | integer | d | integer | e | integer | f | integer | g | integer |
Indexes:"stuff_a_idx" btree (a) WITH
(fillfactor=80) "stuff_b_idx" btree (b) "stuff_c_idx" btree (c) "stuff_d_idx" btree (d) "stuff_e_idx" btree (e) "stuff_f_idx" btree (f) "stuff_g_a_b_idx" btree (g, a, b) "stuff_g_idx" btree (g)
E os planos de acesso (EXPLAIN) ?
http://www.postgresql.org/docs/9.4/static/using-explain.html
▪ Execute Analyze nas tabelas primeiro para atualizar as estatísticas!
▪ Confira os planos de acesso!
▪ RTFM: using-explain!
▪ (again) Why not work together?
EXPLAIN - Exemplo 1
teste_pgday2015=# explain analyze select count(*) from stuff where c < 999990; QUERY PLAN--------------------------------------------------------------------------------- Aggregate (cost=43046.97..43046.99 rows=1 width=0) (actual time=20302.453..20302.454 rows=1 loops=1)-> Seq Scan on stuff (cost=0.00..40547.00 rows=999990 width=0) (actual time=0.008..19200.425 rows=999989 loops=1) Filter: (c < 999990) Rows Removed by Filter: 11 Planning time: 0.161 ms Execution time: 20302.504 ms(6 rows)
EXPLAIN - Exemplo 2
teste_pgday2015=# explain analyze select count(*) from stuff where c > 999990; QUERY PLAN ----------------------------------------------------------------------------------- Aggregate (cost=4.62..4.63 rows=1 width=0) (actual time=0.040..0.041 rows=1 loops=1) -> Index Only Scan using stuff_c_idx on stuff (cost=0.42..4.60 rows=10 width=0) (actual time=0.009..0.022 rows=10 loops=1) Index Cond: (c > 999990) Heap Fetches: 0 Planning time: 0.156 ms Execution time: 0.077 ms(6 rows) Não basta a coluna estar indexada
temos que entender como o operador compara os items dentro dos nós da “árvore”
CREATE INDEX CONCURRENTLY
Criar índice bloqueia a tabela?
Simple like this! 8)
http://www.postgresql.org/docs/9.4/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY
▪ … sim, mas não se usar “concurrently”
▪ Escolha o dia e horário
▪ Não os crie à toa; se você testá-los e os índices não servirem, apague-os
CREATE INDEX orders_unbilled_index ON orders (order_nr);
CREATE INDEX CONCURRENTL
Y idx_pessoa_cpf ON
pessoa (cpf);
DROP INDEX CONCURRENTLY
idx_pessoa_cpf;
Parâmetro::FILLFACTOR
▪ Pode variar de 10 a 100 (%)
▪ Valor padrão 90 em B-trees
▪ Se tabela é estática → 100
▪ Se tabela é muito atualizada, um número menor (→ 10) é melhor para evitar a necessidade de quebras/divisões (splits) nas páginas
▪ “É a percentagem que determina o quão cheio os nós vão estar” (?)
▪ Em B-trees as folhas (leafs) são preenchidas nessa percentagem
▪ Se as páginas ficam cheias (+1), elas sofrem split
CREATE INDEX CONCURRENTL
Y idx_pedidos_cpf
ON pedidos (cpf) WITH (f
illfactor = 40);
Parâmetro:: ASC / DESC
CREATE INDEX title_idx ON films (title DESC);
▪ Como você pensa em ORDER BY?
▪ Qual sua incidência sobre o código?
▪ Prepare o índice de forma a fazer com que a incidência de buscas da sua aplicação seja compatível com a forma de ordenação do índice.
Parâmetro:: NULLS FIRST / NULLS LAST
▪ Pense em como estão distribuídos os seus dados nas tabelas!
▪ Valores nulos são permitidos na coluna indexada?
▪ Em consultas, os valores nulos devem ser exibidos primeiro ou por último (na maioria dos casos)?
Parâmetro:: Unique
▪ Apenas em B-trees
▪ Chaves Primárias são índices únicos em colunas que não podem conter valores nulos
▪ Representa uma “regra de negócio”
▪ Pode ser “composto”
▪ Pode ser “parcial”
Múltiplas colunas (composto)
▪ Atualmente apenas B-tree, GiST e GIN são os métodos que suportam multiplas colunas
▪ Até 32 colunas
▪ Use as mais restritivas primeiro!
▪ (produto, deposito, filial)
▪ Será parcialmente usado, se utilizar as colunas da esquerda para direita, sem pular nenhuma, mesmo que não todas
Parciais (where)
▪ O modelo de dados pode beneficiar a tomada de decisões
▪ Imagine um índice especializado (será mais eficiente)
▪ Apenas com colunas da própria tabela que usará tal índice
▪ Não é porque o EXPLAIN mostra que usa um índice que isso basta
http://www.postgresql.org/docs/9.4/static/indexes-partial.html
CREATE INDEX orders_unbilled_index ON orders (order_nr) WHERE billed is not true;
CREATE INDEX access_log_client_ip_ix ON access_log (client_ip) WHERE NOT (client_ip > inet '192.168.100.0' AND client_ip < inet '192.168.100.255');
Parciais:: Outro caso prático
CREATE INDEX event_signups ON event (time DESC)
WHERE (data->>'type')='submit' AND (data->>'path')='/signup/' ;
http://blog.heapanalytics.com/speeding-up-postgresql-queries-with-partial-indexes/
SELECT * FROM event
WHERE (data->>'type')='submit' AND (data->>'path')='/signup/'
ORDER BY time DESC ;
Funções
CREATE INDEX ON films ((lower(title)));
▪ Quando se chama uma função passando uma coluna como parâmetro, um índice simples nessa coluna não será utilizado
▪ Contornar este problema é simples, se indexarmos a função com essa coluna como parâmetro
CREATE INDEX pointloc ON points USING gist (box(location,location));
SELECT * FROM points WHERE box(location,location) && '(0,0),(1,1)'::box;
Storage::Tablespaces
▪ Mantenha dados e índices em tablespaces distintas
▪ Índices em discos bastante rápidos
▪ SSDs?
▪ ZFS + (NFS ou iSCSI)
▪ “Hardware”
ALTER INDEX
http://www.postgresql.org/docs/9.4/static/sql-alterindex.html
▪ Renomear
▪ Alterar tablespace
▪ Alterar fillfactor
Outros Métodos de Acesso
▪ Framework extensível
▪ Vasta documentação
▪ Eu falei que é OpenSource?
▪ DIY
Outros Métodos de Acesso::GiST
▪ Suporta tipos de dados customizados!
▪ Utilizados em visão computacional, bioinformática, remote sensing[1],
▪ São “grandes”, demoram um pouco mais para serem construidos
▪ Generalized Search Tree (GiST)
▪ box, circle, inet, cidr, point, polygon,
ranges, tsquery, tsvector
▪ Utiliza operadores especializados
▪ Utiliza operadores customizados
▪ Em contribs (btree_gist, cube, hstore,
intarray, ltree, pg_trgm, seg)
Outros Métodos de Acesso::GIN
▪ Generalized Inverted Index
▪ Internamente, o GIN contem B-trees construídas sob chaves...
▪ ...onde a própria chave é um elemento de um ou mais itens indexados
▪ São “pesados” para serem criados numa inserção massiva de dados
▪ Contribs: hstore, intarray, pg_trgm, btree_gin
Outros Métodos de Acesso::Hash
▪ Evite utilizá-lo! Suas operações não estão presentes no WAL
▪ Só serve para comparações de igualdade (=)
▪ Após um crash necessita REINDEX
Outros Métodos de Acesso::SP-GiST
▪ Space Partitioned GiST
▪ Estruturas não balanceadas (quad-tree, k-d tree, radix tree)
▪ Essas não são originalmente desenhadas para fazer muito acesso a disco
▪ São estruturas de grande complexidade para se armazenar no disco (este é o desafio!)
Outros Métodos de Acesso::BRIN (novo)
▪ Novidade da versão 9.5
▪ Vantagem: ocupa menos espaço
▪ Relativamente menos eficiente
▪ Block Range Index
Outros Métodos de Acesso::VODKA (super-novo)
▪ Fortemente baseado no GIN, mas substitui a parte “btree” por “sp-gist”
▪ Idealizado para se trabalhar com colunas jsonb e hstore
▪ jsquery (jsonb query language)
▪ Trabalho suportado pela Heroku
https://www.youtube.com/watch?v=2dQjfdXxtJw- by Alexander Korotkov, Oleg Bartunov, and Teodor Sigaev
- CREATE INDEX … USING VODKA
PGStrom [wtf?]
▪ NVidias’s CUDA Support
https://wiki.postgresql.org/wiki/PGStrom
PGStrom [wtf?^2]
https://wiki.postgresql.org/wiki/PGStrom
Considerações finais
▪ Verifique índices que não estão sendo utilizados: pg_stat_user_indexes
▪ Verifique índices duplicados
▪ Monitore seu tamanho e fragmentação
Material de Referência (references)
Links acessados entre 2015-07-15 e 2015-08-07:
http://michael.otacoo.com/postgresql-2/postgres-9-5-feature-highlight-pageinspect-gin-indexes/
http://michael.otacoo.com/postgresql-2/postgres-contrib-module-highlight-pageinspect/
http://www.depesz.com/2010/09/09/why-is-my-index-not-being-used/
http://pgsqldeepdive.blogspot.com.br/2012/12/postgresqlhot.html
https://devcenter.heroku.com/articles/postgresql-indexes
http://www.sai.msu.su/~megera/postgres/gist/
http://www.sai.msu.su/~megera/wiki/spgist_dev
http://www.postgresql.org/message-id/CAM3SWZTyRMDgHyikJu_Dsx046jRzoTR94YRd+iB5R7tq1hv2aQ@mail.gmail.com
pageinspect :: http://www.postgresql.org/docs/9.4/static/pageinspect.html
Material de Referência (references) - 2
Links acessados entre 2015-07-15 e 2015-08-07:
http://www.postgresql.org/docs/9.5/static/index-scanning.html
http://www.depesz.com/2010/06/28/should-you-use-hash-index/
https://www.youtube.com/watch?v=2dQjfdXxtJw
http://www.slideshare.net/Tech_MX/b-tree-14155416
http://use-the-index-luke.com/
Obrigado!@vischmidt@sec_vinnix