Menulis kueri optimal

Pengantar Redshift

Jason Myers

Principal Engineer

Batasi kolom

Hindari SELECT *
Jangan pilih kolom yang tidak dibutuhkan di hasil
- Ingat Redshift bersifat kolumnar dan menarik data per kolom

Gunakan DISTKEY dan SORTKEY

Gunakan pada klausa berikut bila memungkinkan

JOIN
WHERE
GROUP BY

Gunakan SORTKEY berurutan di ORDER BY

Sangat optimal sortkey_1, sortkey_2, sortkey_3
Tidak optimal sort_key_1, sort_key_3

Kueri Terdistribusi

Membuat predikat yang baik

Gunakan DISTKEY dan SORTKEY
Dekatkan ke join tabel
Hindari fungsi di dalamnya

SELECT receipts.cookie_id, 
       sum(receipts.total)
FROM receipts
JOIN cookies ON receipts.cookie_id = cookies.cookie_id
  -- Keep cookies predicates in the join to push down to nodes holding the records for cookies
 AND cookies.available_on < '2023-11-14'
 AND cookies.end_of_sale IS null
-- Predicates that are not part of the join or on the joined table stay in the WHERE clause
WHERE receipts.order_time > '2023-11-13'
GROUP BY 1 ORDER BY 1;

Konsistenkan urutan kolom

Saat menggunakan:

GROUP BY
ORDER BY

Buruk

GROUP BY col_one, col_two, col_three
ORDER BY col_two, col_three, col_one

Baik

GROUP BY col_two, col_three, col_one
ORDER BY col_two, col_three, col_one

Gunakan subkueri dengan bijak

Gunakan strategi join yang tepat, bukan sekadar subkueri

Gunakan EXISTS di predikat saat hanya memeriksa kebenaran hasil subkueri

SELECT column_name
FROM table_name
WHERE EXISTS
  (SELECT column_name 
   FROM table_name 
   WHERE active is True);

Jika menulis ulang subkueri, gunakan CTE untuk memanfaatkan caching

Ayo berlatih!

Pengantar Redshift