AnalyticDB:Optimalkan kinerja kueri

Kumpulkan statistik tabel

Pengoptimal kueri menghasilkan rencana eksekusi berdasarkan statistik tabel. Statistik yang usang atau tidak tersedia menyebabkan pengoptimal membuat estimasi yang tidak akurat, sehingga menghasilkan rencana yang tidak efisien.

Jalankan ANALYZE setelah pemuatan data besar atau setelah lebih dari 20% baris tabel diperbarui.

-- Kumpulkan statistik pada semua tabel
ANALYZE;

-- Kumpulkan statistik pada semua kolom tabel t
ANALYZE t;

-- Kumpulkan statistik pada kolom tertentu
ANALYZE t(a);

Untuk sebagian besar workload, menjalankan ANALYZE t pada tabel yang dimodifikasi sudah cukup. Gunakan ANALYZE tingkat kolom hanya jika Anda memerlukan kontrol lebih ketat—misalnya, pada kolom yang digunakan sebagai kunci gabungan, kondisi filter, atau kolom indeks.

Pilih query optimizer

AnalyticDB for PostgreSQL menyertakan dua query optimizer, masing-masing dioptimalkan untuk workload yang berbeda.

Ganti optimizer pada tingkat sesi:

-- Aktifkan Legacy query optimizer
SET optimizer = off;

-- Aktifkan ORCA query optimizer
SET optimizer = on;

-- Periksa optimizer saat ini
SHOW optimizer;
-- on = ORCA query optimizer
-- off = Legacy optimizer

Untuk mengubah optimizer pada tingkat instans, ajukan tiket.

Gunakan indeks untuk mempercepat kueri

Indeks mempercepat kueri yang memindai sebagian kecil tabel berdasarkan kondisi filter. AnalyticDB for PostgreSQL mendukung tiga jenis indeks.

Contoh: menambahkan B-tree index

Tanpa indeks, pengoptimal melakukan pemindaian tabel penuh:

postgres=# EXPLAIN SELECT * FROM t WHERE b = 1;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
   ->  Table Scan on t  (cost=0.00..431.00 rows=1 width=16)
         Filter: b = 1
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(5 rows)

Buat B-tree index pada kolom b:

postgres=# CREATE INDEX i_t_b ON t USING btree (b);
CREATE INDEX

Dengan indeks tersebut, pengoptimal menggunakan Pemindaian indeks sebagai gantinya:

postgres=# EXPLAIN SELECT * FROM t WHERE b = 1;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..2.00 rows=1 width=16)
   ->  Index Scan using i_t_b on t  (cost=0.00..2.00 rows=1 width=16)
         Index Cond: b = 1
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(5 rows)

Lihat rencana kueri

Rencana kueri adalah struktur pohon operator yang menjelaskan cara database mengeksekusi kueri. Membaca rencana tersebut membantu Anda mengidentifikasi penyebab kueri lambat.

Cara membaca rencana kueri

• Rencana tersebut berupa pohon. Eksekusi dimulai dari node daun (paling bawah) dan mengalir ke akar.

• cost=<startup>..<total> adalah estimasi biaya oleh pengoptimal, bukan milidetik aktual. Estimasi biaya tinggi berarti pengoptimal memperkirakan operasi tersebut mahal—gunakan untuk perbandingan, bukan sebagai ukuran absolut.

• rows= adalah perkiraan jumlah baris. Selisih besar antara estimasi dan jumlah baris aktual sering kali menunjukkan statistik usang—jalankan ANALYZE untuk memperbaikinya.

EXPLAIN vs EXPLAIN ANALYZE

• EXPLAIN menampilkan rencana tanpa menjalankan kueri.

• EXPLAIN ANALYZE menjalankan kueri dan menambahkan waktu eksekusi aktual serta jumlah baris ke dalam rencana.

-- Tampilkan hanya rencana (tanpa eksekusi)
postgres=# EXPLAIN SELECT a, b FROM t;
                                      QUERY PLAN
------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..4.00 rows=100 width=8)
   ->  Seq Scan on t  (cost=0.00..4.00 rows=34 width=8)
 Optimizer status: legacy query optimizer
(3 rows)

-- Jalankan kueri dan tampilkan waktu aktual
postgres=# EXPLAIN ANALYZE SELECT a, b FROM t;
                                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..4.00 rows=100 width=8)
   Rows out:  100 rows at destination with 2.728 ms to first row, 2.838 ms to end, start offset by 0.418 ms.
   ->  Seq Scan on t  (cost=0.00..4.00 rows=34 width=8)
         Rows out:  Avg 33.3 rows x 3 workers.  Max 37 rows (seg2) with 0.088 ms to first row, 0.107 ms to end, start offset by 2.887 ms.
 Slice statistics:
   (slice0)    Executor memory: 131K bytes.
   (slice1)    Executor memory: 163K bytes avg x 3 workers, 163K bytes max (seg0).
 Statement statistics:
   Memory used: 128000K bytes
 Optimizer status: legacy query optimizer
 Total runtime: 3.739 ms
