explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gMnk

Settings
# exclusive inclusive rows x rows loops node
1. 3,451.599 19,860.974 ↓ 1.9 289,840 1

Sort (cost=161,331.95..161,714.18 rows=152,894 width=426) (actual time=19,783.323..19,860.974 rows=289,840 loops=1)

  • Sort Key: sub.nielsengebiet NULLS FIRST, sub.bundesland NULLS FIRST, sub.ort NULLS FIRST
  • Sort Method: quicksort Memory: 141095kB
2. 166.311 16,409.375 ↓ 1.9 289,840 1

Subquery Scan on sub (cost=122,174.13..148,166.11 rows=152,894 width=426) (actual time=14,372.639..16,409.375 rows=289,840 loops=1)

3. 1,774.138 16,243.064 ↓ 1.9 289,840 1

GroupAggregate (cost=122,174.13..146,637.17 rows=152,894 width=471) (actual time=14,372.636..16,243.064 rows=289,840 loops=1)

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

Sort (cost=122,174.13..122,556.37 rows=152,894 width=273) (actual time=14,372.590..14,468.926 rows=289,840 loops=1)

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

Hash Left Join (cost=11,934.32..109,008.30 rows=152,894 width=273) (actual time=231.188..2,027.308 rows=289,840 loops=1)

  • Hash Cond: (twv.terminartid = tta.terminartid)
6. 211.027 1,577.219 ↓ 1.9 289,840 1

Hash Join (cost=11,933.25..107,147.03 rows=152,894 width=251) (actual time=231.073..1,577.219 rows=289,840 loops=1)

  • Hash Cond: (twv.anbieterid = tan.anbieterid)
7. 203.010 1,366.120 ↓ 1.9 289,840 1

Hash Left Join (cost=11,931.44..105,252.62 rows=152,894 width=233) (actual time=230.988..1,366.120 rows=289,840 loops=1)

  • Hash Cond: (twv.verbrauchermarktids[1] = tvm.verbrauchermarktid)
8. 236.046 969.980 ↓ 1.9 289,840 1

Hash Join (cost=3,647.42..95,038.33 rows=152,894 width=241) (actual time=36.898..969.980 rows=289,840 loops=1)

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

Index Scan using t_werbeflaechen_versionen_idx_test on t_werbeflaechen_versionen twv (cost=0.42..88,905.08 rows=153,169 width=220) (actual time=0.128..697.291 rows=289,840 loops=1)

  • Index Cond: (jahr = 2019)
10. 8.882 36.643 ↑ 1.0 11,115 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 861kB
11. 14.152 27.761 ↑ 1.0 11,115 1

Hash Join (cost=3.20..3,508.06 rows=11,115 width=33) (actual time=0.123..27.761 rows=11,115 loops=1)

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

Index Scan using t_geo_orte_pkey on t_geo_orte tgo (cost=0.29..3,352.32 rows=11,115 width=24) (actual time=0.019..13.514 rows=11,115 loops=1)

  • Index Cond: (jahr = 2019)
13. 0.015 0.095 ↑ 1.0 16 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.025 0.080 ↑ 1.0 16 1

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

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

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

16. 0.016 0.030 ↑ 1.0 16 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.014 0.014 ↑ 1.0 16 1

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

18. 76.769 193.130 ↑ 1.0 112,223 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 8199kB
19. 116.361 116.361 ↑ 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.056..116.361 rows=112,223 loops=1)

20. 0.033 0.072 ↑ 1.0 36 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
21. 0.039 0.039 ↑ 1.0 36 1

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

22. 0.011 0.029 ↑ 1.0 3 1

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

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

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