explain.depesz.com

PostgreSQL's explain analyze made readable

Result: j6zp

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=224,582.01..224,582.33 rows=8 width=619) (actual rows= loops=)

  • Group Key: ko.kontogruppe_nr, (concat(ko.nummer, ' ', ko.bezeichnung_de)), bewegungen.valuta, buchungen.referenz, bewegungen.text
  • Filter: (sum(bewegungen.betrag) <> '0'::numeric)
2. 0.000 0.000 ↓ 0.0

Sort (cost=224,582.01..224,582.03 rows=8 width=592) (actual rows= loops=)

  • Sort Key: ko.kontogruppe_nr, (concat(ko.nummer, ' ', ko.bezeichnung_de)), bewegungen.valuta, buchungen.referenz, bewegungen.text
3. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=224,269.53..224,581.89 rows=8 width=592) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=224,269.10..224,514.31 rows=8 width=592) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

HashAggregate (cost=224,268.68..224,268.97 rows=29 width=834) (actual rows= loops=)

  • Group Key: bw.id, bw.id, bu.id, bh.id, gj.id, ko.nummer, ko.bezeichnung_de, ko.bezeichnung_fr, ko.bezeichnung_it, ko.bezeichnung_en, ko.kontogruppe_nr, kp.bezeichnung_de, kp.bezeichnung_fr, kp.bezeichnung_it, kp.bezeichnung_en, kp.ausgabe_in_detailjournal, (true), bu.buchungsdatum, bu.buchungsdatum
6. 0.000 0.000 ↓ 0.0

Append (cost=70.31..224,267.30 rows=29 width=834) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Hash Join (cost=70.31..4,742.20 rows=2 width=259) (actual rows= loops=)

  • Hash Cond: (((SubPlan 2) = ko.id) AND (kp.nummer = (ko.kontogruppe_nr)::text))
  • Join Filter: ((ko.nummer)::text <> COALESCE((dk.forderungen_bruttomiete_konto_nr)::text, 'keine'::text))
8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=9.31..2,652.72 rows=112 width=195) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=8.88..2,524.95 rows=167 width=182) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=8.46..17.11 rows=1 width=182) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop (cost=8.32..16.85 rows=1 width=25) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Nested Loop (cost=8.17..13.84 rows=1 width=25) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Hash Join (cost=8.17..10.10 rows=1 width=20) (actual rows= loops=)

  • Hash Cond: (gj.buchhaltung_id = fn_konsolidierungs_root_id(bh.id))
14. 0.000 0.000 ↓ 0.0

Seq Scan on geschaeftsjahre gj (cost=0.00..1.37 rows=37 width=16) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Hash (cost=8.16..8.16 rows=1 width=4) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Index Only Scan using buchhaltungen_pkey on buchhaltungen bh (cost=0.14..8.16 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = 200006)
17. 0.000 0.000 ↓ 0.0

Seq Scan on default_kontierungen dk (cost=0.00..3.73 rows=1 width=9) (actual rows= loops=)

  • Filter: (buchhaltung_id = 200006)
18. 0.000 0.000 ↓ 0.0

Index Scan using buchhaltungen_pkey on buchhaltungen bh_root (cost=0.14..2.97 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = gj.buchhaltung_id)
19. 0.000 0.000 ↓ 0.0

Index Scan using kontenplan_view_idx on kontenplan_view kp (cost=0.14..0.24 rows=1 width=165) (actual rows= loops=)

  • Index Cond: (id = bh_root.kontenplan_id)
  • Filter: ausgabe_in_detailjournal
20. 0.000 0.000 ↓ 0.0

Index Scan using index_buchungen_on_buchungsdatum on buchungen bu (cost=0.42..1,924.36 rows=58,348 width=8) (actual rows= loops=)

  • Index Cond: ((gj.beginn <= buchungsdatum) AND (gj.ende >= buchungsdatum))
21. 0.000 0.000 ↓ 0.0

Index Scan using index_bewegungen_konto_saldi on bewegungen bw (cost=0.43..0.76 rows=1 width=17) (actual rows= loops=)

  • Index Cond: ((buchhaltung_id = 200006) AND (buchung_id = bu.id))
22. 0.000 0.000 ↓ 0.0

