Criação de um novo índice Recriação de um índice Exemplos

Recriação de índices

O comando REINDEX do PostgreSQL recria um índice usando os dados armazenados na tabela do índice e substituindo a cópia antiga do índice. Recomendamos que use REINDEX nos seguintes cenários:

Quando um índice está corrompido e não contém mais dados válidos. Isso pode acontecer como resultado de falhas de software ou hardware.
Quando as consultas que anteriormente usavam o índice param de usá-lo.
Quando o índice fica inchado com um grande número de páginas vazias ou praticamente vazias. Você deve executar REINDEX quando a porcentagem de inchaço (bloat_pct) for maior que 20.

A consulta a seguir ajuda você a encontrar bloat_pct:


SELECT current_database(), nspname AS schemaname, tblname, idxname, bs*(relpages)::bigint AS real_size,
  bs*(relpages-est_pages)::bigint AS extra_size,
  100 * (relpages-est_pages)::float / relpages AS extra_pct,
  fillfactor,
  CASE WHEN relpages > est_pages_ff
    THEN bs*(relpages-est_pages_ff)
    ELSE 0
  END AS bloat_size,
  100 * (relpages-est_pages_ff)::float / relpages AS bloat_pct,
  is_na
  -- , 100-(pst).avg_leaf_density AS pst_avg_bloat, est_pages, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, reltuples, relpages -- (DEBUG INFO)
FROM (
  SELECT coalesce(1 +
         ceil(reltuples/floor((bs-pageopqdata-pagehdr)/(4+nulldatahdrwidth)::float)), 0 -- ItemIdData size + computed avg size of a tuple (nulldatahdrwidth)
      ) AS est_pages,
      coalesce(1 +
         ceil(reltuples/floor((bs-pageopqdata-pagehdr)*fillfactor/(100*(4+nulldatahdrwidth)::float))), 0
      ) AS est_pages_ff,
      bs, nspname, tblname, idxname, relpages, fillfactor, is_na
      -- , pgstatindex(idxoid) AS pst, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, reltuples -- (DEBUG INFO)
  FROM (
      SELECT maxalign, bs, nspname, tblname, idxname, reltuples, relpages, idxoid, fillfactor,
            ( index_tuple_hdr_bm +
                maxalign - CASE -- Add padding to the index tuple header to align on MAXALIGN
                  WHEN index_tuple_hdr_bm%maxalign = 0 THEN maxalign
                  ELSE index_tuple_hdr_bm%maxalign
                END
              + nulldatawidth + maxalign - CASE -- Add padding to the data to align on MAXALIGN
                  WHEN nulldatawidth = 0 THEN 0
                  WHEN nulldatawidth::integer%maxalign = 0 THEN maxalign
                  ELSE nulldatawidth::integer%maxalign
                END
            )::numeric AS nulldatahdrwidth, pagehdr, pageopqdata, is_na
            -- , index_tuple_hdr_bm, nulldatawidth -- (DEBUG INFO)
      FROM (
          SELECT n.nspname, i.tblname, i.idxname, i.reltuples, i.relpages,
              i.idxoid, i.fillfactor, current_setting('block_size')::numeric AS bs,
              CASE -- MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?)
                WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN 8
                ELSE 4
              END AS maxalign,
              /* per page header, fixed size: 20 for 7.X, 24 for others */
              24 AS pagehdr,
              /* per page btree opaque data */
              16 AS pageopqdata,
              /* per tuple header: add IndexAttributeBitMapData if some cols are null-able */
              CASE WHEN max(coalesce(s.null_frac,0)) = 0
                  THEN 8 -- IndexTupleData size
                  ELSE 8 + (( 32 + 8 - 1 ) / 8) -- IndexTupleData size + IndexAttributeBitMapData size ( max num filed per index + 8 - 1 /8)
              END AS index_tuple_hdr_bm,
              /* data len: we remove null values save space using it fractionnal part from stats */
              sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024)) AS nulldatawidth,
              max( CASE WHEN i.atttypid = 'pg_catalog.name'::regtype THEN 1 ELSE 0 END ) > 0 AS is_na
          FROM (
              SELECT ct.relname AS tblname, ct.relnamespace, ic.idxname, ic.attpos, ic.indkey, ic.indkey[ic.attpos], ic.reltuples, ic.relpages, ic.tbloid, ic.idxoid, ic.fillfactor,
                  coalesce(a1.attnum, a2.attnum) AS attnum, coalesce(a1.attname, a2.attname) AS attname, coalesce(a1.atttypid, a2.atttypid) AS atttypid,
                  CASE WHEN a1.attnum IS NULL
                  THEN ic.idxname
                  ELSE ct.relname
                  END AS attrelname
              FROM (
                  SELECT idxname, reltuples, relpages, tbloid, idxoid, fillfactor, indkey,
                      pg_catalog.generate_series(1,indnatts) AS attpos
                  FROM (
                      SELECT ci.relname AS idxname, ci.reltuples, ci.relpages, i.indrelid AS tbloid,
                          i.indexrelid AS idxoid,
                          coalesce(substring(
                              array_to_string(ci.reloptions, ' ')
                              from 'fillfactor=([0-9]+)')::smallint, 90) AS fillfactor,
                          i.indnatts,
                          pg_catalog.string_to_array(pg_catalog.textin(
                              pg_catalog.int2vectorout(i.indkey)),' ')::int[] AS indkey
                      FROM pg_catalog.pg_index i
                      JOIN pg_catalog.pg_class ci ON ci.oid = i.indexrelid
                      WHERE ci.relam=(SELECT oid FROM pg_am WHERE amname = 'btree')
                      AND ci.relpages > 0
                  ) AS idx_data
              ) AS ic
              JOIN pg_catalog.pg_class ct ON ct.oid = ic.tbloid
              LEFT JOIN pg_catalog.pg_attribute a1 ON
                  ic.indkey[ic.attpos] <> 0
                  AND a1.attrelid = ic.tbloid
                  AND a1.attnum = ic.indkey[ic.attpos]
              LEFT JOIN pg_catalog.pg_attribute a2 ON
                  ic.indkey[ic.attpos] = 0
                  AND a2.attrelid = ic.idxoid
                  AND a2.attnum = ic.attpos
            ) i
            JOIN pg_catalog.pg_namespace n ON n.oid = i.relnamespace
            JOIN pg_catalog.pg_stats s ON s.schemaname = n.nspname
                                      AND s.tablename = i.attrelname
                                      AND s.attname = i.attname
            GROUP BY 1,2,3,4,5,6,7,8,9,10,11
      ) AS rows_data_stats
  ) AS rows_hdr_pdg_stats
) AS relation_stats
ORDER BY nspname, tblname, idxname;

