iMasters | Por uma internet mais criativa e dinâmica

Segunda-feira, 19/04/2004 - 01:59 - Por Daniel Oslei
Seções relacionadas:

• PostgreSQL

• Imprimir
• Indicar
• Digg it
• Del.icio.us
• Twitter
• 1 comentários

Olá comunidade iMasters! Depois de três artigos mostrando conceitos de índices no PostgreSQL, enfim chegamos à última parte. E nesse artigo teremos como objetivo obter idéias mais avançadas na criação de índice, tentando conduzir sempre ao melhor desempenho possível.

Para que esse objetivo seja conquistado, dependerá do responsável pela administração do banco de dados fazer una análise correta da situação que se passa com auxílio de algumas estatísticas sobre o banco. Como obter estas estatísticas ficará para ser aprofundado num próximo artigo, mas desde já comessem a se acostumar com algumas idéias interessantes. Colocarei em forma de itens uma breve seqüência de dicas:

- É importante utilizar índices em chaves estrangeiras, já que estes são muito utilizados em joins. O índice será útil quando a tabela que possui uma chave estrangeira tentar acessar dados na tabela que dá suporte à esta chave. Também será útil no caso em que for excluir determinada linha de uma tabela que possui uma chave estrangeira, terá que ser feita uma leitura na tabela de onde vem os dados para a chave estrangeira para excluir os dados nesta segunda tabela no caso em que foi definido ON DELETE CASCADE, ou para não permitir a deleção na primeira tabela se caso foi determinado ON DELETE RESTRICT.

- As expressões de índice são relativamente dispendiosas de serem mantidas, uma vez que a expressão derivada deve ser computada para cada linha ao ser inserida ou sempre que for atualizada. Portanto, devem ser utilizadas somente quando as consultas que usam o índice são muito freqüentes.

- Deve-se haver muito cuidado em comparações de proporcionalidade envolvendo números relativamente pequenos. Enquanto selecionar 1.000 de cada 100.000 linhas (1% do total) pode ser um candidato para um índice, selecionar 1 de cada 100 linhas, que também corresponde a 1% do total, dificilmente será, porque as 100 linhas provavelmente cabem dentro de uma única página do disco, não havendo nenhum plano melhor que uma busca seqüencial em uma página do disco.

- Quando os índices não são usados, pode ser útil como teste forçar sua utilização. Existem parâmetros em tempo de execução que podem desativar vários tipos de planos. Por exemplo, desativar varreduras seqüenciais (enable_seqscan) e junções de laço-aninhado (nested-loop joins) (enable_nestloop), que são os planos mais básicos, forçam o sistema a utilizar um plano diferente. Se o sistema ainda assim escolher a varredura seqüencial ou a junção de laço-aninhado então existe, provavelmente, algum problema mais fundamental devido ao qual o índice não está sendo utilizado como, por exemplo, a condição da consulta não corresponder ao índice.

- Se forçar a utilização do índice não faz o índice ser usado, então existem duas possibilidades: ou o sistema está correto e a utilização do índice não é apropriada, ou a estimativa de custo dos planos de comando não estão refletindo a realidade. Portanto, deve ser medido o tempo da consulta com e sem índices. O comando EXPLAIN ANALYZE pode ser útil neste caso.

- Quando achar necessário que determinadas informações tenham que ser únicas em uma determinada coluna, evite que a aplicação que utiliza o banco de dados faça isto. Haverá mais vantagens se o SGBD se responsabilizar pela unicidade dos dados.

- Se estiver sendo carregada uma tabela recém criada, a maneira mais rápida é criar a tabela, carregar os dados usando o COPY e, depois, criar todos os índices necessários para a tabela. Criar um índice sobre dados pré-existentes é mais rápido que atualizar de forma incremental durante a carga de cada linha. Para carregar uma tabela existente, pode-se remover o índice, carregar a tabela e, depois, recriar o índice. É claro que o desempenho do banco de dados para os outros usuários será afetado negativamente durante o tempo que o índice não existir. Deve-se pensar duas vezes antes de remover um índice único, porque a verificação de erro efetuada pela restrição de unicidade não existirá enquanto o índice não tiver sido criado novamente.

- Utilizando o comando abaixo, poderá se obter informações sobre os índices contidos no banco de dados, como por exemplo, o número total de varreduras que utilizaram um determinado índice e o número de linhas lidas com aquele índice.

