Deeper Understanding of PostgreSQL Execution Plan: At Plan Time and Run Time

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