explain.depesz.com

PostgreSQL's explain analyze made readable

Result: e2Jl

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

Sort (cost=98,371.85..98,394.79 rows=9,174 width=1,166) (actual time=12,127.924..12,229.664 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. 196.480 7,638.927 ↓ 31.6 289,840 1

Subquery Scan on sub (cost=94,878.24..97,768.05 rows=9,174 width=1,166) (actual time=4,611.032..7,638.927 rows=289,840 loops=1)

3. 2,719.818 7,442.447 ↓ 31.6 289,840 1

GroupAggregate (cost=94,878.24..97,676.31 rows=9,174 width=1,275) (actual time=4,611.029..7,442.447 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,796.440 4,722.629 ↓ 31.6 289,840 1

Sort (cost=94,878.24..94,901.17 rows=9,174 width=792) (actual time=4,610.940..4,722.629 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. 423.091 1,926.189 ↓ 31.6 289,840 1

Hash Full Join (cost=1,009.00..94,274.44 rows=9,174 width=792) (actual time=5.299..1,926.189 rows=289,840 loops=1)

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

Gather (cost=1,009.00..94,171.22 rows=9,174 width=273) (actual time=5.279..1,503.095 rows=289,840 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 116.939 1,798.563 ↓ 25.3 96,613 3

Hash Join (cost=9.00..92,253.82 rows=3,822 width=273) (actual time=1.482..1,798.563 rows=96,613 loops=3)

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

Nested Loop Left Join (cost=7.19..92,204.70 rows=3,822 width=255) (actual time=1.059..1,681.486 rows=96,613 loops=3)

9. 113.029 1,446.884 ↓ 25.3 96,613 3

Hash Join (cost=6.78..90,371.81 rows=3,822 width=263) (actual time=0.983..1,446.884 rows=96,613 loops=3)

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

Nested Loop (cost=3.86..90,316.34 rows=3,822 width=254) (actual time=0.652..1,333.625 rows=96,613 loops=3)

11. 139.175 760.065 ↓ 25.2 96,613 3

Hash Join (cost=3.57..89,009.32 rows=3,829 width=242) (actual time=0.525..760.065 rows=96,613 loops=3)

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

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

  • Index Cond: (jahr = 2019)
13. 0.034 0.220 ↑ 1.0 36 3

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

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

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

15. 0.070 0.070 ↑ 1.0 12 3

Seq Scan on t_bauarten tba (cost=0.00..1.12 rows=12 width=2) (actual time=0.066..0.070 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.043 0.043 ↑ 1.0 3 3

Seq Scan on t_terminarten tta (cost=0.00..1.03 rows=3 width=6) (actual time=0.041..0.043 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.018 0.230 ↑ 1.0 16 3

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

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

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

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

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

22. 0.021 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.050 0.050 ↑ 1.0 16 3

Seq Scan on t_geo_bundeslaender tgb (cost=0.00..1.16 rows=16 width=7) (actual time=0.042..0.050 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.041 0.138 ↑ 1.0 36 3

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

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

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

27. 0.001 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.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
Planning time : 130.508 ms
Execution time : 12,326.811 ms