SELECT * FROM pg_stat_all_indexes

- Em determinados casos, em que haja várias consultas que utilizem os comandos ORDER BY, GROUP BY e DISTINCT é aconselhável criar um índice para a coluna que está sendo utilizada nestas consultas. Isso se deve ao fato de que cada vez que ocorre isto, o SGBD dispara um SORT para a ordenação dos dados, o que pode corromper desempenho. Havendo índices para este caso, os dados já poderão estar ordenados e no final das contas economizando alguns milissegundos em processamento.

Brevemente será lançado um artigo para esclarecimento da utilização de informações estatísticas que o PostgreSQL possibilita para que se possa melhor planejar o caminho que o SGBD terá que percorrer e também para que se possa mais facilmente encontrar pontos que causem problemas no seu desempenho.

Por enquanto é isso pessoal, até a próxima semana.

Todos os artigos de Daniel Oslei

26/01/2005 - Instalação do PostgreSQL 8.0 13/01/2005 - Junção entre tabelas no PostgreSQL - Parte 02 13/01/2005 - Junção entre tabelas no PostgreSQL 10/01/2005 - Iniciando a utilização do Geronimo - Parte 02 04/01/2005 - Chamando Stored Procedures do PostgreSQL em aplicações Java 21/12/2004 - Iniciando a utilização do Geronimo - novo servidor da Apache 10/08/2004 - Melhorando a performance do PostgreSQL com o comando VACUUM 07/07/2004 - Stored Procedures - Parte 02 07/06/2004 - Consultando a estrutura de uma tabela 25/05/2004 - Integrando PostgreSQL com PHP 18/05/2004 - Lançado o Pl/PHP 17/05/2004 - Stored Procedures 19/04/2004 - Índices - Dicas de desempenho 13/04/2004 - Índices - Parte 3 31/03/2004 - Indices - Parte 02 24/03/2004 - Índices 16/02/2004 - O Tipo de Dados Serial 09/02/2004 - Uso de imagens no PostgreSQL

1 comentários publicados

• 1. Índices em campos timestamp

  Sexta-feira, 26/08/2005, por Leandro Damasceno Alves
  

  Prezado,
  
  Verifiquei, através do explain que o índice não é utilizado para o campo tipo timestamp quando adiciono condições do tipo:
  data between '...' and '...'
  
  Há uma solução alternativa para que o banco utilize o índice criado, nestes casos?

  Responder comentário

Poste um comentário

(clique se não for cadastrado)

Login: Senha: Titulo: digite aqui seu comentário Site/blog: Enviar Receber notificação de novos comentários

Sobre o autor

Daniel Oslei trabalha com TI, utilizando PHP, Java e PostgreSQL nos principais projetos, cursando também Sistemas de Informação no CEFET-PR.

Indique para um amigo

Escreva aqui sua mensagem! ok

Tags relacionadas

windows android tecnologia desenvolvimento linux ti lançamento php google firefox seguranca 3g yahoo iphone twitter microsoft crescimento mercado criação internet youtube brasil apple web celular mobile

TI SHOP Produtos iMasters

• Lançamento: CD-ROM Treinamento Aplicado de SQL - Lançamento! Treinamento Aplicado de SQL - Aprenda a trabalhar com SQL com bancos de dados Oracle e SQL Server. São mais de 100 tópicos explicados por Mauro Pichilliani, um articulistas mais lidos do iMasters. Aproveite! Apenas R$ 69,90 no TI SHOP.
• Lançamento: Livro iMasters "O Encontro de 2 Mundos"- Este livro conta com 56 crônicas de profissionais mais admirados e influentes do mercado brasileiro de Internet. Aproveite o preço especial para leitores do iMasters. Apenas R$ 40,00 e envio imediato!
• DVD Curso Completo de Photoshop - Do conceito à finalização Lançamento! Curso Completo de Photoshop, em DVD, com mais de 230 aulas dividas em 4 módulos: conceito, básico, avançado e finalização. Apenas R$ 69,50 no TI SHOP - Frete com 50% de desconto
• DVD Javascript Starter - Curso Completo Com mais de 9 horas de vídeo-aulas, é um curso completo sobre Javascript. Ideal para quem deseja aprender a linguagem. Apenas R$ 64,90 no TI SHOP - Frete com 50% de desconto!