Hash (cost=41.80..41.80 rows=1,280 width=105) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Seq Scan on konti ko (cost=0.00..41.80 rows=1,280 width=105) (actual rows= loops=)

24.          

SubPlan (forHash Join)

25. 0.000 0.000 ↓ 0.0

Limit (cost=349.04..362.12 rows=1 width=8) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Nested Loop (cost=349.04..401.37 rows=4 width=8) (actual rows= loops=)

  • Join Filter: (k_inner.buchhaltung_id = parents.id)
27. 0.000 0.000 ↓ 0.0

Sort (cost=349.04..349.29 rows=101 width=8) (actual rows= loops=)

  • Sort Key: parents.level
28.          

CTE parents

29. 0.000 0.000 ↓ 0.0

Recursive Union (cost=0.14..343.66 rows=101 width=12) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Index Scan using buchhaltungen_pkey on buchhaltungen (cost=0.14..8.16 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (id = bw.buchhaltung_id)
31. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.33..33.35 rows=10 width=12) (actual rows= loops=)

  • Hash Cond: (bh2.id = p.konsolidierungs_buchhaltung_id)
32. 0.000 0.000 ↓ 0.0

Seq Scan on buchhaltungen bh2 (cost=0.00..32.38 rows=138 width=8) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Hash (cost=0.20..0.20 rows=10 width=8) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

WorkTable Scan on parents p (cost=0.00..0.20 rows=10 width=8) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

CTE Scan on parents (cost=0.00..2.02 rows=101 width=8) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..45.02 rows=4 width=8) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Seq Scan on konti k_inner (cost=0.00..45.00 rows=4 width=8) (actual rows= loops=)

  • Filter: ((nummer)::text = (bw.konto_nr)::text)
38. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=26,260.48..219,524.94 rows=27 width=309) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Hash Join (cost=26,260.48..219,524.67 rows=27 width=273) (actual rows= loops=)

  • Hash Cond: (((SubPlan 4) = ko_1.id) AND ((dk_1.forderungen_bruttomiete_konto_nr)::text = (ko_1.nummer)::text))
40. 0.000 0.000 ↓ 0.0

Merge Join (cost=26,199.48..29,148.80 rows=10,508 width=71) (actual rows= loops=)

  • Merge Cond: (bh_root_1.id = gj_1.buchhaltung_id)
  • Join Filter: ((gj_1.beginn <= bu_1.buchungsdatum) AND (gj_1.ende >= bu_1.buchungsdatum))
41. 0.000 0.000 ↓ 0.0

Nested Loop (cost=26,197.15..75,092.89 rows=352,740 width=75) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Nested Loop (cost=45.46..84.65 rows=1 width=13) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Merge Join (cost=45.46..80.91 rows=1 width=8) (actual rows= loops=)

  • Merge Cond: ((fn_konsolidierungs_root_id(bh_1.id)) = bh_root_1.id)
44. 0.000 0.000 ↓ 0.0

Sort (cost=8.17..8.18 rows=1 width=4) (actual rows= loops=)

  • Sort Key: (fn_konsolidierungs_root_id(bh_1.id))
45. 0.000 0.000 ↓ 0.0

Index Only Scan using buchhaltungen_pkey on buchhaltungen bh_1 (cost=0.14..8.16 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = 200006)
46. 0.000 0.000 ↓ 0.0

Sort (cost=37.28..37.63 rows=138 width=4) (actual rows= loops=)

  • Sort Key: bh_root_1.id
47. 0.000 0.000 ↓ 0.0

Seq Scan on buchhaltungen bh_root_1 (cost=0.00..32.38 rows=138 width=4) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Seq Scan on default_kontierungen dk_1 (cost=0.00..3.73 rows=1 width=9) (actual rows= loops=)

  • Filter: (buchhaltung_id = 200006)
49. 0.000 0.000 ↓ 0.0

Hash Join (cost=26,151.69..71,480.84 rows=352,740 width=62) (actual rows= loops=)

  • Hash Cond: (mv.hauptmieter_id = mieter.id)
50. 0.000 0.000 ↓ 0.0

Hash Join (cost=25,162.34..69,565.37 rows=352,740 width=47) (actual rows= loops=)

  • Hash Cond: (fp.mietvertrag_id = mv.id)
51. 0.000 0.000 ↓ 0.0

