explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pxPg

Settings
# exclusive inclusive rows x rows loops node
1. 0.023 8,600.377 ↑ 1.0 30 1

Limit (cost=1,946,203.74..1,946,203.82 rows=30 width=1,538) (actual time=8,600.352..8,600.377 rows=30 loops=1)

2. 2.233 8,600.354 ↑ 1,259.5 30 1

Sort (cost=1,946,203.74..1,946,298.20 rows=37,784 width=1,538) (actual time=8,600.351..8,600.354 rows=30 loops=1)

  • Sort Key: sub.ort NULLS FIRST
  • Sort Method: top-N heapsort Memory: 48kB
3. 0.404 8,598.121 ↑ 130.3 290 1

Subquery Scan on sub (cost=1,922,473.86..1,945,087.81 rows=37,784 width=1,538) (actual time=6,924.421..8,598.121 rows=290 loops=1)

4. 979.437 8,597.717 ↑ 130.3 290 1

GroupAggregate (cost=1,922,473.86..1,943,954.29 rows=37,784 width=1,992) (actual time=6,924.418..8,597.717 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,587.343 7,618.280 ↓ 4.9 332,698 1

Sort (cost=1,922,473.86..1,922,643.89 rows=68,012 width=1,652) (actual time=6,911.520..7,618.280 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: 62592kB
6. 208.541 5,030.937 ↓ 4.9 332,698 1

Nested Loop (cost=417,121.66..1,819,378.71 rows=68,012 width=1,652) (actual time=3,433.245..5,030.937 rows=332,698 loops=1)

7. 153.557 4,489.698 ↓ 4.9 332,698 1

Nested Loop (cost=417,111.39..1,119,025.14 rows=68,012 width=1,094) (actual time=3,433.238..4,489.698 rows=332,698 loops=1)

8. 284.504 4,003.443 ↓ 4.9 332,698 1

Merge Full Join (cost=417,101.13..418,671.57 rows=68,012 width=1,030) (actual time=3,433.215..4,003.443 rows=332,698 loops=1)

  • Merge Cond: ((twv.standortnummer = twv_1.standortnummer) AND (twv.paechternummer = twv_1.paechternummer) AND (twv.flaechennummer = twv_1.flaechennummer))
9. 561.141 1,804.625 ↓ 4.9 331,966 1

Sort (cost=208,550.56..208,720.59 rows=68,012 width=527) (actual time=1,681.552..1,804.625 rows=331,966 loops=1)

  • Sort Key: twv.standortnummer, twv.paechternummer, twv.flaechennummer
  • Sort Method: external merge Disk: 51136kB
10. 87.670 1,243.484 ↓ 4.9 331,966 1

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

  • Hash Cond: (twv.anbieterid = tan.anbieterid)
11. 85.732 1,155.766 ↓ 4.9 331,966 1

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

  • Hash Cond: (tgo.bundeslandnummer = tgb.bundeslandnummer)
12. 92.930 1,069.987 ↓ 4.9 331,966 1

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

  • Hash Cond: (twv.ortsnummer = tgo.ortsnummer)
13. 122.082 941.893 ↓ 4.9 331,966 1

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

  • Hash Cond: (twv.verbrauchermarktids[1] = tvm.verbrauchermarktid)
14. 703.272 765.786 ↓ 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=68.628..765.786 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
15. 62.514 62.514 ↓ 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=62.514..62.514 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))
16. 22.057 54.025 ↑ 1.0 111,412 1

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

  • Buckets: 65536 Batches: 2 Memory Usage: 4072kB
17. 31.968 31.968 ↑ 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.029..31.968 rows=111,412 loops=1)

18. 2.174 35.164 ↓ 1.0 11,115 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 758kB
19. 31.825 32.990 ↓ 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.512..32.990 rows=11,115 loops=1)

  • Recheck Cond: (jahr = 2019)
  • Heap Blocks: exact=3936
