explain.depesz.com

PostgreSQL's explain analyze made readable

Result: E4km

Settings
# exclusive inclusive rows x rows loops node
1. 0.022 6,037.790 ↑ 7.0 1 1

HashAggregate (cost=266,584,512,395,898.12..266,584,512,478,792.72 rows=7 width=279) (actual time=6,037.790..6,037.790 rows=1 loops=1)

2.          

CTE subscription

3. 42.740 640.520 ↓ 5.9 50,955 1

HashAggregate (cost=46,937.38..47,023.47 rows=8,609 width=22) (actual time=625.047..640.520 rows=50,955 loops=1)

4. 79.214 597.780 ↓ 5.9 50,955 1

HashAggregate (cost=46,765.20..46,851.29 rows=8,609 width=22) (actual time=577.830..597.780 rows=50,955 loops=1)

5. 518.566 518.566 ↓ 1.0 86,198 1

Seq Scan on contractrate (cost=0.00..45,904.35 rows=86,085 width=22) (actual time=5.825..518.566 rows=86,198 loops=1)

  • Filter: ((rateitem)::text ~~ 'SUP-F-SUBSCRIPTION%'::text)
  • Rows Removed by Filter: 1,099,430
6.          

CTE margin

7. 195.596 770.159 ↓ 1.7 44,188 1

HashAggregate (cost=48,456.54..48,711.76 rows=25,522 width=22) (actual time=752.294..770.159 rows=44,188 loops=1)

8. 574.563 574.563 ↓ 1.0 257,335 1

Seq Scan on contractrate contractrate_1 (cost=0.00..45,904.35 rows=255,219 width=22) (actual time=0.042..574.563 rows=257,335 loops=1)

  • Filter: ((rateitem)::text ~~ 'SUP-V-ENERGY-X%'::text)
  • Rows Removed by Filter: 928,293
9.          

CTE contractualvolume

10. 45.604 53.987 ↓ 1.0 32,128 1

HashAggregate (cost=996.52..1,390.22 rows=31,496 width=13) (actual time=30.384..53.987 rows=32,128 loops=1)

11. 8.383 8.383 ↑ 1.0 37,301 1

Seq Scan on contractualvolume contractualvolume_1 (cost=0.00..810.01 rows=37,301 width=13) (actual time=0.027..8.383 rows=37,301 loops=1)

12.          

CTE discountsubscription

13. 1.537 610.982 ↓ 63.1 1,452 1

HashAggregate (cost=45,906.61..45,906.84 rows=23 width=22) (actual time=610.553..610.982 rows=1,452 loops=1)

14. 609.445 609.445 ↓ 6.5 1,459 1

Seq Scan on contractrate contractrate_2 (cost=0.00..45,904.35 rows=226 width=22) (actual time=4.453..609.445 rows=1,459 loops=1)

  • Filter: ((rateitem)::text ~~ 'SUP-F-DSC-SUBSCRIPTION%'::text)
  • Rows Removed by Filter: 1,184,169
15.          

CTE discdom

16. 706.874 706.874 ↓ 1.9 6,942 1

Seq Scan on contractrate contractrate_3 (cost=0.00..45,913.37 rows=3,609 width=31) (actual time=702.101..706.874 rows=6,942 loops=1)

  • Filter: ((rateitem)::text ~~ '%SUP-F-DSC-DOM-EMAIL%'::text)
  • Rows Removed by Filter: 1,178,686
17.          

CTE realvolume

18. 532.461 2,321.679 ↑ 1.0 428,878 1

Hash Left Join (cost=87,837.59..246,084.30 rows=430,574 width=51) (actual time=995.620..2,321.679 rows=428,878 loops=1)

  • Hash Cond: ((a_1.invoicenbr)::text = (b_1.invoicenbr)::text)
19. 820.582 954.031 ↑ 1.0 428,878 1

Bitmap Heap Scan on printshop a_1 (cost=12,448.97..148,922.99 rows=430,574 width=43) (actual time=160.301..954.031 rows=428,878 loops=1)

  • Recheck Cond: (((resultset)::text ~~ 'det.SUP-V-ENERGY%'::text) OR ((resultset)::text ~~ 'sub.SUP-V-ENERGY-X%'::text))
  • Filter: (((resultset)::text ~~ 'det.SUP-V-ENERGY%'::text) OR (((resultset)::text ~~ 'sub.SUP-V-ENERGY-X%'::text) AND ((resultset)::text !~~ 'det.SUP-V-ENERGY-TTF%'::text)))
20. 0.003 133.449 ↓ 0.0 0 1

BitmapOr (cost=12,448.97..12,448.97 rows=424,458 width=0) (actual time=133.449..133.449 rows=0 loops=1)

21. 121.302 121.302 ↓ 1.0 388,087 1

Bitmap Index Scan on idx_printshop_resultset_like (cost=0.00..11,058.87 rows=383,831 width=0) (actual time=121.302..121.302 rows=388,087 loops=1)

  • Index Cond: (((resultset)::text ~>=~ 'det.SUP-V-ENERGY'::text) AND ((resultset)::text ~<~ 'det.SUP-V-ENERGZ'::text))
