explain.depesz.com

PostgreSQL's explain analyze made readable

Result: COf6

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 8,743.715 ↑ 18,462,767,400.0 1 1

Unique (cost=24,359,973,874,327,612.00..24,360,058,525,415,152.00 rows=18,462,767,400 width=453) (actual time=8,743.711..8,743.715 rows=1 loops=1)

2.          

CTE subscription

3. 16.147 1,227.304 ↑ 1.2 58,668 1

Unique (cost=68,792.11..69,665.22 rows=69,849 width=48) (actual time=1,200.121..1,227.304 rows=58,668 loops=1)

4. 397.593 1,211.157 ↑ 1.2 58,668 1

Sort (cost=68,792.11..68,966.73 rows=69,849 width=48) (actual time=1,200.120..1,211.157 rows=58,668 loops=1)

  • Sort Key: contractrate.contractnbr, contractrate.attachmentfromdate, contractrate.attachmenttodate, (sum(contractrate.unitprice))
  • Sort Method: external merge Disk: 1,768kB
5. 40.776 813.564 ↑ 1.2 58,668 1

GroupAggregate (cost=59,867.98..61,944.08 rows=69,849 width=48) (actual time=756.187..813.564 rows=58,668 loops=1)

  • Group Key: contractrate.attachmentfromdate, contractrate.attachmenttodate, contractrate.contractnbr
6. 303.716 772.788 ↓ 1.0 97,558 1

Sort (cost=59,867.98..60,108.57 rows=96,239 width=21) (actual time=756.176..772.788 rows=97,558 loops=1)

  • Sort Key: contractrate.attachmentfromdate, contractrate.attachmenttodate, contractrate.contractnbr
  • Sort Method: external merge Disk: 2,912kB
7. 469.072 469.072 ↓ 1.0 97,558 1

Seq Scan on contractrate (cost=0.00..50,774.11 rows=96,239 width=21) (actual time=4.780..469.072 rows=97,558 loops=1)

  • Filter: ((rateitem)::text ~~ 'SUP-F-SUBSCRIPTION%'::text)
  • Rows Removed by Filter: 1,207,884
8.          

CTE margin

9. 86.780 1,466.398 ↑ 2.3 52,083 1

GroupAggregate (cost=80,720.78..85,875.17 rows=120,193 width=48) (actual time=1,141.713..1,466.398 rows=52,083 loops=1)

  • Group Key: contractrate_1.attachmentfromdate, contractrate_1.attachmenttodate, contractrate_1.contractnbr
10. 883.425 1,379.618 ↑ 1.0 290,059 1

Sort (cost=80,720.78..81,451.18 rows=292,158 width=21) (actual time=1,141.703..1,379.618 rows=290,059 loops=1)

  • Sort Key: contractrate_1.attachmentfromdate, contractrate_1.attachmenttodate, contractrate_1.contractnbr
  • Sort Method: external merge Disk: 8,936kB
11. 496.193 496.193 ↑ 1.0 290,059 1

Seq Scan on contractrate contractrate_1 (cost=0.00..50,774.11 rows=292,158 width=21) (actual time=0.075..496.193 rows=290,059 loops=1)

  • Filter: ((rateitem)::text ~~ 'SUP-V-ENERGY-X%'::text)
  • Rows Removed by Filter: 1,015,383
12.          

CTE contractualvolume

13. 27.292 228.757 ↓ 1.0 39,358 1

GroupAggregate (cost=4,483.02..5,392.92 rows=38,028 width=40) (actual time=197.667..228.757 rows=39,358 loops=1)

  • Group Key: contractualvolume_1.contractnbr
14. 195.972 201.465 ↑ 1.0 45,265 1

Sort (cost=4,483.02..4,596.18 rows=45,265 width=13) (actual time=197.646..201.465 rows=45,265 loops=1)

  • Sort Key: contractualvolume_1.contractnbr
  • Sort Method: quicksort Memory: 3,514kB
15. 5.493 5.493 ↑ 1.0 45,265 1

Seq Scan on contractualvolume contractualvolume_1 (cost=0.00..982.65 rows=45,265 width=13) (actual time=0.011..5.493 rows=45,265 loops=1)

16.          

CTE discountsubscription

17. 0.747 461.956 ↑ 1.1 1,508 1

GroupAggregate (cost=50,867.46..50,910.71 rows=1,725 width=48) (actual time=461.094..461.956 rows=1,508 loops=1)

  • Group Key: contractrate_2.attachmentfromdate, contractrate_2.attachmenttodate, contractrate_2.contractnbr
