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.
/* 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;
... 
