Usando e criando índices

Melhorando o Desempenho de Consultas no PostgreSQL

Amy McCarty

Instructor

Visão geral de índices

O que

  • Método para criar chaves de colunas ordenadas e acelerar buscas
  • Semelhante ao índice de um livro
  • Referência à localização dos dados

Por quê

  • Consultas mais rápidas

Onde

  • Colunas comuns de filtro
  • Chave primária
Melhorando o Desempenho de Consultas no PostgreSQL

Exemplo de índice

ingrediente receita
tomates espaguete com almôndegas
cebolinha arroz frito
ovos arroz frito
carne moída espaguete com almôndegas
massa espaguete com almôndegas
arroz arroz frito
molho de soja arroz frito 
SELECT *
FROM cookbook
WHERE recipe = 'fried rice'
Melhorando o Desempenho de Consultas no PostgreSQL

Índice como chave e ponteiro

Duas tabelas. Uma tabela de índice contém a coluna recipe e uma coluna de ponteiros, que vão de _12 a _18 sequencialmente. A Tabela com índice é a mesma tabela à esquerda com uma coluna adicional de ponteiros. Os valores dessa coluna de ponteiros são os mesmos da coluna de ponteiros da Tabela de índice. Setas mostram como os quatro valores de fried rice na Tabela de índice correspondem aos quatro valores de fried rice na Tabela com índice.

Melhorando o Desempenho de Consultas no PostgreSQL

Encontrando índices existentes

pg_tables
  • Semelhante a information_schema
    • específico do Postgres
  • Metadados do banco de dados
Melhorando o Desempenho de Consultas no PostgreSQL

Encontrando índices existentes

pg_tables
  • Semelhante a information_schema
    • específico do Postgres
  • Metadados do banco de dados

 

SELECT * FROM pg_indexes
schemaname tablename indexname tablespace indexdef
food dinner recipe_index null CREATE INDEX recipe_index ...
Melhorando o Desempenho de Consultas no PostgreSQL

Criando um índice

Tabela dinner com colunas ingredient, recipe e serving size. tomatoes - spaghetti & meatballs - 4. green onions - fried rice - 2. eggs - fried rice - 2. ground beef - spaghetti & meatballs - 4. pasta - spaghetti & meatballs - 4. rice - fried rice - 2. soy sauce - fried rice - 2.

CREATE INDEX recipe_index 
ON cookbook (recipe);

CREATE INDEX CONCURRENTLY recipe_index
ON cookbook (recipe, serving_size);
Melhorando o Desempenho de Consultas no PostgreSQL

Usar ou não usar

Usar um índice

  • Tabelas grandes
  • Condições comuns de filtro
  • Chave primária

Evitar índice

  • Tabelas pequenas
  • Colunas com muitos nulls
  • Tabelas atualizadas com frequência
    • Índice ficará fragmentado
    • Escrita em dois lugares
Melhorando o Desempenho de Consultas no PostgreSQL

Tabelas atualizadas com frequência

Duas tabelas. Uma tabela de índice contém a coluna recipe e uma coluna de ponteiros, que vão de _12 a _19 sequencialmente. A Tabela com índice é a mesma tabela à esquerda com uma coluna adicional de ponteiros. Os valores dessa coluna de ponteiros são os mesmos da coluna de ponteiros da Tabela de índice. Setas mostram como os quatro valores de spaghetti and meatballs na Tabela de índice correspondem aos quatro valores de spaghetti and meatballs na Tabela com índice. Três valores estão juntos no topo da Tabela de índice. Um spaghetti and meatballs está no fim da Tabela de índice, correspondendo à nova entrada basil na Tabela com índice.

Melhorando o Desempenho de Consultas no PostgreSQL

Avaliação de consulta com índice

Planejador de consultas

Vários chefs ao redor de uma grande panela 

EXPLAIN
SELECT * 
FROM cookbook

 

Plano de consulta

Seq scan on cookbook (cost=0.00...22.70
  rows = 1270 width = 36)
  • Estimativas de custo (tempo)
Melhorando o Desempenho de Consultas no PostgreSQL

Vamos praticar!

Melhorando o Desempenho de Consultas no PostgreSQL

Preparing Video For Download...