explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IK6m

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 9,780.123 ↑ 7.0 1 1

Sort (cost=266,584,512,582,701.97..266,584,512,582,702.00 rows=7 width=279) (actual time=9,780.123..9,780.123 rows=1 loops=1)

  • Sort Key: b.customernbr, a.nbr
  • Sort Method: quicksort Memory: 25kB
2.          

CTE subscription

3. 44.093 783.016 ↓ 5.9 50,955 1

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

4. 89.579 738.923 ↓ 5.9 50,955 1

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

5. 649.344 649.344 ↓ 1.0 86,198 1

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

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

CTE margin

7. 191.567 778.512 ↓ 1.7 44,188 1

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

8. 586.945 586.945 ↓ 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.076..586.945 rows=257,335 loops=1)

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

CTE contractualvolume

10. 50.023 65.176 ↓ 1.0 32,128 1

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

11. 15.153 15.153 ↑ 1.0 37,301 1

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

12.          

CTE discountsubscription

13. 1.624 698.201 ↓ 63.1 1,452 1

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

14. 696.577 696.577 ↓ 6.5 1,459 1

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

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

CTE discdom

16. 683.319 683.319 ↓ 1.9 6,942 1

Seq Scan on contractrate contractrate_3 (cost=0.00..45,913.37 rows=3,609 width=31) (actual time=678.619..683.319 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. 544.620 5,762.896 ↑ 1.0 428,878 1

Hash Left Join (cost=75,388.62..349,993.46 rows=430,574 width=51) (actual time=1,200.858..5,762.896 rows=428,878 loops=1)

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

Seq Scan on printshop a_1 (cost=0.00..252,832.15 rows=430,574 width=43) (actual time=0.048..4,017.584 rows=428,878 loops=1)

  • 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)))
  • Rows Removed by Filter: 6,644,616
20. 297.744 1,200.692 ↓ 1.0 862,418 1

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

  • Buckets: 65,536 Batches: 2 Memory Usage: 21,926kB
21. 902.948 902.948 ↓ 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.003..902.948 rows=862,418 loops=1)

22.          

CTE businesspartner

23. 66.777 211.784 ↓ 1.0 94,318 1

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

  • Hash Cond: ((b_2.customernbr)::text = (a_2.customernbr)::text)
24. 109.071 109.071 ↑ 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.201..109.071 rows=93,850 loops=1)

25. 7.018 35.936 ↑ 1.0 24,091 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 1,591kB
26. 28.918 28.918 ↑ 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.254..28.918 rows=24,091 loops=1)

27. 0.022 9,780.111 ↑ 7.0 1 1

HashAggregate (cost=266,584,511,952,699.62..266,584,512,035,594.22 rows=7 width=279) (actual time=9,780.111..9,780.111 rows=1 loops=1)

28. 0.126 9,780.089 ↑ 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=9,630.321..9,780.089 rows=2 loops=1)

  • Hash Cond: ((a.contractnbr)::text = (contractualvolume.contractnbr)::text)
29. 0.032 9,390.327 ↑ 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=9,390.310..9,390.327 rows=2 loops=1)

  • Hash Cond: ((a.contractnbr)::text = (realvolume.contractnbr)::text)
  • Join Filter: ((realvolume.begindate >= a.startdate) AND (realvolume.enddate <= a.enddate))
30. 0.021 3,260.561 ↑ 599,382.0 1 1

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

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

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

  • Hash Cond: ((a.contractnbr)::text = (discdom.contractnbr)::text)
32. 0.004 2,306.265 ↑ 71.0 1 1

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

  • Join Filter: ((a.startdate >= margin.attachmentfromdate) AND (a.startdate < margin.attachmenttodate) AND ((a.contractnbr)::text = (margin.contractnbr)::text))
33. 0.011 1,507.678 ↑ 5.0 1 1

Nested Loop Left Join (cost=1.00..220.18 rows=5 width=151) (actual time=1,507.669..1,507.678 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
34. 0.006 701.500 ↑ 1.0 1 1

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

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

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

36. 0.005 700.093 ↑ 1.0 1 1

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

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

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

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

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

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

Index Scan using contract_pkey on contract b (cost=0.29..8.31 rows=1 width=38) (actual time=0.696..0.697 rows=1 loops=1)

  • Index Cond: ((contractnbr)::text = 'C004509'::text)
  • Filter: ((energytype)::text = 'NG'::text)
40. 0.697 0.697 ↑ 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.693..0.697 rows=1 loops=1)

  • Index Cond: ((contractnbr)::text = 'C004509'::text)
41. 806.167 806.167 ↑ 43.0 1 1

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

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

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

  • Filter: ((contractnbr)::text = 'C004509'::text)
  • Rows Removed by Filter: 44,188
43. 0.002 685.714 ↓ 0.0 0 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 0kB
44. 685.712 685.712 ↓ 0.0 0 1

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

  • Filter: ((contractnbr)::text = 'C004509'::text)
  • Rows Removed by Filter: 6,942
45. 0.005 268.540 ↑ 469.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
46. 268.535 268.535 ↑ 469.0 1 1

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
48. 6,129.728 6,129.728 ↑ 1,076.5 2 1

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 0kB
50. 81.187 81.187 ↓ 0.0 0 1

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

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

SubPlan (for Hash Left Join)

52. 0.020 308.448 ↑ 1.0 1 2

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

53. 0.050 308.428 ↑ 1.0 1 2

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

54. 308.378 308.378 ↑ 119.5 2 2

CTE Scan on realvolume realvolume_1 (cost=0.00..11,840.79 rows=239 width=52) (actual time=20.476..154.189 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 : 9,787.068 ms