Otimização de consultas no PostgreSQL

A todo o momento, quando se trabalha no desenvolvimento de aplicações e/ou administração de dados, busca-se sempre uma melhora de desempenho. Ao trabalhar com consultas em bancos de dados deve-se ter uma atenção maior com relação à sua eficiência, uma vez que consultas mal elaboradas podem degradar consideravelmente o desempenho do sistema como um todo.

Para que seja possível fazer melhorias relativas às consultas, é preciso entender como analisar seu desempenho, bem como os fatores que contribuem para sua melhoria.

Este artigo busca esclarecer estas questões e apresentar as ferramentas disponíveis no PostgreSQL que auxiliam nesta tarefa de otimização e, também, fornecer uma base de conhecimento para que seja possível criar consultas mais inteligentes, refinadas, objetivando o ganho de performance.

Entendendo o plano de execução

Quando se executa uma operação no banco de dados, seja ela um SELECT, INSERT, ou outra qualquer, o PostgreSQL, assim como outros SGBDs, possui um mecanismo interno chamado planejador (ou otimizador), que reescreve a consulta com a intenção de aperfeiçoar os resultados, gerando um Plano de Execução.

O Plano de Execução, ou de Consulta, é uma seqüência de passos que serão executados pelo SGBD para executar uma consulta, ou seja, quais os tipos de processamento que serão feitos diretamente nos registros ou em estruturas de índices, bem como informações como o tempo de entrada, o tempo de resposta e o total de registros percorridos. O planejador precisa então fazer uso de estatísticas como o número total de registros da tabela, o número de blocos de disco ocupados por cada tabela e se há a presença de índices ou não.

Criando um banco de dados

Para exemplificar os trabalhos do plano de execução, será criado o banco de dados ACADEMICO, utilizando a ferramenta de interface do PostgreSQL, pgAdmin III (ver Figura 1).

Para a criação deste banco de dados foi utilizado o usuário Administrador como proprietário, WIN1252 como tipo de caractere padrão e o template sendo o default. O PostgreSQL possui uma propriedade (tablespace) que permite ao proprietário do banco definir onde os objetos da base de dados (como tabelas e índices) irão residir. No nosso caso foi usado o caminho padrão.

Na Figura 2, pode-se visualizar, em linguagem SQL, o script de criação do banco de dados. A Listagem 1 apresenta o script de criação das tabelas do banco. Para os exemplos deste artigo, foram inseridos alguns poucos registros em cada uma destas tabelas.