Crear un índice nuevo Reconstruir un índice Ejemplos

Reconstrucción de índices

El comando REINDEX de PostgreSQL reconstruye un índice utilizando los datos almacenados en la tabla del índice y reemplazando la copia anterior del índice. Le recomendamos que lo utilice REINDEX en los siguientes escenarios:

Cuando un índice se daña y ya no contiene datos válidos. Esto puede ocurrir como resultado de errores de software o hardware.
Cuando las consultas que anteriormente utilizaban el índice dejan de usarlo.
Cuando el índice se llena con un gran número de páginas vacías o casi vacías. Se debe ejecutar REINDEX cuando el porcentaje de hinchazón (bloat_pct) sea superior a 20.

La siguiente consulta le ayuda a encontrarbloat_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 volver a indexarlas periódicamente si se han eliminado las claves de índice de una página pero se mantiene 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 índice anterior. El optimizador genera planes utilizando el índice recién creado en lugar del índice anterior. Durante las horas de menor actividad, puede eliminar el índice anterior.	La creación del índice lleva más tiempo cuando se utiliza `CONCURRENTLY` esta opción, ya que tiene que realizar un seguimiento de todos los cambios entrantes. Cuando los cambios están bloqueados, el proceso se marca como completado.
`REINDEX`con la `CONCURRENTLY` opción	Bloquea las operaciones de escritura durante el proceso de reconstrucción. La versión 12 y las versiones posteriores de PostgreSQL ofrecen esta opción, lo que `CONCURRENTLY` evita estos bloqueos.	El uso `CONCURRENTLY` requiere más tiempo para reconstruir el índice.
`pg_repack`extensión	Limpia la hinchazón de una tabla y reconstruye el índice.	Debe ejecutar esta extensión desde una EC2 instancia o desde un equipo local que esté conectado a la base de datos.

Crear un índice nuevo

Los CREATE INDEX comandos DROP INDEX y, cuando se utilizan juntos, reconstruyen un índice:


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

La desventaja de este enfoque es su exclusividad, lo que afecta al rendimiento durante esta actividad. El DROP INDEX comando adquiere un bloqueo exclusivo, que bloquea las operaciones de lectura y escritura en la tabla. El CREATE INDEX comando bloquea las operaciones de escritura en la tabla. Permite operaciones de lectura, pero son caras durante la creación del índice.

Reconstruir un índice

El REINDEX comando le ayuda a mantener un rendimiento uniforme de la base de datos. Al realizar un gran número de operaciones de DML en una tabla, se produce una saturación 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 saturación de los índices afecta al rendimiento de las búsquedas y las consultas. Por lo tanto, se recomienda volver a indexar las tablas que tienen un gran volumen de operaciones de DML para mantener la coherencia en el rendimiento de las consultas.

El REINDEX comando reconstruye el índice desde cero al bloquear las operaciones de escritura en la tabla subyacente, pero permite las operaciones de lectura en la tabla. Sin embargo, bloquea las operaciones de lectura en el índice. Las consultas que utilizan el índice correspondiente se bloquean, pero las demás consultas no.

La versión 12 de PostgreSQL introdujo un nuevo parámetro opcional que reconstruye el índice desde ceroCONCURRENTLY, pero no bloquea las operaciones de escritura o lectura en la tabla ni en las consultas que utilizan el índice. Sin embargo, se tarda más tiempo en completar el proceso cuando se utiliza esta opción.

Ejemplos

Crear y eliminar un índice

Cree un índice nuevo con la CONCURRENTLY opción:


create index CONCURRENTLY on table(columns) ;

Elimine el índice anterior con la CONCURRENTLY opción:


drop index CONCURRENTLY <index name> ;

Reconstruir un índice

Para reconstruir un índice único:


reindex index <index name> ;

Para volver a generar todos los índices de una tabla:


reindex table <table name> ;

Para reconstruir todos los índices de un esquema:


reindex schema <schema name> ;

Reconstruir un índice simultáneamente

Para volver a crear un índice único:


reindex index CONCURRENTLY <indexname> ;

Para volver a generar todos los índices de una tabla:


reindex table CONCURRENTLY <tablename> ;

Para reconstruir todos los índices de un esquema:


reindex schema CONCURRENTLY <schemaname> ;

Reconstruir o reubicar índices únicamente

Para reconstruir un único índice:


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

Para reconstruir todos los índices:


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

Aviso JavaScript está desactivado o no está disponible en su navegador.

Para utilizar la documentación de AWS, debe estar habilitado JavaScript. Para obtener más información, consulte las páginas de ayuda de su navegador.

Convenciones del documento

Eliminar la hinchazón con pg_repack

Ejemplo: Recuperación de espacio mediante autovacuum y VACUUM FULL