explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cvKY

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 11,293.219 ↑ 7.0 1 1

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

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

CTE subscription

3. 60.840 934.050 ↓ 5.9 50,955 1

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

4. 119.885 873.210 ↓ 5.9 50,955 1

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

5. 753.325 753.325 ↓ 1.0 86,198 1

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

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

CTE margin

7. 268.923 1,021.524 ↓ 1.7 44,188 1

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

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

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

CTE contractualvolume

10. 73.169 79.840 ↓ 1.0 32,128 1

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

11. 6.671 6.671 ↑ 1.0 37,301 1

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

12.          

CTE discountsubscription

13. 1.882 730.403 ↓ 63.1 1,452 1

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

14. 728.521 728.521 ↓ 6.5 1,459 1

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

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

CTE discdom

16. 836.134 836.134 ↓ 1.9 6,942 1

Seq Scan on contractrate contractrate_3 (cost=0.00..45,913.37 rows=3,609 width=31) (actual time=830.827..836.134 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. 647.244 6,457.571 ↑ 1.0 428,878 1

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

  • Hash Cond: ((a_1.invoicenbr)::text = (b_1.invoicenbr)::text)
19. 4,392.375 4,392.375 ↑ 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.335..4,392.375 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. 360.238 1,417.952 ↓ 1.0 862,418 1

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

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

22.          

CTE businesspartner

23. 82.030 115.087 ↓ 1.0 94,318 1

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

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

25. 8.870 19.120 ↑ 1.0 24,091 1

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

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

27. 0.031 11,293.199 ↑ 7.0 1 1

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

28. 0.156 11,293.168 ↑ 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=11,101.566..11,293.168 rows=2 loops=1)

  • Hash Cond: ((a.contractnbr)::text = (contractualvolume.contractnbr)::text)
29. 0.036 10,687.139 ↑ 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=10,687.118..10,687.139 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.019 3,765.424 ↑ 599,382.0 1 1

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

  • Hash Cond: ((a.contractnbr)::text = (d.contractnbr)::text)
31. 0.020 3,583.037 ↑ 1,278.0 1 1

Hash Left Join (cost=82.43..905.31 rows=1,278 width=183) (actual time=3,583.024..3,583.037 rows=1 loops=1)

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

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

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

Nested Loop Left Join (cost=1.00..220.18 rows=5 width=151) (actual time=1,693.404..1,693.415 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 731.322 ↑ 1.0 1 1

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

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

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

36. 0.005 731.256 ↑ 1.0 1 1

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

  • Join Filter: ((a.startdate >= discountsubscription.attachmentfromdate) AND ((a.contractnbr)::text = (discountsubscription.contractnbr)::text) AND ((a.enddate - 15) <= discountsubscription.attachmenttodate))
37. 0.045 0.045 ↑ 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.043..0.045 rows=1 loops=1)

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

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

  • Filter: ((contractnbr)::text = 'C004509'::text)
  • Rows Removed by Filter: 1,452
39. 0.033 0.033 ↑ 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.031..0.033 rows=1 loops=1)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SubPlan (for Hash Left Join)

52. 0.024 500.488 ↑ 1.0 1 2

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

53. 0.068 500.464 ↑ 1.0 1 2

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

54. 500.396 500.396 ↑ 119.5 2 2

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