Fitur Baru di Query Planner Postgres 16

Fitur Baru di Query Planner PostgreSQL 16

• PostgreSQL 16 memperkenalkan banyak peningkatan pada query planner, sehingga banyak kueri SQL dapat dijalankan lebih cepat dibandingkan versi sebelumnya.
• Peningkatan planner ini dapat dilihat di catatan rilis PG16, tetapi karena setiap rilis PostgreSQL memiliki banyak perubahan, sulit untuk memberikan penjelasan rinci untuk masing-masing perubahan.
• Dalam posting blog ini, disajikan analisis mendalam tentang 10 peningkatan yang dilakukan pada query planner PostgreSQL 16, beserta perbandingan output planner PG15 dan PG16 serta contoh mengenai apa yang berubah.

10 peningkatan pada query planner PostgreSQL 16

• Incremental sort: Incremental sort, yang pertama kali diperkenalkan di PostgreSQL 13, memanfaatkan kondisi ketika sebagian himpunan hasil sudah terurut berdasarkan satu atau lebih kolom awal, sehingga pengurutan hanya dilakukan untuk kolom sisanya.
• Agregasi yang memanfaatkan data terurut: Query planner PostgreSQL 16 kini mencoba membentuk rencana yang memasok baris ke node agregasi dalam urutan yang sudah terurut.
• Memoization: Node rencana memoization, yang pertama kali diperkenalkan di PostgreSQL 14, berfungsi sebagai lapisan cache untuk menghindari pencarian nilai yang berulang.
• Anti join: PostgreSQL 16 memungkinkan hashing pada tabel yang lebih kecil saat menjalankan anti join.
• Parallel hash join: PostgreSQL 16 mendukung parallel hash join untuk tipe join FULL dan RIGHT.
• Optimisasi window function: PostgreSQL 16 memungkinkan penggunaan window function yang lebih cepat saat memakai mode ROWS dibanding mode RANGE.
• Optimisasi window function yang selalu meningkat: PostgreSQL 16 memperluas optimisasi untuk window function seperti ntile(), cume_dist(), dan percent_rank().
• Join removal pada tabel partisi: PostgreSQL 16 mengizinkan optimisasi penghapusan LEFT JOIN pada tabel partisi.
• Penggunaan Limit untuk kueri DISTINCT: Query planner PostgreSQL 16 tidak menyertakan node rencana untuk penghapusan duplikasi hasil ketika dapat mendeteksi bahwa semua baris berisi nilai yang sama.
• Pelonggaran aturan untuk Merge Join: Query planner PostgreSQL 16, saat mempertimbangkan Merge Join, memeriksa apakah setidaknya satu kolom awal telah diurutkan dengan benar, alih-alih mensyaratkan urutan baris harus benar-benar sama.

Opini GN⁺

• Peningkatan query planner di PostgreSQL 16 memainkan peran penting dalam meningkatkan performa database. Khususnya, fitur seperti incremental sort dan memoization memungkinkan eksekusi kueri kompleks menjadi lebih efisien.
• Peningkatan ini akan sangat berguna bagi developer dan administrator database yang menggunakan PostgreSQL, terutama pada sistem yang menangani data dalam skala besar, di mana peningkatan performa akan lebih terasa.
• Upaya inovasi dan perbaikan berkelanjutan dari komunitas PostgreSQL terus mendorong perkembangan teknologi database open source, yang pada akhirnya menyediakan solusi pengelolaan data yang lebih baik bagi pengguna dan perusahaan.

[GN⁺]

Komentar Hacker News

• Ada pendapat bahwa akan bagus jika perencana kueri Postgres bisa merencanakan ulang kueri di tengah eksekusi. Karena kurangnya informasi tentang distribusi data, rencana kueri yang tidak efisien bisa dibuat, dan ini dapat sangat memengaruhi waktu eksekusi. Jika kueri berjalan lebih lambat dari perkiraan, diperlukan fitur untuk merencanakan ulang kueri berdasarkan informasi progres saat ini. Namun, karena Postgres mendukung kueri streaming, mengubah rencana di tengah eksekusi memerlukan perubahan infrastruktur yang cukup besar.
• Ada pengguna yang mengatakan mereka memakai explain.dalibo.com dan www.pgexplain.dev sebagai alat visualisasi kueri. Kedua alat tersebut memberikan hasil keluaran yang serupa.
• Ada pendapat bahwa perbaikan perencana kueri adalah bagian penting dalam database, tetapi biasanya baru terlihat ketika tidak bekerja sesuai harapan. Pengalaman yang dibagikan menyebutkan bahwa kompiler JIT (Just-In-Time) pada versi Postgres terbaru tampaknya memiliki heuristik waktu penggunaan yang kurang tangguh, dan bisa memperlambat data kecil, sehingga lebih baik menonaktifkan JIT.
• Ada pendapat yang mempertanyakan seberapa sering perubahan ini efektif pada kueri nyata, khususnya apakah perubahan seperti "menggunakan Limit alih-alih DISTINCT saat memungkinkan" benar-benar pernah diterapkan dalam praktik. Ada pertanyaan apakah pengembang Postgres memiliki informasi tentang hal ini.
• Ada pendapat bahwa akan bagus jika ada "mode ketat" untuk pengujian aplikasi. Dalam mode ini, jika tidak ada indeks yang dapat memperbaiki kueri, sistem akan mengembalikan error, dan indeks yang diperlukan bisa dibuat melalui perintah CREATE INDICES FOR <sql>. Juga diusulkan mode pembuatan indeks otomatis untuk pengembangan dan penggunaan interaktif.
• Teman dari seorang pengguna bekerja sebagai DBA Microsoft untuk UKM dan mengklaim bahwa pekerjaan serius tidak bisa dilakukan dengan Postgres. Ia juga disebut terkejut karena mengira Postgres tidak punya perencana kueri, tetapi itu adalah informasi yang salah. Ada pertanyaan tentang seberapa dapat dipercayanya klaim bahwa MSSQL bisa menangani pekerjaan dalam skala lebih besar daripada Postgres.
• Muncul pertanyaan mengapa Postgres tidak mengimplementasikan hint.
• Ada pertanyaan mengapa fitur yang diumumkan Citus Data muncul di tempat lain, bukan di postgresql.org, dan apakah itu fitur berbayar atau tambahan open source.
• Ada pertanyaan tentang kapan Postgres bisa menggunakan indeks untuk mempercepat kueri IS NOT DISTINCT FROM.
• Satu komentar dilaporkan sehingga ditandai (flagged).