Apakah Anda ingat bagaimana semuanya dimulai. Semuanya untuk pertama kalinya dan lagi

Tentang bagaimana kami harus mengoptimalkan kueri PostgreSQL dan apa hasilnya.
Mengapa Anda harus melakukannya? Ya, karena selama 4 tahun sebelumnya semuanya berjalan dengan tenang, tenang, seperti jam yang terus berdetak.
sebagai epigraf.

Berdasarkan peristiwa nyata.
Semua nama telah diubah, kebetulan acak.

Ketika Anda mencapai hasil tertentu, selalu menarik untuk mengingat apa yang mendorong permulaan, di mana semuanya dimulai.

Jadi, apa yang terjadi sebagai akibatnya dijelaskan secara singkat di artikel “Sintesis sebagai salah satu metode untuk meningkatkan kinerja PostgreSQL'.

Mungkin akan menarik untuk menciptakan kembali rangkaian peristiwa sebelumnya.
Riwayat menyimpan tanggal mulai yang tepat - 2018-09-10 18:02:48.
Juga dalam cerita ada permintaan dari mana semuanya dimulai:
Permintaan masalahMEMILIH
p.“PARAMETER_ID” sebagai parameter_id,
pd."PD_NAME" SEBAGAI nama_pd,
pd."CUSTOMER_PARTNUMBER" SEBAGAI nomor pelanggan_part,
w."LRM" SEBAGAI LRM,
w. "LOTID" SEBAGAI lotid,
w."RTD_VALUE" SEBAGAI nilai_RTD,
w.“LOWER_SPEC_LIMIT” SEBAGAI batas_spesifikasi_lebih rendah,
w."UPPER_SPEC_LIMIT" SEBAGAI batas_spesifikasi_atas,
p."TYPE_CALCUL" SEBAGAI type_calcul,
s."SPENT_NAME" SEBAGAI nama_belanja,
s.“SPENT_DATE” SEBAGAI tanggal_dibelanjakan,
ekstrak(tahun dari "SPENT_DATE") SEBAGAI tahun,
ekstrak(bulan dari "SPENT_DATE") sebagai bulan,
s."REPORT_NAME" SEBAGAI nama_laporan,
p."STPM_NAME" SEBAGAI nama_stpm,
hal.“CUSTOMERPARAM_NAME” SEBAGAI nama_param pelanggan
DARI data w,
menghabiskan s,
pmtr hal,
menghabiskan_pd sp,
pd pd
DIMANA s.“SPENT_ID” = w.“SPENT_ID”
DAN p."PARAMETER_ID" = w."PARAMETER_ID"
DAN s.“SPENT_ID” = sp.“SPENT_ID”
DAN pd."PD_ID" = sp."PD_ID"
DAN s.“TANGGAL_SPENT” >= '2018-07-01' DAN s.“TANGGAL_SPENT” <= '2018-09-30'
dan s.“SPENT_DATE” = (SELECT MAX(s2.“SPENT_DATE”)
DARI menghabiskan s2,
wdata w2
DIMANA s2.“SPENT_ID” = w2.“SPENT_ID”
DAN w2.“LRM” = w.“LRM”);

Deskripsi masalahnya bisa ditebak standar - “Semuanya buruk. Katakan padaku apa masalahnya.”
Saya langsung teringat sebuah anekdot dari masa drive 3 setengah inci:

Lamer mendatangi si peretas.
-Tidak ada yang berhasil untuk saya, beri tahu saya di mana masalahnya.
-Dalam DNA...

Namun tentu saja, ini bukan cara untuk menyelesaikan insiden kinerja. “Mereka mungkin tidak memahami kita" (Dengan). Kita perlu mencari tahu.
Baiklah, mari kita gali. Mungkin sesuatu akan terakumulasi sebagai hasilnya.

Investigasi dimulai

