explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ow5B

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 6,266.643 ↑ 7.0 1 1

Sort (cost=266,584,512,478,792.81..266,584,512,478,792.84 rows=7 width=279) (actual time=6,266.643..6,266.643 rows=1 loops=1)

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

CTE subscription

3. 42.851 768.460 ↓ 5.9 50,955 1

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

4. 88.525 725.609 ↓ 5.9 50,955 1

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

5. 637.084 637.084 ↓ 1.0 86,198 1

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

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

CTE margin

7. 207.380 798.248 ↓ 1.7 44,188 1

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

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

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

CTE contractualvolume

10. 37.972 45.145 ↓ 1.0 32,128 1

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

11. 7.173 7.173 ↑ 1.0 37,301 1

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

12.          

CTE discountsubscription

13. 4.239 771.097 ↓ 63.1 1,452 1

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

14. 766.858 766.858 ↓ 6.5 1,459 1

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

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

CTE discdom

16. 641.258 641.258 ↓ 1.9 6,942 1

Seq Scan on contractrate contractrate_3 (cost=0.00..45,913.37 rows=3,609 width=31) (actual time=636.533..641.258 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. 546.638 2,355.789 ↑ 1.0 428,878 1

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

  • Hash Cond: ((a_1.invoicenbr)::text = (b_1.invoicenbr)::text)
19. 875.269 972.581 ↑ 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=116.636..972.581 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.002 97.312 ↓ 0.0 0 1

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

21. 86.841 86.841 ↓ 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=86.841..86.841 rows=388,087 loops=1)

  • Index Cond: (((resultset)::text ~>=~ 'det.SUP-V-ENERGY'::text) AND ((resultset)::text ~<~ 'det.SUP-V-ENERGZ'::text))
22. 10.469 10.469 ↓ 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=10.469..10.469 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. 306.600 836.570 ↓ 1.0 862,418 1

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

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

25.          

CTE businesspartner

26. 63.404 124.157 ↓ 1.0 94,318 1

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

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

28. 6.953 19.805 ↑ 1.0 24,091 1

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

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

30. 0.019 6,266.630 ↑ 7.0 1 1

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

31. 0.119 6,266.611 ↑ 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=6,137.163..6,266.611 rows=2 loops=1)

  • Hash Cond: ((a.contractnbr)::text = (contractualvolume.contractnbr)::text)
32. 0.031 5,949.863 ↑ 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,949.848..5,949.863 rows=2 loops=1)

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

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

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

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

  • Hash Cond: ((a.contractnbr)::text = (discdom.contractnbr)::text)
35. 0.007 2,387.103 ↑ 71.0 1 1

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

  • Join Filter: ((a.startdate >= margin.attachmentfromdate) AND (a.startdate < margin.attachmenttodate) AND ((a.contractnbr)::text = (margin.contractnbr)::text))
36. 0.009 1,565.805 ↑ 5.0 1 1

Nested Loop Left Join (cost=1.00..220.18 rows=5 width=151) (actual time=1,565.796..1,565.805 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
37. 0.007 774.706 ↑ 1.0 1 1

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

  • Join Filter: ((a.contractnbr)::text = (c.contractnbr)::text)
38. 0.018 774.639 ↑ 1.0 1 1

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

39. 0.004 774.137 ↑ 1.0 1 1

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

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

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

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

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

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

  • Index Cond: ((contractnbr)::text = 'C004509'::text)
44. 791.090 791.090 ↑ 43.0 1 1

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

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

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 0kB
47. 643.722 643.722 ↓ 0.0 0 1

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
49. 180.084 180.084 ↑ 469.0 1 1

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

  • Filter: ((contractnbr)::text = 'C004509'::text)
  • Rows Removed by Filter: 94,317
50. 0.005 2,738.880 ↑ 1,076.5 2 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
51. 2,738.875 2,738.875 ↑ 1,076.5 2 1

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

  • Filter: ((contractnbr)::text = 'C004509'::text)
  • Rows Removed by Filter: 428,876
52. 0.000 58.745 ↓ 0.0 0 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 0kB
53. 58.745 58.745 ↓ 0.0 0 1

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

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

SubPlan (for Hash Left Join)

55. 0.020 257.884 ↑ 1.0 1 2

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

56. 0.042 257.864 ↑ 1.0 1 2

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

57. 257.822 257.822 ↑ 119.5 2 2

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