explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Cdfi : 15802

Settings
# exclusive inclusive rows x rows loops node
1. 18.433 19,237.282 ↑ 1,515.1 252 1

Unique (cost=963,252.24..971,842.92 rows=381,808 width=228) (actual time=19,216.803..19,237.282 rows=252 loops=1)

  • Buffers: shared hit=12265075 read=1, temp read=112699 written=112686
2. 148.420 19,218.849 ↑ 9.9 38,542 1

Sort (cost=963,252.24..964,206.76 rows=381,808 width=228) (actual time=19,216.802..19,218.849 rows=38,542 loops=1)

  • Sort Key: (CASE "AufschlagDetail_Kategorie"."Kategorie_ID" WHEN 5 THEN 'Abschlussbonus'::text WHEN 6 THEN 'Treue-/Laufzeitbonus'::text ELSE NULL::text END), (CASE WHEN (("Aufschlag"."Einheit_ID" = 2) AND ("Aufschlag"."AufschlagBezug_ID" = 1)) THEN "Aufschlag"."Wert" ELSE 0.0 END), (CASE WHEN (("Aufschlag"."Einheit_ID" = 1) AND ("Aufschlag"."AufschlagBezug_ID" = ANY ('{2,3}'::integer[]))) THEN "Aufschlag"."Wert" ELSE 0.0 END), ((CASE WHEN (("Aufschlag"."Einheit_ID" = 3) AND ("Aufschlag"."AufschlagBezug_ID" = 1)) THEN "Aufschlag"."Wert" ELSE 0.0 END / '100'::numeric)), ((CASE WHEN (("Aufschlag"."Einheit_ID" = 3) AND ("Aufschlag"."AufschlagBezug_ID" = 2)) THEN "Aufschlag"."Wert" ELSE 0.0 END / '100'::numeric)), ((CASE WHEN (("Aufschlag"."Einheit_ID" = 3) AND ("Aufschlag"."AufschlagBezug_ID" = ANY ('{3,4}'::integer[]))) THEN "Aufschlag"."Wert" ELSE 0.0 END / '100'::numeric)), (CASE WHEN (("Aufschlag"."Einheit_ID" = 1) AND ("Aufschlag"."AufschlagBezug_ID" <> ALL ('{1,2,3}'::integer[]))) THEN "Aufschlag"."Wert" ELSE 0.0 END), "Preisregelung_Aufschlag_AufschlagDetail"."Preisregelung_ID"
  • Sort Method: quicksort Memory: 4551kB
  • Buffers: shared hit=12265075 read=1, temp read=112699 written=112686
3. 197.018 19,070.429 ↑ 9.9 38,542 1

Gather (cost=828,653.11..886,091.89 rows=381,808 width=228) (actual time=18,583.116..19,070.429 rows=38,542 loops=1)

  • Buffers: shared hit=12265075 read=1, temp read=112699 written=112686
4. 126.359 18,873.411 ↑ 12.4 7,708 5

Merge Join (cost=827,653.11..834,383.02 rows=95,452 width=228) (actual time=18,555.537..18,873.411 rows=7,708 loops=5)

  • Buffers: shared hit=12265075 read=1, temp read=112699 written=112686
5. 989.668 18,691.877 ↓ 2.4 530,806 5

Sort (cost=758,046.48..758,608.22 rows=224,698 width=23) (actual time=18,502.34..18,691.877 rows=530,806 loops=5)

  • Sort Key: "Preisregelung"."Gebiet_ID"
  • Sort Method: external merge Disk: 16488kB
  • Buffers: shared hit=12183827 read=1, temp read=112699 written=112686
6. 4,154.009 17,702.209 ↓ 2.4 530,808 5

Nested Loop (cost=76,276.76..738,073.49 rows=224,698 width=23) (actual time=204.509..17,702.209 rows=530,808 loops=5)

  • Buffers: shared hit=12183811 read=1, temp read=101940 written=101910
7. 4,498.969 13,548.194 ↓ 2.7 608,528 5

Hash Join (cost=76,276.32..631,611.84 rows=224,703 width=19) (actual time=204.483..13,548.194 rows=608,528 loops=5)

  • Buffers: shared hit=380671 read=1, temp read=101940 written=101910
8. 4,186.222 8,857.044 ↓ 4.2 7,851,295 5

Hash Join (cost=831..529,663.11 rows=1,862,249 width=10) (actual time=10.811..8,857.044 rows=7,851,295 loops=5)

  • Buffers: shared hit=312422
