O Comando SHOW

O PostgreSQL tem dúzias de variáveis de configuração que tem influência no comportamento do banco, em sua segurança e no desempenho. Acompanhar estes parâmetros de forma simples permite melhor controle do funcionamento do servidor.

O Comando SHOW é uma extensão do PostgreSQL que facilita este acompanhamento. Este comando permite que se visualize todos os valores das variáveis de configuração do seu banco de dados. O autovacuum está ativado? Qual é o estilo de data que está sendo utilizado no momento? Perguntas como essa são facilmente respondidas pela utilização deste comando.

Problemas conhecidos:
- Este comando não permite a cláusula WHERE. Desta forma recuperamos todas as dezenas de parâmetros ou apenas parâmetros específicos, um de cada vez.
- O comando não aceita recuperar uma lista de parâmetros definida pelo usuário (SHOW par1, par2,..., parN).
- Testes com os comandos SELECT, UNION e SELECT INTO não funcionaram, isto é, não é possível utilizar o SHOW com estes comandos.

Sintaxe:

SHOW name
SHOW ALL

Exemplos:

1 - Mostrando todos os parâmetros de configuração

SHOW ALL;

2 - Mostrando parâmetros específicos

SHOW array_nulls;
SHOW autovacuum;

O Comando ALTER INDEX

Este comando altera um índice, podendo mudar o nome, o tablespace e redefinir parâmetros do mesmo.

Dependendo da sintaxe, pode demandar um comando REINDEX para efetivar a alteração.

O tipo de índice (btree, hash, etc.) não pode ser alterado através do comando ALTER INDEX, assim como os campos indexados e outras características. Neste caso, o objeto índice deve ser excluído e recriado.

Sintaxe:

ALTER INDEX name RENAME TO new_name
ALTER INDEX name SET TABLESPACE tablespace_name
ALTER INDEX name SET ( storage_parameter = value [, ... ] )
ALTER INDEX name RESET ( storage_parameter [, ... ] )

Exemplo:

0 - Índices utilizados no teste.

create index tstdel_ind on tstdel (cod);
create index tstdel_ind_date on tstdel (date);
create index tstdel_ind_coddate on tstdel (cod, date);

1 - Alteração de nome.

ALTER INDEX tstdel_ind_coddate RENAME TO tstdel_ind_coddata;
ALTER INDEX tstdel_ind_date RENAME TO tstdel_ind_data;

2 - Alteração de tablespace.

ALTER INDEX tstdel_ind SET TABLESPACE pg_default;

3 - Alteração de parâmetro de armazenamento, seguida por um REINDEX.

ALTER INDEX tstdel_ind SET ( fillfactor = 80 );
REINDEX INDEX tstdel_ind;

4 - RESET de alterações de parâmetro de armazenamento, seguido por um REINDEX.

ALTER INDEX tstdel_ind RESET (fillfactor);
REINDEX INDEX tstdel_ind;

Aberta a chamada de trabalhos para o PG Con Brasil!!!

Está aberta a Chamada de Trabalhos da PgCon Brasil 2007.
Mais informações no site do PostgreSQL.ORG.BR ou no e-mail que aparece abaixo:

=============================================

Já estão abertas as inscrições para a apresentação de trabalhos da comunidade no PostgreSQL Conference Brasil (PGCon Brasil). Os interessados devem enviar um e-mail para trabalhos_pgcon@postgresql.org.br contendo os dados a seguir:

* Título da palestra (até 50 toques)
* Assunto - Selecionar a partir da seguinte lista:
o Casos de Sucesso;
o Melhores práticas (configuração, organização, segurança, etc);
o Ajustes de desempenho (tuning);
o Administração de servidores PostgreSQL;
o Técnicas de migração para PostgreSQL;
o Funcionalidades específicas do PostgreSQL
* Resumo da palestra (até 250 toques)
* Principais tópicos abordados (até 250 toques)
* Palestrante(s) com mini currículo (até 250 toques por palestrante)

Observações:

* Os trabalhos devem ser enviados até 21/09/2007 e serão avaliados por uma comissão julgadora de desenvolvedores de PostgreSQL que escolherão até 8 trabalhos a serem expostos durante o PGCon Brasil.
* Os trabalhos que não forem enviados até 21/09/2007 não serão avaliados pela comissão e não participarão do PGCon Brasil.
* Será disponibilizado um projetor multimídia e um computador com uma distribuição linux padrão instalada. Qualquer recurso adicional necessário para a apresentação ficará sob inteira responsabilidade do palestrante.
* Serão selecionados trabalhos para ficarem em lista de espera em caso de desistência de algum trabalho selecionado.
* Os trabalhos terão duração máxima de 50 minutos incluindo o tempo para exposição e perguntas.

Meu Curso de PostgreSQL

Semana passada aconteceu a primeira turma do "Meu Curso de PostgreSQL".
Foi um desafio muito divertido no qual aprendi bastante.
Agradeço a presença e paciência dos alunos.

O Comando Drop Index

Remove um índice do seu banco de dados. Para executar o comando Drop Index, o usuário deve ser owner do objeto.

A remoção de índices relacionados com constraints como as chaves primárias vai causar erro. Neste caso deve ser empregado o comando ALTER TABLE DROP CONSTRAINT .

Sintaxe:

