explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JuiG

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 8,426.593 ↑ 1.0 30 1

Limit (cost=98,039.00..98,039.07 rows=30 width=1,166) (actual time=8,426.582..8,426.593 rows=30 loops=1)

2. 536.501 8,426.583 ↑ 305.8 30 1

Sort (cost=98,039.00..98,061.93 rows=9,174 width=1,166) (actual time=8,426.579..8,426.583 rows=30 loops=1)

  • Sort Key: sub.nielsengebiet NULLS FIRST, sub.bundesland NULLS FIRST, sub.ort NULLS FIRST
  • Sort Method: top-N heapsort Memory: 45kB
3. 217.403 7,890.082 ↓ 31.6 289,840 1

Subquery Scan on sub (cost=94,878.24..97,768.05 rows=9,174 width=1,166) (actual time=4,636.859..7,890.082 rows=289,840 loops=1)

4. 2,925.020 7,672.679 ↓ 31.6 289,840 1

GroupAggregate (cost=94,878.24..97,676.31 rows=9,174 width=1,275) (actual time=4,636.856..7,672.679 rows=289,840 loops=1)

  • Group Key: twv.paechternummer, tan.anbieter, twv.standortnummer, twv.anz_flaechen_am_standort, twv.flaechennummer, twv.qid, twv.flaechenbezeichnung, tta.terminart, twv.block, tgb.bundesland_abk, tgn.nielsengebiet, twv.ortsnummer, tgo.ort, twv.ortsteil, twv.plz, twv.hauptstellenart, NULL::text, twv.stellenart, NULL::text, twv.beleuchtung, NULL::text, tvm.marktnummer, tvm.verbrauchermarkt, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, twv.produktsperren, NULL::text, ((concat(twv.paechternummer, '-', twv.versandanschriftid))::character varying), NULL::text, twv.preisgruppe, NULL::text, twv.tagespreis, NULL::text, twv.bauart_kurz, NULL::text, twv.flag_einzelstellenvermarktung, NULL::text
5. 3,086.793 4,747.659 ↓ 31.6 289,840 1

Sort (cost=94,878.24..94,901.17 rows=9,174 width=792) (actual time=4,636.774..4,747.659 rows=289,840 loops=1)

  • Sort Key: twv.paechternummer, tan.anbieter, twv.standortnummer, twv.anz_flaechen_am_standort, twv.flaechennummer, twv.qid, twv.flaechenbezeichnung, tta.terminart, twv.block, tgb.bundesland_abk, tgn.nielsengebiet, twv.ortsnummer, tgo.ort, twv.ortsteil, twv.plz, twv.hauptstellenart, twv.stellenart, twv.beleuchtung, tvm.marktnummer, tvm.verbrauchermarkt, twv.produktsperren, ((concat(twv.paechternummer, '-', twv.versandanschriftid))::character varying), twv.preisgruppe, twv.tagespreis, twv.bauart_kurz, twv.flag_einzelstellenvermarktung
  • Sort Method: quicksort Memory: 112163kB
6. 427.455 1,660.866 ↓ 31.6 289,840 1

Hash Full Join (cost=1,009.00..94,274.44 rows=9,174 width=792) (actual time=4.925..1,660.866 rows=289,840 loops=1)

  • Hash Cond: ((twv.standortnummer = standortnummer) AND (twv.paechternummer = paechternummer) AND (twv.flaechennummer = flaechennummer))
7. 0.000 1,233.407 ↓ 31.6 289,840 1