22. 12.144 12.144 ↓ 1.0 40,791 1

Bitmap Index Scan on idx_printshop_resultset_like (cost=0.00..1,174.82 rows=40,626 width=0) (actual time=12.144..12.144 rows=40,791 loops=1)

  • Index Cond: (((resultset)::text ~>=~ 'sub.SUP-V-ENERGY-X'::text) AND ((resultset)::text ~<~ 'sub.SUP-V-ENERGY-Y'::text))
23. 298.706 835.187 ↓ 1.0 862,418 1

Hash (cost=59,556.94..59,556.94 rows=862,294 width=20) (actual time=835.187..835.187 rows=862,418 loops=1)

  • Buckets: 65,536 Batches: 2 Memory Usage: 21,926kB
24. 536.481 536.481 ↓ 1.0 862,418 1

Seq Scan on invoice b_1 (cost=0.00..59,556.94 rows=862,294 width=20) (actual time=0.005..536.481 rows=862,418 loops=1)

25.          

CTE businesspartner

26. 69.698 134.691 ↓ 1.0 94,318 1

Hash Right Join (cost=1,360.05..8,168.55 rows=93,850 width=45) (actual time=19.661..134.691 rows=94,318 loops=1)

  • Hash Cond: ((b_2.customernbr)::text = (a_2.customernbr)::text)
27. 45.359 45.359 ↑ 1.0 93,850 1

Seq Scan on contract b_2 (cost=0.00..4,931.50 rows=93,850 width=19) (actual time=0.015..45.359 rows=93,850 loops=1)

28. 6.761 19.634 ↑ 1.0 24,091 1

Hash (cost=1,058.91..1,058.91 rows=24,091 width=37) (actual time=19.634..19.634 rows=24,091 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 1,591kB
29. 12.873 12.873 ↑ 1.0 24,091 1

Seq Scan on businesspartner a_2 (cost=0.00..1,058.91 rows=24,091 width=37) (actual time=0.020..12.873 rows=24,091 loops=1)

30. 0.130 6,037.768 ↑ 11,255,761,279.0 2 1

Hash Left Join (cost=12,625.36..266,583,442,655,378.12 rows=22,511,522,558 width=279) (actual time=5,887.573..6,037.768 rows=2 loops=1)

  • Hash Cond: ((a.contractnbr)::text = (contractualvolume.contractnbr)::text)
31. 0.038 5,667.347 ↑ 71,692,747.0 2 1

Hash Left Join (cost=11,914.74..19,538,433.77 rows=143,385,494 width=247) (actual time=5,667.332..5,667.347 rows=2 loops=1)

  • Hash Cond: ((a.contractnbr)::text = (realvolume.contractnbr)::text)
  • Join Filter: ((realvolume.begindate >= a.startdate) AND (realvolume.enddate <= a.enddate))
32. 0.020 2,974.030 ↑ 599,382.0 1 1

Hash Left Join (cost=2,199.91..9,094.89 rows=599,382 width=247) (actual time=2,974.019..2,974.030 rows=1 loops=1)

  • Hash Cond: ((a.contractnbr)::text = (d.contractnbr)::text)
33. 0.021 2,775.070 ↑ 1,278.0 1 1

Hash Left Join (cost=82.43..905.31 rows=1,278 width=183) (actual time=2,775.059..2,775.070 rows=1 loops=1)

  • Hash Cond: ((a.contractnbr)::text = (discdom.contractnbr)::text)
34. 0.005 2,065.824 ↑ 71.0 1 1

Nested Loop Left Join (cost=1.00..810.74 rows=71 width=183) (actual time=2,065.814..2,065.824 rows=1 loops=1)

  • Join Filter: ((a.startdate >= margin.attachmentfromdate) AND (a.startdate < margin.attachmenttodate) AND ((a.contractnbr)::text = (margin.contractnbr)::text))
35. 0.014 1,275.655 ↑ 5.0 1 1

Nested Loop Left Join (cost=1.00..220.18 rows=5 width=151) (actual time=1,275.646..1,275.655 rows=1 loops=1)

  • Join Filter: ((a.startdate >= subscription.attachmentfromdate) AND ((a.contractnbr)::text = (subscription.contractnbr)::text) AND ((a.enddate - 15) <= subscription.attachmenttodate))
  • Rows Removed by Join Filter: 1
36. 0.004 611.801 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.00..25.61 rows=1 width=119) (actual time=611.793..611.801 rows=1 loops=1)

  • Join Filter: ((a.contractnbr)::text = (c.contractnbr)::text)
37. 0.007 611.766 ↑ 1.0 1 1

Nested Loop (cost=0.71..17.30 rows=1 width=100) (actual time=611.763..611.766 rows=1 loops=1)