20. 1.165 1.165 ↓ 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=1.165..1.165 rows=11,115 loops=1)

  • Index Cond: (jahr = 2019)
21. 0.001 0.047 ↑ 1.0 16 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.011 0.046 ↑ 1.0 16 1

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

  • Hash Cond: (tgn.bundeslandnummer = tgb.bundeslandnummer)
23. 0.016 0.016 ↑ 1.0 16 1

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

24. 0.004 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
25. 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)

26. 0.007 0.048 ↑ 1.0 33 1

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

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

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

28. 31.683 1,914.314 ↓ 4.9 332,311 1

Materialize (cost=208,550.56..208,890.62 rows=68,012 width=527) (actual time=1,751.643..1,914.314 rows=332,311 loops=1)

29. 519.040 1,882.631 ↓ 4.9 332,311 1

Sort (cost=208,550.56..208,720.59 rows=68,012 width=527) (actual time=1,751.637..1,882.631 rows=332,311 loops=1)

  • Sort Key: twv_1.standortnummer, twv_1.paechternummer, twv_1.flaechennummer
  • Sort Method: external merge Disk: 51184kB
30. 82.968 1,363.591 ↓ 4.9 332,311 1

Hash Join (cost=20,034.67..187,050.91 rows=68,012 width=527) (actual time=350.183..1,363.591 rows=332,311 loops=1)

  • Hash Cond: (twv_1.anbieterid = tan_1.anbieterid)
31. 81.098 1,280.562 ↓ 4.9 332,311 1

Hash Join (cost=20,032.93..186,847.71 rows=68,012 width=217) (actual time=350.108..1,280.562 rows=332,311 loops=1)

  • Hash Cond: (tgo_1.bundeslandnummer = tgb_1.bundeslandnummer)
32. 90.627 1,199.382 ↓ 4.9 332,311 1

Hash Join (cost=20,030.16..185,909.77 rows=68,012 width=183) (actual time=350.016..1,199.382 rows=332,311 loops=1)

  • Hash Cond: (twv_1.ortsnummer = tgo_1.ortsnummer)
33. 116.644 1,030.614 ↓ 4.9 332,311 1

Hash Left Join (cost=15,248.88..180,949.54 rows=68,147 width=171) (actual time=271.857..1,030.614 rows=332,311 loops=1)

  • Hash Cond: (twv_1.verbrauchermarktids[1] = tvm_1.verbrauchermarktid)
34. 667.950 851.807 ↓ 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=208.946..851.807 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. 183.857 183.857 ↓ 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=183.857..183.857 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. 26.952 62.163 ↑ 1.0 111,412 1

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

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

Seq Scan on t_verbrauchermaerkte tvm_1 (cost=0.00..3,489.12 rows=111,412 width=29) (actual time=0.059..35.211 rows=111,412 loops=1)

38. 4.554 78.141 ↓ 1.0 11,115 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 758kB
39. 71.380 73.587 ↓ 1.0 11,115 1

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

  • Recheck Cond: (jahr = 2019)
  • Heap Blocks: exact=3936
40. 2.207 2.207 ↓ 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=2.207..2.207 rows=11,115 loops=1)

  • Index Cond: (jahr = 2019)
41. 0.004 0.082 ↑ 1.0 16 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
42. 0.013 0.078 ↑ 1.0 16 1

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

  • Hash Cond: (tgn_1.bundeslandnummer = tgb_1.bundeslandnummer)
43. 0.028 0.028 ↑ 1.0 16 1

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

44. 0.008 0.037 ↑ 1.0 16 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
45. 0.029 0.029 ↑ 1.0 16 1

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

46. 0.015 0.061 ↑ 1.0 33 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
47. 0.046 0.046 ↑ 1.0 33 1

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

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

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

51. 0.000 0.000 ↓ 0.0 0

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

52. 0.000 0.000 ↓ 0.0 0

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

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

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

56. 0.000 0.000 ↓ 0.0 0

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

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