Optimasi Query Atlas 3D: Desain Index untuk Slice, Filter, dan Zoom

Pada aplikasi atlas 3D atau medical imaging, gejala yang paling sering muncul saat data tumbuh adalah navigasi slice-by-slice mulai tersendat, filter region terasa tidak konsisten cepatnya, dan zoom level tertentu memicu lonjakan latensi. Masalah ini biasanya bukan karena satu query yang “buruk” saja, melainkan kombinasi desain skema, indeks yang tidak cocok dengan pola akses, serta kebiasaan seperti OFFSET besar, COUNT(*) mahal, atau ORDER BY yang memaksa database melakukan sort besar di memori atau disk.

Dalam konteks atlas brainstem 3D yang belakangan mendapat perhatian publik, tantangan tekniknya relevan untuk banyak sistem serupa: data bertambah, akses bersifat spasial dan hierarkis, dan UI menuntut respons interaktif. Fokus artikel ini bukan pada beritanya, melainkan pada cara merancang query dan index agar operasi slice, filter, dan zoom tetap efisien. Untuk konteks umum atlas brainstem 3D, lihat laporan BBC: BBC News.

Mengapa query atlas 3D melambat saat data tumbuh

Pada fase awal, dataset kecil sering menutupi desain yang kurang tepat. Query tetap terasa cepat meski tanpa indeks komposit yang sesuai. Namun ketika jumlah tile, anotasi, atau hasil segmentasi bertambah, beberapa gejala mulai muncul:

Navigasi slice lambat pada index tertentu karena database harus memindai banyak baris yang tidak relevan.
Filter region cepat untuk beberapa region tetapi lambat untuk region lain karena selektivitas data tidak merata.
Zoom level tinggi memicu jumlah tile jauh lebih banyak, sehingga offset, sorting, dan join menjadi mahal.
COUNT total untuk kebutuhan pagination atau badge UI lebih mahal daripada pengambilan data itu sendiri.
ORDER BY tidak cocok dengan indeks, sehingga database membuat sort tambahan.

Pola akses atlas 3D umumnya memiliki beberapa dimensi sekaligus: dataset_id, axis, slice_index, region_id, zoom_level, lalu koordinat tile seperti tile_x dan tile_y. Karena itu, indeks tunggal per kolom sering tidak cukup. Yang dibutuhkan adalah indeks yang mengikuti urutan filter dan urutan sort yang benar-benar dipakai oleh query paling penting.

Skema tabel sederhana yang realistis

Contoh berikut cukup sederhana untuk dibahas, tetapi masih mendekati kebutuhan atlas 3D. Misalnya kita menyimpan tile hasil rendering atau potongan data yang bisa diambil per-slice dan per-zoom:

CREATE TABLE atlas_tiles (
  id BIGINT PRIMARY KEY,
  dataset_id BIGINT NOT NULL,
  axis CHAR(1) NOT NULL,          -- x, y, atau z
  slice_index INT NOT NULL,
  region_id BIGINT NULL,
  zoom_level INT NOT NULL,
  tile_x INT NOT NULL,
  tile_y INT NOT NULL,
  intensity_min REAL NULL,
  intensity_max REAL NULL,
  updated_at TIMESTAMP NOT NULL,
  payload_url TEXT NOT NULL
);

Query yang umum pada API viewer bisa terlihat seperti ini:

SELECT id, tile_x, tile_y, payload_url
FROM atlas_tiles
WHERE dataset_id = 42
  AND axis = 'z'
  AND slice_index = 180
  AND zoom_level = 6
ORDER BY tile_y, tile_x
LIMIT 256;

Atau jika pengguna memfilter region tertentu:

SELECT id, tile_x, tile_y, payload_url
FROM atlas_tiles
WHERE dataset_id = 42
  AND axis = 'z'
  AND slice_index BETWEEN 170 AND 190
  AND region_id = 9001
  AND zoom_level = 4
ORDER BY slice_index, tile_y, tile_x
LIMIT 500;

Dari sini terlihat jelas bahwa urutan kolom pada indeks tidak boleh ditebak. Ia harus mengikuti bentuk WHERE dan ORDER BY yang paling sering terjadi.

Desain index untuk slice, filter, dan zoom

Prinsip dasar indeks komposit

Untuk query atlas 3D, aturan praktisnya adalah:

Letakkan kolom dengan filter kesetaraan yang hampir selalu ada di depan, misalnya dataset_id, axis, dan sering kali zoom_level.
Setelah itu, letakkan kolom range utama seperti slice_index jika query sering menggunakan rentang slice.
Jika query perlu mengembalikan data dalam urutan tertentu, usahakan urutan kolom indeks mendukung ORDER BY agar database tidak perlu sort tambahan.
Jangan membuat terlalu banyak indeks yang tumpang tindih karena setiap INSERT/UPDATE menjadi lebih mahal.

