explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HTtM

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 6,314.498 ↑ 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,314.498..6,314.498 rows=1 loops=1)

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

CTE subscription

3. 48.018 703.340 ↓ 5.9 50,955 1

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

4. 88.085 655.322 ↓ 5.9 50,955 1

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

5. 567.237 567.237 ↓ 1.0 86,198 1

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

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

CTE margin

7. 213.555 827.326 ↓ 1.7 44,188 1

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

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

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

CTE contractualvolume

10. 37.327 44.224 ↓ 1.0 32,128 1

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

11. 6.897 6.897 ↑ 1.0 37,301 1

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

12.          

CTE discountsubscription

13. 1.885 590.420 ↓ 63.1 1,452 1

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

14. 588.535 588.535 ↓ 6.5 1,459 1

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

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

CTE discdom

16. 594.323 594.323 ↓ 1.9 6,942 1

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

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

  • Hash Cond: ((a_1.invoicenbr)::text = (b_1.invoicenbr)::text)
19. 1,037.489 1,144.442 ↑ 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=127.157..1,144.442 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 106.953 ↓ 0.0 0 1

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

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

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

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

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

25.          

CTE businesspartner

26. 72.579 148.222 ↓ 1.0 94,318 1

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

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

28. 7.612 27.216 ↑ 1.0 24,091 1

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

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

30. 0.022 6,314.485 ↑ 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,314.485..6,314.485 rows=1 loops=1)

31. 0.115 6,314.463 ↑ 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,186.980..6,314.463 rows=2 loops=1)

  • Hash Cond: ((a.contractnbr)::text = (contractualvolume.contractnbr)::text)
32. 0.036 6,001.698 ↑ 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=6,001.682..6,001.698 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.027 2,975.759 ↑ 599,382.0 1 1

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

  • Hash Cond: ((a.contractnbr)::text = (d.contractnbr)::text)
34. 0.018 2,765.122 ↑ 1,278.0 1 1

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

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

Nested Loop Left Join (cost=1.00..810.74 rows=71 width=183) (actual time=2,168.017..2,168.027 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,320.751 ↑ 5.0 1 1

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

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

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

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

39. 0.003 591.209 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.42..8.97 rows=1 width=70) (actual time=591.208..591.209 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.109 0.109 ↑ 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.108..0.109 rows=1 loops=1)

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

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

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

  • Index Cond: ((contractnbr)::text = 'C004509'::text)
  • Filter: ((energytype)::text = 'NG'::text)
43. 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)
44. 729.442 729.442 ↑ 43.0 1 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SubPlan (for Hash Left Join)

55. 0.020 254.696 ↑ 1.0 1 2

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

56. 0.038 254.676 ↑ 1.0 1 2

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

57. 254.638 254.638 ↑ 119.5 2 2

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