ApsaraDB RDS:Menggunakan ekstensi index_adviser

Komponen dari ekstensi index_adviser

Saat Anda mengeksekusi pernyataan untuk membuat ekstensi index_adviser, tabel index_advisory, fungsi show_index_advisory(), dan tampilan select_index_advisory akan dibuat.

Gunakan ekstensi

1. Buat ekstensi index_adviser.

   postgres=# create extension index_adviser;
   CREATE EXTENSION

2. Muat ekstensi index_adviser.

   postgres=# LOAD 'index_adviser';
   LOAD

   Catatan

   Pernyataan sebelumnya hanya berlaku untuk sesi saat ini. Untuk memuat ekstensi index_adviser secara default di semua sesi, konfigurasikan ulang parameter shared_preload_libraries dan mulai ulang instans RDS. Namun, ini dapat mempengaruhi kinerja instans RDS.

   shared_preload_libraries='index_adviser'

Contoh

• Buat sebuah tabel.

  CREATE TABLE t( a INT, b INT );
  INSERT INTO t SELECT s, 99999 - s FROM generate_series(0,99999) AS s;
  ANALYZE t;
  Tabel berisi baris-baris berikut:
     a   |   b
  -------+-------
       0 | 99999
       1 | 99998
       2 | 99997
       3 | 99996
         .
         .
         .
   99997 |     2
   99998 |     1
   99999 |     0

• Kueri saran pengindeksan untuk satu Pernyataan SQL tunggal.

  • Untuk menganalisis kueri dan mendapatkan saran pengindeksan tanpa mengeksekusi kueri, gunakan kata kunci EXPLAIN sebagai awalan dari Pernyataan SQL. Contoh:

    postgres=# EXPLAIN SELECT * FROM t WHERE a < 10000;
                                       QUERY PLAN
    ---------------------------------------------------------------------------------
     Seq Scan on t  (cost=0.00..1693.00 rows=9983 width=8)
       Filter: (a < 10000)
     Result  (cost=0.00..0.00 rows=0 width=0)
       One-Time Filter: '** plan (using Index Adviser) **'::text
       ->  Index Scan using "<1>t_a_idx" on t  (cost=0.42..256.52 rows=9983 width=8)
             Index Cond: (a < 10000)
    (6 rows)

    postgres=# EXPLAIN SELECT * FROM t WHERE a = 100;
                                     QUERY PLAN
    ----------------------------------------------------------------------------
     Seq Scan on t  (cost=0.00..1693.00 rows=1 width=8)
       Filter: (a = 100)
     Result  (cost=0.00..0.00 rows=0 width=0)
       One-Time Filter: '** plan (using Index Adviser) **'::text
       ->  Index Scan using "<1>t_a_idx" on t  (cost=0.42..2.64 rows=1 width=8)
             Index Cond: (a = 100)
    (6 rows)

    postgres=# EXPLAIN SELECT * FROM t WHERE b = 10000;
                                     QUERY PLAN
    ----------------------------------------------------------------------------
     Seq Scan on t  (cost=0.00..1693.00 rows=1 width=8)
       Filter: (b = 10000)
     Result  (cost=0.00..0.00 rows=0 width=0)
       One-Time Filter: '** plan (using Index Adviser) **'::text
       ->  Index Scan using "<1>t_b_idx" on t  (cost=0.42..2.64 rows=1 width=8)
             Index Cond: (b = 10000)
    (6 rows)

  • Gunakan CLI PostgreSQL untuk menanyakan saran pengindeksan dari tabel index_advisory. Contoh:

    postgres=# SELECT * FROM index_advisory;
     reloid | relname | attrs | benefit | original_cost | new_cost | index_size | backend_pid |            timestamp
    --------+---------+-------+---------+---------------+----------+------------+-------------+----------------------------------
      16438 | t       | {1}   | 1337.43 |          1693 |  355.575 |       2624 |       79370 | 18-JUN-21 08:55:51.492388 +00:00
      16438 | t       | {1}   | 1684.56 |          1693 |    8.435 |       2624 |       79370 | 18-JUN-21 08:59:00.319336 +00:00
      16438 | t       | {2}   | 1684.56 |          1693 |    8.435 |       2624 |       79370 | 18-JUN-21 08:59:07.814453 +00:00
    (3 rows)

    Bidang	Tipe	Deskripsi
    reloid	oid	OID dari tabel untuk indeks.
    relname	name	Nama tabel untuk indeks.
    attrs	integer[]	Kolom tempat saran pengindeksan dihasilkan. Kolom diidentifikasi oleh ID.
    benefit	real	Manfaat menggunakan indeks untuk mempercepat kueri.
    original_cost	real	Rata-rata waktu yang diperlukan untuk mengeksekusi Pernyataan SQL sebelum Anda menggunakan indeks untuk mempercepat kueri.
    new_cost	real	Rata-rata waktu yang diperlukan untuk mengeksekusi Pernyataan SQL setelah Anda menggunakan indeks untuk mempercepat kueri.
    index_size	integer	Perkiraan ukuran indeks di halaman disk.
    backend_pid	integer	ID proses yang menghasilkan saran ini.
    timestamp	timestamp	Tanggal dan waktu ketika saran ini dihasilkan.

  • Jika Pernyataan SQL tidak diawali dengan kata kunci EXPLAIN, ekstensi index_adviser menganalisis Pernyataan SQL saat kueri dieksekusi dan mencatat saran pengindeksan.

  Catatan

  Jangan gunakan ekstensi index_adviser dalam transaksi read-only.