18. 1.349 461.209 ↑ 1.1 1,516 1

Sort (cost=50,867.46..50,871.80 rows=1,735 width=21) (actual time=461.084..461.209 rows=1,516 loops=1)

  • Sort Key: contractrate_2.attachmentfromdate, contractrate_2.attachmenttodate, contractrate_2.contractnbr
  • Sort Method: quicksort Memory: 167kB
19. 459.860 459.860 ↑ 1.1 1,516 1

Seq Scan on contractrate contractrate_2 (cost=0.00..50,774.11 rows=1,735 width=21) (actual time=2.749..459.860 rows=1,516 loops=1)

  • Filter: ((rateitem)::text ~~ 'SUP-F-DSC-SUBSCRIPTION%'::text)
  • Rows Removed by Filter: 1,303,926
20.          

CTE discdom

21. 540.180 540.180 ↓ 1.8 8,599 1

Seq Scan on contractrate contractrate_3 (cost=0.00..50,785.91 rows=4,718 width=12) (actual time=536.418..540.180 rows=8,599 loops=1)

  • Filter: ((rateitem)::text ~~ '%SUP-F-DSC-DOM-EMAIL%'::text)
  • Rows Removed by Filter: 1,296,843
22.          

CTE realvolume

23. 443.667 4,188.198 ↓ 1.0 503,806 1

Hash Left Join (cost=96,708.17..444,317.17 rows=496,655 width=51) (actual time=950.560..4,188.198 rows=503,806 loops=1)

  • Hash Cond: ((a_1.invoicenbr)::text = (b_1.invoicenbr)::text)
24. 2,794.931 2,794.931 ↓ 1.0 503,806 1