Contoh indeks yang masuk akal untuk query per-slice tanpa filter region:

CREATE INDEX idx_tiles_dataset_axis_slice_zoom_tile
ON atlas_tiles (dataset_id, axis, slice_index, zoom_level, tile_y, tile_x);

Indeks ini cocok jika pola dominan adalah memilih satu dataset, satu axis, satu slice, satu zoom, lalu mengurutkan tile untuk digambar.

Index komposit untuk filter region

Jika filter region sering dipakai dan sangat selektif, buat indeks terpisah yang menempatkan region_id sebelum kolom range atau sort yang relevan:

CREATE INDEX idx_tiles_dataset_axis_region_zoom_slice
ON atlas_tiles (dataset_id, axis, region_id, zoom_level, slice_index, tile_y, tile_x);

Mengapa tidak cukup satu indeks untuk semua kasus? Karena query berikut punya pola seleksi berbeda:

Tanpa region: dataset_id + axis + slice_index + zoom_level
Dengan region: dataset_id + axis + region_id + zoom_level + slice_index

Jika region_id diletakkan terlalu belakang, database bisa gagal memanfaatkan indeks secara efisien untuk query region. Sebaliknya, jika selalu menaruh region_id di depan, query tanpa region bisa kehilangan efisiensi.

Catatan: tidak ada satu indeks universal yang optimal untuk semua kombinasi filter. Fokuslah pada 2-3 pola query yang benar-benar dominan di produksi.

Urutan kolom: equality, range, lalu sort

Aturan yang sering berhasil adalah: kolom equality filter lebih dulu, lalu kolom range, lalu kolom untuk mendukung urutan hasil. Misalnya:

WHERE dataset_id = ?
  AND axis = ?
  AND zoom_level = ?
  AND slice_index BETWEEN ? AND ?
ORDER BY slice_index, tile_y, tile_x

Indeks yang selaras dengan pola ini:

CREATE INDEX idx_tiles_browse
ON atlas_tiles (dataset_id, axis, zoom_level, slice_index, tile_y, tile_x);

Dengan urutan tersebut, database bisa membatasi pencarian lebih cepat dan berpotensi menghindari sort mahal.

Pagination: offset vs keyset pada atlas 3D

Masalah OFFSET saat data besar

OFFSET terlihat sederhana:

SELECT id, tile_x, tile_y, payload_url
FROM atlas_tiles
WHERE dataset_id = 42
  AND axis = 'z'
  AND zoom_level = 6
ORDER BY slice_index, tile_y, tile_x
LIMIT 200 OFFSET 20000;

Masalahnya, database tetap perlu melangkahi banyak baris sebelum mengembalikan 200 baris terakhir. Pada data besar, ini membuat latensi membengkak. Untuk UI atlas, offset juga rentan menghasilkan pengalaman yang tidak stabil saat data berubah di tengah navigasi.

Kapan keyset pagination lebih tepat

Jika urutan hasil stabil dan deterministik, keyset pagination biasanya lebih murah. Misalnya gunakan tuple terakhir sebagai penanda:

SELECT id, slice_index, tile_y, tile_x, payload_url
FROM atlas_tiles
WHERE dataset_id = 42
  AND axis = 'z'
  AND zoom_level = 6
  AND (
    slice_index > 180 OR
    (slice_index = 180 AND tile_y > 12) OR
    (slice_index = 180 AND tile_y = 12 AND tile_x > 20)
  )
ORDER BY slice_index, tile_y, tile_x
LIMIT 200;

Dengan indeks yang sesuai, database tidak perlu memproses seluruh baris sebelumnya. Ini cocok untuk:

scroll atau navigasi berurutan,
pengambilan tile batch berikutnya,
API yang melayani viewer interaktif.

Trade-off:

OFFSET mudah dipakai untuk lompat ke halaman sembarang.
keyset lebih cepat untuk dataset besar, tetapi lebih sulit jika UI benar-benar butuh nomor halaman absolut.
keyset mensyaratkan ORDER BY yang stabil; jika ada nilai duplikat, tambahkan id sebagai tie-breaker.

Strategi COUNT yang lebih murah

Banyak sistem atlas melakukan COUNT(*) untuk menampilkan jumlah hasil filter, total tile, atau jumlah anotasi per slice. Ini sering tampak sepele, padahal dalam beberapa kasus justru menjadi query termahal.

Kurangi COUNT penuh jika UI tidak benar-benar membutuhkannya

Tanyakan lebih dulu: apakah UI butuh total persis, atau cukup tahu “masih ada halaman berikutnya”? Jika hanya perlu tombol next, ambil LIMIT n+1 lalu lihat apakah ada satu baris ekstra.

