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%';