PostgreSQL Prático/DDL/Índices, Tipos de Dados e Integridade Referencial
3.3 - Índices, Tipos de Dados e Integridade Referencial
É importante conhecer bem o máximo de recursos existentes no banco, especialmente aqueles relacionados às nossas necessidades. Assim trabalhamos com mais eficiência e criamos bancos mais leves e com mais potencial. Os tipos de dados são fatores de desempenho.
Exemplo:
Se um campo tipo inteiro irá precisar de valores até 100 e nunca mudará esta faixa. Não devemos usar este campo com o tipo INT8, quando o INT2 atende e sobra.
De forma semelhante escolher todos os demais campos da tabela com bom senso.
Mais Detalhes no Capítulo 8 do Manual:
http://pgdocptbr.sourceforge.net/pg80/datatype.html
Índices
Os índices são recursos do SGBD para melhorar o desempenho de consultas. Mas como o uso de índices também tem um preço é importante planejar bem e conhecer as particularidades antes de adicionar um índice.
Cada vez que um registro é inserido ou atualizado a tabela de índices também é atualizada.
Quando criamos consultas SQL, que pesquisam tabelas com muitos registros e esta consulta usa a cláusula WHERE, então os campos que fazem parte da cláusula WHERE são bastante indicados para índice, para que melhore o desempenho da consulta.
Os índices são uma forma de melhorar o desempenho de bancos de dados. Ao invés de procurar de forma sequencial, o servidor procura pelo índice, como se faz uma busca em índices de livros e vai-se diretamente à página procurada.
O índice é passado para cada registro adicionado ou removido.
É difícil criar regras genéricas para determinar que índices devem ser definidos. Muita experiência por parte do administrador e muita verificação experimental é necessária na maioria dos casos.
Criar um índice:
CREATE INDEX nomeindice ON tabela (campo);
Regra geral para nome de índice: idx_nometabela_nomecampo
Obs.: índices não importantes ou não utilizados devem ser removidos.
Remover índice:
DROP INDEX nomeindice;
Criar um índice Único:
CREATE UNIQUE INDEX nomeindice ON tabela (campo);
Obs.: Somente os índices tipo B-tree podem ser do tipo Unique.
Criar um índice com várias colunas:
CREATE INDEX idx_clientes_ps ON clientes (codigo, nome);
Boa indicação para consultas com WHERE...AND. Ao usar OR o índice não será utilizado pelo PostgreSQL:
SELECT nome FROM clientes WHERE codigo = 12 AND nome = 'João';
Usar índices com várias colunas com moderação. Índices com mais de 3 colunas tem grandes possibilidades de não serem utilizados internamente.
Tipos de Índices
O PostgreSQL suporta atualmente quatro tipos de índices: B-tree (árvore B), R-tree (árvore R), Hash e GiST.
B-tree -> é o tipo padrão (assume quando não indicamos). São índices que podem tratar consultas de igualdade e de faixa, em dados que podem ser classificados.
Indicado para consultas com os operadores: <, <=, =, >=, >. Também pode ser utilizado com LIKE, ILIKE, ~ e ~*.
R-tree -> tipo mais adequado a dados espaciais. Adequado para consultas com os operadores: <<, &<, &>, >>, @, ~=, &&.
Hash ->indicados para consultas com comparações de igualdade simples. É desencorajado seu uso. Em seu lugar recomenda-se o B-tree.
GiST ->
Criando índices de tipos diferentes:
CREATE INDEX nome ON tabela USING tipo (campo);
tipo: BTREE, RTREE, HASH, GIST
Obs.: Somente os tipos B-tree e GiST suportam índices com várias colunas.
Índices com mais de um campo somente será utilizado se as cláusulas com os campos indexados forem ligados por AND.
Um índice com mais de 3 campos dificilmente será utilizado.
Índice Parcial
Criado apenas sobre um subconjunto dos registros de uma tabela, definido numa expressão durante a criação do índice parcial. É um recurso para melhorar o desempenho dos índices, já que atualiza somente parte dos registros.
Obs.: na maioria dos casos a vantagem de um índice parcial sobre um índice integral não é muita.
Exemplos:
Examinando a Utilização dos Índices
A verificação de uso de índices deve ser feita com os comandos EXPLAIN e ANALYZE, sendo que o comando ANALYZE sempre deve ser executado antes. O comando ANALYZE coleta estatísticas sobre a distribuição dos valores na tabela. Devem ser utilizados dados reais e o conjunto de dados de teste nunca deve ser pequeno.
Atentar para usar índices nos campos das Cláusulas
- FOREIGN KEY
- ORDER BY
- WHERE
- ON
- GROUP BY
- HAVING
Exemplos prático da vantagem do Índice
- Uma tabela contendo os CEPs do Brasil, com 633.401 registros.
Esta tabela sem nenhum índice executa a consulta abaixo:
\timing
SELECT * FROM cep_tabela WHERE cep = ‘60420440’;
Em 7691 ms
- Pós adicionar um índice:
ALTER TABLE cep_tabela ADD CONSTRAINT cep_pk PRIMARY KEY (cep);
A mesma consulta anterior agora gasta apenas 10 ms.
Isso num AMD Duron 1300, 128MB de RAM).
Índice Funcional
CREATE INDEX nomeindice ON tabela (lower (nomecampo));
Ótimo artigo no iMasters
http://www.imasters.com.br/artigo.php?cn=1897&cc=23
http://www.imasters.com.br/artigo.php?cn=1922&cc=23
http://www.imasters.com.br/artigo.php?cn=1959&cc=23
Vide manual oficial, capítulo 11 para detalhes.
Boleanos
Representações:
't', 'true', 'y', 'yes' e '1'
FALSE
Representações:
'f', 'false', 'n', 'no', '0'
Apenas um dos dois estados. O terceiro estado, desconhecido, é representado pelo NULL.
Exemplo de consulta com boolean:
CREATE TABLE teste1 (a boolean, b text);
INSERT INTO teste1 VALUES (TRUE, 'sic est');
INSERT INTO teste1 VALUES (FALSE, 'non est');
SELECT * FROM teste1;
Retorno:
a | b
---+---------
t | sic est
f | non est
Alerta: a entrada pode ser: 1/0, t/f, true/false, TRUE/FALSE, mas o retorno será semp re t/f.
Obs.: Para campos tipo data que permitam NULL, devemos prever isso na consulta SQL e passar NULL sem delimitadores e valores não NULL com delimitadores.
Obs2: Evite o tipo MONEY que está em obsolescência. Em seu lugar use NUMERIC. Prefira INT (INTEGER) em lugar de INT4, pois os primeiros são padrão SQL. Em geral evitar os nomes INT2, INT4 e INT8, que não são padrão. O INT8 ou bigint não é padrão SQL. Em índices utilize somente INT, evitando smallint e bigint, que nunca serão utilizados.
Tipos SQL Padrão
bit, bit varying, boolean, char, character varying, character, varchar, date, double precision, integer, interval, numeric, decimal, real, smallint, time (com ou sem zona horária), timezone (com ou sem zona horária).
O tipo NUMERIC pode realizar cálculos exatos. Recomendado para quantias monetárias e outras quantidades onde a exatidão seja importante. Isso paga o preço de queda de desempenho comparado aos inteiros e flutuantes.
Pensando em portabilidade evita usar NUMERIC(12) e usar NUMERIC (12,0).
Alerta: A comparação de igualdade de dois valores de ponto flutuante pode funcionar conforme o esperado ou não.
O PostgreSQL trabalha com datas do calendário Juliano.
Trabalha com a faixa de meio dia de Janeiro de 4713 AC (ano bisexto, domingo de lua nova) até uma data bem distante no futuro. Leva em conta que o ano tem 365,2425 dias.
SERIAL
No PostgreSQL um campo criado do “tipo” SERIAL é internamente uma seqüência, inteiro positivo.
Os principais SGBDs utilizam alguma variação deste tipo de dados (auto-incremento). Serial é o “tipo” auto-incremento do PostgreSQL. Quando criamos um campo do tipo SERIAL ao inserir um novo registro na tabela com o comando INSERT omitimos o campo tipo SERIAL, pois ele será inserido automaticamente pelo PostgreSQL.
CREATE TABLE serial_teste (codigo SERIAL, nome VARCHAR(45));
INSERT INTO serial_teste (nome) VALUES ('Ribamar FS');
Obs.: A regra é nomear uma seqüência “serial_teste_codigo_seq”,ou seja,
tabela_campo_seq.
select * from serial_teste_codigo_seq;
Esta consulta acima retorna muitas informações importantes sobre a seqüência criada: nome, valor inicial, incremento, valor final, maior e menor valor além de outras informações.
Veja que foi omitido o campo código mas o PostgreSQL irá atribuir para o mesmo o valor do próximo registro de código. Por default o primeiro valor de um serial é 1, mas se precisarmos começar com um valor diferente veja a solução abaixo:
Setando o Valor Inicial do Serial
ALTER SEQUENCE tabela_campo_seq RESTART WITH 1000;
CHAR corresponde a CHAR(1).
VARCHAR corresponde a uma cadeia de tamanho sem limites.
Diferença de Desempenho
Internamente o PostgreSQL armazena em tabelas separados os valores longos, para não interferirem no acesso dos valores curtos da coluna. O maior valor permitido para uma cadeia de caracteres é de 1GB. Para valores maiores usar TEXT ou VARCHAR sem especificar comprimento.
Tipos de Dados Array
Podemos ter campos com tipos de dados que não são simples, mas arrays.
CREATE TABLE salario (
nome text,
apgamento integer[],
agendamento text[][]
);
CREATE TABLE tictactoe (
quadrado integer[3][3]
);
Entrando os valores:
'{{1,2,3},{4,5,6},{7,8,9}}'
INSERT INTO sal_emp
VALUES ('Bill',
'{10000, 10000, 10000, 10000}',
'{{"meeting", "lunch"}, {"meeting"}}');
ERROR: multidimensional arrays must have array expressions with matching dimensions
Precisa ter a mesma quantidade de elementos.
INSERT INTO sal_emp
VALUES ('Bill',
'{10000, 10000, 10000, 10000}',
'{{"meeting", "lunch"}, {"training", "presentation"}}');
INSERT INTO sal_emp
VALUES ('Carol',
'{20000, 25000, 25000, 25000}',
'{{"breakfast", "consulting"}, {"meeting", "lunch"}}');
SELECT * FROM sal_emp;
name | pay_by_quarter | schedule
+---------------------------+-------------------------------------------
Bill | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(2 rows)
O construtor ARRAY também pode ser usado:
INSERT INTO sal_emp
VALUES ('Bill',
ARRAY[10000, 10000, 10000, 10000],
ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);
INSERT INTO sal_emp
VALUES ('Carol',
ARRAY[20000, 25000, 25000, 25000],
ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);
Acessando:
SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
SELECT pay_by_quarter[3] FROM sal_emp;
Faixa de valores- inferior:superior:
SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';
SELECT array_dims(ARRAY[1,2] || 3);
SELECT array_prepend(1, ARRAY[2,3]);
SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
SELECT 1 || ARRAY[2,3] AS array;
SELECT ARRAY[1,2] || ARRAY3,4 AS array;
SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;