explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hEuU

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 7,348.536 ↑ 1.0 30 1

Limit (cost=98,039.00..98,039.07 rows=30 width=1,166) (actual time=7,348.525..7,348.536 rows=30 loops=1)

2. 485.833 7,348.526 ↑ 305.8 30 1

Sort (cost=98,039.00..98,061.93 rows=9,174 width=1,166) (actual time=7,348.522..7,348.526 rows=30 loops=1)

  • Sort Key: sub.nielsengebiet NULLS FIRST, sub.bundesland NULLS FIRST, sub.ort NULLS FIRST
  • Sort Method: top-N heapsort Memory: 45kB
3. 189.236 6,862.693 ↓ 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,017.743..6,862.693 rows=289,840 loops=1)

4. 2,568.300 6,673.457 ↓ 31.6 289,840 1

GroupAggregate (cost=94,878.24..97,676.31 rows=9,174 width=1,275) (actual time=4,017.741..6,673.457 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
5. 2,272.670 4,105.157 ↓ 31.6 289,840 1

Sort (cost=94,878.24..94,901.17 rows=9,174 width=792) (actual time=4,017.673..4,105.157 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
6. 377.352 1,832.487 ↓ 31.6 289,840 1

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

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

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

  • Workers Planned: 2
  • Workers Launched: 2
8. 113.420 1,666.550 ↓ 25.3 96,613 3

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

  • Hash Cond: (twv.anbieterid = tan.anbieterid)
9. 228.256 1,553.001 ↓ 25.3 96,613 3

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

10. 112.545 1,324.744 ↓ 25.3 96,613 3

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

  • Hash Cond: (tgo.bundeslandnummer = tgb.bundeslandnummer)
11. 562.785 1,211.974 ↓ 25.3 96,613 3

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

12. 134.042 649.185 ↓ 25.2 96,613 3

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

  • Hash Cond: (((COALESCE(twv.bauart_kurz, ' '::character varying))::text = (tba.bauart_kurz)::text) AND (twv.terminartid = tta.terminartid))
13. 514.959 514.959 ↓ 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.212..514.959 rows=96,613 loops=3)

  • Index Cond: (jahr = 2019)
14. 0.033 0.184 ↑ 1.0 36 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
15. 0.095 0.151 ↑ 1.0 36 3

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

16. 0.051 0.051 ↑ 1.0 12 3

Seq Scan on t_bauarten tba (cost=0.00..1.12 rows=12 width=2) (actual time=0.047..0.051 rows=12 loops=3)

17. 0.000 0.005 ↑ 1.0 3 36

Materialize (cost=0.00..1.04 rows=3 width=6) (actual time=0.004..0.005 rows=3 loops=36)

18. 0.036 0.036 ↑ 1.0 3 3

Seq Scan on t_terminarten tta (cost=0.00..1.03 rows=3 width=6) (actual time=0.034..0.036 rows=3 loops=3)

19. 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))
20. 0.018 0.225 ↑ 1.0 16 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.062 0.207 ↑ 1.0 16 3

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

  • Hash Cond: (tgn.bundeslandnummer = tgb.bundeslandnummer)
22. 0.061 0.061 ↑ 1.0 16 3

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

23. 0.018 0.084 ↑ 1.0 16 3

Hash (cost=1.16..1.16 rows=16 width=7) (actual time=0.084..0.084 rows=16 loops=3)

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

Seq Scan on t_geo_bundeslaender tgb (cost=0.00..1.16 rows=16 width=7) (actual time=0.058..0.066 rows=16 loops=3)

25. 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])
26. 0.037 0.129 ↑ 1.0 36 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
27. 0.092 0.092 ↑ 1.0 36 3

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

28. 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
29. 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