explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nQaR

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 12,697.506 ↑ 1.0 30 1

Limit (cost=103,732.30..103,732.38 rows=30 width=2,108) (actual time=12,697.497..12,697.506 rows=30 loops=1)

2. 670.318 12,697.498 ↑ 305.8 30 1

Sort (cost=103,732.30..103,755.24 rows=9,174 width=2,108) (actual time=12,697.494..12,697.498 rows=30 loops=1)

  • Sort Key: sub.nielsengebiet NULLS FIRST, sub.bundesland NULLS FIRST, sub.ort NULLS FIRST
  • Sort Method: top-N heapsort Memory: 51kB
3. 329.425 12,027.180 ↓ 31.6 289,840 1

Subquery Scan on sub (cost=100,158.72..103,461.36 rows=9,174 width=2,108) (actual time=7,909.190..12,027.180 rows=289,840 loops=1)

4. 3,659.958 11,697.755 ↓ 31.6 289,840 1

GroupAggregate (cost=100,158.72..103,186.14 rows=9,174 width=2,104) (actual time=7,909.186..11,697.755 rows=289,840 loops=1)

  • Group Key: (COALESCE(twv.paechternummer, paechternummer)), (COALESCE(tan.anbieter, anbieter)), (COALESCE(twv.standortnummer, standortnummer)), (COALESCE(twv.anz_flaechen_am_standort, CASE WHEN (twv.anz_flaechen_am_standort IS NULL) THEN anz_flaechen_am_standort ELSE NULL::integer END)), (COALESCE(twv.flaechennummer, flaechennummer)), (COALESCE(twv.qid, qid)), (COALESCE(twv.flaechenbezeichnung, flaechenbezeichnung)), (COALESCE(tta.terminart, terminart)), (COALESCE(twv.block, block)), (COALESCE(tgb.bundesland_abk, bundesland_abk)), (COALESCE(tgn.nielsengebiet, nielsengebiet)), (COALESCE(twv.ortsnummer, ortsnummer)), (COALESCE(tgo.ort, ort)), (COALESCE(twv.ortsteil, ortsteil)), (COALESCE(twv.plz, plz)), (COALESCE(twv.hauptstellenart, hauptstellenart)), (COALESCE(hauptstellenart, twv.hauptstellenart)), (COALESCE(twv.stellenart, stellenart)), (COALESCE(stellenart, twv.stellenart)), (COALESCE(twv.beleuchtung, beleuchtung)), (COALESCE(beleuchtung, twv.beleuchtung)), (COALESCE(tvm.marktnummer, CASE WHEN (twv.werbeflaechenid IS NULL) THEN marktnummer ELSE NULL::bigint END)), (COALESCE(tvm.verbrauchermarkt, CASE WHEN (twv.werbeflaechenid IS NULL) THEN verbrauchermarkt ELSE NULL::character varying END)), (COALESCE(marktnummer, CASE WHEN (werbeflaechenid IS NULL) THEN tvm.marktnummer ELSE NULL::bigint END)), (COALESCE(verbrauchermarkt, CASE WHEN (werbeflaechenid IS NULL) THEN tvm.verbrauchermarkt ELSE NULL::character varying END)), NULL::text, NULL::text, NULL::text, NULL::text, (COALESCE(twv.produktsperren, CASE WHEN (twv.werbeflaechenid IS NULL) THEN produktsperren ELSE NULL::integer[] END)), (COALESCE(produktsperren, CASE WHEN (werbeflaechenid IS NULL) THEN twv.produktsperren ELSE NULL::integer[] END)), (COALESCE(((concat(twv.paechternummer, '-', twv.versandanschriftid))::character varying), ((concat(paechternummer, '-', versandanschriftid))::character varying))), (COALESCE(((concat(paechternummer, '-', versandanschriftid))::character varying), ((concat(twv.paechternummer, '-', twv.versandanschriftid))::character varying))), (COALESCE(twv.preisgruppe, CASE WHEN (twv.werbeflaechenid IS NULL) THEN preisgruppe ELSE NULL::integer END)), (COALESCE(preisgruppe, CASE WHEN (werbeflaechenid IS NULL) THEN twv.preisgruppe ELSE NULL::integer END)), (COALESCE(twv.tagespreis, tagespreis)), (COALESCE(tagespreis, twv.tagespreis)), (COALESCE(twv.bauart_kurz, CASE WHEN (twv.werbeflaechenid IS NULL) THEN bauart_kurz ELSE NULL::character varying END)), (COALESCE(bauart_kurz, CASE WHEN (werbeflaechenid IS NULL) THEN twv.bauart_kurz ELSE NULL::character varying END)), (COALESCE(twv.flag_einzelstellenvermarktung, CASE WHEN (twv.werbeflaechenid IS NULL) THEN flag_einzelstellenvermarktung ELSE NULL::boolean END)), (COALESCE(flag_einzelstellenvermarktung, CASE WHEN (werbeflaechenid IS NULL) THEN twv.flag_einzelstellenvermarktung ELSE NULL::boolean END))
