Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra. # Reconstruction des index La commande [PostgreSQL](https://www.postgresql.org/docs/current/sql-reindex.html) 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 CREATE INDEX ON TABLE ([,]) ``` 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 facultatif`CONCURRENTLY`, 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'`CONCURRENTLY`option suivante : ``` create index CONCURRENTLY on table(columns) ; ``` Supprimez l'ancien index avec l'`CONCURRENTLY`option suivante : ``` drop index CONCURRENTLY ; ``` **Reconstruction d'un index** Pour reconstruire un index unique : ``` reindex index ; ``` Pour reconstruire tous les index d'une table : ``` reindex table ; ``` Pour reconstruire tous les index d'un schéma : ``` reindex schema ; ``` **Reconstruction simultanée d'un index** Pour reconstruire un index unique : ``` reindex index CONCURRENTLY ; ``` Pour reconstruire tous les index d'une table : ``` reindex table CONCURRENTLY ; ``` Pour reconstruire tous les index d'un schéma : ``` reindex schema CONCURRENTLY ; ``` **Reconstruction ou déplacement des index uniquement** Pour reconstruire un index unique : ``` pg_repack -h -d -i -k ``` Pour reconstruire tous les index : ``` pg_repack -h -d -x -t -k ```