explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Sf0B : Bonus query (using with); plan #qC4o

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.054 18,380.070 ↑ 19,345.5 200 1

Unique (cost=3,155,770.95..3,242,825.81 rows=3,869,105 width=228) (actual time=18,380.005..18,380.070 rows=200 loops=1)

2.          

CTE Pr_Str

3. 3.550 169.919 ↑ 2,528.2 949 1

Unique (cost=422,700.41..434,696.69 rows=2,399,255 width=4) (actual time=160.443..169.919 rows=949 loops=1)

4. 21.873 166.369 ↑ 41.4 57,903 1

Sort (cost=422,700.41..428,698.55 rows=2,399,255 width=4) (actual time=160.442..166.369 rows=57,903 loops=1)

  • Sort Key: "Preisregelung"."ID
  • Sort Method: quicksort Memory: 4,251kB
5. 15.718 144.496 ↑ 41.4 57,903 1

Nested Loop (cost=1,001.13..135,644.00 rows=2,399,255 width=4) (actual time=2.951..144.496 rows=57,903 loops=1)

6. 0.000 54.018 ↑ 1.2 7,476 1

Gather (cost=1,000.57..56,018.77 rows=8,933 width=4) (actual time=2.884..54.018 rows=7,476 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
7. 0.696 121.957 ↑ 1.5 1,869 4 / 4

Nested Loop (cost=0.57..54,125.47 rows=2,882 width=4) (actual time=1.861..121.957 rows=1,869 loops=4)

8. 116.566 116.566 ↑ 1.8 235 4 / 4

Parallel Seq Scan on "Strassenabschnitt" (cost=0.00..34,585.19 rows=417 width=4) (actual time=1.818..116.566 rows=235 loops=4)

  • Filter: ("Postort_ID" = 11,938)
  • Rows Removed by Filter: 646,065
9. 4.695 4.695 ↑ 1.4 8 939 / 4

Index Only Scan using "IX_Strassenabschnitt_Gebiet_StrAbID_gs_gb_GebID" on "Strassenabschnitt_Gebiet" (cost=0.57..46.75 rows=11 width=8) (actual time=0.012..0.020 rows=8 loops=939)

  • Index Cond: (("Strassenabschnitt_ID" = "Strassenabschnitt"."ID") AND (gueltig_seit <= ('now'::cstring)::date))
  • Heap Fetches: 679
10. 74.760 74.760 ↑ 34.5 8 7,476

Index Scan using "IX_Preisregelung_GID_TID_gs_gb" on "Preisregelung" (cost=0.56..6.15 rows=276 width=8) (actual time=0.005..0.010 rows=8 loops=7,476)

  • Index Cond: (("Gebiet_ID" = "Strassenabschnitt_Gebiet"."Gebiet_ID") AND (gueltig_seit <= ('now'::cstring)::date))
11. 1.097 18,380.016 ↑ 19,154.0 202 1

Sort (cost=2,721,074.26..2,730,747.02 rows=3,869,105 width=228) (actual time=18,380.004..18,380.016 rows=202 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: 41kB
12. 426.307 18,378.919 ↑ 19,154.0 202 1

Hash Join (cost=817,935.53..1,451,355.14 rows=3,869,105 width=228) (actual time=17,946.269..18,378.919 rows=202 loops=1)

  • Hash Cond: ("Pr_Str"."ID" = "Preisregelung_Aufschlag_AufschlagDetail"."Preisregelung_ID")
13. 170.083 170.083 ↑ 2,528.2 949 1

CTE Scan on "Pr_Str" (cost=0.00..47,985.10 rows=2,399,255 width=4) (actual time=160.445..170.083 rows=949 loops=1)

14. 1,336.779 17,782.529 ↓ 3.4 2,829,034 1

Hash (cost=802,790.80..802,790.80 rows=824,859 width=19) (actual time=17,782.529..17,782.529 rows=2,829,034 loops=1)

  • Buckets: 262,144 (originally 262144) Batches: 16 (originally 8) Memory Usage: 10,849kB
15. 49.565 16,445.750 ↓ 3.4 2,829,034 1

Gather (cost=75,109.66..802,790.80 rows=824,859 width=19) (actual time=232.867..16,445.750 rows=2,829,034 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
16. 3,751.343 16,396.185 ↓ 2.7 565,807 5 / 5

Hash Join (cost=74,109.66..719,304.90 rows=206,215 width=19) (actual time=253.315..16,396.185 rows=565,807 loops=5)

  • Hash Cond: ("Preisregelung_Aufschlag_AufschlagDetail"."Aufschlag_ID" = "Aufschlag"."ID")
17. 3,809.808 12,404.158 ↓ 4.1 6,972,673 5 / 5

Hash Join (cost=803.53..617,614.40 rows=1,693,122 width=10) (actual time=10.028..12,404.158 rows=6,972,673 loops=5)

  • Hash Cond: ("Preisregelung_Aufschlag_AufschlagDetail"."AufschlagDetail_ID" = "AufschlagDetail_Kategorie"."AufschlagDetail_ID")
18. 8,584.528 8,584.528 ↑ 1.3 7,599,986 5 / 5

Parallel Seq Scan on "Preisregelung_Aufschlag_AufschlagDetail" (cost=0.00..563,955.45 rows=9,579,787 width=12) (actual time=0.055..8,584.528 rows=7,599,986 loops=5)

  • Filter: (gueltig_seit <= ('now'::cstring)::date)
  • Rows Removed by Filter: 446,874
19. 2.861 9.822 ↓ 1.0 10,451 5 / 5

Hash (cost=673.75..673.75 rows=10,382 width=6) (actual time=9.822..9.822 rows=10,451 loops=5)

  • Buckets: 16,384 Batches: 1 Memory Usage: 537kB
20. 6.961 6.961 ↓ 1.0 10,451 5 / 5

Index Scan using "idx_AufschlagDetail_Kategorie_ADID" on "AufschlagDetail_Kategorie" (cost=0.29..673.75 rows=10,382 width=6) (actual time=0.042..6.961 rows=10,451 loops=5)

21. 69.493 240.684 ↑ 4.6 126,667 5 / 5

Hash (cost=62,537.16..62,537.16 rows=586,558 width=17) (actual time=240.684..240.684 rows=126,667 loops=5)

  • Buckets: 262,144 Batches: 4 Memory Usage: 3,605kB
22. 92.170 171.191 ↑ 4.6 126,667 5 / 5

Bitmap Heap Scan on "Aufschlag" (cost=13,992.79..62,537.16 rows=586,558 width=17) (actual time=83.351..171.191 rows=126,667 loops=5)

  • Recheck Cond: ((3500 >= "von_kWh") AND (3500 <= "bis_kWh"))
  • Heap Blocks: exact=11,260
23. 79.021 79.021 ↑ 4.6 126,667 5 / 5

Bitmap Index Scan on "idx_Aufschlag_vonkwh_biskwh" (cost=0.00..13,846.15 rows=586,558 width=0) (actual time=79.021..79.021 rows=126,667 loops=5)

  • Index Cond: ((3500 >= "von_kWh") AND (3500 <= "bis_kWh"))
Planning time : 5.570 ms
Execution time : 18,382.041 ms