postgresql performancessn/2010/lbaw/slides/lbaw-pgsql.pdf · para cada tuplo da tabela dependendo...

PostgreSQL Performance

André Restivo

Faculdade de Engenharia da Universidade do Porto

March 2, 2011

André Restivo (FEUP) PostgreSQL Performance March 2, 2011 1 / 45

Sumário

1 Armazenamento

2 Índices

3 Planeamento

4 PostgreSQL


Armazenamento

Outline

1 Armazenamento

2 Índices

3 Planeamento

4 PostgreSQL


Armazenamento

Armazenamento

Dados guardados em blocos (unidades mínimas de leitura).

Cada bloco contém vários tuplos.


Armazenamento

Indicadores

Número de Tuplos: t

Tamanho do Bloco: B bytes

Tamanho de cada tuplo: T bytes

Normalmente B >= T

Blocking Factor - bfr = B / T

Número de Blocos - b = t / bfr


Armazenamento

Performance

Um dos maiores obstáculos a uma boa performance é o acesso aodisco.

Os acessos ao disco são feitos bloco a bloco.

É necessário minimizar o número de blocos que é necessário ler decada vez que se consulta informação da base de dados.


Armazenamento

Exemplo

t = 30000 tuplos

B = 1024 bytes

T = 100 bytes

bfr = 1024 / 100 = 10 tuplos por bloco

b = 30000 / 10 = 3000 blocos


Armazenamento

Tuplos Desordenados

Ficheiro Sequencial de Tuplos Desordenados.Pesquisa:

◮ Pela chave primária◮ Por outro campo

Vantagens: inserção.

Desvantagens: remoção, pesquisa, ordenação, ...


Armazenamento

Tuplos Desordenados

Ficheiro Sequencial de Tuplos Desordenados.Pesquisa:

◮ Pela chave primária - b/2 leituras = 1500◮ Por outro campo - b leituras = 3000

Vantagens: inserção.

Desvantagens: remoção, pesquisa, ordenação, ...


Armazenamento

Tuplos Ordenados pela Chave Primária

Ficheiro Sequencial de Tuplos Ordenados pela ChavePrimária.Pesquisa:

◮ Pela chave primária◮ Por outro campo

Vantagens: pesquisa e ordenação pela chave primária.

Desvantagens: inserção, remoção, pesquisa eordenação por outras colunas, ...


Armazenamento

Tuplos Ordenados pela Chave Primária

Ficheiro Sequencial de Tuplos Ordenados pela ChavePrimária.Pesquisa:

◮ Pela chave primária - log2b = 12 leituras◮ Por outro campo - b = 3000 leituras

Vantagens: pesquisa e ordenação pela chave primária.

Desvantagens: inserção, remoção, pesquisa eordenação por outras colunas, ...


Índices

Outline

1 Armazenamento

2 Índices

3 Planeamento

4 PostgreSQL


Índices

Indexação Hash

Ficheiros indexados usando uma funçãode Hash.

Pesquisa

Vantagens: pesquisa (igualdade),remoção, inserção.

Desvantagens: pesquisa (outrométodo), ordenação, colisões, espaçoem disco, ...


Índices

Indexação Hash

Ficheiros indexados usando uma funçãode Hash.

Pesquisa - 1 leitura

Vantagens: pesquisa (igualdade),remoção, inserção.

Desvantagens: pesquisa (outrométodo), ordenação, colisões, espaçoem disco, ...


Índices

Indexação por Apontadores

Ficheiros auxiliares ordenados contendo pares valor/apontador.

Índices densos contêm um par valor/apontador para cada tuplo.

Índices esparsos contêm um par valor/apontador para cada bloco.Usados apenas quando os tuplos estão ordenados pelo campo deindexação.

Índices primários quando o campo de indexação é a chave primária.

Índices secundários quando o campo de indexação é outro.

Índices aglomerado (clustered) quando os tuplos estão ordenadospelo campo de indexação.


Índices

Indexação por Apontadores

Índice esparso primário aglomerado. Índice denso secundário.


Índices

Exemplo Índice Denso

Campo de indexação = 9 bytesApontador para bloco = 6 bytes (2.8x1014 tuplos)

ti = r = 30000

Ti = 9 + 6 = 15 bytes

bfri = 1024 / 15 = 68 tuplos por bloco

bi = 30000 / 68 = 442 blocos

Pesquisa


Índices

Exemplo Índice Denso


ti = r = 30000

Ti = 9 + 6 = 15 bytes


bi = 30000 / 68 = 442 blocos

Pesquisa - log2442+1 = 10 leituras


Índices

Exemplo Índice Esparso


ti = b = 3000

Ti = 9 + 6 = 15 bytes


bi = 3000 / 68 = 45 blocos

Pesquisa


Índices

Exemplo Índice Esparso


ti = b = 3000

Ti = 9 + 6 = 15 bytes


bi = 3000 / 68 = 45 blocos

Pesquisa - log245+1 = 7 leituras


Índices

Índice Multinível

Cada nível indexa o nível seguinte.

bi2 = 30000/68 = 442 blocosbi1 = 442/68 = 7 blocosbi0 = 7/68 = 1 blocoPesquisa