Hash Join (cost=19,428.60..62,905.56 rows=352,740 width=29) (actual rows= loops=)

  • Hash Cond: (bw_1.buchung_id = bu_1.id)
52. 0.000 0.000 ↓ 0.0

Merge Join (cost=2.09..35,677.09 rows=352,740 width=25) (actual rows= loops=)

  • Merge Cond: (bw_1.forderungs_posten_id = fp.id)
53. 0.000 0.000 ↓ 0.0

Index Scan using index_bewegungen_on_forderungs_posten_id on bewegungen bw_1 (cost=0.43..90,641.77 rows=352,740 width=25) (actual rows= loops=)

  • Filter: (buchhaltung_id = 200006)
54. 0.000 0.000 ↓ 0.0

Index Scan using forderungs_posten_pkey on forderungs_posten fp (cost=0.42..10,894.81 rows=211,714 width=8) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Hash (cost=10,810.34..10,810.34 rows=525,134 width=8) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Seq Scan on buchungen bu_1 (cost=0.00..10,810.34 rows=525,134 width=8) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Hash (cost=5,357.77..5,357.77 rows=30,077 width=26) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Seq Scan on mietvertraege mv (cost=0.00..5,357.77 rows=30,077 width=26) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Hash (cost=690.27..690.27 rows=23,927 width=23) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Seq Scan on personen mieter (cost=0.00..690.27 rows=23,927 width=23) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Sort (cost=2.33..2.43 rows=37 width=16) (actual rows= loops=)

  • Sort Key: gj_1.buchhaltung_id
62. 0.000 0.000 ↓ 0.0

Seq Scan on geschaeftsjahre gj_1 (cost=0.00..1.37 rows=37 width=16) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Hash (cost=41.80..41.80 rows=1,280 width=101) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Seq Scan on konti ko_1 (cost=0.00..41.80 rows=1,280 width=101) (actual rows= loops=)

65.          

SubPlan (forHash Join)

66. 0.000 0.000 ↓ 0.0

Limit (cost=349.04..362.12 rows=1 width=8) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Nested Loop (cost=349.04..401.37 rows=4 width=8) (actual rows= loops=)

  • Join Filter: (k_inner_1.buchhaltung_id = parents_1.id)
68. 0.000 0.000 ↓ 0.0

Sort (cost=349.04..349.29 rows=101 width=8) (actual rows= loops=)

  • Sort Key: parents_1.level
69.          

CTE parents

70. 0.000 0.000 ↓ 0.0

Recursive Union (cost=0.14..343.66 rows=101 width=12) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Index Scan using buchhaltungen_pkey on buchhaltungen buchhaltungen_1 (cost=0.14..8.16 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (id = bw_1.buchhaltung_id)
72. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.33..33.35 rows=10 width=12) (actual rows= loops=)

  • Hash Cond: (bh2_1.id = p_1.konsolidierungs_buchhaltung_id)
73. 0.000 0.000 ↓ 0.0

Seq Scan on buchhaltungen bh2_1 (cost=0.00..32.38 rows=138 width=8) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

Hash (cost=0.20..0.20 rows=10 width=8) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

WorkTable Scan on parents p_1 (cost=0.00..0.20 rows=10 width=8) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

CTE Scan on parents parents_1 (cost=0.00..2.02 rows=101 width=8) (actual rows= loops=)

77. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..45.02 rows=4 width=8) (actual rows= loops=)

78. 0.000 0.000 ↓ 0.0

Seq Scan on konti k_inner_1 (cost=0.00..45.00 rows=4 width=8) (actual rows= loops=)

  • Filter: ((nummer)::text = (bw_1.konto_nr)::text)
79. 0.000 0.000 ↓ 0.0

Index Scan using buchungen_pkey on buchungen (cost=0.42..8.45 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (id = bu.id)
  • Filter: ((NOT ist_storno) AND (buchungsdatum >= '2018-01-01'::date) AND (buchungsdatum <= '2018-12-31'::date) AND ((typ_cd)::text <> 'ABB'::text))
80. 0.000 0.000 ↓ 0.0

Index Scan using bewegungen_pkey on bewegungen (cost=0.43..8.45 rows=1 width=40) (actual rows= loops=)

  • Index Cond: (id = bw.id)