SELECT id, tile_x, tile_y, payload_url
FROM atlas_tiles
WHERE dataset_id = 42
  AND axis = 'z'
  AND slice_index = 180
  AND zoom_level = 6
ORDER BY tile_y, tile_x
LIMIT 257;

Jika hasil 257 baris, artinya masih ada halaman berikutnya. Ini menghindari COUNT(*) terpisah.

Simpan agregasi jika hitungan sering dipakai

Untuk kebutuhan seperti jumlah tile per dataset_id, axis, slice_index, atau region_id, pertimbangkan tabel agregat:

CREATE TABLE atlas_tile_counts (
  dataset_id BIGINT NOT NULL,
  axis CHAR(1) NOT NULL,
  slice_index INT NOT NULL,
  zoom_level INT NOT NULL,
  region_id BIGINT NULL,
  tile_count BIGINT NOT NULL,
  PRIMARY KEY (dataset_id, axis, slice_index, zoom_level, region_id)
);

Tabel ini bisa diisi saat ingest, melalui job batch, atau pipeline ETL. Cocok jika data relatif append-only atau pembaruan tidak terlalu sering.

Trade-off: Anda menukar biaya baca yang mahal dengan kompleksitas sinkronisasi data agregat.

Gunakan hitungan perkiraan hanya bila memang cukup

Untuk dashboard internal atau indikator kasar, hitungan perkiraan kadang memadai. Namun untuk aplikasi medis atau viewer ilmiah, berhati-hatilah: pembaca sering mengasumsikan angka yang tampil adalah angka persis.

Pola query yang memicu sort mahal

Salah satu sumber latensi yang sering diabaikan adalah ORDER BY yang tidak sejalan dengan indeks. Contoh anti-pattern:

SELECT id, tile_x, tile_y, payload_url
FROM atlas_tiles
WHERE dataset_id = 42
  AND axis = 'z'
  AND slice_index = 180
ORDER BY updated_at DESC
LIMIT 100;

Jika tidak ada indeks yang mendukung filter tersebut sekaligus urutan updated_at DESC, database bisa:

memindai banyak baris yang cocok dulu,
lalu menyortirnya di memori,
atau membuang ke disk bila hasil antara terlalu besar.

Ini sangat mahal jika query sebenarnya hanya butuh sebagian kecil data.

Hindari ORDER BY pada kolom yang tidak relevan dengan kebutuhan akses

Pada viewer atlas, urutan paling masuk akal biasanya mengikuti layout render: slice_index, tile_y, tile_x. Menambahkan sort lain hanya karena “sekalian mau terbaru” sering merusak efisiensi query utama.

Waspadai fungsi pada kolom terindeks

Contoh lain:

SELECT id
FROM atlas_tiles
WHERE dataset_id = 42
  AND LOWER(axis) = 'z';

Jika kolom disimpan konsisten, penggunaan fungsi seperti LOWER() dapat membuat indeks biasa sulit dipakai. Simpan data dalam format normal sejak awal, atau gunakan pendekatan indexing yang memang sesuai dengan kebutuhan tersebut.

Cara membaca EXPLAIN untuk diagnosis nyata

EXPLAIN membantu melihat apakah query memakai indeks yang diharapkan, berapa banyak baris yang diperkirakan dipindai, dan apakah ada sort atau langkah mahal lain. Format output berbeda antar database, tetapi ide utamanya sama.

Apa yang perlu dicari

Akses indeks atau full scan: apakah query memakai index seek/range scan, atau justru scan tabel besar?
Perkiraan jumlah baris: jika terlalu besar dibanding hasil akhir, selektivitas indeks mungkin buruk.
Sort tambahan: cari indikasi adanya operasi sort eksplisit.
Filter setelah scan: artinya database membaca banyak baris dulu lalu membuang sebagian besar.
Join order: jika ada join, lihat apakah tabel terbesar diproses terlalu dini.

Contoh pembacaan sederhana

Misalkan query Anda memfilter dataset_id, axis, zoom_level, dan slice_index, tetapi EXPLAIN menunjukkan scan pada indeks yang hanya diawali dataset_id. Itu tanda bahwa indeks yang tersedia tidak cocok dengan pola query penuh. Jika plan juga menunjukkan sort terpisah untuk ORDER BY tile_y, tile_x, maka indeks baru seperti berikut patut diuji:

CREATE INDEX idx_tiles_viewer_fetch
ON atlas_tiles (dataset_id, axis, zoom_level, slice_index, tile_y, tile_x);

Setelah itu, jalankan EXPLAIN lagi dan bandingkan:

apakah estimasi baris turun,
apakah sort tambahan hilang,
apakah langkah filter pasca-scan berkurang.

Jangan berhenti pada plan saja. Selalu uji dengan parameter yang realistis, karena query atlas sering punya distribusi data yang timpang antar region atau zoom.

Anti-pattern umum pada optimasi query atlas 3D

