PolarDB:Rekomendasi indeks otomatis

Informasi Latar Belakang

Optimasi performa database merupakan tugas utama dalam manajemen database. Indeks sangat penting untuk meningkatkan performa kueri SQL. Indeks yang baik membantu database menemukan baris tertentu dalam tabel dan mengambil data yang telah diurutkan secara cepat, sehingga mempersingkat waktu kueri secara signifikan.

Namun, tidak semua indeks meningkatkan performa. Indeks yang sesuai dapat mempercepat kueri secara drastis, tetapi indeks yang tidak tepat mungkin tidak memberikan manfaat dan bahkan dapat memperlambat database karena overhead pemeliharaan. Indeks juga menggunakan ruang penyimpanan, dan terlalu banyak indeks yang tidak berguna akan membuang sumber daya penyimpanan. Menentukan kapan dan pada kolom mana harus membuat indeks memerlukan keahlian teknis dan pengalaman. Keputusan ini sering kali membutuhkan analisis mendalam terhadap rencana eksekusi kueri dan distribusi data.

Untuk membantu mengatasi tantangan ini dan mengoptimalkan performa database, PolarDB for PostgreSQL dan menyediakan fitur rekomendasi indeks otomatis. Fitur ini menyederhanakan proses pembuatan indeks, sehingga memudahkan pengembangan dan pemeliharaan indeks database. Anda dapat menggunakan perintah EXPLAIN untuk menganalisis pernyataan SQL yang lambat. Sistem kemudian akan menyarankan indeks yang sesuai dan memprediksi peningkatan performa yang diharapkan.

Manfaat utama dari fitur rekomendasi indeks otomatis adalah:

• Rekomendasi yang akurat: Fitur ini menggunakan statistik yang dikumpulkan secara otomatis dan model biaya pengoptimal untuk merekomendasikan indeks yang tepat bagi kueri SQL tertentu. Hal ini menghindari masalah performa dan biaya trial-and-error yang terkait dengan penambahan indeks secara membabi buta.

• Peningkatan efisiensi: Fitur ini mengurangi waktu yang diperlukan untuk menganalisis kueri lambat secara manual. Ini membantu Anda menemukan dan memperbaiki bottleneck performa dengan cepat, sehingga mempersingkat waktu untuk mengidentifikasi dan menyelesaikan masalah.

• Mudah digunakan: Cukup dengan perintah EXPLAIN yang sederhana, Anda sudah bisa mendapatkan rekomendasi indeks. Fitur ini juga menyediakan perintah SQL untuk membuat indeks tersebut dan memprediksi peningkatan performa. Hal ini menyederhanakan alur kerja Anda.

Panduan penggunaan

Konfigurasikan rekomendasi indeks otomatis

1. Tambahkan polar_advisor ke session_preload_libraries untuk memuat polar_advisor bagi semua pengguna.

   -- Parameter session_preload_libraries kosong secara default. Perintah ini menambahkan polar_advisor.
   alter role all set session_preload_libraries to 'polar_advisor';

2. Aktifkan rekomendasi indeks pada node tertentu untuk semua pengguna. Kami menyarankan agar Anda hanya mengaktifkan fitur ini pada node read-only (RO) untuk menghindari konsumsi sumber daya komputasi pada node read-write (RW).

   -- Aktifkan rekomendasi indeks pada node RO. [Direkomendasikan]
   alter role all set polar_advisor_type to 'index(ro)';

   -- Aktifkan rekomendasi indeks pada semua node.
   alter role all set polar_advisor_type to 'index';

Gunakan rekomendasi indeks otomatis

Saat Anda menghasilkan rencana eksekusi untuk pernyataan SQL yang lambat menggunakan perintah EXPLAIN tanpa pernyataan ANALYZE, fitur rekomendasi indeks otomatis akan dipicu. Jika ditemukan indeks yang direkomendasikan, sistem secara otomatis akan menampilkan informasi berikut:

• advise: Perintah untuk membuat indeks yang direkomendasikan.

• old cost: Biaya dari rencana asli.

• new cost: Perkiraan biaya dari rencana setelah indeks yang direkomendasikan dibuat.

• saved cost: Penghematan biaya oleh rencana baru dibandingkan dengan rencana lama.

• estimate: Perkiraan peningkatan performa.

Fitur rekomendasi indeks otomatis dapat mengenali jenis operator berikut. Saat ini, hanya indeks B-tree yang dapat direkomendasikan. Contoh berikut menunjukkan cara kerja fitur ini.

Buat tabel dan masukkan data.

create table t( a int,b int);
insert into t select i,i from generate_series(1,10000)i;
analyze t;