Gather (cost=1,009.00..94,171.22 rows=9,174 width=273) (actual time=4.903..1,233.407 rows=289,840 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 107.885 1,501.765 ↓ 25.3 96,613 3

Hash Join (cost=9.00..92,253.82 rows=3,822 width=273) (actual time=1.439..1,501.765 rows=96,613 loops=3)

  • Hash Cond: (twv.anbieterid = tan.anbieterid)
9. 218.679 1,393.749 ↓ 25.3 96,613 3

Nested Loop Left Join (cost=7.19..92,204.70 rows=3,822 width=255) (actual time=0.992..1,393.749 rows=96,613 loops=3)

10. 104.349 1,175.069 ↓ 25.3 96,613 3

Hash Join (cost=6.78..90,371.81 rows=3,822 width=263) (actual time=0.979..1,175.069 rows=96,613 loops=3)

  • Hash Cond: (tgo.bundeslandnummer = tgb.bundeslandnummer)
11. 519.630 1,070.499 ↓ 25.3 96,613 3

Nested Loop (cost=3.86..90,316.34 rows=3,822 width=254) (actual time=0.651..1,070.499 rows=96,613 loops=3)

12. 120.715 550.865 ↓ 25.2 96,613 3

Hash Join (cost=3.57..89,009.32 rows=3,829 width=242) (actual time=0.529..550.865 rows=96,613 loops=3)

  • Hash Cond: (((COALESCE(twv.bauart_kurz, ' '::character varying))::text = (tba.bauart_kurz)::text) AND (twv.terminartid = tta.terminartid))
13. 429.943 429.943 ↓ 1.5 96,613 3

Parallel Index Scan using t_werbeflaechen_versionen_idx_test on t_werbeflaechen_versionen twv (cost=0.42..88,170.14 rows=63,820 width=244) (actual time=0.236..429.943 rows=96,613 loops=3)

  • Index Cond: (jahr = 2019)
14. 0.033 0.207 ↑ 1.0 36 3

Hash (cost=2.61..2.61 rows=36 width=8) (actual time=0.207..0.207 rows=36 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
15. 0.109 0.174 ↑ 1.0 36 3

Nested Loop (cost=0.00..2.61 rows=36 width=8) (actual time=0.116..0.174 rows=36 loops=3)

16. 0.059 0.059 ↑ 1.0 12 3

Seq Scan on t_bauarten tba (cost=0.00..1.12 rows=12 width=2) (actual time=0.055..0.059 rows=12 loops=3)

17. 0.000 0.006 ↑ 1.0 3 36

Materialize (cost=0.00..1.04 rows=3 width=6) (actual time=0.005..0.006 rows=3 loops=36)

18. 0.040 0.040 ↑ 1.0 3 3

Seq Scan on t_terminarten tta (cost=0.00..1.03 rows=3 width=6) (actual time=0.038..0.040 rows=3 loops=3)

19. 0.004 0.004 ↑ 1.0 1 289,840

Index Scan using t_geo_orte_pkey on t_geo_orte tgo (cost=0.29..0.34 rows=1 width=24) (actual time=0.004..0.004 rows=1 loops=289,840)

  • Index Cond: ((jahr = 2019) AND (ortsnummer = twv.ortsnummer))
20. 0.019 0.221 ↑ 1.0 16 3

Hash (cost=2.71..2.71 rows=16 width=21) (actual time=0.221..0.221 rows=16 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.058 0.202 ↑ 1.0 16 3

Hash Join (cost=1.36..2.71 rows=16 width=21) (actual time=0.181..0.202 rows=16 loops=3)

  • Hash Cond: (tgn.bundeslandnummer = tgb.bundeslandnummer)
22. 0.072 0.072 ↑ 1.0 16 3

Seq Scan on t_geo_nielsengebiete tgn (cost=0.00..1.16 rows=16 width=14) (actual time=0.067..0.072 rows=16 loops=3)

23. 0.020 0.072 ↑ 1.0 16 3

Hash (cost=1.16..1.16 rows=16 width=7) (actual time=0.072..0.072 rows=16 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.052 0.052 ↑ 1.0 16 3

Seq Scan on t_geo_bundeslaender tgb (cost=0.00..1.16 rows=16 width=7) (actual time=0.044..0.052 rows=16 loops=3)

25. 0.001 0.001 ↓ 0.0 0 289,840

Index Scan using t_verbrauchermaerkte_pkey on t_verbrauchermaerkte tvm (cost=0.42..0.48 rows=1 width=29) (actual time=0.001..0.001 rows=0 loops=289,840)

  • Index Cond: (verbrauchermarktid = (twv.verbrauchermarktids)[1])
26. 0.040 0.131 ↑ 1.0 36 3

Hash (cost=1.36..1.36 rows=36 width=34) (actual time=0.131..0.131 rows=36 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
27. 0.091 0.091 ↑ 1.0 36 3

Seq Scan on t_anbieter tan (cost=0.00..1.36 rows=36 width=34) (actual time=0.072..0.091 rows=36 loops=3)

28. 0.002 0.004 ↓ 0.0 0 1

Hash (cost=0.00..0.00 rows=0 width=55) (actual time=0.004..0.004 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
29. 0.002 0.002 ↓ 0.0 0 1

Result (cost=0.00..0.00 rows=0 width=55) (actual time=0.002..0.002 rows=0 loops=1)

  • One-Time Filter: false