Jadi, apa yang bisa langsung dilihat dengan mata telanjang, tanpa harus MENJELASKAN.
1) GABUNG tidak digunakan. Ini buruk, apalagi jika jumlah koneksinya lebih dari satu.
2) Namun yang lebih buruk lagi adalah subkueri yang berkorelasi, apalagi dengan agregasi. Ini sangat buruk.
Tentu saja ini buruk. Tapi ini hanya di satu sisi. Di sisi lain, hal ini sangat baik, karena masalah tersebut jelas ada solusinya dan ada permintaan yang bisa diperbaiki.
Jangan pergi ke peramal (C).
Rencana kuerinya tidak terlalu rumit, namun cukup indikatif:
Rencana eksekusi

Yang paling menarik dan bermanfaat, seperti biasa, ada di awal dan akhir.
Loop Bersarang (biaya=935.84..479763226.18 baris=3322 lebar=135) (waktu sebenarnya=31.536..8220420.295 baris=8111656 loop=1)
Waktu perencanaan: 3.807 ms
Waktu eksekusi: 8222351.640 ms
Waktu penyelesaian lebih dari 2 jam.

Hipotesis palsu yang membutuhkan waktu

Hipotesis 1 - Pengoptimal membuat kesalahan dan membuat rencana yang salah.

Untuk memvisualisasikan rencana pelaksanaan, kami akan menggunakan situs ini https://explain.depesz.com/. Namun, situs tersebut tidak menampilkan sesuatu yang menarik atau berguna. Sekilas dan kedua, tidak ada yang bisa membantu. Apa mungkin Full Scannya minim. Teruskan.

Hipotesis 2-Dampak pada pangkalan dari sisi autovacuum, Anda perlu melepas rem.

Tetapi daemon autovacuum berperilaku baik, tidak ada proses yang memakan waktu lama. Tidak ada beban serius. Kita perlu mencari sesuatu yang lain.

Hipotesis 3 - Statistik sudah ketinggalan zaman, semuanya perlu dihitung ulang

Sekali lagi, bukan itu. Statistiknya terkini. Hal ini, mengingat tidak adanya masalah dengan autovacuum, tidak mengherankan.

Mari mulai mengoptimalkan

Tabel utama 'wdata' tentu tidak sedikit, hampir 3 juta record.
Dan tabel inilah yang diikuti oleh Pemindaian Penuh.

Tahap Hash: ((w."SPENT_ID" = s."SPENT_ID") DAN ((SubRencana 1) = s."SPENT_DATE"))
-> Pemindaian Seq pada wdata w (biaya=0.00..574151.49 baris=26886249 lebar=46) (waktu sebenarnya=0.005..8153.565 baris=26873950 loop=1)
Kami melakukan hal standar: “ayolah, mari kita buat indeks dan semuanya akan terbang.”
Membuat indeks pada kolom “SPENT_ID”.
Hasil dari:
Rencana eksekusi kueri menggunakan indeks

Apakah itu membantu?
Dulu: 8 222 351.640 mdtk (sedikit lebih dari 2 jam)
Menjadi: 6 985 431.575 ms (hampir 2 jam)
Secara umum apel sama, tampak samping.
Mari kita ingat yang klasik:
“Apakah kamu punya yang sama, tapi tanpa sayap? Akan mencari".

Prinsipnya, ini bisa disebut hasil yang baik, ya, tidak bagus, tapi bisa diterima. Paling tidak, berikan laporan besar kepada pelanggan yang menjelaskan seberapa banyak yang telah dilakukan dan mengapa apa yang dilakukan itu baik.
Tapi tetap saja, keputusan akhir masih jauh. Sangat jauh.

Dan sekarang yang paling menarik adalah kami terus mengoptimalkan dan memoles permintaan tersebut

Langkah Pertama - Gunakan GABUNG