(11 rows)

Identifikasi bottleneck dari output EXPLAIN ANALYZE

Gunakan data waktu aktual dan jumlah baris untuk mengklasifikasikan bottleneck:

Jenis operator yang didukung

Contoh: membaca rencana kueri join

postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.b = t2.b;
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice3; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.b = t2.b
         ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
               Hash Key: t1.b
               ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
                     Hash Key: t2.b
                     ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(12 rows)

Membaca rencana ini dari bawah ke atas:

1. Table Scan — memindai t1 dan t2.

2. Redistribute Motion — mengacak baris dari kedua tabel di seluruh node komputasi berdasarkan nilai hash dari b, sehingga baris yang cocok berada di node yang sama.

3. Hash — membangun tabel hash pada t2 untuk operasi join.

4. Hash Join — menggabungkan t1 dan t2 berdasarkan kunci hash.

5. Gather Motion — mengirim hasil dari node komputasi ke node koordinator, yang kemudian mengembalikannya ke klien.

Hapus operator distribute

Saat AnalyticDB for PostgreSQL melakukan join atau agregasi data di seluruh node komputasi, sistem menyisipkan operator Redistribute Motion atau Broadcast Motion untuk mengacak data. Operator-operator ini mengonsumsi lebar pita jaringan yang signifikan. Menyelaraskan kunci distribusi pada kolom join menghilangkan kebutuhan redistribusi data.

Cara kerjanya

Jika dua tabel di-join berdasarkan kolom a dan keduanya didistribusikan berdasarkan a, setiap node komputasi sudah menyimpan baris yang cocok—tidak diperlukan perpindahan data.

Contoh

SELECT * FROM t1, t2 WHERE t1.a = t2.a;

t1 didistribusikan berdasarkan kolom a. Jika t2 didistribusikan berdasarkan kolom b (tidak cocok), AnalyticDB for PostgreSQL harus mendistribusikan ulang t2:

postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
                                                  QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice2; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.a = t2.a
         ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
                     Hash Key: t2.a
                     ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(10 rows)

Jika t2 juga didistribusikan berdasarkan a, operasi join berjalan tanpa redistribusi:

postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
                                      QUERY PLAN
-------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.a = t2.a
         ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(8 rows)

Untuk memperbaiki ketidakcocokan tersebut, ubah kunci distribusi t2:

ALTER TABLE t2 SET DISTRIBUTED BY (a);

Gunakan tipe data yang sama pada kolom join

Menggabungkan kolom dengan tipe data berbeda memicu konversi tipe data, yang memaksa redistribusi data. Gunakan tipe data yang sama pada kedua sisi kunci join.

Konversi tipe eksplisit

Cast eksplisit dalam pernyataan SQL mengubah fungsi hash yang diterapkan pada kolom, menyebabkan pengoptimal mendistribusikan ulang kedua tabel:

-- Tanpa konversi tipe: tidak ada redistribusi
postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
                                      QUERY PLAN
-------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.a = t2.a
         ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(8 rows)

-- Cast eksplisit ke numeric: memicu redistribusi kedua tabel
postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a::numeric;
                                                  QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice3; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.a::numeric = t2.a::numeric
         ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
               Hash Key: t1.a::numeric
               ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
                     Hash Key: t2.a::numeric
                     ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(12 rows)

Konversi tipe implisit

Saat dua kolom join menggunakan tipe yang tidak kompatibel, database secara otomatis mengonversi salah satu tipe. Misalnya, timestamp without time zone dan timestamp with time zone menggunakan fungsi hash berbeda. Pengoptimal kembali menggunakan Broadcast Motion alih-alih join berbasis hash:

postgres=# CREATE TABLE t1 (a timestamp without time zone);
CREATE TABLE
postgres=# CREATE TABLE t2 (a timestamp with time zone);
CREATE TABLE

postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
                                               QUERY PLAN
-------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice2; segments: 3)  (cost=0.04..0.11 rows=4 width=16)
   ->  Nested Loop  (cost=0.04..0.11 rows=2 width=16)
         Join Filter: t1.a = t2.a
         ->  Seq Scan on t1  (cost=0.00..0.00 rows=1 width=8)
         ->  Materialize  (cost=0.04..0.07 rows=1 width=8)
               ->  Broadcast Motion 3:3  (slice1; segments: 3)  (cost=0.00..0.04 rows=1 width=8)
                     ->  Seq Scan on t2  (cost=0.00..0.00 rows=1 width=8)
(7 rows)

Untuk menghilangkan konversi implisit, sejajarkan tipe kolom saat definisi tabel. Misalnya, definisikan t1.a dan t2.a sebagai timestamp without time zone.

Temukan data skew

Data skew terjadi ketika baris tersebar tidak merata di seluruh node komputasi. Tabel yang skewed menyebabkan satu node komputasi melakukan sebagian besar pekerjaan sementara node lain menganggur, sehingga memperlambat seluruh kueri.

Periksa distribusi baris di seluruh node komputasi:

postgres=# SELECT gp_segment_id, count(1) FROM t1 GROUP BY 1 ORDER BY 2 DESC;
 gp_segment_id | count
---------------+-------
             0 | 16415
             2 |    37
             1 |    32
(3 rows)

Output ini menunjukkan skew parah: node 0 menyimpan 99% baris. Perbaiki dengan mengganti kunci distribusi:

-- Opsi 1: ubah kunci distribusi langsung
ALTER TABLE t1 SET DISTRIBUTED BY (b);

-- Opsi 2: buat ulang tabel dengan kunci distribusi yang lebih baik
-- Buat tabel baru, muat data secara massal, lalu tukar posisinya dengan tabel asli

Pilih kunci distribusi dengan kardinalitas tinggi dan tanpa nilai dominan untuk memastikan penyebaran yang merata.

Lihat Pernyataan SQL yang sedang berjalan

Saat banyak kueri dijalankan secara konkuren, instans mungkin melaporkan respons lambat atau sumber daya tidak mencukupi. Gunakan tampilan pg_stat_activity untuk melihat apa yang sedang berjalan.

postgres=# SELECT * FROM pg_stat_activity;

Bidang utama:

Filter hanya untuk kueri aktif:

SELECT * FROM pg_stat_activity WHERE current_query != '<IDLE>';

Temukan lima kueri dengan waktu eksekusi terlama:

SELECT current_timestamp - query_start AS runtime,
       datname,
       usename,
       current_query
FROM pg_stat_activity
WHERE current_query != '<IDLE>'
ORDER BY runtime DESC
LIMIT 5;

Periksa status lock

Jika suatu kueri memegang lock dalam waktu lama, kueri lain pada objek yang sama akan menunggu tanpa batas. Jalankan kueri berikut untuk melihat tabel mana yang dikunci dan sesi mana yang memegang lock tersebut:

SELECT pgl.locktype   AS locktype,
       pgl.database   AS database,
       pgc.relname    AS relname,
       pgl.relation   AS relation,
       pgl.transaction AS transaction,
       pgl.pid        AS pid,
       pgl.mode       AS mode,
       pgl.granted    AS granted,
       pgsa.current_query AS query
FROM pg_locks pgl
JOIN pg_class pgc ON pgl.relation = pgc.oid
JOIN pg_stat_activity pgsa ON pgl.pid = pgsa.procpid
ORDER BY pgc.relname;

Untuk membuka blokir kueri yang menunggu, batalkan atau hentikan sesi yang memegang lock:

-- Batalkan kueri (tidak berfungsi jika sesi IDLE)
SELECT pg_cancel_backend(pid);

-- Hentikan sesi dan rollback transaksi yang belum dikomit
SELECT pg_terminate_backend(pid);

pg_cancel_backend tidak berpengaruh ketika pg_stat_activity.current_query menampilkan IDLE. Gunakan pg_terminate_backend dalam kasus tersebut.

Aktifkan nested loop joins

Nested loop joins dinonaktifkan secara default. Untuk kueri yang mengembalikan jumlah baris kecil—karena kondisi filter sangat selektif dan klausa LIMIT—mengaktifkan nested loop joins dapat secara signifikan mengurangi waktu kueri.

Kapan mengaktifkan

Optimasi ini efektif ketika:

• Kondisi filter pada satu tabel mengembalikan sangat sedikit baris.

• Klausa LIMIT lebih lanjut membatasi set hasil.

• Indeks tersedia pada kolom join kedua tabel.

Contoh

SELECT *
FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c2 >= '230769548' AND t1.c2 < '230769549'
LIMIT 100;

Periksa dan aktifkan nested loop joins untuk sesi tersebut:

SHOW enable_nestloop;
 enable_nestloop
-----------------
 off

SET enable_nestloop = on;

SHOW enable_nestloop;
 enable_nestloop
-----------------
 on

EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c2 >= '230769548' AND t1.c2 < '23432442'
LIMIT 100;
                                            QUERY PLAN
-----------------------------------------------------------------------------------------------
 Limit  (cost=0.26..16.31 rows=1 width=18608)
   ->  Nested Loop  (cost=0.26..16.31 rows=1 width=18608)
         ->  Index Scan using t1 on c2  (cost=0.12..8.14 rows=1 width=12026)
               Filter: ((c2 >= '230769548'::bpchar) AND (c2 < '230769549'::bpchar))
         ->  Index Scan using t2 on c1  (cost=0.14..8.15 rows=1 width=6582)
               Index Cond: ((c1)::text = (T1.c1)::text)

Rencana tersebut menggunakan pemindaian indeks pada kedua tabel dan nested loop join, menghindari pemindaian tabel penuh dan redistribusi data.

enable_nestloop adalah pengaturan tingkat sesi. Atur ulang setelah kueri jika kueri lain dalam sesi yang sama lebih diuntungkan oleh hash join.

Topik terkait

• Diagnostik kueri lambat

• Gunakan antrian sumber daya untuk melakukan manajemen workload