Índices

Índice Multinível

Cada nível indexa o nível seguinte.

bi2 = 30000/68 = 442 blocosbi1 = 442/68 = 7 blocosbi0 = 7/68 = 1 blocoPesquisa - 4 leituras


Índices

Árvores B+

Este tipo de indexação baseia-se numa estrutura de dados em formade árvore em que cada nodo contém:

◮ q apontadores para outro nodo◮ q – 1 valores

Os nodos do último nível (folhas) contêm:◮ q – 1 apontadores◮ – q – 1 valores◮ – apontador para o próximo bloco folha

Permite também ordenar e pesquisar em intervalos (ao contrário dosindíces hash).

Vantagens: pesquisa em intervalos, ordenação, ...

Desvantagens: pesquisa (um pouco mais lenta do que com hash),inserção, remoção, reorganização, ...


Índices

Árvores B+


Índices

Árvores B+

A ocupação de uma árvore B+ nunca deve ultrapassar 90% da suacapacidade.

Quando este valor é atingido, é criada um novo nível na árvore.Numa árvore B+ a 70% da sua capacidade:

◮ Árvore B+ com espaço para 34 pares valor-apontador.◮ 34 * 0.7 = 22 valores e 23 apontadores.◮ Raiz: 1 nó = 22 valores e 23 apontadores.◮ Nível 1: 23 nós = 506 valores e 529 apontadores.◮ Nível 2: 529 nós = 11638 entradas e 12167 apontadores.◮ Folhas: 12167 nós = 255507 apontadores para blocos.◮ 2.5 milhões de tuplos.

Permite pesquisar qualquer valor lendo apenas 5 blocos.


Índices

Índices Bitmap

Os índices bitmap são utilizados em colunas com poucos valoresdiferentes.

Para cada valor possível é criado um bitmap contendo um 0 ou um 1para cada tuplo da tabela dependendo do valor da coluna ser igual aovalor do bitmap.

Utilizando operações extremamente rápidas, um índice bitmap écapaz de calcular quais os tuplos que tornam verdade uma expressãobooleana.


Índices

Índices Bitmap


Índices

Índices Bitmap

Campo de indexação → Masculino / Feminino

ti = t = 30000

Ti = 2 estados = 2 bits

bfri = 1024 * 8 / 2 = 4096 tuplos por bloco

bi = 30000 / 4096 = 8 blocos


Índices

Full Text Search

Pesquisa em texto. Sem mais nenhum mecanismo:◮ Podemos pesquisar por The mas não por five.


Índices

Full Text Search

Separando o índice em palavras e retirando as maiúsculas.◮ Podemos pesquisar por five mas não por ive.


Índices

Full Text Search

Separando cada palavra nas suas terminações.◮ Podemos pesquisar por pack.◮ Podemos pesquisar por ack.◮ Podemos pesquisar por pac.


Índices

Full Text Search

Pesquisa ainda mais eficaz:◮ Palavras que não devem ser indexadas (stop words).◮ Sinónimos (usando dicionários).◮ Frases como uma só palavra (thesaurus).◮ Diferentes variações de uma palavra (stem rules e dicionários).


Índices

Escolher Índices

São criados índices nas chaves primárias e únicas automaticamente.

Criar índices nos atributos pesquisados frequentemente em tabelasgrandes.

Criar índices nos atributos de junção (sempre?).

Criar índices hash se as pesquisas usar o operador de igualdade(junções também).

Criar índices do tipo btree para ordenações e pesquisas em intervalos.

Criar índices clustered se as pesquisas retornarem muitos resultados( 10%).

Usar ferramentas de análise.


Planeamento

Outline

1 Armazenamento

2 Índices

3 Planeamento

4 PostgreSQL


Planeamento

Planeamento e Optimização de Queries

Ordem das junções (JOIN)◮ A (10 linhas), B (1000 linhas), C (1000000 linhas)◮ A⋊⋉ (B ⋊⋉ C) ou (A⋊⋉ B)⋊⋉ C◮ System R algorithm → programação dinâmica◮ Que índices usar?

Perguntas encadeadas (IN, NOT IN, EXISTS, ...)◮ Nem sempre podem ser transformadas em junções.◮ Por vezes são queries separadas (plano separado).

Estimativa de Custo de um Plano◮ Problema extremamente complicado.◮ O SGBD usam sistemas de regras e dados estatísticos.

"Inside the PostgreSQL Query Optimizer", Neil Conway, http://neilconway.org/talks/optimizer/optimizer.pdf


Planeamento

Planos Alternativos


PostgreSQL

Outline

1 Armazenamento

2 Índices

3 Planeamento

4 PostgreSQL


PostgreSQL

Criar Índices

Sintaxe

CREATE INDEX name ON tab le ( column ) USING ( type ) ;DROP INDEX name ;

type pode ser:◮ hash (não recomendado em PostgreSQL)◮ btree (por omissão)◮ gin (Generalized Inverted Index)◮ gist (Generalized Search Tree)


PostgreSQL

Índices Multi-coluna

Sintaxe

CREATE INDEX idxname ON tab lename ( column1 , column2 ) ;

