As traduções são geradas por tradução automática. Em caso de conflito entre o conteúdo da tradução e da versão original em inglês, a versão em inglês prevalecerá. # Recriação de índices O comando [REINDEX](https://www.postgresql.org/docs/current/sql-reindex.html) 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 CREATE INDEX ON TABLE ([,]) ``` 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 ; ``` **Recriação de um índice** Para recriar um único índice: ``` reindex index ; ``` Para recriar todos os índices em uma tabela: ``` reindex table ; ``` Para recriar todos os índices em um esquema: ``` reindex schema ; ``` **Recriação de um índice simultaneamente** Para recriar um único índice: ``` reindex index CONCURRENTLY ; ``` Para recriar todos os índices em uma tabela: ``` reindex table CONCURRENTLY ; ``` Para recriar todos os índices em um esquema: ``` reindex schema CONCURRENTLY ; ``` **Recriação ou realocamento somente de índices** Para recriar um único índice: ``` pg_repack -h -d -i -k ``` Para recriar todos os índices: ``` pg_repack -h -d -x -t -k ```