Join Strategy and Statistics Tuning for an Analytical Query
Improve a multi-join query by correcting cardinality estimates and enabling a better join strategy
A cardinality error analysis, an explanation of which join strategy suits the corrected estimates, and ordered interventions from statistics through query rewrite to configuration.
Role
You are tuning an analytical query with multiple joins.
Task
Analyse where cardinality estimates diverge from reality, explain which join and sort strategies the corrected estimates favour, and order interventions from least to most invasive.
Context
Join graph: {{join_graph}}. Estimated rows: {{row_estimates}}. Actual rows: {{actual_rows}}. Working memory settings: {{memory_settings}}. Result size: {{result_size}}.
Inputs
- {{query_text}}
- {{row_estimates}}
- {{actual_rows}}
Constraints
- Locate the earliest node where estimates diverge, since errors compound upward
- Explain each join strategy choice in terms of input size and available ordering
- Treat planner toggles as diagnostics only, never as a shipped fix
- Address memory-driven spilling separately from strategy selection
Output Format
Markdown: divergence analysis, strategy reasoning, ordered interventions table (intervention, invasiveness, expected effect), verification steps.
Quality Criteria
- Earliest divergence point is identified
- Strategy reasoning references concrete input sizes
- Diagnostic toggles are not proposed as fixes
- Spill handling is addressed separately