cbo_stat_dump : Reproduce those complex query planner issues without having to replicate customer data
Presented by:
Gaurav Kukreja
Software Engineer at YugabyteDB working on the Query Optimizer. I have a passion for tackling performance critical challenges.
No video of the event yet, sorry!
The CBO (Cost Based Optimizer) tries to find the most optimal execution plan for your query. Unfortunately, it often fails to find the best plan and picks a suboptimal plan instead. When this occurs on a large production deployment, debugging the issue becomes a challenge. The issue cannot easily be reproduced on a test setup without replicating the user data. Turns out, the CBO depends only on statistics stored in pg_statistic
and pg_class
and some GUC parameters to make its plan choice. By replicating the schema and importing the statistics and configuration from the production deployment to the test setup, we can induce the CBO to make the same plan choices on the test system. That’s exactly what the cbo_stat_dump
tool does. Reproducing the issue allows us to debug and analyze the root cause. It allows us to offer effective workarounds in the form of schema optimizations or query planner hints. This helps improve customer support efficiency and enhances support capabilities. The tool can also be used in regression testing of the query planner on a wide variety of datasets with different data distribution patterns and sizes without having to actually create these datasets. In this talk, we will introduce the tool and demonstrate how it has proved useful in all of these ways at YugabyteDB.
- Date:
- Duration:
- 45 min
- Room:
- Conference:
- PGConf India, 2025
- Language:
- Track:
- Application Developer
- Difficulty:
- Medium