Adakah anda ingat bagaimana semuanya bermula. Semuanya buat pertama kali dan sekali lagi

Mengenai bagaimana saya perlu mengoptimumkan pertanyaan PostgreSQL dan perkara yang terhasil daripada semuanya.
Kenapa awak terpaksa? Ya, kerana selama 4 tahun sebelumnya semuanya berjalan dengan tenang, tenang, seperti jam berdetik.
Sebagai epigraf.

Berdasarkan peristiwa sebenar.
Semua nama telah ditukar, kebetulan adalah rawak.

Apabila anda mencapai hasil tertentu, ia sentiasa menarik untuk mengingati apa dorongan untuk permulaan, di mana semuanya bermula.

Jadi, apa yang berlaku sebagai hasilnya diterangkan secara ringkas dalam artikel "Sintesis sebagai salah satu kaedah untuk meningkatkan prestasi PostgreSQL'.

Mungkin menarik untuk mencipta semula rangkaian acara sebelumnya.
Sejarah menyimpan tarikh mula yang tepat - 2018-09-10 18:02:48.
Juga, dalam cerita itu ada permintaan dari mana semuanya bermula:
Permintaan masalahSELECT
p.“PARAMETER_ID” sebagai parameter_id,
pd."PD_NAME" SEBAGAI pd_name,
pd."CUSTOMER_PARTNUMBER" SEBAGAI nombor_bahagian pelanggan,
w."LRM" SEBAGAI LRM,
w. "LOTID" SEBAGAI lotid,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” SEBAGAI lower_spec_limit,
w.“UPPER_SPEC_LIMIT” SEBAGAI had_spec_atas,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s.“SPENT_DATE” AS spent_date,
ekstrak(tahun dari "SPENT_DATE") AS tahun,
ekstrak(bulan dari "SPENT_DATE") sebagai bulan,
s."REPORT_NAME" SEBAGAI nama_laporan,
p."STPM_NAME" SEBAGAI stpm_name,
p.“CUSTOMERPARAM_NAME” SEBAGAI customerparam_name
DARIPADA wdata w,
menghabiskan s,
pmtr p,
spent_pd sp,
pd pd
DI MANA 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.“SPENT_DATE” >= '2018-07-01' DAN s.“SPENT_DATE” <= '2018-09-30'
dan s.“SPENT_DATE” = (PILIH MAX(s2.“SPENT_DATE”)
DARIPADA menghabiskan s2,
wdata w2
DI MANA s2.“SPENT_ID” = w2.“SPENT_ID”
DAN w2.“LRM” = w.“LRM”);

Penerangan mengenai masalah ini boleh diramalkan standard - “Semuanya buruk. Beritahu saya apa masalahnya.”
Saya segera teringat anekdot dari masa pemacu 3 setengah inci:

Lamer datang kepada penggodam.
-Tiada apa-apa yang berkesan untuk saya, beritahu saya di mana masalahnya.
-Dalam DNA...

Tetapi sudah tentu, ini bukan cara untuk menyelesaikan insiden prestasi. “Mereka mungkin tidak memahami kita"(Dengan). Kita perlu memikirkannya.
Baiklah, mari kita gali. Mungkin sesuatu akan terkumpul akibatnya.

Siasatan bermula

Jadi, apa yang boleh dilihat serta-merta dengan mata kasar, tanpa perlu pun MENJELASKAN.
1) JOIN tidak digunakan. Ini buruk, terutamanya jika bilangan sambungan lebih daripada satu.
2) Tetapi yang lebih teruk lagi adalah subkueri berkorelasi, lebih-lebih lagi, dengan pengagregatan. Ini sangat teruk.
Ini sudah tentu buruk. Tetapi ini hanya di satu pihak. Sebaliknya, ini sangat baik, kerana masalah itu jelas mempunyai penyelesaian dan permintaan yang boleh diperbaiki.
Jangan pergi ke tukang tilik (C).
Pelan pertanyaan tidak begitu rumit, tetapi ia agak menunjukkan:
Pelan Perlaksanaan