Seq Scan on printshop a_1 (cost=0.00..325,574.99 rows=496,655 width=43) (actual time=0.181..2,794.931 rows=503,806 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: 8,637,266
25. 279.847 949.600 ↓ 1.0 1,104,961 1

Hash (cost=76,426.74..76,426.74 rows=1,104,674 width=20) (actual time=949.600..949.600 rows=1,104,961 loops=1)

  • Buckets: 65,536 Batches: 32 Memory Usage: 2,289kB
26. 669.753 669.753 ↓ 1.0 1,104,961 1

Seq Scan on invoice b_1 (cost=0.00..76,426.74 rows=1,104,674 width=20) (actual time=0.007..669.753 rows=1,104,961 loops=1)

27.          

CTE businesspartner

28. 38.147 59.789 ↓ 1.0 109,260 1

Hash Right Join (cost=1,526.13..8,721.54 rows=108,733 width=45) (actual time=11.551..59.789 rows=109,260 loops=1)

  • Hash Cond: ((b_2.customernbr)::text = (a_2.customernbr)::text)
29. 10.133 10.133 ↑ 1.0 108,733 1

Seq Scan on contract b_2 (cost=0.00..5,700.33 rows=108,733 width=19) (actual time=0.007..10.133 rows=108,733 loops=1)

30. 5.416 11.509 ↑ 1.0 27,028 1

Hash (cost=1,188.28..1,188.28 rows=27,028 width=37) (actual time=11.509..11.509 rows=27,028 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 2,045kB
31. 6.093 6.093 ↑ 1.0 27,028 1

Seq Scan on businesspartner a_2 (cost=0.00..1,188.28 rows=27,028 width=37) (actual time=0.010..6.093 rows=27,028 loops=1)

32. 0.019 8,743.711 ↑ 891,064,079,360.0 2 1

Sort (cost=24,359,973,873,611,944.00..24,359,978,328,932,340.00 rows=1,782,128,158,720 width=453) (actual time=8,743.710..8,743.711 rows=2 loops=1)

  • Sort Key: b.customernbr, a.nbr, c.ean, d.vatnumber, d.companyname, a.productcode, (to_char((a.subscriptiondate)::timestamp with time zone, 'DD/MM/YYYY'::text)), (to_char((a.startdate)::timestamp with time zone, 'DD/MM/YYYY'::text)), (to_char((a.enddate)::timestamp with time zone, 'DD/MM/YYYY'::text)), b.invdeliverymodecode, (CASE WHEN ((b.domiciliationstatuscode)::text ~~ 'active'::text) THEN 'YES'::text ELSE 'NO'::text END), (CASE WHEN (contractualvolume.volume IS NULL) THEN '0'::numeric ELSE contractualvolume.volume END), (replace((round((SubPlan 8), 2))::text, '.'::text, ','::text)), (replace((round(CASE WHEN (subscription.subscr IS NULL) THEN '0'::numeric ELSE subscription.subscr END, 2))::text, '.'::text, ','::text)), (replace((round(CASE WHEN (discountsubscription.subscr IS NULL) THEN '0'::numeric ELSE discountsubscription.subscr END, 2))::text, '.'::text, ','::text)), (replace((round(CASE WHEN (discountsubscription.subscr IS NOT NULL) THEN (subscription.subscr + discountsubscription.subscr) WHEN (discountsubscription.subscr IS NULL) THEN CASE WHEN (subscription.subscr IS NULL) THEN '0'::numeric ELSE subscription.subscr END ELSE NULL::numeric END, 2))::text, '.'::text, ','::text)), (replace((round(CASE WHEN (margin.marge IS NULL) THEN '0'::numeric ELSE margin.marge END, 2))::text, '.'::text, ','::text)), b.custinfovalue30
  • Sort Method: quicksort Memory: 25kB
33. 0.087 8,743.692 ↑ 891,064,079,360.0 2 1

Hash Left Join (cost=16,200.62..24,357,940,494,447,320.00 rows=1,782,128,158,720 width=453) (actual time=8,634.377..8,743.692 rows=2 loops=1)

  • Hash Cond: ((a.contractnbr)::text = (contractualvolume.contractnbr)::text)
34. 0.026 8,284.108 ↑ 4,689,810,944.0 2 1

Hash Left Join (cost=15,342.61..1,277,236,780.66 rows=9,379,621,888 width=247) (actual time=8,284.097..8,284.108 rows=2 loops=1)

  • Hash Cond: ((a.contractnbr)::text = (realvolume.contractnbr)::text)
  • Join Filter: ((realvolume.begindate >= a.startdate) AND (realvolume.enddate <= a.enddate))
35. 0.020 3,844.825 ↑ 33,997,824.0 1 1

Hash Left Join (cost=4,136.84..352,300.01 rows=33,997,824 width=247) (actual time=3,844.817..3,844.825 rows=1 loops=1)

  • Hash Cond: ((a.contractnbr)::text = (d.contractnbr)::text)
36. 0.010 3,736.396 ↑ 62,496.0 1 1

Hash Left Join (cost=1,683.54..5,493.76 rows=62,496 width=183) (actual time=3,736.388..3,736.396 rows=1 loops=1)

  • Hash Cond: ((a.contractnbr)::text = (discdom.contractnbr)::text)
37. 0.025 3,193.696 ↑ 2,604.0 1 1

Hash Left Join (cost=1,577.09..4,749.32 rows=2,604 width=183) (actual time=3,193.688..3,193.696 rows=1 loops=1)

  • Hash Cond: ((a.contractnbr)::text = (subscription.contractnbr)::text)
  • Join Filter: ((a.startdate >= subscription.attachmentfromdate) AND ((a.enddate - 15) <= subscription.attachmenttodate))
  • Rows Removed by Join Filter: 1
38. 0.001 1,952.632 ↑ 67.0 1 1

Nested Loop Left Join (cost=1.12..2,761.05 rows=67 width=151) (actual time=1,952.626..1,952.632 rows=1 loops=1)

  • Join Filter: ((a.startdate >= margin.attachmentfromdate) AND (a.startdate < margin.attachmenttodate) AND ((a.contractnbr)::text = (margin.contractnbr)::text))
39. 0.004 462.572 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.12..46.20 rows=1 width=119) (actual time=462.566..462.572 rows=1 loops=1)

  • Join Filter: ((a.contractnbr)::text = (c.contractnbr)::text)
40. 0.005 462.551 ↑ 1.0 1 1

Nested Loop (cost=0.83..43.88 rows=1 width=100) (actual time=462.549..462.551 rows=1 loops=1)

41. 0.002 462.511 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.42..41.43 rows=1 width=69) (actual time=462.511..462.511 rows=1 loops=1)

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

Index Scan using idx_contracthistory_contractnbr on contracthistory a (cost=0.42..2.44 rows=1 width=37) (actual time=0.022..0.022 rows=1 loops=1)

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

CTE Scan on discountsubscription (cost=0.00..38.81 rows=9 width=72) (actual time=462.487..462.487 rows=0 loops=1)

  • Filter: ((contractnbr)::text = 'C004509'::text)
  • Rows Removed by Filter: 1,508
44. 0.035 0.035 ↑ 1.0 1 1

