explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ph4f : Optimization for: plan #Hjdo

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 9.856 10,939.736 ↑ 1.0 30 1

Limit (cost=512,256.59..512,256.67 rows=30 width=869) (actual time=10,929.882..10,939.736 rows=30 loops=1)

2. 349.321 10,929.880 ↑ 10,945.4 30 1

Sort (cost=512,256.59..513,077.50 rows=328,362 width=869) (actual time=10,929.879..10,929.880 rows=30 loops=1)

  • Sort Key: sub.nielsengebiet NULLS FIRST, sub.bundesland NULLS FIRST, sub.ort NULLS FIRST
  • Sort Method: top-N heapsort Memory: 46kB
3. 64.857 10,580.559 ↓ 1.0 331,965 1

Subquery Scan on sub (cost=446,737.06..502,558.60 rows=328,362 width=869) (actual time=7,951.445..10,580.559 rows=331,965 loops=1)

4. 1,170.932 10,515.702 ↓ 1.0 331,965 1

GroupAggregate (cost=446,737.06..499,274.98 rows=328,362 width=915) (actual time=7,951.444..10,515.702 rows=331,965 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
5. 8,418.515 9,344.770 ↓ 1.0 331,965 1

Sort (cost=446,737.06..447,557.96 rows=328,362 width=715) (actual time=7,951.388..9,344.770 rows=331,965 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: external merge Disk: 76456kB
6. 25.566 926.255 ↓ 1.0 331,965 1

Gather (cost=9,439.46..207,897.01 rows=328,362 width=715) (actual time=95.555..926.255 rows=331,965 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 52.267 900.689 ↑ 1.2 110,655 3

Hash Left Join (cost=8,439.46..174,060.81 rows=136,818 width=715) (actual time=48.270..900.689 rows=110,655 loops=3)

  • Hash Cond: (twv.terminartid = tta.terminartid)
8. 29.038 848.237 ↑ 1.2 110,655 3

Hash Join (cost=8,438.39..172,851.18 rows=136,818 width=687) (actual time=48.049..848.237 rows=110,655 loops=3)

  • Hash Cond: (twv.anbieterid = tan.anbieterid)
9. 35.229 819.007 ↑ 1.2 110,655 3

Parallel Hash Left Join (cost=8,436.65..172,444.17 rows=136,818 width=377) (actual time=47.732..819.007 rows=110,655 loops=3)

  • Hash Cond: (twv.verbrauchermarktids[1] = tvm.verbrauchermarktid)
10. 45.613 759.286 ↑ 1.2 110,655 3

Parallel Hash Join (cost=4,587.08..168,235.45 rows=136,818 width=385) (actual time=22.639..759.286 rows=110,655 loops=3)

  • Hash Cond: (twv.ortsnummer = tgo.ortsnummer)
11. 692.039 692.039 ↑ 1.2 110,655 3

Parallel Seq Scan on t_werbeflaechen_versionen twv (cost=0.00..162,564.19 rows=137,088 width=221) (actual time=0.892..692.039 rows=110,655 loops=3)

  • Filter: ((stammdatenlieferung_bis IS NULL) AND upper_inf(zeitraum) AND (jahr = 2019))
  • Rows Removed by Filter: 616395
12. 0.000 21.634 ↑ 1.2 3,705 3

Parallel Hash (cost=4,529.19..4,529.19 rows=4,631 width=176) (actual time=21.634..21.634 rows=3,705 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 960kB
13. 1.593 30.802 ↓ 1.2 5,558 2

Hash Join (cost=2.77..4,529.19 rows=4,631 width=176) (actual time=0.490..30.802 rows=5,558 loops=2)

  • Hash Cond: (tgo.bundeslandnummer = tgb.bundeslandnummer)
14. 28.959 28.959 ↓ 1.2 5,558 2

Parallel Seq Scan on t_geo_orte tgo (cost=0.00..4,462.74 rows=4,631 width=24) (actual time=0.225..28.959 rows=5,558 loops=2)

  • Filter: (jahr = 2019)
  • Rows Removed by Filter: 11122
15. 0.008 0.250 ↑ 1.0 16 2

Hash (cost=2.57..2.57 rows=16 width=164) (actual time=0.250..0.250 rows=16 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.016 0.242 ↑ 1.0 16 2

Hash Join (cost=1.36..2.57 rows=16 width=164) (actual time=0.238..0.242 rows=16 loops=2)

  • Hash Cond: (tgn.bundeslandnummer = tgb.bundeslandnummer)
17. 0.102 0.102 ↑ 1.0 16 2

Seq Scan on t_geo_nielsengebiete tgn (cost=0.00..1.16 rows=16 width=122) (actual time=0.102..0.102 rows=16 loops=2)

18. 0.014 0.124 ↑ 1.0 16 2

Hash (cost=1.16..1.16 rows=16 width=42) (actual time=0.124..0.124 rows=16 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.110 0.110 ↑ 1.0 16 2

Seq Scan on t_geo_bundeslaender tgb (cost=0.00..1.16 rows=16 width=42) (actual time=0.108..0.110 rows=16 loops=2)

20. 0.574 24.492 ↑ 1.8 37,137 3

Parallel Hash (cost=3,030.36..3,030.36 rows=65,536 width=29) (actual time=24.492..24.492 rows=37,137 loops=3)

  • Buckets: 131072 Batches: 1 Memory Usage: 8192kB
21. 23.918 23.918 ↑ 1.2 55,706 2

Parallel Seq Scan on t_verbrauchermaerkte tvm (cost=0.00..3,030.36 rows=65,536 width=29) (actual time=0.130..23.918 rows=55,706 loops=2)

22. 0.011 0.192 ↑ 1.0 33 3

Hash (cost=1.33..1.33 rows=33 width=326) (actual time=0.192..0.192 rows=33 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
23. 0.181 0.181 ↑ 1.0 33 3

Seq Scan on t_anbieter tan (cost=0.00..1.33 rows=33 width=326) (actual time=0.178..0.181 rows=33 loops=3)

24. 0.009 0.185 ↑ 1.0 3 3

Hash (cost=1.03..1.03 rows=3 width=12) (actual time=0.185..0.185 rows=3 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 0.176 0.176 ↑ 1.0 3 3

Seq Scan on t_terminarten tta (cost=0.00..1.03 rows=3 width=12) (actual time=0.175..0.176 rows=3 loops=3)