explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iNGn

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 10,032.042 ↑ 1.0 30 1

Limit (cost=1,906,849.48..1,906,849.56 rows=30 width=1,538) (actual time=10,032.026..10,032.042 rows=30 loops=1)

2. 2.222 10,032.026 ↑ 1,259.5 30 1

Sort (cost=1,906,849.48..1,906,943.94 rows=37,784 width=1,538) (actual time=10,032.026..10,032.026 rows=30 loops=1)

  • Sort Key: sub.ort NULLS FIRST
  • Sort Method: top-N heapsort Memory: 48kB
3. 0.402 10,029.804 ↑ 130.3 290 1

Subquery Scan on sub (cost=1,883,119.60..1,905,733.55 rows=37,784 width=1,538) (actual time=7,920.947..10,029.804 rows=290 loops=1)

4. 1,073.641 10,029.402 ↑ 130.3 290 1

GroupAggregate (cost=1,883,119.60..1,904,600.03 rows=37,784 width=1,992) (actual time=7,920.942..10,029.402 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. 3,331.736 8,955.761 ↓ 4.9 332,698 1

Sort (cost=1,883,119.60..1,883,289.63 rows=68,012 width=1,652) (actual time=7,883.704..8,955.761 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: 62600kB
6. 243.557 5,624.025 ↓ 4.9 332,698 1

Nested Loop (cost=202,627.49..1,780,024.45 rows=68,012 width=1,652) (actual time=2,607.243..5,624.025 rows=332,698 loops=1)

7. 193.864 5,047.770 ↓ 4.9 332,698 1

Nested Loop (cost=202,617.22..1,079,670.88 rows=68,012 width=1,094) (actual time=2,607.235..5,047.770 rows=332,698 loops=1)

8. 759.048 4,521.208 ↓ 4.9 332,698 1

Hash Full Join (cost=202,606.95..379,317.31 rows=68,012 width=1,030) (actual time=2,607.209..4,521.208 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. 101.412 1,316.118 ↓ 4.9 331,966 1

Hash Join (cost=20,034.67..187,050.91 rows=68,012 width=527) (actual time=157.100..1,316.118 rows=331,966 loops=1)

  • Hash Cond: (twv.anbieterid = tan.anbieterid)
10. 96.332 1,214.669 ↓ 4.9 331,966 1

Hash Join (cost=20,032.93..186,847.71 rows=68,012 width=217) (actual time=157.051..1,214.669 rows=331,966 loops=1)

  • Hash Cond: (tgo.bundeslandnummer = tgb.bundeslandnummer)
11. 107.062 1,118.285 ↓ 4.9 331,966 1

Hash Join (cost=20,030.16..185,909.77 rows=68,012 width=183) (actual time=156.989..1,118.285 rows=331,966 loops=1)

  • Hash Cond: (twv.ortsnummer = tgo.ortsnummer)
12. 125.620 981.423 ↓ 4.9 331,966 1

Hash Left Join (cost=15,248.88..180,949.54 rows=68,147 width=171) (actual time=127.125..981.423 rows=331,966 loops=1)

  • Hash Cond: (twv.verbrauchermarktids[1] = tvm.verbrauchermarktid)
13. 733.964 809.012 ↓ 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=80.301..809.012 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
14. 75.048 75.048 ↓ 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=75.048..75.048 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))
15. 20.623 46.791 ↑ 1.0 111,412 1

Hash (cost=3,489.12..3,489.12 rows=111,412 width=29) (actual time=46.791..46.791 rows=111,412 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 4072kB
16. 26.168 26.168 ↑ 1.0 111,412 1

Seq Scan on t_verbrauchermaerkte tvm (cost=0.00..3,489.12 rows=111,412 width=29) (actual time=0.014..26.168 rows=111,412 loops=1)

17. 4.615 29.800 ↓ 1.0 11,115 1

Hash (cost=4,642.35..4,642.35 rows=11,114 width=24) (actual time=29.800..29.800 rows=11,115 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 758kB
18. 24.294 25.185 ↓ 1.0 11,115 1

Bitmap Heap Scan on t_geo_orte tgo (cost=214.42..4,642.35 rows=11,114 width=24) (actual time=1.228..25.185 rows=11,115 loops=1)

  • Recheck Cond: (jahr = 2019)
  • Heap Blocks: exact=3936
19. 0.891 0.891 ↓ 1.0 11,115 1

Bitmap Index Scan on t_geo_orte_pkey (cost=0.00..211.65 rows=11,114 width=0) (actual time=0.891..0.891 rows=11,115 loops=1)

  • Index Cond: (jahr = 2019)
20. 0.008 0.052 ↑ 1.0 16 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.011 0.044 ↑ 1.0 16 1

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

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

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

23. 0.003 0.018 ↑ 1.0 16 1

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

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

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

25. 0.015 0.037 ↑ 1.0 33 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
26. 0.022 0.022 ↑ 1.0 33 1

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

27. 282.252 2,446.042 ↓ 81.4 332,311 1

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

  • Buckets: 32768 (originally 4096) Batches: 16 (originally 1) Memory Usage: 3885kB
28. 100.253 2,163.790 ↓ 81.4 332,311 1

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

  • Hash Cond: (twv_1.terminartid = tta.terminartid)
29. 106.836 2,063.493 ↓ 81.4 332,311 1

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

  • Hash Cond: (twv_1.anbieterid = tan_1.anbieterid)
30. 251.133 1,956.608 ↓ 81.4 332,311 1

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

31. 108.612 1,705.475 ↓ 81.4 332,311 1

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

  • Hash Cond: (tgo_1.bundeslandnummer = tgb_1.bundeslandnummer)
32. 17.970 1,596.811 ↓ 81.4 332,311 1

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

33. 117.375 914.219 ↓ 81.3 332,311 1

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

  • Hash Cond: ((COALESCE(twv_1.bauart_kurz, ' '::character varying))::text = (tba.bauart_kurz)::text)
34. 711.636 796.822 ↓ 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=90.450..796.822 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
35. 85.186 85.186 ↓ 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=85.186..85.186 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))
36. 0.004 0.022 ↑ 1.0 12 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.018 0.018 ↑ 1.0 12 1

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

38. 664.622 664.622 ↑ 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.002..0.002 rows=1 loops=332,311)

  • Index Cond: ((jahr = 2019) AND (ortsnummer = twv_1.ortsnummer))
39. 0.004 0.052 ↑ 1.0 16 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
40. 0.008 0.048 ↑ 1.0 16 1

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

  • Hash Cond: (tgn_1.bundeslandnummer = tgb_1.bundeslandnummer)
41. 0.018 0.018 ↑ 1.0 16 1

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

42. 0.006 0.022 ↑ 1.0 16 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
43. 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)

44. 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])
45. 0.014 0.049 ↑ 1.0 33 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
46. 0.035 0.035 ↑ 1.0 33 1

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

47. 0.010 0.044 ↑ 1.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
48. 0.034 0.034 ↑ 1.0 3 1

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

49. 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)

50. 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)
51. 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)

52. 0.000 0.000 ↓ 0.0 0

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

53. 0.000 0.000 ↓ 0.0 0

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

54. 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)

55. 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)
56. 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)

57. 0.000 0.000 ↓ 0.0 0

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

58. 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)