Index Scan using contract_pkey on contract b (cost=0.42..2.44 rows=1 width=39) (actual time=0.034..0.035 rows=1 loops=1)

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

Index Scan using idx_contractdelivery_contractnbr on contractdelivery c (cost=0.29..2.31 rows=1 width=27) (actual time=0.014..0.017 rows=1 loops=1)

  • Index Cond: ((contractnbr)::text = 'C004509'::text)
46. 1,490.059 1,490.059 ↓ 0.0 0 1

CTE Scan on margin (cost=0.00..2,704.34 rows=601 width=72) (actual time=1,490.059..1,490.059 rows=0 loops=1)

  • Filter: ((contractnbr)::text = 'C004509'::text)
  • Rows Removed by Filter: 52,083
47. 0.003 1,241.039 ↑ 349.0 1 1

Hash (cost=1,571.60..1,571.60 rows=349 width=72) (actual time=1,241.039..1,241.039 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
48. 1,241.036 1,241.036 ↑ 349.0 1 1

CTE Scan on subscription (cost=0.00..1,571.60 rows=349 width=72) (actual time=1,209.168..1,241.036 rows=1 loops=1)

  • Filter: ((contractnbr)::text = 'C004509'::text)
  • Rows Removed by Filter: 58,667
49. 0.001 542.690 ↓ 0.0 0 1

Hash (cost=106.16..106.16 rows=24 width=32) (actual time=542.690..542.690 rows=0 loops=1)

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

CTE Scan on discdom (cost=0.00..106.16 rows=24 width=32) (actual time=542.689..542.689 rows=0 loops=1)

  • Filter: ((contractnbr)::text = 'C004509'::text)
  • Rows Removed by Filter: 8,599
51. 0.003 108.409 ↑ 544.0 1 1

Hash (cost=2,446.49..2,446.49 rows=544 width=96) (actual time=108.409..108.409 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
52. 108.406 108.406 ↑ 544.0 1 1

CTE Scan on businesspartner d (cost=0.00..2,446.49 rows=544 width=96) (actual time=11.556..108.406 rows=1 loops=1)

  • Filter: ((contractnbr)::text = 'C004509'::text)
  • Rows Removed by Filter: 109,259
53. 0.020 4,439.257 ↑ 1,241.5 2 1

Hash (cost=11,174.74..11,174.74 rows=2,483 width=40) (actual time=4,439.257..4,439.257 rows=2 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 33kB
54. 4,439.237 4,439.237 ↑ 1,241.5 2 1

CTE Scan on realvolume (cost=0.00..11,174.74 rows=2,483 width=40) (actual time=4,228.575..4,439.237 rows=2 loops=1)

  • Filter: ((contractnbr)::text = 'C004509'::text)
  • Rows Removed by Filter: 503,804
55. 0.000 240.799 ↓ 0.0 0 1

Hash (cost=855.63..855.63 rows=190 width=64) (actual time=240.799..240.799 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
56. 240.799 240.799 ↓ 0.0 0 1

CTE Scan on contractualvolume (cost=0.00..855.63 rows=190 width=64) (actual time=240.799..240.799 rows=0 loops=1)

  • Filter: ((contractnbr)::text = 'C004509'::text)
  • Rows Removed by Filter: 39,358
57.          

SubPlan (for Hash Left Join)

58. 0.004 218.698 ↑ 150.0 1 2

Unique (cost=13,667.06..13,667.81 rows=150 width=64) (actual time=109.348..109.349 rows=1 loops=2)

59. 0.022 218.694 ↑ 150.0 1 2

Sort (cost=13,667.06..13,667.44 rows=150 width=64) (actual time=109.347..109.347 rows=1 loops=2)

  • Sort Key: ((sum(realvolume_1.realvolume) / '1000'::numeric))
  • Sort Method: quicksort Memory: 25kB
60. 0.032 218.672 ↑ 150.0 1 2

GroupAggregate (cost=0.00..13,661.64 rows=150 width=64) (actual time=109.335..109.336 rows=1 loops=2)

  • Group Key: realvolume_1.contractnbr
61. 218.640 218.640 ↑ 138.0 2 2

CTE Scan on realvolume realvolume_1 (cost=0.00..13,658.01 rows=276 width=52) (actual time=62.710..109.320 rows=2 loops=2)

  • Filter: ((begindate >= a.startdate) AND (enddate <= a.enddate) AND ((a.contractnbr)::text = (contractnbr)::text))
  • Rows Removed by Filter: 503,804
Planning time : 2.309 ms
Execution time : 8,745.914 ms