explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pREP

Settings
# exclusive inclusive rows x rows loops node
1. 18.566 19,454.623 ↑ 972.7 252 1

Unique (cost=915,077.71..920,593.06 rows=245,127 width=228) (actual time=19,434..19,454.623 rows=252 loops=1)

  • Buffers: shared hit=12265075, temp read=112150 written=112137
2. 147.933 19,436.057 ↑ 6.4 38,542 1

Sort (cost=915,077.71..915,690.52 rows=245,127 width=228) (actual time=19,433.998..19,436.057 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, temp read=112150 written=112137
3. 490.990 19,288.124 ↑ 6.4 38,542 1

Gather (cost=829,039.7..866,321.45 rows=245,127 width=228) (actual time=18,872.124..19,288.124 rows=38,542 loops=1)

  • Buffers: shared hit=12265075, temp read=112150 written=112137
4. 113.471 18,797.134 ↑ 8.0 7,708 5

Merge Join (cost=828,039.7..832,765.54 rows=61,282 width=228) (actual time=18,536.754..18,797.134 rows=7,708 loops=5)

  • Buffers: shared hit=12265075, temp read=112150 written=112137
5. 920.565 18,642.054 ↓ 2.4 530,805 5

Sort (cost=758,433.1..758,995.88 rows=225,114 width=23) (actual time=18,491.35..18,642.054 rows=530,805 loops=5)

  • Sort Key: "Preisregelung"."Gebiet_ID"
  • Sort Method: external merge Disk: 17648kB
  • Buffers: shared hit=12183827, temp read=112150 written=112137
6. 4,147.047 17,721.489 ↓ 2.4 530,808 5

Nested Loop (cost=76,276.76..738,420.13 rows=225,114 width=23) (actual time=201.479..17,721.489 rows=530,808 loops=5)

  • Buffers: shared hit=12183811, temp read=101393 written=101363
7. 4,431.575 13,574.436 ↓ 2.7 608,528 5

Hash Join (cost=76,276.32..631,764.97 rows=225,126 width=19) (actual time=201.449..13,574.436 rows=608,528 loops=5)

  • Buffers: shared hit=380671, temp read=101393 written=101363
8. 4,176.267 8,953.917 ↓ 4.2 7,851,295 5

Hash Join (cost=831..529,773.02 rows=1,865,761 width=10) (actual time=10.044..8,953.917 rows=7,851,295 loops=5)

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

Seq Scan on Preisregelung_Aufschlag_AufschlagDetail Preisregelung_Aufschlag_AufschlagDetail (cost=0..470,554.93 rows=10,594,528 width=12) (actual time=0.006..4,767.766 rows=8,476,208 loops=5)

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

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

  • Buffers: shared hit=684
11. 5.377 5.377 ↓ 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.024..5.377 rows=10,452 loops=5)

  • Buffers: shared hit=684
12. 65.011 188.944 ↑ 4.6 126,885 5

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

  • Buffers: shared hit=68129, temp written=2225
13. 78.821 123.933 ↑ 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=48.823..123.933 rows=126,885 loops=5)

  • Buffers: shared hit=68129
14. 45.112 45.112 ↑ 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=45.112..45.112 rows=126,885 loops=5)

  • Index Cond: ((3500 >= "Aufschlag"."von_kWh") AND (3500 <= "Aufschlag"."bis_kWh"))
  • Buffers: shared hit=11674
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.391 41.609 ↓ 2.1 22,208 5

Sort (cost=69,606.59..69,632.52 rows=10,373 width=4) (actual time=38.827..41.609 rows=22,208 loops=5)

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

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

  • Buffers: shared hit=81248
18. 1.269 1.465 ↑ 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.282..1.465 rows=1,269 loops=5)

  • Buffers: shared hit=3199
19. 0.196 0.196 ↑ 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.196..0.196 rows=1,269 loops=5)

  • Index Cond: ("Strassenabschnitt"."Postort_ID" = 15802)
  • Buffers: shared hit=54
20. 0.023 0.023 ↓ 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.008..0.023 rows=14 loops=6,345)

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