5. 2,799.825 8,037.797 ↓ 31.6 289,840 1

Sort (cost=100,158.72..100,181.65 rows=9,174 width=1,768) (actual time=7,909.052..8,037.797 rows=289,840 loops=1)

  • Sort Key: (COALESCE(twv.paechternummer, paechternummer)), (COALESCE(tan.anbieter, anbieter)), (COALESCE(twv.standortnummer, standortnummer)), (COALESCE(twv.anz_flaechen_am_standort, CASE WHEN (twv.anz_flaechen_am_standort IS NULL) THEN anz_flaechen_am_standort ELSE NULL::integer END)), (COALESCE(twv.flaechennummer, flaechennummer)), (COALESCE(twv.qid, qid)), (COALESCE(twv.flaechenbezeichnung, flaechenbezeichnung)), (COALESCE(tta.terminart, terminart)), (COALESCE(twv.block, block)), (COALESCE(tgb.bundesland_abk, bundesland_abk)), (COALESCE(tgn.nielsengebiet, nielsengebiet)), (COALESCE(twv.ortsnummer, ortsnummer)), (COALESCE(tgo.ort, ort)), (COALESCE(twv.ortsteil, ortsteil)), (COALESCE(twv.plz, plz)), (COALESCE(twv.hauptstellenart, hauptstellenart)), (COALESCE(hauptstellenart, twv.hauptstellenart)), (COALESCE(twv.stellenart, stellenart)), (COALESCE(stellenart, twv.stellenart)), (COALESCE(twv.beleuchtung, beleuchtung)), (COALESCE(beleuchtung, twv.beleuchtung)), (COALESCE(tvm.marktnummer, CASE WHEN (twv.werbeflaechenid IS NULL) THEN marktnummer ELSE NULL::bigint END)), (COALESCE(tvm.verbrauchermarkt, CASE WHEN (twv.werbeflaechenid IS NULL) THEN verbrauchermarkt ELSE NULL::character varying END)), (COALESCE(marktnummer, CASE WHEN (werbeflaechenid IS NULL) THEN tvm.marktnummer ELSE NULL::bigint END)), (COALESCE(verbrauchermarkt, CASE WHEN (werbeflaechenid IS NULL) THEN tvm.verbrauchermarkt ELSE NULL::character varying END)), (COALESCE(twv.produktsperren, CASE WHEN (twv.werbeflaechenid IS NULL) THEN produktsperren ELSE NULL::integer[] END)), (COALESCE(produktsperren, CASE WHEN (werbeflaechenid IS NULL) THEN twv.produktsperren ELSE NULL::integer[] END)), (COALESCE(((concat(twv.paechternummer, '-', twv.versandanschriftid))::character varying), ((concat(paechternummer, '-', versandanschriftid))::character varying))), (COALESCE(((concat(paechternummer, '-', versandanschriftid))::character varying), ((concat(twv.paechternummer, '-', twv.versandanschriftid))::character varying))), (COALESCE(twv.preisgruppe, CASE WHEN (twv.werbeflaechenid IS NULL) THEN preisgruppe ELSE NULL::integer END)), (COALESCE(preisgruppe, CASE WHEN (werbeflaechenid IS NULL) THEN twv.preisgruppe ELSE NULL::integer END)), (COALESCE(twv.tagespreis, tagespreis)), (COALESCE(tagespreis, twv.tagespreis)), (COALESCE(twv.bauart_kurz, CASE WHEN (twv.werbeflaechenid IS NULL) THEN bauart_kurz ELSE NULL::character varying END)), (COALESCE(bauart_kurz, CASE WHEN (werbeflaechenid IS NULL) THEN twv.bauart_kurz ELSE NULL::character varying END)), (COALESCE(twv.flag_einzelstellenvermarktung, CASE WHEN (twv.werbeflaechenid IS NULL) THEN flag_einzelstellenvermarktung ELSE NULL::boolean END)), (COALESCE(flag_einzelstellenvermarktung, CASE WHEN (werbeflaechenid IS NULL) THEN twv.flag_einzelstellenvermarktung ELSE NULL::boolean END))
  • Sort Method: quicksort Memory: 159755kB