Permite pesquisar por column1.

Permite pesquisar por column1 e column2.

Mas não por column2 isoladamente.

Similar a uma lista telefónica.


PostgreSQL

Índices Únicos

Sintaxe

CREATE UNIQUE INDEX idxname ON tab lename ( column ) ;

Garantem que não existem repetidos.

Unique Keys são açucar sintático.


PostgreSQL

Índices Parciais

Sintaxe

CREATE INDEX idxname ON tab lename ( column )WHERE c o n d i t i o n ;

Permitem criar índices sobre partes de uma tabela.

São usados quando uma pesquisa usa a mesma condição do índice.

Permitem poupar espaço e tempo.

Exemplo

SELECT ∗ FROM u s e r WHERE t ype = ’ADMIN ’ ;


PostgreSQL

Índices Únicos Parciais

Sintaxe

CREATE UNIQUE INDEX idxname ON tab lename ( column )WHERE c o n d i t i o n ;

Permitem criar restrições que de outra forma tinham de ser feitasusando triggers.

Exemplo

CREATE UNIQUE INDEX admin_department_idxON peop l e ( dep_id ) WHERE t ype = ’ADMIN ’ ;


PostgreSQL

Índices Sobre Funções

Sintaxe

CREATE INDEX idxname ON tab lename ( e x p r e s s i o n ) ;

Permitem criar índices sobre o resultado de uma função.

Úteis quando queremos fazer pesquisas, não pelo valor de umacoluna, mas sim por o resultado de uma função.

Exemplo

CREATE INDEX d i f f e r e n c e _ i d xON t r a n s a c t i o n s ( c r e d i t − d e b i t ) ;

SELECT ∗ FROM t r a n s a c t i o n sWHERE c r e d i t − d e b i t > 1000 ;


PostgreSQL

Índices Sobre Funções

Sintaxe

CREATE INDEX idxname ON tab lename ( e x p r e s s i o n ) ;

Permitem criar índices sobre o resultado de uma função.

Úteis quando queremos fazer pesquisas, não pelo valor de umacoluna, mas sim por o resultado de uma função.

Exemplo

CREATE INDEX name_lower_idxON peop l e ( l o w e r c a s e (name ) ) ;


PostgreSQL

Clustering

Em PostgreSQL não existem índices clustered.

É no entanto possível organizar os tuplos de uma tabelaperiodicamente de acordo com um índice.

Cada tabela só pode estar organizada segundo um índice de cada vez.

A operação de clustering faz lock à tabela.

Sintaxe

CLUSTER tab lename USING idxname ;


PostgreSQL

Full Text Search

Usar índices do tipo gin ou gist.

Os índices são índices sobre expressões. Usar a mesma expressão nasperguntas.

Sintaxe

CREATE INDEX idxname ON tab lenameUSING g i n ( t o_ t s v e c t o r ( ’ e n g l i s h ’ , column ) ) ;

Sintaxe

SELECT ∗ FROM tab lenameWHERE t o_ t s v e c t o r ( ’ e n g l i s h ’ , column )

@@ to_t sque r y ( ’ s e a r c h ␣&␣ term ’ ) ;


PostgreSQL

Full Text Search Ranking

Sintaxe

SELECT column , t s_rank ( t o_ t s v e c t o r ( ’ e n g l i s h ’ , column ) ,t o_t sque r y ( ’ s e a r c h ␣&␣ term ’ ) )

FROM tab lenameWHERE t o_ t s v e c t o r ( ’ e n g l i s h ’ , column )

@@ to_t sque r y ( ’ s e a r c h ␣&␣ term ’ )ORDER BY t s_rank ( t o_ t s v e c t o r ( ’ e n g l i s h ’ , column ) ,

t o_t sque r y ( ’ s e a r c h ␣&␣ term ’ ) ) DESC


PostgreSQL

Explain

Mostra o plano que o PostgreSQL pensa utilizar para umadeterminada query.

Pode não ser o plano efectivamente usado (estatísticas podem estarerradas).

Sintaxe

EXPLAIN query ;


PostgreSQL

Explain Analyze

Mostra o plano que o PostgreSQL pensa utilizar para umadeterminada query e o realmente utilizado.

Executa mesmo a query.

Sintaxe

EXPLAIN ANALYZE query ;


PostgreSQL

Analyze e Explain

Alguns nodos dos planos:◮ Nested Loop◮ Sequential Scan◮ Index Scan◮ Index Condition◮ Aggregate◮ Bitmap Heap Scan◮ Bitmap Index Scan◮ Bitmap And/Or◮ Sort◮ ...


PostgreSQL

Reindex, Vacuum e Analyze

Comandos que devem ser usados periodicamente:◮ Reindex - Reorganiza um índice.◮ Vaccum - Recupera espaço desperdiçado.◮ Analyze - Calcula estatísticas.

Sintaxe

REINDEX [ idxname | tab lename ] ;ANALYZE [ tab lename ] ;VACUUM [ANALYZE] [ tab le ]


postgresql performancessn/2010/lbaw/slides/lbaw-pgsql.pdf · para cada tuplo da tabela dependendo...

Documents