9. 4,660.170 4,660.170 ↑ 1.2 8,476,208 5

Seq Scan on Preisregelung_Aufschlag_AufschlagDetail Preisregelung_Aufschlag_AufschlagDetail (cost=0..470,554.93 rows=10,574,584 width=12) (actual time=0.008..4,660.17 rows=8,476,208 loops=5)

  • Filter: ("Preisregelung_Aufschlag_AufschlagDetail".gueltig_seit <= CURRENT_DATE)
  • Buffers: shared hit=311626
10. 3.638 10.652 ↓ 1.0 10,452 5

Hash (cost=701.69..701.69 rows=10,345 width=6) (actual time=10.652..10.652 rows=10,452 loops=5)

  • Buffers: shared hit=684
11. 7.014 7.014 ↓ 1.0 10,452 5

Index Scan using "idx_AufschlagDetail_Kategorie_ADID" on AufschlagDetail_Kategorie AufschlagDetail_Kategorie (cost=0.29..701.69 rows=10,345 width=6) (actual time=0.022..7.014 rows=10,452 loops=5)

  • Buffers: shared hit=684
12. 65.131 192.181 ↑ 4.6 126,885 5

Hash (cost=64,708.52..64,708.52 rows=584,784 width=17) (actual time=192.181..192.181 rows=126,885 loops=5)

  • Buffers: shared hit=68129 read=1, temp written=2230
13. 77.093 127.050 ↑ 4.6 126,885 5

Bitmap Heap Scan on Aufschlag Aufschlag (cost=15,941.76..64,708.52 rows=584,784 width=17) (actual time=54.792..127.05 rows=126,885 loops=5)

  • Buffers: shared hit=68129 read=1
14. 49.957 49.957 ↑ 4.6 126,885 5

Bitmap Index Scan on "idx_Aufschlag_vonkwh_biskwh" (cost=0..15,795.56 rows=584,784 width=0) (actual time=49.957..49.957 rows=126,885 loops=5)

  • Index Cond: ((3500 >= "Aufschlag"."von_kWh") AND (3500 <= "Aufschlag"."bis_kWh"))
  • Buffers: shared hit=11674 read=1
15. 0.006 0.006 ↑ 1.0 1 3,042,638

Index Scan using "idx_ID" on Preisregelung Preisregelung (cost=0.43..0.47 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=3,042,638)

  • Index Cond: ("Preisregelung"."ID" = "Preisregelung_Aufschlag_AufschlagDetail"."Preisregelung_ID")
  • Filter: ("Preisregelung".gueltig_seit <= CURRENT_DATE)
  • Buffers: shared hit=11803140
16. 7.846 55.175 ↓ 2.2 22,968 5

Sort (cost=69,606.59..69,632.52 rows=10,373 width=4) (actual time=52.661..55.175 rows=22,968 loops=5)

  • Sort Key: "Strassenabschnitt_Gebiet"."Gebiet_ID"
  • Sort Method: quicksort Memory: 1603kB
  • Buffers: shared hit=81248
17. 45.736 47.329 ↓ 1.7 17,807 5

Nested Loop (cost=41.68..68,914.68 rows=10,373 width=4) (actual time=0.364..47.329 rows=17,807 loops=5)

  • Buffers: shared hit=81248
18. 1.341 1.560 ↑ 1.3 1,269 5

Bitmap Heap Scan on Strassenabschnitt Strassenabschnitt (cost=41.12..5,180.84 rows=1,637 width=4) (actual time=0.313..1.56 rows=1,269 loops=5)

  • Buffers: shared hit=3199
19. 0.219 0.219 ↑ 1.3 1,269 5

Bitmap Index Scan on strassenabschnitt_postort_id_index (cost=0..40.71 rows=1,637 width=0) (actual time=0.219..0.219 rows=1,269 loops=5)

  • Index Cond: ("Strassenabschnitt"."Postort_ID" = 15802)
  • Buffers: shared hit=54
20. 0.033 0.033 ↓ 1.6 14 6,345

Index Only Scan using "idx_Strassenabschnitt_Gebiet" on Strassenabschnitt_Gebiet Strassenabschnitt_Gebiet (cost=0.56..38.84 rows=9 width=8) (actual time=0.013..0.033 rows=14 loops=6,345)

  • Index Cond: (("Strassenabschnitt_Gebiet"."Strassenabschnitt_ID" = "Strassenabschnitt"."ID") AND ("Strassenabschnitt_Gebiet".gueltig_seit <= CURRENT_DATE))
  • Buffers: shared hit=78049