explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Wc6i

Settings
# exclusive inclusive rows x rows loops node
1. 4,496.899 13,230.692 ↓ 1.9 289,840 1

Sort (cost=185,214.25..185,596.49 rows=152,894 width=1,166) (actual time=13,105.426..13,230.692 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. 242.645 8,733.793 ↓ 1.9 289,840 1

Subquery Scan on sub (cost=123,886.81..172,048.42 rows=152,894 width=1,166) (actual time=5,150.960..8,733.793 rows=289,840 loops=1)

3. 3,234.583 8,491.148 ↓ 1.9 289,840 1

GroupAggregate (cost=123,886.81..170,519.48 rows=152,894 width=1,275) (actual time=5,150.955..8,491.148 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,599.787 5,256.565 ↓ 1.9 289,840 1

Sort (cost=123,886.81..124,269.04 rows=152,894 width=792) (actual time=5,150.852..5,256.565 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. 304.852 2,656.778 ↓ 1.9 289,840 1

Hash Full Join (cost=11,926.24..110,720.98 rows=152,894 width=792) (actual time=392.815..2,656.778 rows=289,840 loops=1)

  • Hash Cond: ((twv.standortnummer = standortnummer) AND (twv.paechternummer = paechternummer) AND (twv.flaechennummer = flaechennummer))
6. 212.596 2,351.921 ↓ 1.9 289,840 1

Hash Left Join (cost=11,926.24..109,000.91 rows=152,894 width=273) (actual time=392.779..2,351.921 rows=289,840 loops=1)

  • Hash Cond: (twv.terminartid = tta.terminartid)
7. 218.290 2,139.292 ↓ 1.9 289,840 1

Hash Join (cost=11,925.17..107,521.87 rows=152,894 width=275) (actual time=392.712..2,139.292 rows=289,840 loops=1)

  • Hash Cond: (twv.anbieterid = tan.anbieterid)
8. 242.149 1,920.808 ↓ 1.9 289,840 1

Hash Left Join (cost=11,923.36..105,627.46 rows=152,894 width=257) (actual time=392.504..1,920.808 rows=289,840 loops=1)

  • Hash Cond: (twv.verbrauchermarktids[1] = tvm.verbrauchermarktid)
9. 276.439 1,331.596 ↓ 1.9 289,840 1

Hash Join (cost=3,639.34..95,413.17 rows=152,894 width=265) (actual time=44.110..1,331.596 rows=289,840 loops=1)

  • Hash Cond: (twv.ortsnummer = tgo.ortsnummer)
10. 1,011.471 1,011.471 ↓ 1.9 289,840 1

Index Scan using t_werbeflaechen_versionen_idx_test on t_werbeflaechen_versionen twv (cost=0.42..89,288.00 rows=153,169 width=244) (actual time=0.230..1,011.471 rows=289,840 loops=1)

  • Index Cond: (jahr = 2019)
11. 9.881 43.686 ↑ 1.0 11,115 1

Hash (cost=3,499.98..3,499.98 rows=11,115 width=33) (actual time=43.686..43.686 rows=11,115 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 861kB
12. 15.648 33.805 ↑ 1.0 11,115 1

Hash Join (cost=3.20..3,499.98 rows=11,115 width=33) (actual time=0.274..33.805 rows=11,115 loops=1)

  • Hash Cond: (tgo.bundeslandnummer = tgb.bundeslandnummer)
13. 17.973 17.973 ↑ 1.0 11,115 1

Index Scan using t_geo_orte_idx2 on t_geo_orte tgo (cost=0.29..3,344.24 rows=11,115 width=24) (actual time=0.073..17.973 rows=11,115 loops=1)

  • Index Cond: (jahr = 2019)
14. 0.019 0.184 ↑ 1.0 16 1

Hash (cost=2.71..2.71 rows=16 width=21) (actual time=0.184..0.184 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.113 0.165 ↑ 1.0 16 1

Hash Join (cost=1.36..2.71 rows=16 width=21) (actual time=0.144..0.165 rows=16 loops=1)

  • Hash Cond: (tgn.bundeslandnummer = tgb.bundeslandnummer)
16. 0.020 0.020 ↑ 1.0 16 1

Seq Scan on t_geo_nielsengebiete tgn (cost=0.00..1.16 rows=16 width=14) (actual time=0.015..0.020 rows=16 loops=1)

17. 0.013 0.032 ↑ 1.0 16 1

Hash (cost=1.16..1.16 rows=16 width=7) (actual time=0.032..0.032 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.019 0.019 ↑ 1.0 16 1

Seq Scan on t_geo_bundeslaender tgb (cost=0.00..1.16 rows=16 width=7) (actual time=0.011..0.019 rows=16 loops=1)

19. 86.314 347.063 ↑ 1.0 112,223 1

Hash (cost=6,881.23..6,881.23 rows=112,223 width=29) (actual time=347.063..347.063 rows=112,223 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 8199kB
20. 260.749 260.749 ↑ 1.0 112,223 1

Seq Scan on t_verbrauchermaerkte tvm (cost=0.00..6,881.23 rows=112,223 width=29) (actual time=0.471..260.749 rows=112,223 loops=1)

21. 0.081 0.194 ↑ 1.0 36 1

Hash (cost=1.36..1.36 rows=36 width=34) (actual time=0.194..0.194 rows=36 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
22. 0.113 0.113 ↑ 1.0 36 1

Seq Scan on t_anbieter tan (cost=0.00..1.36 rows=36 width=34) (actual time=0.060..0.113 rows=36 loops=1)

23. 0.013 0.033 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=6) (actual time=0.033..0.033 rows=3 loops=1)

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

Seq Scan on t_terminarten tta (cost=0.00..1.03 rows=3 width=6) (actual time=0.019..0.020 rows=3 loops=1)

25. 0.003 0.005 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
26. 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