Création d'un nouvel index Reconstruction d'un index Exemples

Reconstruction des index

La commande PostgreSQL REINDEX reconstruit un index en utilisant les données stockées dans la table de l'index et en remplaçant l'ancienne copie de l'index. Nous vous recommandons de l'utiliser REINDEX dans les scénarios suivants :

Lorsqu'un index est endommagé et ne contient plus de données valides. Cela peut se produire à la suite de défaillances logicielles ou matérielles.
Lorsque les requêtes qui utilisaient auparavant l'index cessent de l'utiliser.
Lorsque l'index est surchargé avec un grand nombre de pages vides ou presque vides. Vous devez courir REINDEX lorsque le pourcentage de ballonnement (bloat_pct) est supérieur à 20.

La requête suivante permet de trouver 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;

Les pages d'index complètement vides sont récupérées pour être réutilisées. Toutefois, nous recommandons une réindexation périodique si les clés d'index d'une page ont été supprimées mais que de l'espace reste alloué.

La recréation de l'index permet d'améliorer les performances des requêtes. Vous pouvez recréer un index de trois manières, comme décrit dans le tableau suivant.

Method	Description	Limites
`CREATE INDEX`et `DROP INDEX` avec l'`CONCURRENTLY`option	Construit un nouvel index et supprime l'ancien index. L'optimiseur génère des plans en utilisant le nouvel index créé au lieu de l'ancien index. Pendant les heures de pointe, vous pouvez supprimer l'ancien indice.	La création d'index prend plus de temps lorsque vous utilisez `CONCURRENTLY` cette option, car elle doit suivre toutes les modifications entrantes. Lorsque les modifications sont gelées, le processus est marqué comme terminé.
`REINDEX`avec l'`CONCURRENTLY`option	Verrouille les opérations d'écriture pendant le processus de reconstruction. La version 12 et les versions ultérieures de PostgreSQL fournissent cette option, qui permet d'`CONCURRENTLY`éviter ces verrous.	L'utilisation `CONCURRENTLY` nécessite plus de temps pour reconstruire l'index.
`pg_repack`extension	Nettoie le superflu d'une table et reconstruit l'index.	Vous devez exécuter cette extension depuis une EC2 instance ou depuis votre ordinateur local connecté à la base de données.

Création d'un nouvel index

Les CREATE INDEX commandes DROP INDEX et, lorsqu'elles sont utilisées ensemble, reconstruisent un index :


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

L'inconvénient de cette approche est qu'elle bloque exclusivement la table, ce qui a un impact sur les performances au cours de cette activité. La DROP INDEX commande acquiert un verrou exclusif qui bloque les opérations de lecture et d'écriture sur la table. La CREATE INDEX commande bloque les opérations d'écriture sur la table. Il permet des opérations de lecture, mais celles-ci sont coûteuses lors de la création d'index.

Reconstruction d'un index

La REINDEX commande vous aide à maintenir des performances de base de données cohérentes. Lorsque vous effectuez un grand nombre d'opérations DML sur une table, celles-ci entraînent une saturation de la table et de l'index. Les index sont utilisés pour accélérer la recherche dans les tables afin d'améliorer les performances des requêtes. Le gonflement de l'index affecte les recherches et les performances des requêtes. Par conséquent, nous vous recommandons de réindexer les tables comportant un volume élevé d'opérations DML afin de garantir la cohérence des performances des requêtes.

La REINDEX commande reconstruit l'index à partir de zéro en verrouillant les opérations d'écriture sur la table sous-jacente, mais elle autorise les opérations de lecture sur la table. Cependant, il bloque les opérations de lecture sur l'index. Les requêtes qui utilisent l'index correspondant sont bloquées, mais pas les autres requêtes.

La version 12 de PostgreSQL a introduit un nouveau paramètre facultatifCONCURRENTLY, qui reconstruit l'index à partir de zéro mais ne bloque pas les opérations d'écriture ou de lecture sur la table ou sur les requêtes utilisant l'index. Toutefois, l'exécution du processus prend plus de temps lorsque vous utilisez cette option.

Exemples

Création et suppression d'un index

Créez un nouvel index avec l'CONCURRENTLYoption suivante :


create index CONCURRENTLY on table(columns) ;

Supprimez l'ancien index avec l'CONCURRENTLYoption suivante :


drop index CONCURRENTLY <index name> ;

Reconstruction d'un index

Pour reconstruire un index unique :


reindex index <index name> ;

Pour reconstruire tous les index d'une table :


reindex table <table name> ;

Pour reconstruire tous les index d'un schéma :


reindex schema <schema name> ;

Reconstruction simultanée d'un index

Pour reconstruire un index unique :


reindex index CONCURRENTLY <indexname> ;

Pour reconstruire tous les index d'une table :


reindex table CONCURRENTLY <tablename> ;

Pour reconstruire tous les index d'un schéma :


reindex schema CONCURRENTLY <schemaname> ;

Reconstruction ou déplacement des index uniquement

Pour reconstruire un index unique :


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

Pour reconstruire tous les index :


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

Avertissement JavaScript est désactivé ou n'est pas disponible dans votre navigateur.

Pour que vous puissiez utiliser la documentation AWS, Javascript doit être activé. Vous trouverez des instructions sur les pages d'aide de votre navigateur.

Conventions de rédaction

Supprimer le ballonnement avec pg_repack

Exemple : récupérer de l'espace en utilisant Autovacuum et VACUUM FULL