explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8Fwx

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 9,864.501 ↑ 1.0 30 1

Limit (cost=454,301.14..454,301.22 rows=30 width=1,538) (actual time=9,864.483..9,864.501 rows=30 loops=1)

2. 1.945 9,864.485 ↑ 75.6 30 1

Sort (cost=454,301.14..454,306.81 rows=2,267 width=1,538) (actual time=9,864.483..9,864.485 rows=30 loops=1)

  • Sort Key: sub.ort NULLS FIRST
  • Sort Method: top-N heapsort Memory: 48kB
3. 0.437 9,862.540 ↑ 7.8 290 1

Subquery Scan on sub (cost=452,877.27..454,234.19 rows=2,267 width=1,538) (actual time=7,984.629..9,862.540 rows=290 loops=1)

4. 949.197 9,862.103 ↑ 7.8 290 1

GroupAggregate (cost=452,877.27..454,166.18 rows=2,267 width=1,992) (actual time=7,984.614..9,862.103 rows=290 loops=1)

  • Group Key: (COALESCE(twv.paechternummer, twv_1.paechternummer)), (COALESCE(tan.anbieter, tan_1.anbieter)), NULL::bigint, NULL::integer, NULL::integer, NULL::integer, NULL::character varying, NULL::character varying, NULL::character varying, (COALESCE(tgb.bundesland_abk, tgb_1.bundesland_abk)), NULL::character varying, (COALESCE(twv.ortsnummer, twv_1.ortsnummer)), (COALESCE(tgo.ort, tgo_1.ort)), NULL::character varying, NULL::character varying, (COALESCE(twv.hauptstellenart, twv_1.hauptstellenart)), NULL::character varying, NULL::character varying, NULL::character varying, NULL::character varying, NULL::character varying, NULL::bigint, NULL::character varying, NULL::bigint, NULL::character varying, (string_agg(((('['::text || (t.produktsperre)::text) || '] '::text) || (COALESCE(tpro.produktgruppe, '<i>Anbieterspezifisch</i>'::character varying))::text), '<br>'::text ORDER BY t.produktsperre)), (string_agg(((('['::text || (t_1.produktsperre)::text) || '] '::text) || (COALESCE(tpro_1.produktgruppe, '<i>Anbieterspezifisch</i>'::character varying))::text), '<br>'::text ORDER BY t_1.produktsperre)), (string_agg(((t.produktsperre)::character varying)::text, ', '::text ORDER BY t.produktsperre)), (string_agg(((t_1.produktsperre)::character varying)::text, ', '::text ORDER BY t_1.produktsperre)), NULL::integer[], NULL::integer[], NULL::character varying, NULL::character varying, NULL::integer, NULL::integer, NULL::numeric, NULL::numeric, NULL::character varying, NULL::character varying, NULL::boolean, NULL::boolean
  • Filter: ((count(*) FILTER (WHERE (COALESCE(((twv.stammdatenlieferung_bis IS NULL)), false) AND (NOT COALESCE(((twv_1.stammdatenlieferung_bis IS NULL)), false)))) > 0) OR (count(*) FILTER (WHERE (COALESCE(((twv_1.stammdatenlieferung_bis IS NULL)), false) AND (NOT COALESCE(((twv.stammdatenlieferung_bis IS NULL)), false)))) > 0))
  • Rows Removed by Filter: 17764
5. 2,931.572 8,912.906 ↓ 81.5 332,698 1

Sort (cost=452,877.27..452,887.48 rows=4,081 width=1,652) (actual time=7,963.086..8,912.906 rows=332,698 loops=1)

  • Sort Key: (COALESCE(twv.paechternummer, twv_1.paechternummer)), (COALESCE(tan.anbieter, tan_1.anbieter)), (COALESCE(tgb.bundesland_abk, tgb_1.bundesland_abk)), (COALESCE(twv.ortsnummer, twv_1.ortsnummer)), (COALESCE(tgo.ort, tgo_1.ort)), (COALESCE(twv.hauptstellenart, twv_1.hauptstellenart)), (string_agg(((('['::text || (t.produktsperre)::text) || '] '::text) || (COALESCE(tpro.produktgruppe, '<i>Anbieterspezifisch</i>'::character varying))::text), '<br>'::text ORDER BY t.produktsperre)), (string_agg(((('['::text || (t_1.produktsperre)::text) || '] '::text) || (COALESCE(tpro_1.produktgruppe, '<i>Anbieterspezifisch</i>'::character varying))::text), '<br>'::text ORDER BY t_1.produktsperre)), (string_agg(((t.produktsperre)::character varying)::text, ', '::text ORDER BY t.produktsperre)), (string_agg(((t_1.produktsperre)::character varying)::text, ', '::text ORDER BY t_1.produktsperre))
  • Sort Method: external merge Disk: 62568kB
