AnalyticDB:Percepat kueri pada tabel berorientasi kolom menggunakan sort keys dan rough indexes

Pilih metode pengurutan

Gunakan panduan berikut untuk memilih metode yang tepat sesuai pola kueri Anda:

• Jika sebagian besar kueri melakukan filter pada kolom leading dari sort key, gunakan compound sorting. Metode ini menghasilkan waktu respons tercepat untuk predikat pada kolom leading. Perhatikan bahwa pengurutan ulang dengan compound sorting membutuhkan waktu lebih lama dibandingkan interleaved sorting karena melakukan analisis data tambahan.

• Jika kueri melakukan filter pada kolom non-leading atau pada subset arbitrer dari sort key, gunakan interleaved sorting. Sort key interleaved mendukung hingga delapan kolom. Semakin banyak kolom sort key yang direferensikan oleh kueri, semakin besar keuntungan performanya.

• Jika Anda tidak yakin, mulailah dengan compound sorting. Ini adalah pilihan yang lebih sederhana dan memberikan performa terbaik ketika kueri memiliki kolom filter leading yang jelas.

Benchmark TPC-H: dampak sort key pada kueri rentang

Bagian ini menunjukkan bagaimana compound sorting meningkatkan performa kueri untuk rough set indexes dibandingkan dengan full table scan, menggunakan tabel Lineitem TPC-H yang menyimpan data selama tujuh tahun.

Implementasi TPC ini berasal dari TPC Benchmark dan tidak dapat dibandingkan dengan hasil TPC Benchmark yang dipublikasikan, karena implementasi ini tidak memenuhi semua persyaratan TPC Benchmark.

Pengaturan pengujian:

1. Buat instans 32 node.

2. Tulis 13 miliar baris ke tabel Lineitem.

3. Lakukan kueri data dalam rentang waktu dari 1997-09-01 hingga 1997-09-30, bandingkan hasil ketika data diurutkan berdasarkan l_shipdate versus data yang tidak diurutkan.

Compound vs. interleaved: performa berdasarkan bentuk kueri

Contoh berikut menggunakan dua tabel dengan data dan sort key identik untuk menunjukkan bagaimana kedua metode ini berperforma pada berbagai bentuk kueri.

Pengaturan pengujian:

• Dua tabel (test dan test_multi), masing-masing dengan empat kolom: id, num1, num2, value

• Sort key: (id, num1, num2) pada kedua tabel

• 10 juta baris per tabel

• test diurutkan dengan compound sorting (SORT test)

• test_multi diurutkan dengan interleaved sorting (MULTISORT test_multi)

Buat tabel dan masukkan data:

CREATE TABLE test (id int, num1 int, num2 int, value varchar)
WITH (APPENDONLY=TRUE, ORIENTATION=column)
DISTRIBUTED BY (id)
ORDER BY (id, num1, num2);

CREATE TABLE test_multi (id int, num1 int, num2 int, value varchar)
WITH (APPENDONLY=TRUE, ORIENTATION=column)
DISTRIBUTED BY (id)
ORDER BY (id, num1, num2);

INSERT INTO test (id, num1, num2, value)
SELECT g,
    (random() * 10000000)::int,
    (random() * 10000000)::int,
    (ARRAY['foo', 'bar', 'baz', 'quux', 'boy', 'girl', 'mouse', 'child', 'phone'])[floor(random() * 10 + 1)]
FROM generate_series(1, 10000000) AS g;

INSERT INTO test_multi SELECT * FROM test;

SORT test;
MULTISORT test_multi;

Performa kueri titik

Ketiga kueri melakukan filter pada kolom sort key, tetapi pada posisi yang berbeda.

-- Q1: filter pada kolom leading (id)
SELECT * FROM test WHERE id = 100000;
SELECT * FROM test_multi WHERE id = 100000;

-- Q2: filter pada kolom kedua (num1)
SELECT * FROM test WHERE num1 = 8766963;
SELECT * FROM test_multi WHERE num1 = 8766963;

-- Q3: filter pada kolom kedua dan ketiga (num1, num2)
SELECT * FROM test WHERE num1 = 100000 AND num2 = 2904114;
SELECT * FROM test_multi WHERE num1 = 100000 AND num2 = 2904114;

Performa kueri rentang

-- Q1: filter rentang pada kolom leading (id)
SELECT count(*) FROM test WHERE id > 5000 AND id < 100000;
SELECT count(*) FROM test_multi WHERE id > 5000 AND id < 100000;

-- Q2: filter rentang pada kolom kedua (num1)
SELECT count(*) FROM test WHERE num1 > 5000 AND num1 < 100000;
SELECT count(*) FROM test_multi WHERE num1 > 5000 AND num1 < 100000;

-- Q3: filter rentang pada kolom kedua dan ketiga (num1, num2)
SELECT count(*) FROM test WHERE num1 > 5000 AND num1 < 100000 AND num2 < 100000;
SELECT count(*) FROM test_multi WHERE num1 > 5000 AND num1 < 100000 AND num2 < 100000;

Poin utama

• Compound sorting unggul pada kolom leading. Hasil Q1 menunjukkan compound sorting memiliki waktu respons kueri lebih singkat dibandingkan interleaved sorting ketika filter menargetkan kolom sort key pertama.

• Interleaved sorting unggul pada kolom non-leading. Hasil Q2 dan Q3 menunjukkan interleaved sorting jauh lebih unggul dibandingkan compound sorting ketika kueri melewati kolom leading.

• Interleaved sorting meningkat seiring jumlah kolom. Semakin banyak kolom sort key yang direferensikan oleh kueri, semakin besar keunggulan performa interleaved sorting (Q3 vs. Q2).

Pengujian ini menggunakan 10 juta baris—ukuran yang masih moderat untuk AnalyticDB for PostgreSQL. Perbedaan performa antara kedua metode ini lebih mencolok pada tabel yang lebih besar.