Las traducciones son generadas a través de traducción automática. En caso de conflicto entre la traducción y la version original de inglés, prevalecerá la version en inglés.
# Nueva creación de índices
El comando [REINDEX](https://www.postgresql.org/docs/current/sql-reindex.html) de PostgreSQL vuelve a crear un índice utilizando los datos que hay almacenados en la tabla del índice y reemplazando la copia antigua del índice. Le recomendamos utilizar `REINDEX` en las siguientes situaciones:
+ Cuando un índice se dañe y deje de contener datos válidos. Esto puede ocurrir a consecuencia de errores de software o hardware.
+ Cuando las consultas que anteriormente utilizaban el índice dejen de usarlo.
+ Cuando el índice se sobrecargue con un gran número de páginas vacías o casi vacías. Debe ejecutar `REINDEX` cuando el porcentaje de sobrecarga (`bloat_pct`) sea superior a 20.
La siguiente consulta lo ayuda 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;
```
Las páginas de índice que están completamente vacías se recuperan para su reutilización. Sin embargo, recomendamos realizar una nueva indexación periódicamente si se han eliminado las claves de índice de una página pero se conserva el espacio asignado.
Volver a crear el índice ayuda a mejorar el rendimiento de las consultas. Puede volver a crear un índice de tres maneras, tal y como se describe en la siguiente tabla.
| | | |
| --- |--- |--- |
| **Método** | **Descripción** | **Limitaciones** |
| `CREATE INDEX` y `DROP INDEX` con la opción `CONCURRENTLY` | Crea un índice nuevo y elimina el antiguo. El optimizador genera planes utilizando el índice recién creado en lugar del índice antiguo. Durante las horas de menor actividad, puede eliminar el índice antiguo. | La creación del índice lleva más tiempo cuando se utiliza la opción `CONCURRENTLY`, ya que tiene que hacer un seguimiento de todos los cambios entrantes. Cuando los cambios se congelan, el proceso se marca como completo. |
| `REINDEX` con la opción `CONCURRENTLY` | Bloquea las operaciones de escritura durante el nuevo proceso de creación. La versión 12** **y las versiones posteriores de PostgreSQL ofrecen la opción `CONCURRENTLY`, lo que evita estos bloqueos. | El uso de `CONCURRENTLY` requiere más tiempo para volver a crear el índice. |
| Extensión `pg_repack` | Limpia la sobrecarga de una tabla y vuelve a crear el índice. | Debe ejecutar esta extensión desde una instancia de EC2 o desde una computadora local que esté conectada a la base de datos. |
## Creación de un nuevo índice
Cuando los comandos `DROP INDEX` y `CREATE INDEX` se utilizan juntos, vuelven a crear un índice:
```
DROP INDEX
CREATE INDEX ON TABLE ([,])
```
La desventaja de este enfoque es el bloqueo de exclusividad que se aplica a la tabla, lo que afecta al rendimiento durante esta actividad. El comando `DROP INDEX` adquiere un bloqueo de exclusividad que bloquea las operaciones de lectura y de escritura en la tabla. El comando `CREATE INDEX` bloquea las operaciones de escritura en la tabla. Permite operaciones de lectura, pero tienen un costo elevado durante la creación del índice.
## Nueva creación de un índice
El comando `REINDEX` lo ayuda a mantener un rendimiento uniforme de la base de datos. Al realizar un gran número de operaciones con DML en una tabla, se produce una sobrecarga tanto de la tabla como de los índices. Los índices se utilizan para acelerar las búsquedas en las tablas y mejorar el rendimiento de las consultas. La sobrecarga de los índices afecta al rendimiento de las búsquedas y de las consultas. Por lo tanto, es recomendable volver a indexar las tablas que tienen un gran volumen de operaciones con DML para mantener la coherencia en el rendimiento de las consultas.
El comando `REINDEX` bloquea las operaciones de escritura en la tabla subyacente para volver a crear el índice desde cero, pero permite las operaciones de lectura en la tabla. Sin embargo, sí bloquea las operaciones de lectura en el índice. Las consultas que utilizan el índice correspondiente están bloqueadas, pero las demás no.
La versión 12 de PostgreSQL introdujo un nuevo parámetro opcional, `CONCURRENTLY`, que vuelve a crear el índice desde cero, pero no bloquea las operaciones de lectura ni escritura en la tabla ni en las consultas que utilizan el índice. Sin embargo, se tarda más en completar el proceso cuando se usa esta opción.
## Ejemplos
**Creación y eliminación de un índice**
Cree un índice nuevo con la opción `CONCURRENTLY`:
```
create index CONCURRENTLY on table(columns) ;
```
Elimine el índice antiguo con la opción `CONCURRENTLY`:
```
drop index CONCURRENTLY ;
```
**Nueva creación de un índice**
Para volver a crear un índice único:
```
reindex index ;
```
Para volver a crear todos los índices de una tabla:
```
reindex table ;
```
Para volver a crear todos los índices de un esquema:
```
reindex schema ;
```
**Nueva creación de un índice de forma simultánea**
Para volver a crear un índice único:
```
reindex index CONCURRENTLY ;
```
Para volver a crear todos los índices de una tabla:
```
reindex table CONCURRENTLY ;
```
Para volver a crear todos los índices de un esquema:
```
reindex schema CONCURRENTLY ;
```
**Nueva creación o reubicación únicamente de índices**
Para volver a crear un índice único:
```
pg_repack -h -d -i -k
```
Para volver a crear todos los índices:
```
pg_repack -h -d -x -t -k
```