Artigos Banco de Dados 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).

Criação do banco de dados Academico
Figura 1. Criação do banco de dados Academico

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.

Script de criação do banco de dados
Figura 2. Script de criação do banco de dados

/*  Criação da tabela Aluno (Aluno)                                                  
*/
 CREATE TABLE Aluno ( 
        matricula_aluno             INTEGER        NOT NULL,
        nome_aluno                    VARCHAR(30)    NOT NULL,
        end_logradouro                VARCHAR(30),
        end_numero                    INTEGER,
        end_bairro                    VARCHAR(20),
        telefone_residencial          VARCHAR(15),
        data_nascimento               DATE,
        cod_curso                     INTEGER        NOT NULL);
  
 /*  Criação da tabela Curso (Curso)                                                  
 */
 CREATE TABLE Curso ( 
        cod_curso                     INTEGER        NOT NULL,
        desc_curso                    VARCHAR(30)    NOT NULL,
        matricula_professor           INTEGER);
  
 /*  Criação da tabela Professor (Professor)                                          
 */
 CREATE TABLE Professor ( 
        matricula_professor           INTEGER        NOT NULL,
        nome_professor                VARCHAR(30)    NOT NULL,
        titulacao_maxima              VARCHAR(10)    NOT NULL);
  
 /*  Criação da tabela Curso_Professor (Curso_Professor)                              
 */
 CREATE TABLE Curso_Professor ( 
        cod_curso                     INTEGER        NOT NULL,
        matricula_professor           INTEGER        NOT NULL);
  
 /*  Criação da tabela Turma (Turma)                                                  
 */
 CREATE TABLE Turma ( 
        cod_curso                     INTEGER        NOT NULL,
        ano_turma                     INTEGER        NOT NULL,
        semestre_turma                INTEGER        NOT NULL,
        desc_turma                    VARCHAR(10)     NOT NULL,
        matricula_professor           INTEGER        NOT NULL);
  
 /*  Criação da tabela Turma_Aluno (Turma_Aluno)                                      
 */
 CREATE TABLE Turma_Aluno ( 
        matricula_aluno               INTEGER        NOT NULL,
        cod_curso                     INTEGER        NOT NULL,
        ano_turma                     INTEGER        NOT NULL,
        semestre_turma                INTEGER        NOT NULL,
        desc_turma                    VARCHAR(10)    NOT NULL);
  
 /*  Criação de chave primária PK_Aluno (PK_Aluno) da tabela Aluno (Aluno)            
 */
 ALTER TABLE Aluno ADD CONSTRAINT PK_Aluno PRIMARY KEY(matricula_aluno);
  
 /*  Criação de chave primária PK_Curso (PK_Curso) da tabela Curso (Curso)            
 */
 ALTER TABLE Curso ADD CONSTRAINT PK_Curso PRIMARY KEY(cod_curso);
  
 /*  Criação de chave primária PK_Professor (PK_Professor) da tabela Professor (Professor) 
 */
 ALTER TABLE Professor ADD CONSTRAINT PK_Professor PRIMARY KEY(matricula_professor);
  
 /*  Criação de chave primária PK_Curso_Professor (PK_Curso_Professor) da 
 tabela Curso_Professor (Curso_Professor) 
 */
 ALTER TABLE Curso_Professor ADD CONSTRAINT PK_Curso_Professor PRIMARY 
 KEY(cod_curso,matricula_professor);
  
 /*  Criação de chave primária PK_Turma (PK_Turma) da tabela Turma (Turma)            
 */
 ALTER TABLE Turma ADD CONSTRAINT PK_Turma PRIMARY KEY(cod_curso,ano_turma,
 semestre_turma,desc_turma);
  
 /*  Criação de chave primária PK_Turma_Aluno (PK_Turma_Aluno) da tabela 
 Turma_Aluno (Turma_Aluno) 
 */
 ALTER TABLE Turma_Aluno ADD CONSTRAINT PK_Turma_Aluno PRIMARY 
 KEY(matricula_aluno,cod_curso,ano_turma,semestre_turma,desc_turma);
  
 /*  Criação das chaves estrangeiras da tabela Aluno                                  
 */
 /*  Criação da chave estrangeira FK_Aluno_01 (FK_Aluno_01)                           
 */
 ALTER TABLE Aluno ADD CONSTRAINT FK_Aluno_01 FOREIGN KEY(cod_curso) REFERENCES 
 Curso;
  
 /*  Criação da chave estrangeira FK_Curso_01 (FK_Curso_01)                           
 */
 ALTER TABLE Curso ADD CONSTRAINT FK_Curso_01 FOREIGN KEY(matricula_professor) 
 REFERENCES Professor;
  
 /*  Criação das chaves estrangeiras da tabela Curso_Professor                        
 */
 /*  Criação da chave estrangeira FK_Curso_Professor_01 (FK_Curso_Professor_01)       
 */
 ALTER TABLE Curso_Professor ADD CONSTRAINT FK_Curso_Professor_01 FOREIGN 
 KEY(cod_curso) REFERENCES Curso;
  
 /*  Criação da chave estrangeira FK_Curso_Professor_02(FK_Curso_Professor_Professor_02) 
 */
 ALTER TABLE Curso_Professor ADD CONSTRAINT FK_Curso_Professor_02 FOREIGN 
 KEY(matricula_professor) REFERENCES Professor ON DELETE CASCADE;
  
 /*  Criação da chave estrangeira FK_Turma_02 (FK_Turma_02)                           
 */
 ALTER TABLE Turma ADD CONSTRAINT FK_Turma_02 FOREIGN KEY(matricula_professor) 
 REFERENCES Professor;
  
 /*  Criação da chave estrangeira FK_Turma_Aluno_01 (FK_Turma_Aluno_01)               
 */
 ALTER TABLE Turma_Aluno ADD CONSTRAINT FK_Turma_Aluno_01 FOREIGN 
 KEY(cod_curso,ano_turma,semestre_turma,desc_turma) REFERENCES Turma;
 ...
Quer ler esse conteúdo completo? Tenha acesso completo