Mengandalkan indeks tunggal per kolom untuk query multi-dimensi.
Membuat terlalu banyak indeks mirip tanpa mengukur dampaknya pada tulis dan storage.
OFFSET besar untuk data yang diakses berurutan.
COUNT(*) di setiap request meski UI hanya butuh status “ada halaman berikutnya”.
ORDER BY yang tidak sesuai dengan pola akses viewer.
SELECT * padahal UI hanya butuh 3-4 kolom.
Fungsi pada kolom filter yang membuat indeks sulit dimanfaatkan.
Skema generik berlebihan seperti satu kolom JSON besar untuk atribut yang sebenarnya sering difilter.

Checklist diagnosis bottleneck

Sebelum menambah indeks atau mempartisi tabel, periksa hal-hal berikut:

Query mana yang benar-benar lambat? Ambil dari slow query log atau tracing aplikasi, bukan asumsi.
Pola filter dominan apa? Misalnya per-slice, per-region, atau per-zoom.
Apakah ORDER BY selaras dengan indeks?
Apakah hasil yang diminta terlalu lebar? Kurangi kolom yang diambil.
Apakah OFFSET besar dipakai? Evaluasi keyset.
Apakah COUNT terpisah benar-benar diperlukan?
Apakah EXPLAIN menunjukkan full scan, sort, atau filter pasca-scan besar?
Apakah satu query lambat hanya pada parameter tertentu? Cek skew data pada region atau slice tertentu.
Apakah penambahan indeks akan memperberat ingest? Ukur biaya tulis juga.
Apakah bottleneck sebenarnya di luar database? Misalnya serialisasi payload, transfer objek besar, atau cache tile yang buruk.

Kapan perlu partisi atau pre-aggregation

Partisi cocok jika data memang alami terbelah

Partisi layak dipertimbangkan jika volume data sangat besar dan pola akses sering menyentuh subset yang jelas, misalnya per dataset_id, per axis, atau rentang slice_index. Dengan desain yang tepat, database bisa memangkas partisi yang tidak relevan dan mengurangi data yang perlu dipindai.

Namun partisi bukan pengganti indeks. Query di dalam satu partisi tetap perlu indeks yang sesuai. Selain itu, terlalu banyak partisi juga bisa menambah kompleksitas operasional.

Pre-aggregation cocok untuk ringkasan yang dibaca berulang

Jika aplikasi sering menampilkan statistik seperti jumlah tile per region, daftar slice aktif, atau heatmap kepadatan anotasi, maka pre-aggregation biasanya lebih masuk akal dibanding menghitung ulang dari tabel detail setiap kali request masuk.

Pendekatan ini sangat berguna bila:

beban baca jauh lebih tinggi daripada tulis,
data sumber relatif stabil setelah ingest,
ringkasan yang dibutuhkan bentuknya tetap dan berulang.

Jika datanya sangat dinamis, biaya menjaga konsistensi agregat bisa menghapus keuntungan yang diharapkan.

Contoh paket indeks yang pragmatis

Untuk sistem yang fokus pada navigasi viewer dan filter region, pendekatan awal yang pragmatis bisa seperti ini:

-- Query viewer utama: per dataset, axis, zoom, slice, urut tile
CREATE INDEX idx_tiles_viewer
ON atlas_tiles (dataset_id, axis, zoom_level, slice_index, tile_y, tile_x);

-- Query dengan filter region dominan
CREATE INDEX idx_tiles_region_viewer
ON atlas_tiles (dataset_id, axis, region_id, zoom_level, slice_index, tile_y, tile_x);

-- Query riwayat sinkronisasi atau invalidasi cache bila perlu
CREATE INDEX idx_tiles_updated
ON atlas_tiles (dataset_id, updated_at);

Ini bukan resep universal, tetapi titik awal yang jauh lebih masuk akal dibanding membuat indeks acak berdasarkan intuisi.

Penutup

Optimasi query atlas 3D hampir selalu kembali ke satu prinsip: susun indeks mengikuti pola akses nyata, bukan sekadar struktur tabel. Untuk operasi slice, filter, dan zoom, indeks komposit yang tepat sering memberi dampak lebih besar daripada menambah hardware. Setelah itu, kurangi OFFSET besar, hindari COUNT mahal bila tidak diperlukan, pastikan ORDER BY tidak memicu sort mahal, dan biasakan membaca EXPLAIN sebelum menebak-nebak.

Jika data atlas terus membesar, langkah berikutnya adalah memutuskan secara sadar kapan cukup dengan tuning indeks, kapan perlu pre-aggregation, dan kapan partisi memang layak. Urutannya penting: ukur query nyata, perbaiki desain akses, lalu tambah kompleksitas hanya bila ada bukti bahwa sistem memang membutuhkannya.

Quick Search

shopping_cart Keranjang