Yang paling menarik dan berguna, seperti biasa, adalah pada permulaan dan akhir.
Gelung Bersarang (kos=935.84..479763226.18 baris=3322 lebar=135) (masa sebenar=31.536..8220420.295 baris=8111656 gelung=1)
Masa perancangan: 3.807 ms
Masa pelaksanaan: 8222351.640 ms
Masa siap lebih dari 2 jam.

Hipotesis palsu yang mengambil masa

Hipotesis 1 - Pengoptimum membuat kesilapan dan membina rancangan yang salah.

Untuk menggambarkan rancangan pelaksanaan, kami akan menggunakan tapak tersebut https://explain.depesz.com/. Walau bagaimanapun, laman web itu tidak menunjukkan apa-apa yang menarik atau berguna. Pada pandangan pertama dan kedua, tiada apa yang boleh membantu. Adakah mungkin Imbasan Penuh adalah minimum. Teruskan.

Hipotesis 2-Kesan pada pangkalan dari bahagian autovakum, anda perlu menyingkirkan brek.

Tetapi daemon autovakum berkelakuan baik, tidak ada proses yang bertahan lama. Tiada beban yang serius. Kita perlu mencari sesuatu yang lain.

Hipotesis 3 - Statistik sudah lapuk, semuanya perlu dikira semula

Sekali lagi, bukan itu. Statistik adalah terkini. Yang, memandangkan kekurangan masalah dengan autovakum, tidak menghairankan.

Mari kita mula mengoptimumkan

Jadual utama 'wdata' pastinya tidak kecil, hampir 3 juta rekod.
Dan jadual inilah yang diikuti oleh Imbasan Penuh.

Hash Cond: ((w."SPENT_ID" = s."SPENT_ID") DAN ((SubPlan 1) = s."SPENT_DATE"))
-> Seq Scan pada wdata w (kos=0.00..574151.49 baris=26886249 lebar=46) (masa sebenar=0.005..8153.565 baris=26873950 gelung=1)
Kami melakukan perkara standard: "ayo, mari buat indeks dan semuanya akan terbang."
Mencipta indeks pada medan "SPENT_ID".
Akibatnya:
Pelan pelaksanaan pertanyaan menggunakan indeks

Nah, adakah ia membantu?
Adakah: 8 222 351.640 ms (lebih sedikit daripada 2 jam)
menjadi: 6 985 431.575 ms (hampir 2 jam)
Secara umum, epal yang sama, pandangan sisi.
Mari kita ingat klasik:
“Adakah anda mempunyai yang sama, tetapi tanpa sayap? Akan mencari".

Pada dasarnya, ini boleh dipanggil hasil yang baik, baik, tidak baik, tetapi boleh diterima. Sekurang-kurangnya, sediakan laporan besar kepada pelanggan yang menerangkan berapa banyak yang telah dilakukan dan mengapa perkara yang dilakukan itu adalah baik.
Namun begitu, keputusan muktamad masih jauh. Sangat jauh.

Dan kini perkara yang paling menarik - kami terus mengoptimumkan, kami akan menggilap permintaan itu

Langkah Pertama - Gunakan JOIN