• Rekomendasikan indeks kolom tunggal berdasarkan kondisi equality.

  /*FORCE_SLAVE*/ explain select * from t where a = 1;
  INFO:  IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (a)"
  NOTICE:  IND ADV:   old cost 0.00..170.00
  NOTICE:  IND ADV:   new cost 0.04..2.25
  NOTICE:  IND ADV: saved cost -0.04..167.75
  NOTICE:  IND ADV: estimate 75.5 kali lebih cepat
                      QUERY PLAN
  ---------------------------------------------------
   Seq Scan on t  (cost=0.00..170.00 rows=1 width=8)
     Filter: (a = 1)
  (2 rows)

• Rekomendasikan indeks satu kolom berdasarkan kondisi perbandingan.

   /*FORCE_SLAVE*/ explain select * from t where a > 10; -- Indeks tidak menyaring banyak data, sehingga tidak ada indeks yang direkomendasikan.
                        QUERY PLAN
  ------------------------------------------------------
   Seq Scan on t  (cost=0.00..170.00 rows=9990 width=8)
     Filter: (a > 10)
  (2 rows)
  
   /*FORCE_SLAVE*/ explain select * from t where a < 10;
  INFO:  IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (a)"
  NOTICE:  IND ADV:   old cost 0.00..170.00
  NOTICE:  IND ADV:   new cost 0.04..2.39
  NOTICE:  IND ADV: saved cost -0.04..167.61
  NOTICE:  IND ADV: estimate 71.1 times faster
                      QUERY PLAN
  ---------------------------------------------------
   Seq Scan on t  (cost=0.00..170.00 rows=9 width=8)
     Filter: (a < 10)
  (2 rows)
  

• Rekomendasikan indeks gabungan berdasarkan beberapa kondisi equality atau comparison.

  /*FORCE_SLAVE*/ explain select * from t where a = 1 and b = 1;
  INFO:  IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (b, a)"
  NOTICE:  IND ADV:   old cost 0.00..195.00
  NOTICE:  IND ADV:   new cost 0.04..1.16
  NOTICE:  IND ADV: saved cost -0.04..193.84
  NOTICE:  IND ADV: estimate 168.8 kali lebih cepat
                      QUERY PLAN
  ---------------------------------------------------
   Seq Scan on t  (cost=0.00..195.00 rows=1 width=8)
     Filter: ((a = 1) AND (b = 1))
  (2 rows)

• Rekomendasikan indeks kolom tunggal berdasarkan operator ORDER BY.

   /*FORCE_SLAVE*/ explain select * from t order by a limit 10;
  INFO:  IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (a)"
  NOTICE:  IND ADV:   old cost 361.10..361.12
  NOTICE:  IND ADV:   new cost 0.04..0.26
  NOTICE:  IND ADV: saved cost 361.06..360.86
  NOTICE:  IND ADV: estimate 1366.8 kali lebih cepat
                              QUERY PLAN
  -------------------------------------------------------------------
   Limit  (cost=361.10..361.12 rows=10 width=8)
     ->  Sort  (cost=361.10..386.10 rows=10000 width=8)
           Sort Key: a
           ->  Seq Scan on t  (cost=0.00..145.00 rows=10000 width=8)
  (4 rows)

• Rekomendasikan indeks kolom tunggal berdasarkan operator GROUP BY.

  /*FORCE_SLAVE*/ explain select a,sum(b) from t group by a having a < 10 ;
  INFO:  IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (a)"
  NOTICE:  IND ADV:   old cost 170.14..170.30
  NOTICE:  IND ADV:   new cost 0.04..2.53
  NOTICE:  IND ADV: saved cost 170.11..167.77
  NOTICE:  IND ADV: estimate 67.4 kali lebih cepat
                            QUERY PLAN
  ---------------------------------------------------------------
   GroupAggregate  (cost=170.14..170.30 rows=9 width=12)
     Group Key: a
     ->  Sort  (cost=170.14..170.17 rows=9 width=8)
           Sort Key: a
           ->  Seq Scan on t  (cost=0.00..170.00 rows=9 width=8)
                 Filter: (a < 10)
  (6 rows)

• Rekomendasikan indeks kolom tunggal berdasarkan operasi equi-join.

  /*FORCE_SLAVE*/ explain select * from t t1,t t2 where t1.a = t2.a limit 10;
  INFO:  IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (a)"
  NOTICE:  IND ADV:   old cost 270.00..270.28
  NOTICE:  IND ADV:   new cost 0.07..0.70
  NOTICE:  IND ADV: saved cost 269.93..269.58
  NOTICE:  IND ADV: estimate 384.3 kali lebih cepat
                                   QUERY PLAN
  ----------------------------------------------------------------------------
   Limit  (cost=270.00..270.28 rows=10 width=16)
     ->  Hash Join  (cost=270.00..552.50 rows=10000 width=16)
           Hash Cond: (t1.a = t2.a)
           ->  Seq Scan on t t1  (cost=0.00..145.00 rows=10000 width=8)
           ->  Hash  (cost=145.00..145.00 rows=10000 width=8)
                 ->  Seq Scan on t t2  (cost=0.00..145.00 rows=10000 width=8)
  (6 rows)