6. 642.955 5,237.972 ↓ 31.6 289,840 1

Hash Full Join (cost=6.93..99,554.91 rows=9,174 width=1,768) (actual time=0.478..5,237.972 rows=289,840 loops=1)

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

Hash Join (cost=6.93..99,451.70 rows=9,174 width=274) (actual time=0.456..4,595.014 rows=289,840 loops=1)

  • Hash Cond: (tgb.bundeslandnummer = tgn.bundeslandnummer)
8. 276.302 4,320.632 ↓ 31.6 289,840 1

Hash Join (cost=5.57..99,324.19 rows=9,174 width=272) (actual time=0.371..4,320.632 rows=289,840 loops=1)

  • Hash Cond: (tgo.bundeslandnummer = tgb.bundeslandnummer)
9. 448.596 4,044.296 ↓ 31.6 289,840 1

Nested Loop (cost=4.21..99,212.10 rows=9,174 width=265) (actual time=0.327..4,044.296 rows=289,840 loops=1)

  • Join Filter: (twv.terminartid = tta.terminartid)
  • Rows Removed by Join Filter: 98070
10. 275.207 3,595.700 ↓ 31.6 289,840 1

Hash Join (cost=4.21..98,867.03 rows=9,174 width=267) (actual time=0.305..3,595.700 rows=289,840 loops=1)

  • Hash Cond: (twv.anbieterid = tan.anbieterid)
11. 286.302 3,320.436 ↓ 31.6 289,840 1

Nested Loop Left Join (cost=2.40..98,751.67 rows=9,174 width=249) (actual time=0.238..3,320.436 rows=289,840 loops=1)

12. 477.400 2,744.294 ↓ 31.6 289,840 1

Nested Loop (cost=1.99..94,352.16 rows=9,174 width=257) (actual time=0.232..2,744.294 rows=289,840 loops=1)

13. 297.549 1,397.374 ↓ 31.5 289,840 1

Hash Join (cost=1.70..91,215.22 rows=9,190 width=245) (actual time=0.209..1,397.374 rows=289,840 loops=1)

  • Hash Cond: ((COALESCE(twv.bauart_kurz, ' '::character varying))::text = (tba.bauart_kurz)::text)
14. 1,099.777 1,099.777 ↓ 1.9 289,840 1

Index Scan using t_werbeflaechen_versionen_idx_test on t_werbeflaechen_versionen twv (cost=0.42..89,286.99 rows=153,169 width=245) (actual time=0.147..1,099.777 rows=289,840 loops=1)

  • Index Cond: (jahr = 2019)
15. 0.014 0.048 ↑ 1.0 12 1

Hash (cost=1.12..1.12 rows=12 width=2) (actual time=0.048..0.048 rows=12 loops=1)

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

Seq Scan on t_bauarten tba (cost=0.00..1.12 rows=12 width=2) (actual time=0.028..0.034 rows=12 loops=1)

17. 869.520 869.520 ↑ 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.003..0.003 rows=1 loops=289,840)

  • Index Cond: ((jahr = 2019) AND (ortsnummer = twv.ortsnummer))
18. 289.840 289.840 ↓ 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])
19. 0.027 0.057 ↑ 1.0 36 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
20. 0.030 0.030 ↑ 1.0 36 1

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

21. 0.000 0.000 ↑ 3.0 1 289,840

Materialize (cost=0.00..1.04 rows=3 width=6) (actual time=0.000..0.000 rows=1 loops=289,840)

22. 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.011..0.014 rows=3 loops=1)

23. 0.014 0.034 ↑ 1.0 16 1

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

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

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

25. 0.019 0.077 ↑ 1.0 16 1

Hash (cost=1.16..1.16 rows=16 width=14) (actual time=0.077..0.077 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.058 0.058 ↑ 1.0 16 1

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

27. 0.002 0.003 ↓ 0.0 0 1

Hash (cost=0.00..0.00 rows=0 width=274) (actual time=0.003..0.003 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
28. 0.001 0.001 ↓ 0.0 0 1

Result (cost=0.00..0.00 rows=0 width=274) (actual time=0.001..0.001 rows=0 loops=1)

  • One-Time Filter: false