Permintaan yang ditulis semula kini kelihatan seperti ini (baik sekurang-kurangnya lebih cantik):
Pertanyaan menggunakan JOINSELECT
p.“PARAMETER_ID” sebagai parameter_id,
pd."PD_NAME" SEBAGAI pd_name,
pd."CUSTOMER_PARTNUMBER" SEBAGAI nombor_bahagian pelanggan,
w."LRM" SEBAGAI LRM,
w. "LOTID" SEBAGAI lotid,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” SEBAGAI lower_spec_limit,
w.“UPPER_SPEC_LIMIT” SEBAGAI had_spec_atas,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s.“SPENT_DATE” AS spent_date,
ekstrak(tahun dari "SPENT_DATE") AS tahun,
ekstrak(bulan dari "SPENT_DATE") sebagai bulan,
s."REPORT_NAME" SEBAGAI nama_laporan,
p."STPM_NAME" SEBAGAI stpm_name,
p.“CUSTOMERPARAM_NAME” SEBAGAI customerparam_name
DARI wdata w Inner JOIN dibelanjakan s ON w.“SPENT_ID”=s.”“SPENT_ID”
SERTAI DALAM pmtr p PADA ms.“ID_PARAMETER” = w.“ID_PARAMETER”
SERTAI DALAM spend_pd sp HIDUP s.“SPENT_ID” = sp.“SPENT_ID”
SERTAI DALAM pd pd PADA pd.“PD_ID” = sp.“PD_ID”
DIMANA
s.“SPENT_DATE” >= '2018-07-01' DAN s.“SPENT_DATE” <= '2018-09-30'DAN
s.“SPENT_DATE” = (PILIH MAX(s2.“SPENT_DATE”)
DARI wdata w2 SERTAI DALAM membelanjakan s2 PADA w2.“SPENT_ID”=s2.“SPENT_ID”
DALAM JOIN wdata w
PADA w2.“LRM” = w.“LRM” );
Masa perancangan: 2.486 ms
Masa pelaksanaan: 1223680.326 ms

Jadi, hasil pertama.
Adakah: 6 ms (hampir 985 jam).
menjadi: 1 223 680.326 ms (hanya lebih 20 minit).
Hasil yang baik. Pada dasarnya, sekali lagi, kita boleh berhenti di sana. Tetapi ia sangat tidak menarik, anda tidak boleh berhenti.
UNTUK

Langkah kedua - buang subquery yang berkaitan

Teks permintaan diubah:
Tanpa subkueri berkorelasiSELECT
p.“PARAMETER_ID” sebagai parameter_id,
pd."PD_NAME" SEBAGAI pd_name,
pd."CUSTOMER_PARTNUMBER" SEBAGAI nombor_bahagian pelanggan,
w."LRM" SEBAGAI LRM,
w. "LOTID" SEBAGAI lotid,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” SEBAGAI lower_spec_limit,
w.“UPPER_SPEC_LIMIT” SEBAGAI had_spec_atas,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s.“SPENT_DATE” AS spent_date,
ekstrak(tahun dari "SPENT_DATE") AS tahun,
ekstrak(bulan dari "SPENT_DATE") sebagai bulan,
s."REPORT_NAME" SEBAGAI nama_laporan,
p."STPM_NAME" SEBAGAI stpm_name,
p.“CUSTOMERPARAM_NAME” SEBAGAI customerparam_name
DARI wdata w SERTAI DALAM yang dibelanjakan untuk s.“SPENT_ID” = w.“SPENT_ID”
SERTAI DALAM pmtr p PADA ms.“ID_PARAMETER” = w.“ID_PARAMETER”
SERTAI DALAM spend_pd sp HIDUP s.“SPENT_ID” = sp.“SPENT_ID”
SERTAI DALAM pd pd PADA pd.“PD_ID” = sp.“PD_ID”
SERTAI DALAM (PILIH w2.“LRM”, MAX(s2.“SPENT_DATE”)
DARI perbelanjaan s2 DALAM SERTAI wdata w2 PADA s2.“SPENT_ID” = w2.“SPENT_ID”
KUMPULAN OLEH w2.“LRM”
) md pada w.“LRM” = md.“LRM”
DIMANA
s."SPENT_DATE" >= '2018-07-01' DAN s."SPENT_DATE" <= '2018-09-30';
Masa perancangan: 2.291 ms
Masa pelaksanaan: 165021.870 ms

Adakah: 1 223 680.326 ms (hanya lebih 20 minit).
menjadi: 165 021.870 ms (lebih daripada 2 minit).
Ini sudah cukup bagus.
Walau bagaimanapun, seperti yang dikatakan oleh British "Tetapi, sentiasa ada tetapi" Keputusan yang terlalu baik akan secara automatik menimbulkan syak wasangka. Ada yang tidak kena di sini.

