explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Hjdo

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 4,400.736 23,537.861 ↓ 1.9 289,840 1

Sort (cost=161,331.95..161,714.18 rows=152,894 width=426) (actual time=23,452.727..23,537.861 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. 170.770 19,137.125 ↓ 1.9 289,840 1

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

3. 1,925.915 18,966.355 ↓ 1.9 289,840 1

GroupAggregate (cost=122,174.13..146,637.17 rows=152,894 width=471) (actual time=16,942.456..18,966.355 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. 14,876.558 17,040.440 ↓ 1.9 289,840 1

Sort (cost=122,174.13..122,556.37 rows=152,894 width=273) (actual time=16,942.403..17,040.440 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. 535.710 2,163.882 ↓ 1.9 289,840 1

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

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

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

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

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

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

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

  • Hash Cond: (twv.ortsnummer = tgo.ortsnummer)
9. 596.955 596.955 ↓ 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.150..596.955 rows=289,840 loops=1)

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

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

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

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

  • Hash Cond: (tgo.bundeslandnummer = tgb.bundeslandnummer)
12. 66.463 66.463 ↑ 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.099..66.463 rows=11,115 loops=1)

  • Index Cond: (jahr = 2019)
13. 0.034 0.212 ↑ 1.0 16 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.056 0.178 ↑ 1.0 16 1

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

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

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

16. 0.040 0.074 ↑ 1.0 16 1

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

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

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

18. 80.400 204.451 ↑ 1.0 112,223 1

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

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

20. 0.027 0.069 ↑ 1.0 36 1

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

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

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

22. 0.011 0.025 ↑ 1.0 3 1

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

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

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