Index-Only Scan — Indeks yang Melewati Tabel

Lewati lookup tabel — Index-Only Scan

Lookup indeks biasa menemukan baris target di indeks, lalu kembali ke tabel dasar untuk membaca kolom lain satu baris pada satu waktu.

Perjalanan kembali ini disebut table lookup (langkah menarik baris dari tabel dasar lewat indeks).

Ketika result set besar, perjalanan pulang-pergi itu menumpuk dan mulai memakan waktu nyata.

Kalau kamu memakai indeks yang berisi setiap kolom yang dirujuk kueri, semua nilai ada di sana di indeks, dan tidak perlu kembali ke tabel itu sendiri.

Pola ini, di mana indeks saja mengantarkan hasil, disebut Index-Only Scan (indeks yang mencakup setiap kolom yang disentuh kueri juga dikenal sebagai covering index).

-- Mengagregasi region dengan indeks yang hanya berisi region
-- Kolom yang dirujuk (region) sepenuhnya di dalam indeks → tidak ada perjalanan kembali ke tabel dasar
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region);

EXPLAIN QUERY PLAN
SELECT region, COUNT(*)
FROM perf_sales
GROUP BY region;

Bayangkan kebutuhannya: "Kami ingin total penjualan per rep." Kueri ini hanya menyentuh dua kolom: emp_id dan amount. Bangun indeks yang berisi keduanya, dan konfirmasi plan mengantarkan hasil dari indeks saja, tanpa kembali ke tabel dasar. Buang dan bangun ulang indeks di dalam satu eksekusi konsol ini supaya plan sepenuhnya mandiri. (Jalankan dengan benar untuk memunculkan penjelasan.)

① Buang indeks dengan DROP INDEX IF EXISTS.

② Buat indeks yang berisi emp_id dan amount — kolom yang dirujuk kueri.

③ Pakai EXPLAIN QUERY PLAN pada kueri agregat yang menjumlahkan amount per emp_id, dan konfirmasi plan tidak pernah membaca tabel dasar.

Lewatkan satu kolom saja dan kamu kembali ke tabel dasar

Index-Only Scan hanya terjadi ketika setiap kolom yang disentuh kueri — di SELECT, WHERE, GROUP BY, dan sebagainya — ada di dalam indeks.

Lewatkan satu kolom saja dan database harus kembali ke tabel dasar untuk membacanya, dan USING COVERING INDEX hilang dari plan.

Misalnya, dengan indeks pada (emp_id, amount), menulis SELECT emp_id, SUM(amount), region menambah region, yang tidak ada di indeks — jadi kueri menuju kembali ke tabel dasar untuk mengambilnya.

-- Dengan indeks pada (region, amount),
-- menambah product ke kolom yang dirujuk merusak Index-Only Scan
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region, amount);

-- region, SUM(amount) saja → sepenuhnya tercakup oleh indeks
EXPLAIN QUERY PLAN
SELECT region, SUM(amount) FROM perf_sales GROUP BY region;

-- Tambah product → tidak ada di indeks, jadi kembali ke tabel dasar
EXPLAIN QUERY PLAN
SELECT region, SUM(amount), MAX(product) FROM perf_sales GROUP BY region;

Memakai indeks yang sama, bandingkan dua agregat: satu yang kolom yang dirujuknya muat di dalam indeks, dan satu yang menambah satu kolom yang tidak. Bangun indeks komposit pada emp_id dan amount, lalu lihat kedua plan berdampingan.

① Buang indeks dengan DROP INDEX IF EXISTS.

② Buat indeks komposit yang berisi emp_id dan amount.

③ Tampilkan EXPLAIN QUERY PLAN untuk agregat yang menjumlahkan amount per emp_id (muat di dalam indeks).

④ Lalu tampilkan EXPLAIN QUERY PLAN untuk agregat yang sama ditambah maximum region, dan bandingkan dengan ③ untuk melihat bagaimana plan berubah (region tidak ada di indeks).

Pertahankan Index-Only Scan dengan menyertakan kolom filter WHERE juga

SELECT bukan satu-satunya tempat kolom muncul di kueri kamu.

Kolom filter WHERE juga dihitung sebagai kolom yang disentuh kueri, jadi mereka perlu ada di indeks juga — kalau tidak kamu masih akan kembali ke tabel dasar.

Lipat semuanya ke dalam satu indeks dalam urutan "kolom filter → kolom output / agregat", dan baik penyaringan maupun pengambilan nilai selesai di dalam indeks yang sama.

Misalnya, untuk memfilter dengan WHERE region = 'East' dan menghitung SUM(amount), taruh kolom filter region pertama dan kolom agregat amount kedua: (region, amount).

Indeks mempersempit ke baris target berdasarkan region dan membaca amount dari indeks yang sama, jadi tidak ada perjalanan kembali ke tabel dasar yang diperlukan.

-- Lipat kolom filter (status) dan kolom agregat (amount) ke dalam satu indeks
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(status, amount);

EXPLAIN QUERY PLAN
SELECT SUM(amount)
FROM perf_sales
WHERE status = 'pending';

Bayangkan kebutuhannya: "Kami ingin total penjualan untuk region tertentu." Kueri memfilter pada region di WHERE dan menghitung total amount. Lipat kolom filter dan kolom agregat ke dalam satu indeks, dan konfirmasi plan tidak pernah kembali ke tabel dasar.

① Buang indeks dengan DROP INDEX IF EXISTS.

② Buat indeks komposit dengan kolom filter region pertama dan kolom agregat amount kedua.

③ Pakai EXPLAIN QUERY PLAN pada agregat yang memfilter berdasarkan region dan menjumlahkan amount, dan konfirmasi plan tidak pernah membaca tabel dasar.