Permintaan yang ditulis ulang sekarang terlihat seperti ini (setidaknya lebih indah):
Kueri menggunakan GABUNGMEMILIH
p.“PARAMETER_ID” sebagai parameter_id,
pd."PD_NAME" SEBAGAI nama_pd,
pd."CUSTOMER_PARTNUMBER" SEBAGAI nomor pelanggan_part,
w."LRM" SEBAGAI LRM,
w. "LOTID" SEBAGAI lotid,
w."RTD_VALUE" SEBAGAI nilai_RTD,
w.“LOWER_SPEC_LIMIT” SEBAGAI batas_spesifikasi_lebih rendah,
w."UPPER_SPEC_LIMIT" SEBAGAI batas_spesifikasi_atas,
p."TYPE_CALCUL" SEBAGAI type_calcul,
s."SPENT_NAME" SEBAGAI nama_belanja,
s.“SPENT_DATE” SEBAGAI tanggal_dibelanjakan,
ekstrak(tahun dari "SPENT_DATE") SEBAGAI tahun,
ekstrak(bulan dari "SPENT_DATE") sebagai bulan,
s."REPORT_NAME" SEBAGAI nama_laporan,
p."STPM_NAME" SEBAGAI nama_stpm,
hal.“CUSTOMERPARAM_NAME” SEBAGAI nama_param pelanggan
DARI wdata w INNER JOIN menghabiskan s ON w.“SPENT_ID”=s.”“SPENT_ID”
GABUNG DALAM pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN menghabiskan_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
MANA
s.“TANGGAL_SPENT” >= '2018-07-01' DAN s.“TANGGAL_SPENT” <= '2018-09-30'DAN
s.“SPENT_DATE” = (PILIH MAX(s2.“SPENT_DATE”)
DARI wdata w2 INNER JOIN menghabiskan s2 ON w2.“SPENT_ID”=s2.“SPENT_ID”
INNER GABUNG wdata w
PADA w2.“LRM” = w.“LRM” );
Waktu perencanaan: 2.486 ms
Waktu eksekusi: 1223680.326 ms

Jadi, hasil pertama.
Dulu: 6 ms (hampir 985 jam).
Menjadi: 1 223 680.326 ms (lebih dari 20 menit).
Hasil yang bagus. Pada prinsipnya, sekali lagi, kita bisa berhenti di situ. Tapi itu sangat tidak menarik, Anda tidak bisa berhenti.
UNTUK

Langkah kedua - singkirkan subquery yang berkorelasi

Teks permintaan diubah:
Tanpa subquery yang berkorelasiMEMILIH
p.“PARAMETER_ID” sebagai parameter_id,
pd."PD_NAME" SEBAGAI nama_pd,
pd."CUSTOMER_PARTNUMBER" SEBAGAI nomor pelanggan_part,
w."LRM" SEBAGAI LRM,
w. "LOTID" SEBAGAI lotid,
w."RTD_VALUE" SEBAGAI nilai_RTD,
w.“LOWER_SPEC_LIMIT” SEBAGAI batas_spesifikasi_lebih rendah,
w."UPPER_SPEC_LIMIT" SEBAGAI batas_spesifikasi_atas,
p."TYPE_CALCUL" SEBAGAI type_calcul,
s."SPENT_NAME" SEBAGAI nama_belanja,
s.“SPENT_DATE” SEBAGAI tanggal_dibelanjakan,
ekstrak(tahun dari "SPENT_DATE") SEBAGAI tahun,
ekstrak(bulan dari "SPENT_DATE") sebagai bulan,
s."REPORT_NAME" SEBAGAI nama_laporan,
p."STPM_NAME" SEBAGAI nama_stpm,
hal.“CUSTOMERPARAM_NAME” SEBAGAI nama_param pelanggan
DARI wdata w INNER JOIN menghabiskan s ON s.“SPENT_ID” = w.“SPENT_ID”
GABUNG DALAM pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN menghabiskan_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
GABUNG DALAM (PILIH w2.“LRM”, MAX(s2.“SPENT_DATE”)
DARI menghabiskan s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
KELOMPOK OLEH w2. “LRM”
) md pada w.“LRM” = md.“LRM”
MANA
s."SPENT_DATE" >= '2018-07-01' DAN s."SPENT_DATE" <= '2018-09-30';
Waktu perencanaan: 2.291 ms
Waktu eksekusi: 165021.870 ms

Dulu: 1 223 680.326 ms (lebih dari 20 menit).
Menjadi: 165 021.870 ms (lebih dari 2 menit).
Ini sudah cukup bagus.
Namun, seperti yang dikatakan orang Inggris "Tapi, selalu ada tapi" Hasil yang terlalu bagus otomatis menimbulkan kecurigaan. Ada yang salah di sini.

