1use crate::sqljoin::Strategy;
48use crate::sqlselect::JoinKind;
49
50#[derive(Debug, Clone, PartialEq, Eq)]
52pub enum Stage {
53 Scan {
55 table: String,
56 binding: String,
60 rows: usize,
61 },
62 Join {
64 kind: JoinKind,
65 table: String,
66 binding: String,
67 strategy: Strategy,
68 keys: usize,
72 left_rows: usize,
73 right_rows: usize,
74 out_rows: usize,
75 early_stopped: bool,
77 post_filter_removed: Option<usize>,
86 },
87 Filter { in_rows: usize, out_rows: usize },
89 Project { columns: usize, out_rows: usize },
91 Distinct { in_rows: usize, out_rows: usize },
93 Sort { keys: usize, rows: usize },
95 Prefilter {
100 binding: String,
101 predicates: usize,
102 in_rows: usize,
103 out_rows: usize,
104 },
105 Limit {
107 limit: Option<usize>,
108 offset: Option<usize>,
109 in_rows: usize,
110 out_rows: usize,
111 },
112}
113
114#[derive(Debug, Clone, PartialEq, Eq)]
132pub enum PlanTree {
133 Leaf(Stage),
134 Unary { stage: Stage, input: Box<PlanTree> },
135 Binary { stage: Stage, left: Box<PlanTree>, right: Box<PlanTree> },
136}
137
138impl PlanTree {
139 pub fn stage(&self) -> &Stage {
140 match self {
141 PlanTree::Leaf(s) => s,
142 PlanTree::Unary { stage, .. } => stage,
143 PlanTree::Binary { stage, .. } => stage,
144 }
145 }
146
147 pub fn children(&self) -> Vec<&PlanTree> {
149 match self {
150 PlanTree::Leaf(_) => vec![],
151 PlanTree::Unary { input, .. } => vec![input],
152 PlanTree::Binary { left, right, .. } => vec![left, right],
153 }
154 }
155
156 pub fn size(&self) -> usize {
158 1 + self.children().iter().map(|c| c.size()).sum::<usize>()
159 }
160
161 pub fn depth(&self) -> usize {
165 1 + self.children().iter().map(|c| c.depth()).max().unwrap_or(0)
166 }
167}
168
169#[derive(Debug, Clone, Default, PartialEq, Eq)]
171pub struct Plan {
172 pub stages: Vec<Stage>,
173 pub budget: Option<usize>,
175 pub refusals: Vec<String>,
179 pub notes: Vec<String>,
183}
184
185impl Plan {
186 pub fn push(&mut self, s: Stage) {
187 self.stages.push(s);
188 }
189
190 pub fn joins(&self) -> Vec<&Stage> {
195 self.stages
196 .iter()
197 .filter(|s| matches!(s, Stage::Join { .. }))
198 .collect()
199 }
200
201 pub fn join_strategy(&self, n: usize) -> Option<Strategy> {
203 match self.joins().get(n) {
204 Some(Stage::Join { strategy, .. }) => Some(*strategy),
205 _ => None,
206 }
207 }
208
209 pub fn join_strategies(&self) -> Vec<Strategy> {
211 self.stages
212 .iter()
213 .filter_map(|s| match s {
214 Stage::Join { strategy, .. } => Some(*strategy),
215 _ => None,
216 })
217 .collect()
218 }
219
220 pub fn join_keys(&self, n: usize) -> Option<usize> {
222 match self.joins().get(n) {
223 Some(Stage::Join { keys, .. }) => Some(*keys),
224 _ => None,
225 }
226 }
227
228 pub fn tree(&self) -> Option<PlanTree> {
234 let mut it = self.stages.iter();
235 let mut node = PlanTree::Leaf(it.next()?.clone());
236 let rest: Vec<&Stage> = it.collect();
237 let mut i = 0usize;
238 while i < rest.len() {
239 let right_len = match (rest.get(i), rest.get(i + 1), rest.get(i + 2)) {
245 (Some(Stage::Scan { .. }), Some(Stage::Join { .. }), _) => Some(1),
246 (
247 Some(Stage::Scan { .. }),
248 Some(Stage::Prefilter { .. }),
249 Some(Stage::Join { .. }),
250 ) => Some(2),
251 _ => None,
252 };
253 if let Some(n) = right_len {
254 let mut right = PlanTree::Leaf(rest[i].clone());
255 if n == 2 {
256 right = PlanTree::Unary {
257 stage: rest[i + 1].clone(),
258 input: Box::new(right),
259 };
260 }
261 node = PlanTree::Binary {
262 stage: rest[i + n].clone(),
263 left: Box::new(node),
264 right: Box::new(right),
265 };
266 i += n + 1;
267 } else {
268 node = PlanTree::Unary {
269 stage: rest[i].clone(),
270 input: Box::new(node),
271 };
272 i += 1;
273 }
274 }
275 Some(node)
276 }
277
278 pub fn render(&self) -> Vec<String> {
285 let mut out = vec![];
286 if let Some(t) = self.tree() {
287 render_node(&t, 0, &mut out);
288 }
289
290 for r in &self.refusals {
291 out.push(r.clone());
292 }
293 for n in &self.notes {
294 out.push(n.clone());
295 }
296 if let Some(b) = self.budget {
297 out.push(format!(
298 "Row budget: {b} — the join was allowed to stop once this many \
299 rows existed"
300 ));
301 }
302 out.push(
303 "NEDB reports ACTUAL rows, never estimates: it has no statistics to \
304 estimate from, and a guess printed as a number is worse than the truth."
305 .to_string(),
306 );
307 out
308 }
309}
310
311fn render_node(n: &PlanTree, depth: usize, out: &mut Vec<String>) {
320 let indent = " ".repeat(depth);
321 let arrow = if depth == 0 { String::new() } else { format!("{indent}-> ") };
322 let line = match n.stage() {
323 Stage::Scan { table, binding, rows } => {
324 format!("{arrow}Seq Scan on {} (actual rows={rows})", named(table, binding))
325 }
326 Stage::Join {
327 kind,
328 table,
329 binding,
330 strategy,
331 keys,
332 left_rows,
333 right_rows,
334 out_rows,
335 early_stopped,
336 post_filter_removed,
337 } => {
338 let k = match keys {
339 0 => "no equality key".to_string(),
340 1 => "1 hash key".to_string(),
341 n => format!("{n} hash keys"),
342 };
343 let stop = if *early_stopped { ", stopped early" } else { "" };
344 let filt = match post_filter_removed {
345 Some(n) => format!(", post-join filter removed {n}"),
346 None => String::new(),
347 };
348 format!(
349 "{arrow}{strategy} {} Join on {} \
350 ({k}, left={left_rows}, right={right_rows}{stop}{filt}) \
351 (actual rows={out_rows})",
352 kind_name(*kind),
353 named(table, binding)
354 )
355 }
356 Stage::Filter { in_rows, out_rows } => format!(
357 "{arrow}Filter (removed {}) (actual rows={out_rows})",
358 in_rows.saturating_sub(*out_rows)
359 ),
360 Stage::Project { columns, out_rows } => {
361 format!("{arrow}Project ({columns} columns) (actual rows={out_rows})")
362 }
363 Stage::Distinct { in_rows, out_rows } => format!(
364 "{arrow}Unique (removed {}) (actual rows={out_rows})",
365 in_rows.saturating_sub(*out_rows)
366 ),
367 Stage::Sort { keys, rows } => {
368 format!("{arrow}Sort ({keys} key(s)) (actual rows={rows})")
369 }
370 Stage::Prefilter { binding, predicates, in_rows, out_rows } => format!(
371 "{arrow}Prefilter on {binding} ({predicates} pushed, removed {}) \
372 (actual rows={out_rows})",
373 in_rows.saturating_sub(*out_rows)
374 ),
375 Stage::Limit { limit, offset, in_rows, out_rows } => {
376 let l = limit.map(|n| n.to_string()).unwrap_or_else(|| "ALL".into());
377 let o = offset.map(|n| format!(", offset {n}")).unwrap_or_default();
378 format!("{arrow}Limit ({l}{o}, from {in_rows}) (actual rows={out_rows})")
379 }
380 };
381 out.push(line);
382 for c in n.children() {
383 render_node(c, depth + 1, out);
384 }
385}
386
387fn named(table: &str, binding: &str) -> String {
389 if table == binding {
390 table.to_string()
391 } else {
392 format!("{table} {binding}")
393 }
394}
395
396fn kind_name(k: JoinKind) -> &'static str {
397 match k {
398 JoinKind::Inner => "Inner",
399 JoinKind::Left => "Left",
400 JoinKind::Right => "Right",
401 JoinKind::Full => "Full",
402 JoinKind::Cross => "Cross",
403 }
404}
405
406#[cfg(test)]
407mod tests {
408 use super::*;
409
410 fn scan(t: &str, rows: usize) -> Stage {
411 Stage::Scan { table: t.into(), binding: t.into(), rows }
412 }
413
414 #[test]
415 fn an_empty_plan_still_explains_itself() {
416 let p = Plan::default();
417 let r = p.render();
418 assert_eq!(r.len(), 1);
419 assert!(r[0].contains("ACTUAL rows"));
420 }
421
422 #[test]
423 fn a_scan_renders_with_actual_rows() {
424 let mut p = Plan::default();
425 p.push(scan("orders", 1000));
426 let r = p.render();
427 assert!(r[0].starts_with("Seq Scan on orders"), "{:?}", r[0]);
428 assert!(r[0].contains("actual rows=1000"));
429 }
430
431 #[test]
432 fn an_alias_is_shown_but_a_redundant_one_is_not() {
433 let mut p = Plan::default();
434 p.push(Stage::Scan { table: "orders".into(), binding: "o".into(), rows: 1 });
435 assert!(p.render()[0].contains("orders o"));
436
437 let mut p = Plan::default();
438 p.push(scan("orders", 1));
439 assert!(!p.render()[0].contains("orders orders"));
440 }
441
442 #[test]
443 fn the_outermost_stage_is_printed_first() {
444 let mut p = Plan::default();
445 p.push(scan("orders", 100));
446 p.push(Stage::Limit { limit: Some(5), offset: None, in_rows: 100, out_rows: 5 });
447 let r = p.render();
448 assert!(r[0].starts_with("Limit"), "{r:?}");
449 assert!(r[1].contains("Seq Scan"), "{r:?}");
450 assert!(r[1].starts_with(" -> "), "{:?}", r[1]);
452 }
453
454 #[test]
455 fn a_join_names_its_strategy_and_key_count() {
456 let mut p = Plan::default();
457 p.push(scan("orders", 1000));
458 p.push(Stage::Join {
459 kind: JoinKind::Inner,
460 table: "customers".into(),
461 binding: "c".into(),
462 strategy: Strategy::Hash,
463 keys: 2,
464 left_rows: 1000,
465 right_rows: 500,
466 out_rows: 1922,
467 early_stopped: false,
468 post_filter_removed: None,
469 });
470 let r = p.render();
471 assert!(r[0].contains("Hash Join"), "{:?}", r[0]);
472 assert!(r[0].contains("Inner"));
473 assert!(r[0].contains("2 hash keys"));
474 assert!(r[0].contains("customers c"));
475 assert!(r[0].contains("actual rows=1922"));
476 }
477
478 #[test]
479 fn a_join_with_no_key_says_so_because_that_is_why_it_is_slow() {
480 let mut p = Plan::default();
481 p.push(Stage::Join {
482 kind: JoinKind::Inner,
483 table: "customers".into(),
484 binding: "customers".into(),
485 strategy: Strategy::NestedLoop,
486 keys: 0,
487 left_rows: 1000,
488 right_rows: 500,
489 out_rows: 2500,
490 early_stopped: false,
491 post_filter_removed: None,
492 });
493 let r = p.render();
494 assert!(r[0].contains("Nested Loop"));
495 assert!(r[0].contains("no equality key"), "{:?}", r[0]);
496 }
497
498 #[test]
499 fn early_termination_is_visible() {
500 let mut p = Plan::default();
501 p.push(Stage::Join {
502 kind: JoinKind::Inner,
503 table: "c".into(),
504 binding: "c".into(),
505 strategy: Strategy::Hash,
506 keys: 1,
507 left_rows: 1000,
508 right_rows: 500,
509 out_rows: 20,
510 early_stopped: true,
511 post_filter_removed: None,
512 });
513 p.budget = Some(20);
514 let r = p.render();
515 assert!(r[0].contains("stopped early"), "{:?}", r[0]);
516 assert!(r.iter().any(|l| l.contains("Row budget: 20")));
517 }
518
519 #[test]
520 fn filter_and_unique_report_what_they_removed() {
521 let mut p = Plan::default();
522 p.push(Stage::Filter { in_rows: 1000, out_rows: 117 });
523 p.push(Stage::Distinct { in_rows: 117, out_rows: 4 });
524 let r = p.render();
525 assert!(r.iter().any(|l| l.contains("Unique") && l.contains("removed 113")), "{r:?}");
526 assert!(r.iter().any(|l| l.contains("Filter") && l.contains("removed 883")), "{r:?}");
527 }
528
529 #[test]
530 fn a_joins_two_inputs_are_siblings_not_nested() {
531 let mut p = Plan::default();
535 p.push(scan("orders", 10));
536 p.push(scan("customers", 5));
537 p.push(Stage::Join {
538 kind: JoinKind::Inner,
539 table: "customers".into(),
540 binding: "customers".into(),
541 strategy: Strategy::Hash,
542 keys: 1,
543 left_rows: 10,
544 right_rows: 5,
545 out_rows: 7,
546 early_stopped: false,
547 post_filter_removed: None,
548 });
549 let r = p.render();
550 assert!(r[0].contains("Hash Join"), "{r:?}");
551 let orders = r.iter().find(|l| l.contains("orders")).expect("orders scanned");
552 let custs = r.iter().find(|l| l.contains("customers (actual")).expect("customers");
553 let depth = |l: &str| l.len() - l.trim_start().len();
554 assert_eq!(
555 depth(orders), depth(custs),
556 "the two inputs of a join must be at the same depth\n{r:#?}"
557 );
558 assert!(depth(orders) > depth(&r[0]), "both are nested under the join");
559 }
560
561 fn join_stage(table: &str) -> Stage {
564 Stage::Join {
565 kind: JoinKind::Inner,
566 table: table.into(),
567 binding: table.into(),
568 strategy: Strategy::Hash,
569 keys: 1,
570 left_rows: 1,
571 right_rows: 1,
572 out_rows: 1,
573 early_stopped: false,
574 post_filter_removed: None,
575 }
576 }
577
578 #[test]
579 fn a_join_node_has_exactly_two_children() {
580 let mut p = Plan::default();
585 p.push(scan("a", 1));
586 p.push(scan("b", 1));
587 p.push(join_stage("b"));
588
589 let t = p.tree().expect("a tree");
590 assert!(matches!(t, PlanTree::Binary { .. }), "a join is binary");
591 assert_eq!(t.children().len(), 2, "two inputs, not one nested in the other");
592 assert_eq!(t.size(), 3, "join + two scans");
593 assert_eq!(t.depth(), 2, "the inputs are siblings\n{t:#?}");
595 for c in t.children() {
596 assert!(matches!(c, PlanTree::Leaf(Stage::Scan { .. })));
597 assert_eq!(c.children().len(), 0, "a scan consumes nothing");
598 }
599 }
600
601 #[test]
602 fn the_outer_side_is_the_left_child() {
603 let mut p = Plan::default();
607 p.push(scan("a", 10));
608 p.push(scan("b", 5));
609 p.push(join_stage("b"));
610 let t = p.tree().unwrap();
611 let kids = t.children();
612 assert_eq!(kids[0].stage(), &scan("a", 10), "outer side first");
613 assert_eq!(kids[1].stage(), &scan("b", 5), "inner side second");
614 }
615
616 #[test]
617 fn a_chained_join_nests_on_the_left() {
618 let mut p = Plan::default();
621 p.push(scan("a", 1));
622 p.push(scan("b", 1));
623 p.push(join_stage("b"));
624 p.push(scan("c", 1));
625 p.push(join_stage("c"));
626
627 let t = p.tree().unwrap();
628 assert_eq!(t.size(), 5, "3 scans + 2 joins");
629 assert_eq!(t.depth(), 3, "left-deep: join -> join -> scan");
630 let kids = t.children();
631 assert!(matches!(kids[0], PlanTree::Binary { .. }), "outer side is the first join");
632 assert!(matches!(kids[1], PlanTree::Leaf(_)), "inner side is c");
633 assert_eq!(kids[0].children().len(), 2);
634 }
635
636 #[test]
637 fn unary_stages_wrap_the_whole_tree_below_them() {
638 let mut p = Plan::default();
639 p.push(scan("a", 100));
640 p.push(scan("b", 5));
641 p.push(join_stage("b"));
642 p.push(Stage::Filter { in_rows: 100, out_rows: 7 });
643 p.push(Stage::Limit { limit: Some(2), offset: None, in_rows: 7, out_rows: 2 });
644
645 let t = p.tree().unwrap();
646 assert!(matches!(t.stage(), Stage::Limit { .. }), "the last stage is outermost");
647 assert_eq!(t.children().len(), 1, "a unary stage has one input");
648 let filter = t.children()[0];
649 assert!(matches!(filter.stage(), Stage::Filter { .. }));
650 assert_eq!(filter.children().len(), 1);
651 let join = filter.children()[0];
652 assert_eq!(join.children().len(), 2, "and the join below still has two");
653 assert_eq!(t.size(), 5);
654 }
655
656 #[test]
657 fn a_plan_with_no_stages_has_no_tree() {
658 assert_eq!(Plan::default().tree(), None);
660 }
661
662 #[test]
663 fn the_rendered_depth_agrees_with_the_tree_depth() {
664 let mut p = Plan::default();
667 p.push(scan("a", 1));
668 p.push(scan("b", 1));
669 p.push(join_stage("b"));
670 p.push(scan("c", 1));
671 p.push(join_stage("c"));
672 let t = p.tree().unwrap();
673
674 let lines = p.render();
675 let plan_lines: Vec<&String> = lines
676 .iter()
677 .filter(|l| !l.starts_with("NEDB reports") && !l.starts_with("Row budget"))
678 .collect();
679 assert_eq!(plan_lines.len(), t.size(), "every node is rendered once");
680
681 let max_indent = plan_lines
682 .iter()
683 .map(|l| (l.len() - l.trim_start().len()) / 2)
684 .max()
685 .unwrap();
686 assert_eq!(max_indent + 1, t.depth(), "rendered nesting matches the tree");
687 }
688
689 #[test]
690 fn joins_and_join_strategy_read_the_same_report() {
691 let mut p = Plan::default();
692 p.push(scan("a", 1));
693 for s in [Strategy::NestedLoop, Strategy::Hash] {
694 p.push(Stage::Join {
695 kind: JoinKind::Left,
696 table: "b".into(),
697 binding: "b".into(),
698 strategy: s,
699 keys: 1,
700 left_rows: 1,
701 right_rows: 1,
702 out_rows: 1,
703 early_stopped: false,
704 post_filter_removed: None,
705 });
706 }
707 assert_eq!(p.joins().len(), 2);
708 assert_eq!(p.join_strategy(0), Some(Strategy::NestedLoop));
709 assert_eq!(p.join_strategy(1), Some(Strategy::Hash));
710 assert_eq!(p.join_strategy(2), None);
711 }
712}