• Kueri saran pengindeksan untuk beban kerja tertentu.

  • Dapatkan saran pengindeksan untuk sesi menggunakan fungsi show_index_advisory().

    Fungsi ini digunakan untuk mendapatkan saran pengindeksan untuk sesi. Sesi diidentifikasi oleh ID proses backend-nya. Anda dapat memanggil fungsi ini dengan menentukan ID proses sesi.

    SELECT show_index_advisory( pid );

    Catatan

    Parameter pid menunjukkan ID proses sesi saat ini. Anda dapat memperoleh ID proses menggunakan parameter backend_pid dalam tabel index_advisory. Anda juga dapat menentukan nilai null untuk mengembalikan set hasil untuk sesi saat ini.

    postgres=# SELECT show_index_advisory(null);
                                                                 show_index_advisory
    ----------------------------------------------------------------------------------------------------------------------------------------------------
     create index idx_t_a on public.t(a);/* size: 2624 KB, benefit: 3021.99, gain: 1.15167301457103, original_cost: 1693, new_cost: 182.005006313324 */
     create index idx_t_b on public.t(b);/* size: 2624 KB, benefit: 1684.56, gain: 0.641983590474943, original_cost: 1693, new_cost: 8.4350004196167 */
    (2 rows)

    Catatan

    Berikut adalah deskripsi setiap baris dalam set hasil:

    • Pernyataan SQL untuk membuat indeks dari saran pengindeksan.

    • Perkiraan ukuran halaman indeks.

    • Manfaat menggunakan indeks untuk mempercepat kueri.

    • Keuntungan menggunakan indeks. Rumus berikut digunakan untuk menghitung keuntungan indeks: Keuntungan menggunakan indeks = Manfaat menggunakan indeks / Ukuran yang dikonsumsi oleh indeks.

    • Rata-rata waktu eksekusi Pernyataan SQL sebelum menggunakan indeks untuk mempercepat kueri.

    • Rata-rata waktu eksekusi Pernyataan SQL setelah menggunakan indeks untuk mempercepat kueri.

  • Dapatkan saran pengindeksan untuk sesi menggunakan tampilan select_index_advisory.

    Tampilan ini berisi metrik yang dihitung dan CREATE INDEX pernyataan serta memberikan saran pengindeksan untuk semua sesi dalam tabel index_advisory. Contoh berikut menunjukkan saran pengindeksan untuk Kolom a dan Kolom b dari Tabel t:

    postgres=# SELECT * FROM select_index_advisory;
     backend_pid |                                                             show_index_advisory
    -------------+----------------------------------------------------------------------------------------------------------------------------------------------------
           79370 | create index t_a_idx on public.t(a);/* size: 2624 KB, benefit: 3021.99, gain: 1.15167301457103, original_cost: 1693, new_cost: 182.005006313324 */
           79370 | create index t_b_idx on public.t(b);/* size: 2624 KB, benefit: 1684.56, gain: 0.641983590474943, original_cost: 1693, new_cost: 8.4350004196167 */
    (2 rows)

    Dalam setiap sesi, hasil semua kueri yang mendapat manfaat dari saran pengindeksan yang sama digabungkan menjadi satu set metrik. Metrik direpresentasikan oleh bidang bernama benefit dan gain. Rumus berikut menunjukkan cara menghitung nilai kedua bidang tersebut:

    size = MAX(index size of all queries)
    benefit = SUM(benefit of each query)
    gain = SUM(benefit of each query) / MAX(index size of all queries)

    Catatan

    Jika saran pengindeksan merekomendasikan pembuatan beberapa indeks untuk satu Pernyataan SQL, bidang new_cost dari tabel index_advisory mencatat biaya setelah beberapa indeks dibuat.

    Bidang gain berguna untuk membandingkan keuntungan antara indeks yang direkomendasikan selama sesi tertentu. Nilai yang lebih besar dari bidang gain menunjukkan manfaat yang lebih tinggi dari indeks yang direkomendasikan, yang dapat mengimbangi ruang disk yang mungkin dikonsumsi oleh indeks tersebut.