Hipotesis tentang membetulkan pertanyaan untuk menyingkirkan subkueri berkorelasi adalah betul. Tetapi anda perlu mengubahnya sedikit untuk keputusan akhir menjadi betul.
Akibatnya, hasil perantaraan pertama:
Pertanyaan yang disunting tanpa subkueri berkorelasiSELECT
p.“PARAMETER_ID” sebagai parameter_id,
pd."PD_NAME" SEBAGAI pd_name,
pd."CUSTOMER_PARTNUMBER" SEBAGAI nombor_bahagian pelanggan,
w."LRM" SEBAGAI LRM,
w. "LOTID" SEBAGAI lotid,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” SEBAGAI lower_spec_limit,
w.“UPPER_SPEC_LIMIT” SEBAGAI had_spec_atas,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s.“SPENT_DATE” AS spent_date,
ekstrak(tahun dari s.“SPENT_DATE”) AS tahun,
ekstrak(bulan dari s.“SPENT_DATE”) sebagai bulan,
s."REPORT_NAME" SEBAGAI nama_laporan,
p."STPM_NAME" SEBAGAI stpm_name,
p.“CUSTOMERPARAM_NAME” SEBAGAI customerparam_name
DARI wdata w SERTAI DALAM yang dibelanjakan untuk s.“SPENT_ID” = w.“SPENT_ID”
SERTAI DALAM pmtr p PADA ms.“ID_PARAMETER” = w.“ID_PARAMETER”
SERTAI DALAM spend_pd sp HIDUP s.“SPENT_ID” = sp.“SPENT_ID”
SERTAI DALAM pd pd PADA pd.“PD_ID” = sp.“PD_ID”
SERTAI DALAM ( PILIH w2. “LRM”, MAX(s2. “SPENT_DATE”) SEBAGAI “SPENT_DATE”
DARI perbelanjaan s2 DALAM SERTAI wdata w2 PADA s2.“SPENT_ID” = w2.“SPENT_ID”
KUMPULAN OLEH w2.“LRM”
) md PADA md.“SPENT_DATE” = s.“SPENT_DATE” DAN md.“LRM” = w.“LRM”
DIMANA
s."SPENT_DATE" >= '2018-07-01' DAN s."SPENT_DATE" <= '2018-09-30';
Masa perancangan: 3.192 ms
Masa pelaksanaan: 208014.134 ms

Jadi, apa yang kami dapati ialah hasil pertama yang boleh diterima, yang tidak memalukan untuk ditunjukkan kepada pelanggan:
Dimulakan dengan: 8 222 351.640 ms (lebih daripada 2 jam)
Kami berjaya mencapai: 1 ms (lebih sedikit daripada 223 minit).
Keputusan (interim): 208 014.134 ms (lebih dari 3 minit).

Keputusan yang cemerlang.

Jumlah

Kami boleh berhenti di sana.
TAPI…
Selera makan datang dengan makan. Orang yang berjalan akan menguasai jalan. Sebarang keputusan adalah pertengahan. Berhenti dan mati. Dan lain-lain.
Mari teruskan pengoptimuman.
Idea bernas. Terutama memandangkan pelanggan tidak kisah. Dan walaupun kuat untuk itu.

Jadi, sudah tiba masanya untuk reka bentuk semula pangkalan data. Struktur pertanyaan itu sendiri tidak lagi boleh dioptimumkan (walaupun, seperti yang ternyata kemudian, terdapat pilihan untuk memastikan bahawa semuanya benar-benar gagal). Tetapi untuk mula mengoptimumkan dan membangunkan reka bentuk pangkalan data sudah menjadi idea yang sangat menjanjikan. Dan yang paling penting menarik. Sekali lagi, ingat masa muda anda. Saya tidak segera menjadi DBA, saya membesar sebagai pengaturcara (BASIC, assembler, C, double-plus C, Oracle, plsql). Topik yang menarik, sudah tentu, untuk memoir yang berasingan ;-).
Namun, janganlah kita terganggu.

Oleh itu,

Atau mungkin pembahagian akan membantu kita?
Spoiler - "Ya, ia membantu, termasuk dalam mengoptimumkan prestasi."

Tetapi itu cerita yang sama sekali berbeza...

Akan bersambung…

Sumber: www.habr.com