Hipotesis tentang mengoreksi kueri untuk menghilangkan subkueri yang berkorelasi adalah benar. Namun Anda perlu mengubahnya sedikit agar hasil akhirnya benar.
Hasilnya, hasil antara pertama:
Kueri yang diedit tanpa subkueri yang berkorelasiMEMILIH
p.“PARAMETER_ID” sebagai parameter_id,
pd."PD_NAME" SEBAGAI nama_pd,
pd."CUSTOMER_PARTNUMBER" SEBAGAI nomor pelanggan_part,
w."LRM" SEBAGAI LRM,
w. "LOTID" SEBAGAI lotid,
w."RTD_VALUE" SEBAGAI nilai_RTD,
w.“LOWER_SPEC_LIMIT” SEBAGAI batas_spesifikasi_lebih rendah,
w."UPPER_SPEC_LIMIT" SEBAGAI batas_spesifikasi_atas,
p."TYPE_CALCUL" SEBAGAI type_calcul,
s."SPENT_NAME" SEBAGAI nama_belanja,
s.“SPENT_DATE” SEBAGAI tanggal_dibelanjakan,
ekstrak(tahun dari s.“SPENT_DATE”) SEBAGAI tahun,
ekstrak(bulan dari s.“SPENT_DATE”) sebagai bulan,
s."REPORT_NAME" SEBAGAI nama_laporan,
p."STPM_NAME" SEBAGAI nama_stpm,
hal.“CUSTOMERPARAM_NAME” SEBAGAI nama_param pelanggan
DARI wdata w INNER JOIN menghabiskan s ON s.“SPENT_ID” = w.“SPENT_ID”
GABUNG DALAM pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN menghabiskan_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
INNER JOIN ( PILIH w2.“LRM”, MAX(s2.“SPENT_DATE”) SEBAGAI “SPENT_DATE”
DARI menghabiskan s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
KELOMPOK OLEH w2. “LRM”
) md PADA md.“SPENT_DATE” = s.“SPENT_DATE” DAN md.“LRM” = w.“LRM”
MANA
s."SPENT_DATE" >= '2018-07-01' DAN s."SPENT_DATE" <= '2018-09-30';
Waktu perencanaan: 3.192 ms
Waktu eksekusi: 208014.134 ms

Jadi, yang kami dapatkan adalah hasil pertama yang dapat diterima, yang tidak sayang untuk ditunjukkan kepada pelanggan:
Dimulai dengan: 8 222 351.640 ms (lebih dari 2 jam)
Kami berhasil mencapai: 1 ms (lebih dari 223 menit).
Hasil (sementara): 208 014.134 ms (lebih dari 3 menit).

Hasil yang sangat baik.

Total

Kita bisa saja berhenti di situ.
TETAPI…
Nafsu makan datang saat makan. Siapa yang berjalan, dialah yang menguasai jalannya. Hasil apa pun bersifat menengah. Berhenti dan mati. Dll.
Mari lanjutkan optimasi.
Ide yang hebat. Apalagi mengingat pelanggannya bahkan tidak keberatan. Dan bahkan sangat mendukungnya.

Jadi, inilah waktunya untuk mendesain ulang database. Struktur kueri itu sendiri tidak dapat lagi dioptimalkan (meskipun ternyata kemudian, ada opsi untuk memastikan bahwa semuanya benar-benar gagal). Namun untuk mulai mengoptimalkan dan mengembangkan desain database sudah merupakan ide yang sangat menjanjikan. Dan yang paling penting menarik. Sekali lagi, ingatlah masa mudamu. Saya tidak langsung menjadi DBA, saya tumbuh sebagai seorang programmer (BASIC, assembler, C, double-plus C, Oracle, plsql). Topik yang menarik tentunya untuk memoar tersendiri ;-).
Namun, jangan sampai kita teralihkan.

Dengan demikian,

Atau mungkin mempartisi akan membantu kita?
Spoiler - “Ya, itu membantu, termasuk dalam mengoptimalkan kinerja.”

Tapi itu cerita yang sama sekali berbeda...

Bersambung…

Sumber: www.habr.com