Deeper Understanding of PostgreSQL Execution Plan : At plan time and run time
Jobin Augustine is a PostgreSQL expert and Open Source advocate and has more than 18 years of working experience as consultant, architect, administrator, writer, and trainer in PostgreSQL, Oracle, and other database technologies. He has always been an active participant in the Open Source communities and his main focus area is database performance and optimization. He is a contributor to various Open Source Projects and is an active blogger and loves to code in C++ and Python.
Jobin holds a Masters in Computer Applications and joined Percona in 2018 as a Senior Support Engineer. Prior to joining Percona, he worked at OpenSCG for 2 years as Architect and was part of the BigSQL core team, a complete PostgreSQL distribution offering. Previous to his work at OpenSCG, Jobin worked at Dell as Database Senior Advisor for 10 years and 5 years with TCS/CMC.
No video of the event yet, sorry!
Right execution plans can deliver maximum performance and throughput from your PostgreSQL database. It is important to understand all factors which affect the execution plan which includes costing, datatypes, indexing, filter and join conditions, pruning, runtime exclusions, etc. Poor understanding of the planner leads to suboptimal performance. There are substantial improvements in new versions of PostgreSQL which everyone should be aware about. This is a talk + small demonstrations of few cases how plan changes/behaves. This talk covers improvements in PostgreSQL 12. 1. Overview of Planner 2. Factors affecting the plan: costing 3. Selection of paths 4. Prepared statements and plan cache. 5. How plan changes with PostgreSQL versions. 6. Automatic elimination of unwanted joins. 7. CTE and Its optimization covering the latest improvements 8. Partition pruning 9. Run time optimizations 10. Optimizer support for functions, COST clause of CREATE FUNCTION
- 20 min
- Postgres Conference 2020