explain.depesz.com

PostgreSQL's explain analyze made readable

Result: E0iP

Settings
# exclusive inclusive rows x rows loops node
1. 4,430.367 12,359.606 ↓ 31.6 289,840 1

Sort (cost=98,372.86..98,395.80 rows=9,174 width=1,166) (actual time=12,289.577..12,359.606 rows=289,840 loops=1)

  • Sort Key: sub.nielsengebiet NULLS FIRST, sub.bundesland NULLS FIRST, sub.ort NULLS FIRST
  • Sort Method: quicksort Memory: 160854kB
2. 237.085 7,929.239 ↓ 31.6 289,840 1

Subquery Scan on sub (cost=94,879.25..97,769.06 rows=9,174 width=1,166) (actual time=4,328.990..7,929.239 rows=289,840 loops=1)

3. 3,236.959 7,692.154 ↓ 31.6 289,840 1

GroupAggregate (cost=94,879.25..97,677.32 rows=9,174 width=1,275) (actual time=4,328.986..7,692.154 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
4. 2,606.742 4,455.195 ↓ 31.6 289,840 1

Sort (cost=94,879.25..94,902.18 rows=9,174 width=792) (actual time=4,328.864..4,455.195 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
5. 430.410 1,848.453 ↓ 31.6 289,840 1

Hash Full Join (cost=1,009.00..94,275.45 rows=9,174 width=792) (actual time=5.041..1,848.453 rows=289,840 loops=1)

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

Gather (cost=1,009.00..94,172.23 rows=9,174 width=273) (actual time=5.019..1,418.040 rows=289,840 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 111.651 1,716.749 ↓ 25.3 96,613 3

Hash Join (cost=9.00..92,254.83 rows=3,822 width=273) (actual time=1.507..1,716.749 rows=96,613 loops=3)

  • Hash Cond: (twv.anbieterid = tan.anbieterid)
8. 227.677 1,604.974 ↓ 25.3 96,613 3

Nested Loop Left Join (cost=7.19..92,205.71 rows=3,822 width=255) (actual time=1.119..1,604.974 rows=96,613 loops=3)

9. 111.263 1,377.296 ↓ 25.3 96,613 3

Hash Join (cost=6.78..90,372.82 rows=3,822 width=263) (actual time=1.104..1,377.296 rows=96,613 loops=3)

  • Hash Cond: (tgo.bundeslandnummer = tgb.bundeslandnummer)
10. 548.514 1,265.806 ↓ 25.3 96,613 3

Nested Loop (cost=3.86..90,317.35 rows=3,822 width=254) (actual time=0.636..1,265.806 rows=96,613 loops=3)

11. 133.479 717.288 ↓ 25.2 96,613 3

Hash Join (cost=3.57..89,010.33 rows=3,829 width=242) (actual time=0.527..717.288 rows=96,613 loops=3)

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

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

  • Index Cond: (jahr = 2019)
13. 0.037 0.214 ↑ 1.0 36 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
14. 0.111 0.177 ↑ 1.0 36 3

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

15. 0.060 0.060 ↑ 1.0 12 3

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

16. 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)

17. 0.038 0.038 ↑ 1.0 3 3

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

18. 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))
19. 0.019 0.227 ↑ 1.0 16 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.064 0.208 ↑ 1.0 16 3

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

  • Hash Cond: (tgn.bundeslandnummer = tgb.bundeslandnummer)
21. 0.073 0.073 ↑ 1.0 16 3

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

22. 0.020 0.071 ↑ 1.0 16 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.051 0.051 ↑ 1.0 16 3

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

24. 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])
25. 0.039 0.124 ↑ 1.0 36 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
26. 0.085 0.085 ↑ 1.0 36 3

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

27. 0.002 0.003 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
28. 0.001 0.001 ↓ 0.0 0 1

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

  • One-Time Filter: false