waypoint_core/commands/
explain.rs

1//! Enhanced dry-run with EXPLAIN for pending migrations.
2//!
3//! Runs EXPLAIN on each DML statement within a rolled-back transaction
4//! to show execution plans and identify potential issues.
5
6use serde::Serialize;
7use tokio_postgres::Client;
8
9use crate::commands::info::{self, MigrationState};
10use crate::config::WaypointConfig;
11use crate::error::Result;
12use crate::placeholder::{build_placeholders, replace_placeholders};
13use crate::sql_parser::split_statements;
14
15/// EXPLAIN report for all pending migrations.
16#[derive(Debug, Serialize)]
17pub struct ExplainReport {
18    /// Per-migration EXPLAIN analysis results.
19    pub migrations: Vec<MigrationExplain>,
20}
21
22/// EXPLAIN analysis for a single migration.
23#[derive(Debug, Serialize)]
24pub struct MigrationExplain {
25    /// Filename of the migration script.
26    pub script: String,
27    /// Version string, or None for repeatable migrations.
28    pub version: Option<String>,
29    /// EXPLAIN results for each statement in the migration.
30    pub statements: Vec<StatementExplain>,
31}
32
33/// EXPLAIN analysis for a single statement.
34#[derive(Debug, Serialize)]
35pub struct StatementExplain {
36    /// Truncated preview of the SQL statement (up to 80 characters).
37    pub statement_preview: String,
38    /// Full EXPLAIN output or a status message for DDL statements.
39    pub plan: String,
40    /// Estimated number of rows from the query plan, if available.
41    pub estimated_rows: Option<f64>,
42    /// Estimated total cost from the query plan, if available.
43    pub estimated_cost: Option<f64>,
44    /// Performance warnings derived from the execution plan.
45    pub warnings: Vec<String>,
46    /// Whether this statement is a DDL operation (not explainable).
47    pub is_ddl: bool,
48}
49
50/// Execute explain analysis for pending migrations.
51pub async fn execute(client: &Client, config: &WaypointConfig) -> Result<ExplainReport> {
52    let infos = info::execute(client, config).await?;
53
54    let pending: Vec<_> = infos
55        .iter()
56        .filter(|i| matches!(i.state, MigrationState::Pending | MigrationState::Outdated))
57        .collect();
58
59    let schema = &config.migrations.schema;
60    let db_user = crate::db::get_current_user(client)
61        .await
62        .unwrap_or_else(|_| "unknown".to_string());
63    let db_name = crate::db::get_current_database(client)
64        .await
65        .unwrap_or_else(|_| "unknown".to_string());
66
67    // Scan migration files to get SQL content
68    let resolved = crate::migration::scan_migrations(&config.migrations.locations)?;
69
70    let mut migrations = Vec::new();
71
72    for info in &pending {
73        // Find the resolved migration matching this info
74        let migration = resolved.iter().find(|m| m.script == info.script);
75        let sql = match migration {
76            Some(m) => {
77                let placeholders =
78                    build_placeholders(&config.placeholders, schema, &db_user, &db_name, &m.script);
79                replace_placeholders(&m.sql, &placeholders)?
80            }
81            None => continue,
82        };
83
84        let statements_raw = split_statements(&sql);
85        let mut statements = Vec::new();
86
87        // Begin a transaction for EXPLAIN
88        client.batch_execute("BEGIN").await?;
89
90        for stmt_str in &statements_raw {
91            let trimmed = stmt_str.trim();
92            if trimmed.is_empty() || trimmed.starts_with("--") {
93                continue;
94            }
95
96            let preview: String = trimmed.chars().take(80).collect();
97            let preview = if trimmed.len() > 80 {
98                format!("{}...", preview)
99            } else {
100                preview
101            };
102
103            let upper = trimmed.to_uppercase();
104            let is_ddl = upper.starts_with("CREATE")
105                || upper.starts_with("ALTER")
106                || upper.starts_with("DROP")
107                || upper.starts_with("TRUNCATE");
108
109            if is_ddl {
110                // DDL can't be meaningfully EXPLAINed; execute it to build schema state
111                match client.batch_execute(trimmed).await {
112                    Ok(()) => {}
113                    Err(e) => {
114                        log::debug!("DDL statement failed during explain: {}", e);
115                    }
116                }
117                statements.push(StatementExplain {
118                    statement_preview: preview,
119                    plan: "DDL statement — not explainable".to_string(),
120                    estimated_rows: None,
121                    estimated_cost: None,
122                    warnings: vec![],
123                    is_ddl: true,
124                });
125            } else {
126                // Try EXPLAIN on DML
127                let explain_sql = format!("EXPLAIN (FORMAT TEXT) {}", trimmed);
128                match client.query(&explain_sql, &[]).await {
129                    Ok(rows_result) => {
130                        let plan_lines: Vec<String> =
131                            rows_result.iter().map(|r| r.get::<_, String>(0)).collect();
132                        let plan_str = plan_lines.join("\n");
133
134                        let (rows, cost, warnings) = extract_plan_info_text(&plan_str);
135
136                        statements.push(StatementExplain {
137                            statement_preview: preview,
138                            plan: plan_str,
139                            estimated_rows: rows,
140                            estimated_cost: cost,
141                            warnings,
142                            is_ddl: false,
143                        });
144                    }
145                    Err(e) => {
146                        statements.push(StatementExplain {
147                            statement_preview: preview,
148                            plan: format!("EXPLAIN failed: {}", e),
149                            estimated_rows: None,
150                            estimated_cost: None,
151                            warnings: vec![],
152                            is_ddl: false,
153                        });
154                    }
155                }
156            }
157        }
158
159        // Rollback the transaction
160        let _ = client.batch_execute("ROLLBACK").await;
161
162        migrations.push(MigrationExplain {
163            script: info.script.clone(),
164            version: info.version.clone(),
165            statements,
166        });
167    }
168
169    Ok(ExplainReport { migrations })
170}
171
172fn extract_plan_info_text(plan_text: &str) -> (Option<f64>, Option<f64>, Vec<String>) {
173    let mut warnings = Vec::new();
174    let mut total_rows = None;
175    let mut total_cost = None;
176
177    // Parse cost and rows from the first line: "Seq Scan on ... (cost=0.00..35.50 rows=2550 width=36)"
178    for line in plan_text.lines() {
179        let trimmed = line.trim();
180        if let Some(cost_start) = trimmed.find("cost=") {
181            let rest = &trimmed[cost_start + 5..];
182            if let Some(dot_dot) = rest.find("..") {
183                let after_dots = &rest[dot_dot + 2..];
184                if let Some(space_pos) = after_dots.find(' ') {
185                    if let Ok(cost) = after_dots[..space_pos].parse::<f64>() {
186                        if total_cost.is_none() {
187                            total_cost = Some(cost);
188                        }
189                    }
190                }
191            }
192        }
193        if let Some(rows_start) = trimmed.find("rows=") {
194            let rest = &trimmed[rows_start + 5..];
195            let end = rest
196                .find(|c: char| !c.is_ascii_digit())
197                .unwrap_or(rest.len());
198            if let Ok(rows) = rest[..end].parse::<f64>() {
199                if total_rows.is_none() {
200                    total_rows = Some(rows);
201                }
202            }
203        }
204
205        // Detect sequential scans
206        if trimmed.contains("Seq Scan") {
207            if let Some(rows) = total_rows {
208                if rows > 10000.0 {
209                    // Try to extract table name
210                    let table = trimmed
211                        .find("on ")
212                        .map(|i| {
213                            let after = &trimmed[i + 3..];
214                            after.split_whitespace().next().unwrap_or("unknown")
215                        })
216                        .unwrap_or("unknown");
217                    warnings.push(format!(
218                        "Sequential Scan on '{}' (~{:.0} rows) — consider adding an index",
219                        table, rows
220                    ));
221                }
222            }
223        }
224    }
225
226    (total_rows, total_cost, warnings)
227}
waypoint_core/commands/explain.rs

waypoint_core/commands/
explain.rs