38. 0.004 611.713 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.42..8.97 rows=1 width=70) (actual time=611.712..611.713 rows=1 loops=1)

  • Join Filter: ((a.startdate >= discountsubscription.attachmentfromdate) AND ((a.contractnbr)::text = (discountsubscription.contractnbr)::text) AND ((a.enddate - 15) <= discountsubscription.attachmenttodate))
39. 0.073 0.073 ↑ 1.0 1 1

Index Scan using idx_contracthistory_contractnbr on contracthistory a (cost=0.42..8.44 rows=1 width=38) (actual time=0.072..0.073 rows=1 loops=1)

  • Index Cond: ((contractnbr)::text = 'C004509'::text)
40. 611.636 611.636 ↓ 0.0 0 1

CTE Scan on discountsubscription (cost=0.00..0.52 rows=1 width=72) (actual time=611.636..611.636 rows=0 loops=1)

  • Filter: ((contractnbr)::text = 'C004509'::text)
  • Rows Removed by Filter: 1,452
41. 0.046 0.046 ↑ 1.0 1 1

Index Scan using idx_contract_contractnbr on contract b (cost=0.29..8.31 rows=1 width=38) (actual time=0.045..0.046 rows=1 loops=1)

  • Index Cond: ((contractnbr)::text = 'C004509'::text)
  • Filter: ((energytype)::text = 'NG'::text)
42. 0.031 0.031 ↑ 1.0 1 1

Index Scan using idx_contractdelivery_contractnbr on contractdelivery c (cost=0.29..8.31 rows=1 width=27) (actual time=0.026..0.031 rows=1 loops=1)

  • Index Cond: ((contractnbr)::text = 'C004509'::text)
43. 663.840 663.840 ↑ 43.0 1 1

CTE Scan on subscription (cost=0.00..193.70 rows=43 width=72) (actual time=636.715..663.840 rows=1 loops=1)

  • Filter: ((contractnbr)::text = 'C004509'::text)
  • Rows Removed by Filter: 50,954
44. 790.164 790.164 ↓ 0.0 0 1

CTE Scan on margin (cost=0.00..574.25 rows=128 width=72) (actual time=790.164..790.164 rows=0 loops=1)

  • Filter: ((contractnbr)::text = 'C004509'::text)
  • Rows Removed by Filter: 44,188
45. 0.000 709.225 ↓ 0.0 0 1

Hash (cost=81.20..81.20 rows=18 width=32) (actual time=709.225..709.225 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 0kB
46. 709.225 709.225 ↓ 0.0 0 1

CTE Scan on discdom (cost=0.00..81.20 rows=18 width=32) (actual time=709.225..709.225 rows=0 loops=1)

  • Filter: ((contractnbr)::text = 'C004509'::text)
  • Rows Removed by Filter: 6,942
47. 0.003 198.940 ↑ 469.0 1 1

Hash (cost=2,111.62..2,111.62 rows=469 width=96) (actual time=198.940..198.940 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
48. 198.937 198.937 ↑ 469.0 1 1

CTE Scan on businesspartner d (cost=0.00..2,111.62 rows=469 width=96) (actual time=28.833..198.937 rows=1 loops=1)

  • Filter: ((contractnbr)::text = 'C004509'::text)
  • Rows Removed by Filter: 94,317
49. 0.006 2,693.279 ↑ 1,076.5 2 1

Hash (cost=9,687.91..9,687.91 rows=2,153 width=40) (actual time=2,693.279..2,693.279 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
50. 2,693.273 2,693.273 ↑ 1,076.5 2 1

CTE Scan on realvolume (cost=0.00..9,687.91 rows=2,153 width=40) (actual time=1,326.432..2,693.273 rows=2 loops=1)

  • Filter: ((contractnbr)::text = 'C004509'::text)
  • Rows Removed by Filter: 428,876
51. 0.001 69.781 ↓ 0.0 0 1

Hash (cost=708.66..708.66 rows=157 width=64) (actual time=69.781..69.781 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 0kB
52. 69.780 69.780 ↓ 0.0 0 1

CTE Scan on contractualvolume (cost=0.00..708.66 rows=157 width=64) (actual time=69.780..69.780 rows=0 loops=1)

  • Filter: ((contractnbr)::text = 'C004509'::text)
  • Rows Removed by Filter: 32,128
53.          

SubPlan (for Hash Left Join)

54. 0.020 300.510 ↑ 1.0 1 2

HashAggregate (cost=11,842.00..11,842.01 rows=1 width=52) (actual time=150.255..150.255 rows=1 loops=2)

55. 0.050 300.490 ↑ 1.0 1 2

GroupAggregate (cost=0.00..11,841.99 rows=1 width=52) (actual time=150.245..150.245 rows=1 loops=2)

56. 300.440 300.440 ↑ 119.5 2 2

CTE Scan on realvolume realvolume_1 (cost=0.00..11,840.79 rows=239 width=52) (actual time=20.413..150.220 rows=2 loops=2)

  • Filter: ((begindate >= a.startdate) AND (enddate <= a.enddate) AND ((a.contractnbr)::text = (contractnbr)::text))
  • Rows Removed by Filter: 428,876
Total runtime : 6,045.276 ms