As páginas de índice que estão completamente vazias são recuperadas para reutilização. No entanto, recomendamos a reindexação periódica se as chaves de índice em uma página tiverem sido excluídas, mas o espaço permanecer alocado.

A recriação do índice ajuda a melhorar a performance da consulta. Você pode recriar um índice de três maneiras, conforme descrito na tabela a seguir.

Method (Método	Descrição	Limitações
`CREATE INDEX` e `DROP INDEX` com a opção `CONCURRENTLY`	Cria um novo índice e remove o índice antigo. O otimizador gera planos usando o índice recém-criado em vez do índice antigo. Durante os horários de baixo pico, você pode descartar o índice antigo.	A criação do índice leva mais tempo quando você usa a opção `CONCURRENTLY`, pois ela precisa rastrear todas as alterações recebidas. Quando as alterações são congeladas, o processo é marcado como concluído.
`REINDEX` com a opção `CONCURRENTLY`	Bloqueia as operações de gravação durante o processo de recriação. A versão 12 do PostgreSQL e as versões posteriores oferecem a opção `CONCURRENTLY`, que evita esses bloqueios.	O uso de `CONCURRENTLY` requer mais tempo para recriar o índice.
Extensão do `pg_repack`	Limpa o inchaço de uma tabela e recria o índice.	Você deve executar essa extensão de uma instância do EC2 ou de um computador local conectado ao banco de dados.

Criação de um novo índice

Os comandos DROP INDEX e CREATE INDEX, quando usados juntos, recriam um índice:


DROP INDEX <index_name>
CREATE INDEX <index_name> ON TABLE <table_name> (<column1>[,<column2>])

A desvantagem dessa abordagem é seu bloqueio exclusivo na tabela, o que afeta a performance durante essa atividade. O comando DROP INDEX adquire um bloqueio exclusivo, que bloqueia as operações de leitura e gravação na tabela. O comando CREATE INDEX bloqueia as operações de gravação na tabela. Ele permite operações de leitura, mas elas são caras durante a criação do índice.

Recriação de um índice

O comando REINDEX ajuda você a manter a performance consistente do banco de dados. Quando você executa um grande número de operações de DML em uma tabela, elas resultam em inchaço da tabela e do índice. Os índices são usados para acelerar a pesquisa em tabelas e melhorar a performance da consulta. O inchaço do índice afeta as pesquisas e a performance das consultas. Portanto, recomendamos que você execute a reindexação em tabelas que tenham um alto volume de operações de DML para manter a consistência na performance das consultas.

O comando REINDEX recria o índice do zero bloqueando as operações de gravação na tabela subjacente, mas permite operações de leitura na tabela. No entanto, ele bloqueia as operações de leitura no índice. As consultas que usam o índice correspondente são bloqueadas, mas outras consultas não.

A versão 12 do PostgreSQL introduziu um novo parâmetro opcional, CONCURRENTLY, que recria o índice do zero, mas não bloqueia as operações de gravação ou leitura na tabela ou nas consultas que usam o índice. No entanto, leva mais tempo para concluir o processo quando você usa essa opção.

Exemplos

Criação e exclusão de um índice

Crie um novo índice com a opção CONCURRENTLY:


create index CONCURRENTLY on table(columns) ;

Exclua o índice antigo com a opção CONCURRENTLY:


drop index CONCURRENTLY <index name> ;

Recriação de um índice

Para recriar um único índice:


reindex index <index name> ;

Para recriar todos os índices em uma tabela:


reindex table <table name> ;

Para recriar todos os índices em um esquema:


reindex schema <schema name> ;

Recriação de um índice simultaneamente

Para recriar um único índice:


reindex index CONCURRENTLY <indexname> ;

Para recriar todos os índices em uma tabela:


reindex table CONCURRENTLY <tablename> ;

Para recriar todos os índices em um esquema:


reindex schema CONCURRENTLY <schemaname> ;

Recriação ou realocamento somente de índices

Para recriar um único índice:


pg_repack -h <hostname> -d <dbname> -i <indexname> -k

Para recriar todos os índices:


pg_repack -h <hostname> -d <dbname> -x <indexname> -t <tablename> -k

Atenção O Javascript está desativado ou não está disponível no seu navegador.

Para usar a documentação da AWS, o Javascript deve estar ativado. Consulte as páginas de Ajuda do navegador para obter instruções.

Convenções do documento

Remoção do inchaço com pg_repack

Exemplo: recuperação de espaço usando autovacuum e VACUUM FULL