Pendahuluan dan Solusi Langsung

Di H-E-B, tim Haskell Enterprise sering menghadapi query lambat yang mengganggu throughput order processing. Masalah utama biasanya diukir dari execution plan yang menjelajah terlalu banyak row dan tidak memanfaatkan indeks. Optimasi query lambat dimulai dengan memahami execution plan, memilih indeks yang tepat, dan menjaga rencana eksekusi tetap konsisten saat data tumbuh. Artikel ini membahas langkah-langkah teknis tersebut secara konkret.

Secara garis besar, fokusnya adalah mengidentifikasi query bermasalah lewat metrik observasi (latensi p99, rows examined per query), mengevaluasi plan, memutuskan composite index yang efektif, menerapkan pagination keyset untuk set data besar, dan menyediakan checklist untuk mencegah regresi performa.

1. Diagnosa Execution Plan di Lingkungan Produksi

Data Observability yang Perlu Dipantau

Mulai dengan metrik latensi (misalnya percentile 95/99) dan jumlah rows examined per request. Gunakan sistem observability yang tersedia di H-E-B—misalnya Prometheus/Grafana untuk latensi request dan logging tracing untuk query SQL yang dieksekusi Haskell. Catat juga metrics I/O dan waktu parsing jika tersedia.

Analisis Execution Plan

Tarik execution plan dari database (PostgreSQL atau lainnya). Perhatikan dua hal penting: apakah sequential scan terjadi pada tabel besar, dan apakah ada bitmap heap scan yang menunjukkan index exist tapi tidak selektif. Contoh query serial order:

SELECT o.id, o.status, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'ready'
  AND o.created_at > NOW() - INTERVAL '1 day'
ORDER BY o.created_at DESC
LIMIT 100;

Plan yang buruk akan menunjukkan Seq Scan alias scanning seluruh orders sebelum apply filter. Untuk mengatasinya, lihat filter dan sort: timestamp + status + join customer_id.

2. Pemilihan Indeks Komposit yang Efektif

Mengidentifikasi Kolom Utama

Gunakan data plan untuk melihat kolom mana yang digunakan di WHERE, JOIN, dan ORDER BY. Pada kasus di atas, key candidates adalah status, created_at, dan customer_id. Jika join ke customer mengikuti filter, composite index berbasis (status, created_at DESC) sangat membantu.

Menerapkan Index di Produksi

Index harus dibangun dengan melihat urutan penggunaan kolom. Untuk query yang menampilkan data terbaru per status, urutan status lalu created_at meminimalkan rows scanned. Di PostgreSQL, contoh perintah:

CREATE INDEX CONCURRENTLY idx_orders_status_created_at
  ON orders (status, created_at DESC);
CREATE INDEX CONCURRENTLY idx_orders_customer_id
  ON orders (customer_id);

Pemilihan index separate untuk join memastikan planner dapat menggabungkan informasi tanpa full scan. Penggunaan CONCURRENTLY mencegah blocking pada tabel aktif.

3. Pagination Keyset untuk Query Tanpa OFFSET Mahal

Kenapa OFFSET Mahal?

OFFSET memaksa database men-scan sejumlah baris sebelum mengambil hasil, menimbulkan latensi linier. Dalam konteks H-E-B, dashboard order real-time memerlukan halaman cepat walaupun data sudah jutaan row.

Implementasi Keyset Pagination dalam Haskell

Gunakan parameter yang menyertakan nilai order terakhir, bukan offset. Struktur query:

SELECT o.id, o.created_at, o.status
FROM orders o
WHERE o.status = 'ready'
  AND (o.created_at, o.id) < ( $1::timestamptz, $2::uuid )
ORDER BY o.created_at DESC, o.id DESC
LIMIT 100;

Keyset pagination memanfaatkan composite index (status, created_at DESC, id DESC) sehingga hanya data relevan yang dibaca. Haskell dapat menyimpan cursor dalam state dengan Maybe (UTCTime, UUID) dan meng-pass ke query berikutnya.

4. Mitigasi Pertumbuhan Data dan Regresi

Strategi Data Retention

Untuk menjaga index tetap ringan, pertimbangkan archiving data yang sudah tidak aktif menggunakan PARTITION atau periodic export. Backup query lamanya bisa diarahkan ke partition lebih tua, sehingga index utama hanya menampung data terbaru.

Checklist Monitoring Performa

  • Latensi p99 query utama (target turun atau stabil).
  • Rows examined per query – harus menurun setelah indeks diterapkan.
  • Execution plan structure – tidak terjadi sequential scan mendadak.
  • Index bloat metric – pastikan vacuum/analyze terjadwal.
  • Keyset cursor validitas – tangani data yang dihapus antar request.

Checklist ini bisa dijadikan alert rule di sistem monitoring H-E-B untuk mendeteksi regresi indeks.

5. Praktik Debugging dan Kesalahan Umum

Mengecek Engagement Index

Selalu bandingkan plan sebelum dan sesudah index. Terkadang index baru belum digunakan karena statistik tidak up-to-date; jalankan ANALYZE atau gunakan SET enable_seqscan = off hanya untuk debugging. Jangan biarkan setting eksperimental masuk ke produksi.

Kesalahan Umum

  • Mengandalkan index dengan cardinality rendah, misalnya hanya status tanpa timestamp.
  • Terlalu banyak index yang menyebabkan penulisan menjadi lambat.
  • Kunci pagination tidak konsisten ketika data dihapus – selalu sertakan nilai unik tambahan (seperti id).

Latensi mendadak sering disebabkan missing index pada join tambahan; periksa kembali foreign key yang dipakai dan pastikan index juga ada untuk kolom yang dijadikan join predicate.

Ringkasan

Optimasi query lambat di H-E-B membutuhkan pendekatan sistematis: pantau metrik latensi dan rows examined, analisis execution plan, buat composite index yang sesuai, ubah pagination ke keyset, dan siapkan checklist untuk regresi. Dengan proses ini, tim Haskell Enterprise dapat menjaga performa aplikasi tetap responsif walau volume data terus bertambah.