Membuat indeks baru Membangun kembali indeks Contoh

Membangun kembali indeks

Perintah PostgreSQL REINDEX membangun kembali indeks dengan menggunakan data yang disimpan dalam tabel indeks dan mengganti salinan indeks yang lama. Kami menyarankan Anda menggunakan REINDEX dalam skenario berikut:

Ketika indeks menjadi rusak dan tidak lagi berisi data yang valid. Hal ini dapat terjadi sebagai akibat dari kegagalan perangkat lunak atau perangkat keras.
Ketika kueri yang sebelumnya menggunakan indeks berhenti menggunakannya.
Ketika indeks menjadi kembung dengan sejumlah besar halaman kosong atau hampir kosong. Anda harus berlari REINDEX ketika persentase kembung (bloat_pct) lebih besar dari 20.

Kueri berikut membantu Anda menemukanbloat_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;

Halaman indeks yang benar-benar kosong direklamasi untuk digunakan kembali. Namun, kami merekomendasikan pengindeksan ulang berkala jika kunci indeks pada halaman telah dihapus tetapi ruang tetap dialokasikan.

Membuat ulang indeks membantu memberikan kinerja kueri yang lebih baik. Anda dapat membuat ulang indeks dalam tiga cara, seperti yang dijelaskan dalam tabel berikut.

Metode	Deskripsi	Batasan
`CREATE INDEX`dan `DROP INDEX` dengan `CONCURRENTLY` opsi	Membangun indeks baru dan menghapus indeks lama. Pengoptimal menghasilkan rencana dengan menggunakan indeks yang baru dibuat, bukan indeks lama. Selama jam sibuk rendah, Anda dapat menjatuhkan indeks lama.	Pembuatan indeks membutuhkan lebih banyak waktu saat Anda menggunakan `CONCURRENTLY` opsi, karena harus melacak semua perubahan yang masuk. Ketika perubahan dibekukan, proses ditandai sebagai selesai.
`REINDEX`dengan `CONCURRENTLY` opsi	Kunci menulis operasi selama proses pembangunan kembali. PostgreSQL versi 12 dan versi yang lebih baru menyediakan opsi, yang menghindari kunci `CONCURRENTLY` ini.	Menggunakan `CONCURRENTLY` membutuhkan waktu yang lebih lama untuk membangun kembali indeks.
`pg_repack`perpanjangan	Membersihkan kembung dari meja dan membangun kembali indeks.	Anda harus menjalankan ekstensi ini dari sebuah EC2 instance atau komputer lokal Anda yang terhubung ke database.

Membuat indeks baru

CREATE INDEXPerintah DROP INDEX dan, ketika digunakan bersama, membangun kembali indeks:


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

Kerugian dari pendekatan ini adalah kunci eksklusifnya di atas meja, yang memengaruhi kinerja selama kegiatan ini. DROP INDEXPerintah memperoleh kunci eksklusif, yang memblokir operasi baca dan tulis di atas meja. CREATE INDEXPerintah memblokir operasi tulis di atas meja. Ini memungkinkan operasi baca, tetapi ini mahal selama pembuatan indeks.

Membangun kembali indeks

REINDEXPerintah ini membantu Anda mempertahankan kinerja database yang konsisten. Ketika Anda melakukan sejumlah besar operasi DHTML di atas meja, ini menghasilkan tabel dan indeks kembung. Indeks digunakan untuk mempercepat pencarian pada tabel untuk meningkatkan kinerja kueri. Indeks kembung mempengaruhi pencarian dan kinerja kueri. Oleh karena itu, kami menyarankan Anda melakukan pengindeksan ulang pada tabel yang memiliki volume operasi DHTML yang tinggi untuk menjaga konsistensi dalam kinerja kueri.

REINDEXPerintah membangun kembali indeks dari awal dengan mengunci operasi tulis pada tabel yang mendasarinya, tetapi memungkinkan operasi baca di atas meja. Namun, itu memblokir operasi baca pada indeks. Kueri yang menggunakan indeks yang sesuai diblokir, tetapi kueri lain tidak.

PostgreSQL versi 12 memperkenalkan parameter opsional baruCONCURRENTLY, yang membangun kembali indeks dari awal tetapi tidak mengunci operasi tulis atau baca di atas meja atau pada kueri yang menggunakan indeks. Namun, dibutuhkan waktu lebih lama untuk menyelesaikan proses saat Anda menggunakan opsi ini.

Contoh

Membuat dan menjatuhkan indeks

Buat indeks baru dengan CONCURRENTLY opsi:


create index CONCURRENTLY on table(columns) ;

Jatuhkan indeks lama dengan CONCURRENTLY opsi:


drop index CONCURRENTLY <index name> ;

Membangun kembali indeks

Untuk membangun kembali indeks tunggal:


reindex index <index name> ;

Untuk membangun kembali semua indeks dalam tabel:


reindex table <table name> ;

Untuk membangun kembali semua indeks dalam skema:


reindex schema <schema name> ;

Membangun kembali indeks secara bersamaan

Untuk membangun kembali indeks tunggal:


reindex index CONCURRENTLY <indexname> ;

Untuk membangun kembali semua indeks dalam tabel:


reindex table CONCURRENTLY <tablename> ;

Untuk membangun kembali semua indeks dalam skema:


reindex schema CONCURRENTLY <schemaname> ;

Membangun kembali atau merelokasi indeks saja

Untuk membangun kembali indeks tunggal:


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

Untuk membangun kembali semua indeks:


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

Awas Javascript dinonaktifkan atau tidak tersedia di browser Anda.

Untuk menggunakan Dokumentasi AWS, Javascript harus diaktifkan. Lihat halaman Bantuan browser Anda untuk petunjuk.

Konvensi Dokumen

Menghapus bloat dengan pg_repack

Contoh: Mereklamasi ruang dengan menggunakan autovacuum dan VACUUM FULL