DROP INDEX [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

A cláusula IF EXISTS restringe a emissão de mensagens de erro caso os índices a serem excluídos não existam. O padrão é a emissão de erro ao se tentar excluir um índice inexistente.

CASCADE - Remove objetos que dependam do índice.

RESTRICT - Faz com que o índice não seja eliminado se existirem objetos filhos. é a opção padrão do PostgreSQL.

Exemplos:

1 - Exclusão de índice com IF EXISTS.

DROP INDEX IF EXISTS pais_cont_pkey_9;

O erro é convertido em NOTICE:

NOTICE: index "pais_cont_pkey_9" does not exist, skipping
Query result with 29 rows discarded.
Query returned successfully with no result in 16 ms.

2 - Exclusão de índice ind4.

DROP INDEX ind4;

PgCon 2007 - Palestras Confirmadas!

Antes mesmo do site da PgCon Brasil 2007 ser lançado, adiantamos aqui a lista com palestrantes confirmados. Boas novidades nacionais e Internacionais! Claro que pode acontecer algum imprevisto até lá, mas o evento promete palestras de peso e grandes encontros.

Josh Berkus - Desenvolvedor do time central do PostgreSQL;
Conheça o palestrante através desta entrevista:
Josh Berkus

Bruce Momjian - Desenvolvedor do time central do PostgreSQL;
O site da criança é simplesmente impressionante:
Bruce Momjian

Euler Taveira - Desenvolvedor nacional
Quem não o conhecia, passou a conhecer depois desta entrevista:
Euler Taveira

Diogo Biazus - Desenvolvedor nacional
Além de ser um desenvolvedor, ele é o contato regional do PostgreSQL.org no Brasil. É o homem que sabe das notícias antes da gente!

Em breve será divulgada a chamada de trabalhos. O prazo não deve ser tão generoso, então aconselho que todos os interessados comecem com antecedência suas pesquisas e preparação de material.

Comentários Sobre Indexação...

Seguem respostas, ao menos as que posso dar hoje, aos comentários do Telles. Você também pode postar os seus!

"Opa, muito bacana esta série sobre índices... este é um assunto que dá pano para manga! Algumas sugestões para explorar mais o
* Seria interessante fazer um EXPLAIN num SELECT de uma grande tabela para comparar o ganho de velocidade e volume em disco de diferentes valores de FILLFACTOR;"

Com certeza esse seria um bom teste a ser feito. Bom, fica na minha fila de coisas a fazer. Como esta lista cresce rápido...

"* Exemplificar o uso da cláusula CONCURRENTLY;"

A cláusula CONCURRENTLY aparece em um dos nossos exemplos, no entanto o texto explicando seu funcionamento pode ser melhorado.

O uso de CONCURRENTLY faz com que o banco de dados ignore que se está fazendo inserções, atualizações e exclusões durante a sua criação, fazendo com que a construção do índice seja mais rápida. No entanto impede que a criação do índice seja feita dentro de uma transação e o índice criado pode não estar 100% correto, em virtude das alterações concorrentes relaizadas durante sua criaçao, demandando um comando REINDEX.

Só utilize esta cláusula se lidares com grandes massas de dados em um sistema em que a performance seja crítica.

"* Apontar quando a utilização de um determinado índice é melhor (B-TREE, HASH, GIST e GIN);"

Nossa, devia ter comentado isso mesmo!
No PostgreSQL, a indexação pode utilizar recursos que reduzam a portabilidade. Caso o mesmo esquema de banco seja utilizado em vários outros SGBDs devem ser ignorados os índices GIST e GIN. Os índices HASH e BTREE têm implementação mais difundida em outras plataformas.

Sugestão:

BTREE - utilizar como padrão. Flexível e com bom custo benefício.

HASH - Utilizar apenas quando as consultas utilizando o operador de igualdade como condicional "=" estiverem lentas, caso não se precise logar a operação, pois operações com HASH não são armazenadas no log do PostgreSQL. O usuário deve lembrar que a reconstrução do índice em caso de falha deve ser disparada manualmente.
A rapidez do HASH é maior que a da árvore B, mas o memso demanda mais manutenção.

GIST - É um "índice programável" para tipos definidos pelo usuário. Não usar caso se deseje compatibilidade com outro banco. Também ainda é um recurso pouco empregado da ferramenta, a não ser que você instale algum módulo extra, que pode utilizá-lo sem que sequer saibamos.
Não consegui fazer funcionar nos meus testes. Acho que é questão de tempo. Aí eu faço um post só para o GIST.

GIN - É um índice muito recentemente adicionado ao banco, voltado para para tipos de dados em que uma chave ocorra mais de uma vez no banco de dados. Não usar caso se deseje compatibilidade com outro SGBD. Também ainda é um recurso pouco empregado da ferramenta, ainda mais por ser relativamente recente.
Achei difícil de utilizar e não consegui fazer funcionar nos meus testes. Acho que é questão de tempo. Aí eu faço um post só para o GIN. Alguém já está usando GIN?

ÍNDICES PARCIAIS - Fáceis de usar e úteis para otimizar certas consultas. Não usar caso se deseje manter compatibilidade para outros bancos.

"* Como criar índices que retornem a correta ordenação para pt_BR utilizando o OPERATOR CLASS;"

Ainda estou estudando isso. Está na minha lista, mas bem atrás.
Alguém conhece algum texto bom a respeito?

"Além disso, seria interessante utilizar uma nomeação para os índices mais consistentes, criar nomes como ind1, ind2 e ind3 não costuma ser muito recomendado."

MEA CULPA!!! Para os próximos exemplos pretendo utilizar NOMEOBJETOINDEXADO_SUFIXO. Minha dúvida é se compensa rever o post e alterar os exemplos...
Alguém sugere uma nomenclatura alternativa?

"Fica aí o desafio... o que você acha? Por último deixo aqui a recomendação de uma apresentação interesante do Sr. Josh Berkus chamada The Joy Of Index que é bem interessante."

Esse texto é realmente MUITO instrutivo.
Nele são detalhados alguns importantes sobre a indexação do PostgreSQL.
Com certeza vou reler mais de uma vez!

O Comando CREATE INDEX

Este comando cria índices relativos aos dados de uma tabela do PostgreSQL. O bom uso dos índices aumenta bastante o desempenho do acesso a dados, daí a importância do comando.

São objetos que ocupam espaço em disco e podem afetar negativamente o desempenho se mal empregados e em virtude disto devem ser devidamente empregados, gerenciados e mantidos. O comando REINDEX pode ser utilizado para reorganizar os índices mais defasados.

Sintaxe:

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ] ( { column | ( expression ) } [ opclass ] [, ...] ) [ WITH ( storage_parameter = value [, ... ] ) ] [ TABLESPACE tablespace ] [ WHERE predicate ]