6. 196.152 5,981.334 ↓ 81.5 332,698 1

Nested Loop (cost=192,205.49..449,703.02 rows=4,081 width=1,652) (actual time=2,450.403..5,981.334 rows=332,698 loops=1)

7. 151.220 5,452.484 ↓ 81.5 332,698 1

Nested Loop (cost=192,195.22..407,678.93 rows=4,081 width=1,094) (actual time=2,450.394..5,452.484 rows=332,698 loops=1)

8. 709.857 4,968.566 ↓ 81.5 332,698 1

Hash Full Join (cost=192,184.96..365,654.83 rows=4,081 width=1,030) (actual time=2,450.376..4,968.566 rows=332,698 loops=1)

  • Hash Cond: ((twv.standortnummer = twv_1.standortnummer) AND (twv.paechternummer = twv_1.paechternummer) AND (twv.flaechennummer = twv_1.flaechennummer))
9. 86.644 1,882.613 ↓ 81.3 331,966 1

Hash Join (cost=9,612.67..182,500.86 rows=4,081 width=527) (actual time=69.498..1,882.613 rows=331,966 loops=1)

  • Hash Cond: (twv.terminartid = tta.terminartid)
10. 88.939 1,795.941 ↓ 81.3 331,966 1

Hash Join (cost=9,611.61..182,473.95 rows=4,081 width=531) (actual time=69.451..1,795.941 rows=331,966 loops=1)

  • Hash Cond: (twv.anbieterid = tan.anbieterid)
11. 216.601 1,706.969 ↓ 81.3 331,966 1

Nested Loop Left Join (cost=9,609.86..182,460.12 rows=4,081 width=221) (actual time=69.407..1,706.969 rows=331,966 loops=1)

12. 91.730 1,490.368 ↓ 81.3 331,966 1

Hash Join (cost=9,609.45..176,694.37 rows=4,081 width=229) (actual time=69.384..1,490.368 rows=331,966 loops=1)

  • Hash Cond: (tgo.bundeslandnummer = tgb.bundeslandnummer)
13. 239.992 1,398.576 ↓ 81.3 331,966 1

Nested Loop (cost=9,606.67..176,635.48 rows=4,081 width=195) (actual time=69.310..1,398.576 rows=331,966 loops=1)

14. 96.332 826.618 ↓ 81.2 331,966 1

Hash Join (cost=9,606.38..171,086.83 rows=4,089 width=183) (actual time=69.297..826.618 rows=331,966 loops=1)

  • Hash Cond: ((COALESCE(twv.bauart_kurz, ' '::character varying))::text = (tba.bauart_kurz)::text)
15. 667.075 730.253 ↓ 4.9 331,966 1

Bitmap Heap Scan on t_werbeflaechen_versionen twv (cost=9,605.11..170,902.88 rows=68,147 width=183) (actual time=69.161..730.253 rows=331,966 loops=1)

  • Recheck Cond: ((jahr = 2019) AND (COALESCE(upper(zeitraum), '2999-12-31 00:00:00'::timestamp without time zone) > '2019-03-10 00:00:00'::timestamp without time zone) AND (lower(zeitraum) < '2019-03-10 00:00:00'::timestamp without time zone) AND (stammdatenlieferung_bis IS NULL))
  • Heap Blocks: exact=50567
16. 63.178 63.178 ↓ 6.3 428,426 1

Bitmap Index Scan on t_werbeflaechen_versionen_idx_btree_zeitraum (cost=0.00..9,588.08 rows=68,147 width=0) (actual time=63.178..63.178 rows=428,426 loops=1)

  • Index Cond: ((jahr = 2019) AND (COALESCE(upper(zeitraum), '2999-12-31 00:00:00'::timestamp without time zone) > '2019-03-10 00:00:00'::timestamp without time zone) AND (lower(zeitraum) < '2019-03-10 00:00:00'::timestamp without time zone))
17. 0.012 0.033 ↑ 1.0 12 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.021 0.021 ↑ 1.0 12 1

Seq Scan on t_bauarten tba (cost=0.00..1.12 rows=12 width=12) (actual time=0.019..0.021 rows=12 loops=1)

19. 331.966 331.966 ↑ 1.0 1 331,966

Index Scan using t_geo_orte_pkey on t_geo_orte tgo (cost=0.29..1.36 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=331,966)

  • Index Cond: ((jahr = 2019) AND (ortsnummer = twv.ortsnummer))
20. 0.003 0.062 ↑ 1.0 16 1