- UNIQUE - Aceitação ou não de valores repetidos. O padrão é aceitar valores repetidos em um arquivo indexado, mas o uso de UNIQUE gera um erro nesta situação.

- CONCURRENTLY - O uso desta cláusula faz com que não sejam feitos bloqueios à inserção, exclusão e alteração de dados durante a criação do índice. O padrão para a criação de índices é o bloqueio destas operações visando garantir que o índice e seus dados estejam perfeitamente sincronizados, o que pode retardar a criação dos objetos. O comando CREATE INDEX pode ser utilizado em transações apenas se for empregado sem a cláusula CONCURRENTLY.

- Método de indexação - Tipo de índice. Pode ser btree, hash, gist ou gin. O padrão é a árvore B.

- O campo com os valores indexados pode ser na verdade uma expressão calculada utilizando-se uma ou mais colunas da tabela.

- Para cada coluna utilizada no índice deve ser fornecido OPERATOR CLASS, que é o operador a ser utilizado para cada tipo de dado. Normalmente o valor padrão para cada tipo de dado resolve a questão e não se precisa fornecer o OPERATOR CLASS.

- storage_parameter - Atualmente o storage parameter definido para índices é o FILLFACTOR. É uma variável que indica o quanto as folhas da árvore de índice serão utilizadas. Varia de 10 a 100 e seu valor padrão é 90. O valor 100 minimiza o gasto com armazenamento físico do índice, mas pode afetar negativamente o desempenho. O valor 10 privilegia o desempenho, mas requer maior espaço físico para a gravação do índice.

- Cláusula WHERE indicando se o índice é relativo a toda a tabela ou apenas a parte dela (índice parcial).

- Em que tablespace o índice será fisicamente armazenado

Exemplos:

1 – Criação de índice UNIQUE, isto é, sem valores repetidos. Tabela continente, campo nome, nome do índice ind1.

CREATE UNIQUE INDEX ind1 ON continente (nome);

2 – Criação de índice sem UNIQUE, isto é, com valores repetidos e com campo calculado. Ao invés de indexar o campo nome, indexará os valores do campo nome convertidos para minúsculas..

CREATE INDEX ind2 ON continente ((lower(nome)));

3 – Criação de índice UNIQUE com o storage_parameter FILLFACTOR apresentando valor 45.

CREATE UNIQUE INDEX ind3 ON continente (population) WITH (fillfactor = 45);

4 – Criação de índice em determinado tablespace.

CREATE INDEX ind4 ON continente(population) TABLESPACE tspteste;

5 – Criação de índice utilizando a cláusula CONCURRENTLY, com ganho de desempenho.

CREATE INDEX CONCURRENTLY ind5 ON continente (population);

6 – Definição de índice HASH.

CREATE INDEX ind6 ON continente USING hash (nome);

7 – Definição de índice com árvore B.

CREATE UNIQUE INDEX ind7 ON continente USING btree (nome) ;

8 – Definição de índice com UNIQUE e expressão no campo indexado.

CREATE UNIQUE INDEX indexp1 ON continente ((upper(nome)));;

9 – Definição de índice com UNIQUE, BTREE e expressão no campo indexado.

CREATE UNIQUE INDEX indexp2 ON continente USING btree ((cod * 100));

10 – Definição de índice com UNIQUE, BTREE e expressão no campo indexado.

CREATE UNIQUE INDEX indexp3 ON continente USING btree ((cod + tamanho));

11 – Definição de índice parcial, empregando cláusula WHERE em campo numérico.

CREATE UNIQUE INDEX ind9 ON continente (population) WHERE population >10;

12 – Definição de índice parcial, empregando cláusula WHERE em campo alfanumérico.

CREATE UNIQUE INDEX ind10 ON continente (nome) WHERE nome like 'TEC%';