Hash (cost=2.57..2.57 rows=16 width=46) (actual time=0.062..0.062 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.016 0.059 ↑ 1.0 16 1

Hash Join (cost=1.36..2.57 rows=16 width=46) (actual time=0.053..0.059 rows=16 loops=1)

  • Hash Cond: (tgn.bundeslandnummer = tgb.bundeslandnummer)
22. 0.018 0.018 ↑ 1.0 16 1

Seq Scan on t_geo_nielsengebiete tgn (cost=0.00..1.16 rows=16 width=4) (actual time=0.017..0.018 rows=16 loops=1)

23. 0.005 0.025 ↑ 1.0 16 1

Hash (cost=1.16..1.16 rows=16 width=42) (actual time=0.025..0.025 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=42) (actual time=0.018..0.020 rows=16 loops=1)

25. 0.000 0.000 ↓ 0.0 0 331,966

Index Scan using t_verbrauchermaerkte_pkey on t_verbrauchermaerkte tvm (cost=0.42..1.41 rows=1 width=29) (actual time=0.000..0.000 rows=0 loops=331,966)

  • Index Cond: (verbrauchermarktid = (twv.verbrauchermarktids)[1])
26. 0.010 0.033 ↑ 1.0 33 1

Hash (cost=1.33..1.33 rows=33 width=326) (actual time=0.033..0.033 rows=33 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
27. 0.023 0.023 ↑ 1.0 33 1

Seq Scan on t_anbieter tan (cost=0.00..1.33 rows=33 width=326) (actual time=0.019..0.023 rows=33 loops=1)

28. 0.003 0.028 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=4) (actual time=0.028..0.028 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.025 0.025 ↑ 1.0 3 1

Seq Scan on t_terminarten tta (cost=0.00..1.03 rows=3 width=4) (actual time=0.024..0.025 rows=3 loops=1)

30. 266.354 2,376.096 ↓ 81.4 332,311 1

Hash (cost=182,500.86..182,500.86 rows=4,081 width=527) (actual time=2,376.096..2,376.096 rows=332,311 loops=1)

  • Buckets: 32768 (originally 4096) Batches: 16 (originally 1) Memory Usage: 3885kB
31. 93.106 2,109.742 ↓ 81.4 332,311 1

Hash Join (cost=9,612.67..182,500.86 rows=4,081 width=527) (actual time=68.176..2,109.742 rows=332,311 loops=1)

  • Hash Cond: (twv_1.terminartid = tta_1.terminartid)
32. 99.199 2,016.594 ↓ 81.4 332,311 1

Hash Join (cost=9,611.61..182,473.95 rows=4,081 width=531) (actual time=68.128..2,016.594 rows=332,311 loops=1)

  • Hash Cond: (twv_1.anbieterid = tan_1.anbieterid)
33. 247.992 1,917.371 ↓ 81.4 332,311 1

Nested Loop Left Join (cost=9,609.86..182,460.12 rows=4,081 width=221) (actual time=68.096..1,917.371 rows=332,311 loops=1)

34. 104.983 1,669.379 ↓ 81.4 332,311 1

Hash Join (cost=9,609.45..176,694.37 rows=4,081 width=229) (actual time=68.087..1,669.379 rows=332,311 loops=1)

  • Hash Cond: (tgo_1.bundeslandnummer = tgb_1.bundeslandnummer)
35. 321.270 1,564.340 ↓ 81.4 332,311 1

Nested Loop (cost=9,606.67..176,635.48 rows=4,081 width=195) (actual time=68.024..1,564.340 rows=332,311 loops=1)

36. 110.405 910.759 ↓ 81.3 332,311 1

Hash Join (cost=9,606.38..171,086.83 rows=4,089 width=183) (actual time=68.008..910.759 rows=332,311 loops=1)

  • Hash Cond: ((COALESCE(twv_1.bauart_kurz, ' '::character varying))::text = (tba_1.bauart_kurz)::text)
37. 737.551 800.337 ↓ 4.9 332,311 1

Bitmap Heap Scan on t_werbeflaechen_versionen twv_1 (cost=9,605.11..170,902.88 rows=68,147 width=183) (actual time=67.982..800.337 rows=332,311 loops=1)

  • Recheck Cond: ((jahr = 2019) AND (COALESCE(upper(zeitraum), '2999-12-31 00:00:00'::timestamp without time zone) > '2019-03-03 00:00:00'::timestamp without time zone) AND (lower(zeitraum) < '2019-03-03 00:00:00'::timestamp without time zone) AND (stammdatenlieferung_bis IS NULL))
  • Heap Blocks: exact=49395
38. 62.786 62.786 ↓ 6.3 429,044 1

Bitmap Index Scan on t_werbeflaechen_versionen_idx_btree_zeitraum (cost=0.00..9,588.08 rows=68,147 width=0) (actual time=62.786..62.786 rows=429,044 loops=1)

  • Index Cond: ((jahr = 2019) AND (COALESCE(upper(zeitraum), '2999-12-31 00:00:00'::timestamp without time zone) > '2019-03-03 00:00:00'::timestamp without time zone) AND (lower(zeitraum) < '2019-03-03 00:00:00'::timestamp without time zone))
39. 0.002 0.017 ↑ 1.0 12 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
40. 0.015 0.015 ↑ 1.0 12 1

Seq Scan on t_bauarten tba_1 (cost=0.00..1.12 rows=12 width=12) (actual time=0.014..0.015 rows=12 loops=1)

41. 332.311 332.311 ↑ 1.0 1 332,311

Index Scan using t_geo_orte_pkey on t_geo_orte tgo_1 (cost=0.29..1.36 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=332,311)

  • Index Cond: ((jahr = 2019) AND (ortsnummer = twv_1.ortsnummer))
42. 0.005 0.056 ↑ 1.0 16 1

Hash (cost=2.57..2.57 rows=16 width=46) (actual time=0.056..0.056 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
43. 0.018 0.051 ↑ 1.0 16 1

Hash Join (cost=1.36..2.57 rows=16 width=46) (actual time=0.050..0.051 rows=16 loops=1)

  • Hash Cond: (tgn_1.bundeslandnummer = tgb_1.bundeslandnummer)
44. 0.014 0.014 ↑ 1.0 16 1

Seq Scan on t_geo_nielsengebiete tgn_1 (cost=0.00..1.16 rows=16 width=4) (actual time=0.014..0.014 rows=16 loops=1)

45. 0.003 0.019 ↑ 1.0 16 1

Hash (cost=1.16..1.16 rows=16 width=42) (actual time=0.019..0.019 rows=16 loops=1)

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

Seq Scan on t_geo_bundeslaender tgb_1 (cost=0.00..1.16 rows=16 width=42) (actual time=0.015..0.016 rows=16 loops=1)

47. 0.000 0.000 ↓ 0.0 0 332,311

Index Scan using t_verbrauchermaerkte_pkey on t_verbrauchermaerkte tvm_1 (cost=0.42..1.41 rows=1 width=29) (actual time=0.000..0.000 rows=0 loops=332,311)

  • Index Cond: (verbrauchermarktid = (twv_1.verbrauchermarktids)[1])
48. 0.007 0.024 ↑ 1.0 33 1

Hash (cost=1.33..1.33 rows=33 width=326) (actual time=0.024..0.024 rows=33 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
49. 0.017 0.017 ↑ 1.0 33 1

Seq Scan on t_anbieter tan_1 (cost=0.00..1.33 rows=33 width=326) (actual time=0.016..0.017 rows=33 loops=1)

50. 0.004 0.042 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=4) (actual time=0.042..0.042 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
51. 0.038 0.038 ↑ 1.0 3 1

Seq Scan on t_terminarten tta_1 (cost=0.00..1.03 rows=3 width=4) (actual time=0.038..0.038 rows=3 loops=1)

52. 332.698 332.698 ↑ 1.0 1 332,698

Aggregate (cost=10.27..10.28 rows=1 width=64) (actual time=0.001..0.001 rows=1 loops=332,698)

53. 0.000 0.000 ↓ 0.0 0 332,698

Hash Left Join (cost=6.75..8.01 rows=100 width=22) (actual time=0.000..0.000 rows=0 loops=332,698)

  • Hash Cond: (t.produktsperre = tpro.produktgruppennummer)
54. 0.000 0.000 ↓ 0.0 0 332,698

Function Scan on unnest t (cost=0.00..1.00 rows=100 width=4) (actual time=0.000..0.000 rows=0 loops=332,698)

55. 0.000 0.000 ↓ 0.0 0

Hash (cost=4.11..4.11 rows=211 width=22) (never executed)

56. 0.000 0.000 ↓ 0.0 0

Seq Scan on t_produktgruppen tpro (cost=0.00..4.11 rows=211 width=22) (never executed)

57. 332.698 332.698 ↑ 1.0 1 332,698

Aggregate (cost=10.27..10.28 rows=1 width=64) (actual time=0.001..0.001 rows=1 loops=332,698)

58. 0.000 0.000 ↓ 0.0 0 332,698

Hash Left Join (cost=6.75..8.01 rows=100 width=22) (actual time=0.000..0.000 rows=0 loops=332,698)

  • Hash Cond: (t_1.produktsperre = tpro_1.produktgruppennummer)
59. 0.000 0.000 ↓ 0.0 0 332,698

Function Scan on unnest t_1 (cost=0.00..1.00 rows=100 width=4) (actual time=0.000..0.000 rows=0 loops=332,698)

60. 0.000 0.000 ↓ 0.0 0

Hash (cost=4.11..4.11 rows=211 width=22) (never executed)

61. 0.000 0.000 ↓ 0.0 0

Seq Scan on t_produktgruppen tpro_1 (cost=0.00..4.11 rows=211 width=22) (never executed)