explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YKhmO

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 5,527.698 ↑ 15,861.9 45 1

Append (cost=43,978,348,125.82..43,978,373,580.37 rows=713,786 width=120) (actual time=3,194.337..5,527.698 rows=45 loops=1)

2.          

CTE picklist_0

3. 0.025 0.025 ↑ 1.0 6 1

Seq Scan on t376_opportunitystagenamepicklistdim (cost=0.00..4.54 rows=6 width=4) (actual time=0.007..0.025 rows=6 loops=1)

  • Filter: (correlated_value = ANY ('{"Closed - Booked","Closed- Booked","Closed - Booked (sales order no. require",Booked/Won,"Closed Booked","Budget Won/Live"}'::text[]))
  • Rows Removed by Filter: 82
4.          

CTE picklist_1

5. 0.038 0.038 ↑ 1.0 7 1

Seq Scan on t376_opportunitystagenamepicklistdim t376_opportunitystagenamepicklistdim_1 (cost=0.00..4.65 rows=7 width=4) (actual time=0.004..0.038 rows=7 loops=1)

  • Filter: (correlated_value = ANY ('{Lost,"Closed - Clean Up","No Commit","Closed Lost","Closed - CleanUp",Closed/Lost,"Closed - Lost"}'::text[]))
  • Rows Removed by Filter: 81
6.          

CTE picklist_4

7. 0.047 0.047 ↑ 1.0 13 1

Seq Scan on t376_opportunitystagenamepicklistdim t376_opportunitystagenamepicklistdim_2 (cost=0.00..5.31 rows=13 width=4) (actual time=0.015..0.047 rows=13 loops=1)

  • Filter: (correlated_value = ANY ('{Lost,"Closed - Clean Up",Booked/Won,"Closed - CleanUp","Closed Booked","Budget Won/Live","Closed - Booked","Closed- Booked","No Commit","Closed Lost","Closed - Booked (sales order no. require",Closed/Lost,"Closed - Lost"}'::text[]))
  • Rows Removed by Filter: 75
8.          

CTE bucketac

9. 3.060 3,198.312 ↑ 19,392.7 19 1

GroupAggregate (cost=15,294,122,045.61..15,294,136,784.09 rows=368,462 width=96) (actual time=3,194.302..3,198.312 rows=19 loops=1)

  • Group Key: aptsta1882_ft0.c2, (CASE WHEN (aptsta1882_ft1.c1 IS NULL) THEN 'deleted'::text WHEN ((aptsta1882_ft1.c2 < 20190302) OR (aptsta1882_ft1.c2 > 20190531)) THEN 'slipped'::text ELSE CASE WHEN (hashed SubPlan 5) THEN 'won'::text WHEN (hashed SubPlan 6) THEN 'lost'::text ELSE CASE WHEN (CASE WHEN (aptsta1882_ft0.c5 IS NULL) THEN cte0_5.c1 ELSE aptsta1882_ft0.c5 END = CASE WHEN (aptsta1882_ft1.c4 IS NULL) THEN cte0_5.c1 ELSE aptsta1882_ft1.c4 END) THEN 'unchanged'::text WHEN (CASE WHEN (aptsta1882_ft0.c5 IS NULL) THEN cte0_5.c1 ELSE aptsta1882_ft0.c5 END > CASE WHEN (aptsta1882_ft1.c4 IS NULL) THEN cte0_5.c1 ELSE aptsta1882_ft1.c4 END) THEN 'upgraded'::text ELSE 'downgraded'::text END END END)
10.          

CTE nullgroupbyvaluecte

11. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on t373_opportunityforecast_indicator__cpicklistdim t373_opportunityforecast_indicator__cpicklistdim_2 (cost=0.00..1.18 rows=1 width=4) (actual time=0.004..0.006 rows=1 loops=1)

  • Filter: (value = 'Value Not Assigned'::text)
  • Rows Removed by Filter: 13
12. 6.318 3,195.252 ↑ 29.4 12,541 1

Sort (cost=15,294,122,044.14..15,294,122,965.29 rows=368,462 width=56) (actual time=3,194.296..3,195.252 rows=12,541 loops=1)

  • Sort Key: aptsta1882_ft0.c2, (CASE WHEN (aptsta1882_ft1.c1 IS NULL) THEN 'deleted'::text WHEN ((aptsta1882_ft1.c2 < 20190302) OR (aptsta1882_ft1.c2 > 20190531)) THEN 'slipped'::text ELSE CASE WHEN (hashed SubPlan 5) THEN 'won'::text WHEN (hashed SubPlan 6) THEN 'lost'::text ELSE CASE WHEN (CASE WHEN (aptsta1882_ft0.c5 IS NULL) THEN cte0_5.c1 ELSE aptsta1882_ft0.c5 END = CASE WHEN (aptsta1882_ft1.c4 IS NULL) THEN cte0_5.c1 ELSE aptsta1882_ft1.c4 END) THEN 'unchanged'::text WHEN (CASE WHEN (aptsta1882_ft0.c5 IS NULL) THEN cte0_5.c1 ELSE aptsta1882_ft0.c5 END > CASE WHEN (aptsta1882_ft1.c4 IS NULL) THEN cte0_5.c1 ELSE aptsta1882_ft1.c4 END) THEN 'upgraded'::text ELSE 'downgraded'::text END END END)
  • Sort Method: quicksort Memory: 1364kB
13. 13.728 3,188.934 ↑ 29.4 12,541 1

Nested Loop Left Join (cost=116,980,618.22..15,294,075,381.20 rows=368,462 width=56) (actual time=1,171.097..3,188.934 rows=12,541 loops=1)

14. 10.743 3,175.135 ↑ 29.4 12,541 1

Nested Loop Left Join (cost=116,980,617.92..15,294,062,484.72 rows=368,462 width=44) (actual time=1,171.086..3,175.135 rows=12,541 loops=1)

15. 5.362 1,220.537 ↑ 29.4 12,541 1

Subquery Scan on aptsta1882_ft0 (cost=116,939,433.51..116,970,752.74 rows=368,462 width=20) (actual time=1,170.577..1,220.537 rows=12,541 loops=1)

  • Filter: (NOT (hashed SubPlan 7))
  • Rows Removed by Filter: 3817
16. 28.271 1,215.122 ↑ 45.0 16,358 1

GroupAggregate (cost=116,939,433.22..116,961,540.91 rows=736,923 width=28) (actual time=1,170.513..1,215.122 rows=16,358 loops=1)

  • Group Key: t338_adobe_product_to_sales_team_assignment__cfact.c3549_opportunity__c, t338_adobe_product_to_sales_team_assignment__cfact.c3612_opportunity__c_forecast_indicator__csid, t338_adobe_product_to_sales_team_assignment__cfact.c3603_opportunity__c_closedate, t338_adobe_product_to_sales_team_assignment__cfact.c3589_opportunity__c_stagenamesid, t373_opportunityforecast_indicator__cpicklistdim_3.correlated_app_order
17. 81.739 1,186.851 ↑ 7.5 98,104 1

Sort (cost=116,939,433.22..116,941,275.52 rows=736,923 width=36) (actual time=1,170.482..1,186.851 rows=98,104 loops=1)

  • Sort Key: t338_adobe_product_to_sales_team_assignment__cfact.c3549_opportunity__c, t338_adobe_product_to_sales_team_assignment__cfact.c3612_opportunity__c_forecast_indicator__csid, t338_adobe_product_to_sales_team_assignment__cfact.c3603_opportunity__c_closedate, t338_adobe_product_to_sales_team_assignment__cfact.c3589_opportunity__c_stagenamesid, t373_opportunityforecast_indicator__cpicklistdim_3.correlated_app_order
  • Sort Method: external merge Disk: 4784kB
18. 96.116 1,105.112 ↑ 7.5 98,104 1

Nested Loop Left Join (cost=0.56..116,847,462.82 rows=736,923 width=36) (actual time=169.649..1,105.112 rows=98,104 loops=1)

  • Join Filter: (t338_adobe_product_to_sales_team_assignment__cfact.c3612_opportunity__c_forecast_indicator__csid = t373_opportunityforecast_indicator__cpicklistdim_3.sid)
  • Rows Removed by Join Filter: 1275386
19. 298.570 910.892 ↑ 7.5 98,104 1

Nested Loop Left Join (cost=0.56..116,692,707.81 rows=736,923 width=32) (actual time=169.606..910.892 rows=98,104 loops=1)

  • Join Filter: (t338_adobe_product_to_sales_team_assignment__cfact.c3548_currencyisocodesid = t144_claricurrencytypedim.sid)
  • Rows Removed by Join Filter: 3727998
20. 248.600 416.114 ↑ 7.5 98,104 1

Index Scan using idx_t228_p2s_closedate_stage_timestamp_nondeleted on t338_adobe_product_to_sales_team_assignment__cfact (cost=0.56..116,261,567.75 rows=736,923 width=28) (actual time=169.313..416.114 rows=98,104 loops=1)

  • Index Cond: ((c3603_opportunity__c_closedate >= 20190302) AND (c3603_opportunity__c_closedate <= 20190531) AND (end_stamp > '1551513600000'::bigint) AND (start_stamp <= '1551513600000'::bigint))
  • Filter: ((alternatives: SubPlan 8 or hashed SubPlan 9) OR (alternatives: SubPlan 10 or hashed SubPlan 11))
  • Rows Removed by Filter: 23
21.          

SubPlan (for Index Scan)

22. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.85..107.19 rows=1 width=0) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t333_userrolehierarchy_sid_c3186_ancestor_role_id_e50459228 on t333_userrolehierarchy (cost=0.43..31.60 rows=8 width=4) (never executed)

  • Index Cond: ((sid = t338_adobe_product_to_sales_team_assignment__cfact.c3635_sales_team_member__c) AND (c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
24. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t125_userroledim_sid_end_stamp_start_stamp on t125_userroledim (cost=0.42..8.45 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t333_userrolehierarchy.c3185_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c1646_portaltype IS NULL) OR (c1646_portaltype = 'None'::text)))
25. 28.864 157.291 ↓ 4.3 43,595 1

Hash Join (cost=16,907.66..53,515.03 rows=10,116 width=4) (actual time=86.724..157.291 rows=43,595 loops=1)

  • Hash Cond: (t333_userrolehierarchy_1.c3185_user_role_id = t125_userroledim_1.sid)
26. 44.235 62.078 ↓ 1.0 194,755 1

Bitmap Heap Scan on t333_userrolehierarchy t333_userrolehierarchy_1 (cost=5,841.24..41,682.84 rows=193,640 width=8) (actual time=20.015..62.078 rows=194,755 loops=1)

  • Recheck Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=12474
27. 17.843 17.843 ↓ 1.0 195,008 1

Bitmap Index Scan on idx_t333_userrolehierarchy_c3186_ancestor_role_id_end_s50459228 (cost=0.00..5,792.83 rows=193,640 width=0) (actual time=17.843..17.843 rows=195,008 loops=1)

  • Index Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
28. 0.195 66.349 ↑ 2.1 1,055 1

Hash (cost=11,039.33..11,039.33 rows=2,167 width=4) (actual time=66.349..66.349 rows=1,055 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 70kB
29. 66.154 66.154 ↑ 2.1 1,055 1

Seq Scan on t125_userroledim t125_userroledim_1 (cost=0.00..11,039.33 rows=2,167 width=4) (actual time=2.268..66.154 rows=1,055 loops=1)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c1646_portaltype IS NULL) OR (c1646_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 214657
30. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t105_userdim_sid_end_stamp_start_stamp on t105_userdim (cost=0.42..8.45 rows=1 width=0) (never executed)

  • Index Cond: ((sid = t338_adobe_product_to_sales_team_assignment__cfact.c3635_sales_team_member__c) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c1256_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
31. 9.198 10.223 ↓ 1.3 5,557 1

Bitmap Heap Scan on t105_userdim t105_userdim_1 (cost=245.15..13,035.12 rows=4,358 width=4) (actual time=1.508..10.223 rows=5,557 loops=1)

  • Recheck Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
  • Rows Removed by Filter: 2764
  • Heap Blocks: exact=3400
32. 1.025 1.025 ↓ 1.0 8,333 1

Bitmap Index Scan on idx_t105_userdim_c1256_userroleid_end_stamp_start_stamp (cost=0.00..244.06 rows=7,964 width=0) (actual time=1.025..1.025 rows=8,333 loops=1)

  • Index Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
33. 195.936 196.208 ↑ 1.0 39 98,104

Materialize (cost=0.00..40.21 rows=39 width=12) (actual time=0.000..0.002 rows=39 loops=98,104)

34. 0.272 0.272 ↑ 1.0 39 1

Seq Scan on t144_claricurrencytypedim (cost=0.00..40.01 rows=39 width=12) (actual time=0.018..0.272 rows=39 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 1482
35. 98.095 98.104 ↑ 1.0 14 98,104

Materialize (cost=0.00..1.21 rows=14 width=8) (actual time=0.000..0.001 rows=14 loops=98,104)

36. 0.009 0.009 ↑ 1.0 14 1

Seq Scan on t373_opportunityforecast_indicator__cpicklistdim t373_opportunityforecast_indicator__cpicklistdim_3 (cost=0.00..1.14 rows=14 width=8) (actual time=0.005..0.009 rows=14 loops=1)

37.          

SubPlan (for Subquery Scan)

38. 0.053 0.053 ↑ 1.0 13 1

CTE Scan on picklist_4 cte0_4 (cost=0.00..0.26 rows=13 width=4) (actual time=0.016..0.053 rows=13 loops=1)

39. 0.000 1,943.855 ↑ 1.0 1 12,541

Subquery Scan on aptsta1882_ft1 (cost=41,184.42..41,190.38 rows=1 width=24) (actual time=0.155..0.155 rows=1 loops=12,541)

  • Filter: (aptsta1882_ft0.c1 = aptsta1882_ft1.c1)
40. 37.623 1,943.855 ↑ 149.0 1 12,541

GroupAggregate (cost=41,184.42..41,188.51 rows=149 width=24) (actual time=0.155..0.155 rows=1 loops=12,541)

  • Group Key: t338_adobe_product_to_sales_team_assignment__cfact_1.c3549_opportunity__c, t338_adobe_product_to_sales_team_assignment__cfact_1.c3603_opportunity__c_closedate, t338_adobe_product_to_sales_team_assignment__cfact_1.c3589_opportunity__c_stagenamesid, t373_opportunityforecast_indicator__cpicklistdim_4.correlated_app_order
41. 50.164 1,906.232 ↑ 18.6 8 12,541

Sort (cost=41,184.42..41,184.79 rows=149 width=32) (actual time=0.152..0.152 rows=8 loops=12,541)

  • Sort Key: t338_adobe_product_to_sales_team_assignment__cfact_1.c3603_opportunity__c_closedate, t338_adobe_product_to_sales_team_assignment__cfact_1.c3589_opportunity__c_stagenamesid, t373_opportunityforecast_indicator__cpicklistdim_4.correlated_app_order
  • Sort Method: quicksort Memory: 25kB
42. 110.245 1,856.068 ↑ 18.6 8 12,541

Nested Loop Left Join (cost=0.56..41,179.04 rows=149 width=32) (actual time=0.042..0.148 rows=8 loops=12,541)

  • Join Filter: (t338_adobe_product_to_sales_team_assignment__cfact_1.c3612_opportunity__c_forecast_indicator__csid = t373_opportunityforecast_indicator__cpicklistdim_4.sid)
  • Rows Removed by Join Filter: 107
43. 320.818 1,642.871 ↑ 18.6 8 12,541

Nested Loop Left Join (cost=0.56..41,146.57 rows=149 width=32) (actual time=0.040..0.131 rows=8 loops=12,541)

  • Join Filter: (t338_adobe_product_to_sales_team_assignment__cfact_1.c3548_currencyisocodesid = t144_claricurrencytypedim_1.sid)
  • Rows Removed by Join Filter: 312
44. 704.327 1,116.149 ↑ 18.6 8 12,541

Index Scan using t338_adobe_product_to_sales_team_assignment__cfact_14214_timest on t338_adobe_product_to_sales_team_assignment__cfact t338_adobe_product_to_sales_team_assignment__cfact_1 (cost=0.56..41,019.30 rows=149 width=28) (actual time=0.035..0.089 rows=8 loops=12,541)

  • Index Cond: ((aptsta1882_ft0.c1 = c3549_opportunity__c) AND (start_stamp <= '1559372399999'::bigint) AND (end_stamp > '1559372399999'::bigint))
  • Filter: ((NOT deleted) AND ((alternatives: SubPlan 12 or hashed SubPlan 13) OR (alternatives: SubPlan 14 or hashed SubPlan 15)))
  • Rows Removed by Filter: 37
45.          

SubPlan (for Index Scan)

46. 0.002 411.812 ↑ 1.0 1 102,953

Nested Loop Semi Join (cost=0.85..107.19 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=102,953)

47. 205.906 205.906 ↑ 8.0 1 102,953

Index Scan using idx_t333_userrolehierarchy_sid_c3186_ancestor_role_id_e50459228 on t333_userrolehierarchy t333_userrolehierarchy_2 (cost=0.43..31.60 rows=8 width=4) (actual time=0.002..0.002 rows=1 loops=102,953)

  • Index Cond: ((sid = t338_adobe_product_to_sales_team_assignment__cfact_1.c3635_sales_team_member__c) AND (c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
48. 205.904 205.904 ↑ 1.0 1 102,952

Index Scan using idx_t125_userroledim_sid_end_stamp_start_stamp on t125_userroledim t125_userroledim_2 (cost=0.42..8.45 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=102,952)

  • Index Cond: ((sid = t333_userrolehierarchy_2.c3185_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c1646_portaltype IS NULL) OR (c1646_portaltype = 'None'::text)))
49. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=16,907.66..53,515.03 rows=10,116 width=4) (never executed)

  • Hash Cond: (t333_userrolehierarchy_3.c3185_user_role_id = t125_userroledim_3.sid)
50. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t333_userrolehierarchy t333_userrolehierarchy_3 (cost=5,841.24..41,682.84 rows=193,640 width=8) (never executed)

  • Recheck Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
51. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_t333_userrolehierarchy_c3186_ancestor_role_id_end_s50459228 (cost=0.00..5,792.83 rows=193,640 width=0) (never executed)

  • Index Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
52. 0.000 0.000 ↓ 0.0 0

Hash (cost=11,039.33..11,039.33 rows=2,167 width=4) (never executed)

53. 0.000 0.000 ↓ 0.0 0

Seq Scan on t125_userroledim t125_userroledim_3 (cost=0.00..11,039.33 rows=2,167 width=4) (never executed)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c1646_portaltype IS NULL) OR (c1646_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
54. 0.010 0.010 ↓ 0.0 0 1

Index Scan using idx_t105_userdim_sid_end_stamp_start_stamp on t105_userdim t105_userdim_2 (cost=0.42..8.45 rows=1 width=0) (actual time=0.010..0.010 rows=0 loops=1)

  • Index Cond: ((sid = t338_adobe_product_to_sales_team_assignment__cfact_1.c3635_sales_team_member__c) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c1256_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
  • Rows Removed by Filter: 1
55. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t105_userdim t105_userdim_3 (cost=245.15..13,035.12 rows=4,358 width=4) (never executed)

  • Recheck Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
56. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_t105_userdim_c1256_userroleid_end_stamp_start_stamp (cost=0.00..244.06 rows=7,964 width=0) (never executed)

  • Index Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
57. 205.665 205.904 ↑ 1.0 39 102,952

Materialize (cost=0.00..40.21 rows=39 width=12) (actual time=0.000..0.002 rows=39 loops=102,952)

58. 0.239 0.239 ↑ 1.0 39 1

Seq Scan on t144_claricurrencytypedim t144_claricurrencytypedim_1 (cost=0.00..40.01 rows=39 width=12) (actual time=0.008..0.239 rows=39 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 1482
59. 102.946 102.952 ↑ 1.0 14 102,952

Materialize (cost=0.00..1.21 rows=14 width=8) (actual time=0.000..0.001 rows=14 loops=102,952)

60. 0.006 0.006 ↑ 1.0 14 1

Seq Scan on t373_opportunityforecast_indicator__cpicklistdim t373_opportunityforecast_indicator__cpicklistdim_4 (cost=0.00..1.14 rows=14 width=8) (actual time=0.006..0.006 rows=14 loops=1)

61. 0.000 0.000 ↑ 1.0 1 12,541

CTE Scan on nullgroupbyvaluecte cte0_5 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=12,541)

62.          

SubPlan (for Nested Loop Left Join)

63. 0.030 0.030 ↑ 1.0 6 1

CTE Scan on picklist_0 cte0_2 (cost=0.00..0.12 rows=6 width=4) (actual time=0.008..0.030 rows=6 loops=1)

64. 0.041 0.041 ↑ 1.0 7 1

CTE Scan on picklist_1 cte0_3 (cost=0.00..0.14 rows=7 width=4) (actual time=0.004..0.041 rows=7 loops=1)

65.          

CTE bucketec

66. 2.577 2,329.251 ↑ 13,281.7 26 1

GroupAggregate (cost=28,684,199,239.58..28,684,211,325.92 rows=345,324 width=120) (actual time=2,325.779..2,329.251 rows=26 loops=1)

  • Group Key: (CASE WHEN (aptsta1882_ft1_1.c1 IS NULL) THEN 'newBucket'::text WHEN (aptsta1882_ft1_1.c2 > 20190531) THEN 'pulledInBucket'::text WHEN (aptsta1882_ft1_1.c2 < 20190302) THEN 'pushedInBucket'::text ELSE 'otherBucket'::text END), (CASE WHEN (hashed SubPlan 17) THEN 'won'::text WHEN (hashed SubPlan 18) THEN 'lost'::text ELSE 'newOpen'::text END), t338_adobe_product_to_sales_team_assignment__cfact_2.c3612_opportunity__c_forecast_indicator__csid
67. 6.641 2,326.674 ↑ 27.3 12,657 1

Sort (cost=28,684,199,239.28..28,684,200,102.59 rows=345,324 width=80) (actual time=2,325.718..2,326.674 rows=12,657 loops=1)

  • Sort Key: (CASE WHEN (aptsta1882_ft1_1.c1 IS NULL) THEN 'newBucket'::text WHEN (aptsta1882_ft1_1.c2 > 20190531) THEN 'pulledInBucket'::text WHEN (aptsta1882_ft1_1.c2 < 20190302) THEN 'pushedInBucket'::text ELSE 'otherBucket'::text END), (CASE WHEN (hashed SubPlan 17) THEN 'won'::text WHEN (hashed SubPlan 18) THEN 'lost'::text ELSE 'newOpen'::text END), t338_adobe_product_to_sales_team_assignment__cfact_2.c3612_opportunity__c_forecast_indicator__csid
  • Sort Method: quicksort Memory: 1373kB
68. 17.315 2,320.033 ↑ 27.3 12,657 1

Nested Loop Left Join (cost=104,162,153.06..28,684,152,129.64 rows=345,324 width=80) (actual time=1,150.036..2,320.033 rows=12,657 loops=1)

  • Filter: ((aptsta1882_ft1_1.c1 IS NULL) OR (aptsta1882_ft1_1.c2 < 20190302) OR (aptsta1882_ft1_1.c2 > 20190531))
  • Rows Removed by Filter: 11130
69. 36.556 1,208.514 ↑ 26.0 23,787 1

GroupAggregate (cost=104,115,992.84..104,133,676.15 rows=619,107 width=24) (actual time=1,149.557..1,208.514 rows=23,787 loops=1)

  • Group Key: t338_adobe_product_to_sales_team_assignment__cfact_2.c3549_opportunity__c, t338_adobe_product_to_sales_team_assignment__cfact_2.c3589_opportunity__c_stagenamesid, t338_adobe_product_to_sales_team_assignment__cfact_2.c3612_opportunity__c_forecast_indicator__csid, t338_adobe_product_to_sales_team_assignment__cfact_2.c3603_opportunity__c_closedate
70. 96.052 1,171.958 ↑ 5.1 129,521 1

Sort (cost=104,115,992.84..104,117,634.59 rows=656,699 width=32) (actual time=1,149.540..1,171.958 rows=129,521 loops=1)

  • Sort Key: t338_adobe_product_to_sales_team_assignment__cfact_2.c3549_opportunity__c, t338_adobe_product_to_sales_team_assignment__cfact_2.c3589_opportunity__c_stagenamesid, t338_adobe_product_to_sales_team_assignment__cfact_2.c3612_opportunity__c_forecast_indicator__csid, t338_adobe_product_to_sales_team_assignment__cfact_2.c3603_opportunity__c_closedate
  • Sort Method: external merge Disk: 5312kB
71. 386.837 1,075.906 ↑ 5.1 129,521 1

Nested Loop Left Join (cost=0.56..104,036,824.72 rows=656,699 width=32) (actual time=166.805..1,075.906 rows=129,521 loops=1)

  • Join Filter: (t338_adobe_product_to_sales_team_assignment__cfact_2.c3548_currencyisocodesid = t144_claricurrencytypedim_2.sid)
  • Rows Removed by Join Filter: 4921839
72. 264.953 430.027 ↑ 5.1 129,521 1

Index Scan using idx_t228_p2s_closedate_stage_timestamp_nondeleted on t338_adobe_product_to_sales_team_assignment__cfact t338_adobe_product_to_sales_team_assignment__cfact_2 (cost=0.56..103,652,615.70 rows=656,699 width=28) (actual time=166.474..430.027 rows=129,521 loops=1)

  • Index Cond: ((c3603_opportunity__c_closedate >= 20190302) AND (c3603_opportunity__c_closedate <= 20190531) AND (end_stamp > '1559372399999'::bigint) AND (start_stamp <= '1559372399999'::bigint))
  • Filter: ((alternatives: SubPlan 19 or hashed SubPlan 20) OR (alternatives: SubPlan 21 or hashed SubPlan 22))
  • Rows Removed by Filter: 19
73.          

SubPlan (for Index Scan)

74. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.85..107.19 rows=1 width=0) (never executed)

75. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t333_userrolehierarchy_sid_c3186_ancestor_role_id_e50459228 on t333_userrolehierarchy t333_userrolehierarchy_4 (cost=0.43..31.60 rows=8 width=4) (never executed)

  • Index Cond: ((sid = t338_adobe_product_to_sales_team_assignment__cfact_2.c3635_sales_team_member__c) AND (c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
76. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t125_userroledim_sid_end_stamp_start_stamp on t125_userroledim t125_userroledim_4 (cost=0.42..8.45 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t333_userrolehierarchy_4.c3185_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c1646_portaltype IS NULL) OR (c1646_portaltype = 'None'::text)))
77. 28.892 154.965 ↓ 4.3 43,595 1

Hash Join (cost=16,907.66..53,515.03 rows=10,116 width=4) (actual time=85.423..154.965 rows=43,595 loops=1)

  • Hash Cond: (t333_userrolehierarchy_5.c3185_user_role_id = t125_userroledim_5.sid)
78. 43.117 60.256 ↓ 1.0 194,755 1

Bitmap Heap Scan on t333_userrolehierarchy t333_userrolehierarchy_5 (cost=5,841.24..41,682.84 rows=193,640 width=8) (actual time=19.285..60.256 rows=194,755 loops=1)

  • Recheck Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=12474
79. 17.139 17.139 ↓ 1.0 195,008 1

Bitmap Index Scan on idx_t333_userrolehierarchy_c3186_ancestor_role_id_end_s50459228 (cost=0.00..5,792.83 rows=193,640 width=0) (actual time=17.139..17.139 rows=195,008 loops=1)

  • Index Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
80. 0.175 65.817 ↑ 2.1 1,055 1

Hash (cost=11,039.33..11,039.33 rows=2,167 width=4) (actual time=65.817..65.817 rows=1,055 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 70kB
81. 65.642 65.642 ↑ 2.1 1,055 1

Seq Scan on t125_userroledim t125_userroledim_5 (cost=0.00..11,039.33 rows=2,167 width=4) (actual time=2.228..65.642 rows=1,055 loops=1)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c1646_portaltype IS NULL) OR (c1646_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 214657
82. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t105_userdim_sid_end_stamp_start_stamp on t105_userdim t105_userdim_4 (cost=0.42..8.45 rows=1 width=0) (never executed)

  • Index Cond: ((sid = t338_adobe_product_to_sales_team_assignment__cfact_2.c3635_sales_team_member__c) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c1256_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
83. 9.096 10.109 ↓ 1.3 5,557 1

Bitmap Heap Scan on t105_userdim t105_userdim_5 (cost=245.15..13,035.12 rows=4,358 width=4) (actual time=1.509..10.109 rows=5,557 loops=1)

  • Recheck Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
  • Rows Removed by Filter: 2764
  • Heap Blocks: exact=3400
84. 1.013 1.013 ↓ 1.0 8,333 1

Bitmap Index Scan on idx_t105_userdim_c1256_userroleid_end_stamp_start_stamp (cost=0.00..244.06 rows=7,964 width=0) (actual time=1.012..1.013 rows=8,333 loops=1)

  • Index Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
85. 258.727 259.042 ↑ 1.0 39 129,521

Materialize (cost=0.00..40.21 rows=39 width=12) (actual time=0.000..0.002 rows=39 loops=129,521)

86. 0.315 0.315 ↑ 1.0 39 1

Seq Scan on t144_claricurrencytypedim t144_claricurrencytypedim_2 (cost=0.00..40.01 rows=39 width=12) (actual time=0.017..0.315 rows=39 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 1482
87. 0.000 1,094.202 ↑ 1.0 1 23,787

Subquery Scan on aptsta1882_ft1_1 (cost=46,159.92..46,163.26 rows=1 width=8) (actual time=0.046..0.046 rows=1 loops=23,787)

  • Filter: (t338_adobe_product_to_sales_team_assignment__cfact_2.c3549_opportunity__c = aptsta1882_ft1_1.c1)
88. 23.787 1,094.202 ↑ 167.0 1 23,787

Group (cost=46,159.92..46,161.18 rows=167 width=8) (actual time=0.045..0.046 rows=1 loops=23,787)

  • Group Key: t338_adobe_product_to_sales_team_assignment__cfact_3.c3549_opportunity__c, t338_adobe_product_to_sales_team_assignment__cfact_3.c3603_opportunity__c_closedate
89. 23.787 1,070.415 ↑ 55.7 3 23,787

Sort (cost=46,159.92..46,160.34 rows=167 width=8) (actual time=0.045..0.045 rows=3 loops=23,787)

  • Sort Key: t338_adobe_product_to_sales_team_assignment__cfact_3.c3603_opportunity__c_closedate
  • Sort Method: quicksort Memory: 25kB
90. 215.349 1,046.628 ↑ 55.7 3 23,787

Nested Loop Left Join (cost=0.56..46,153.76 rows=167 width=8) (actual time=0.015..0.044 rows=3 loops=23,787)

  • Join Filter: (t338_adobe_product_to_sales_team_assignment__cfact_3.c3548_currencyisocodesid = t144_claricurrencytypedim_3.sid)
  • Rows Removed by Join Filter: 113
91. 406.799 689.823 ↑ 55.7 3 23,787

Index Scan using t338_adobe_product_to_sales_team_assignment__cfact_14214_timest on t338_adobe_product_to_sales_team_assignment__cfact t338_adobe_product_to_sales_team_assignment__cfact_3 (cost=0.56..46,015.95 rows=167 width=12) (actual time=0.013..0.029 rows=3 loops=23,787)

  • Index Cond: ((t338_adobe_product_to_sales_team_assignment__cfact_2.c3549_opportunity__c = c3549_opportunity__c) AND (start_stamp <= '1551513600000'::bigint) AND (end_stamp > '1551513600000'::bigint))
  • Filter: ((NOT deleted) AND ((alternatives: SubPlan 23 or hashed SubPlan 24) OR (alternatives: SubPlan 25 or hashed SubPlan 26)))
  • Rows Removed by Filter: 10
92.          

SubPlan (for Index Scan)

93. 0.028 282.968 ↑ 1.0 1 70,742

Nested Loop Semi Join (cost=0.85..107.19 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=70,742)

94. 141.484 141.484 ↑ 8.0 1 70,742

Index Scan using idx_t333_userrolehierarchy_sid_c3186_ancestor_role_id_e50459228 on t333_userrolehierarchy t333_userrolehierarchy_6 (cost=0.43..31.60 rows=8 width=4) (actual time=0.002..0.002 rows=1 loops=70,742)

  • Index Cond: ((sid = t338_adobe_product_to_sales_team_assignment__cfact_3.c3635_sales_team_member__c) AND (c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
95. 141.456 141.456 ↑ 1.0 1 70,728

Index Scan using idx_t125_userroledim_sid_end_stamp_start_stamp on t125_userroledim t125_userroledim_6 (cost=0.42..8.45 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=70,728)

  • Index Cond: ((sid = t333_userrolehierarchy_6.c3185_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c1646_portaltype IS NULL) OR (c1646_portaltype = 'None'::text)))
96. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=16,907.66..53,515.03 rows=10,116 width=4) (never executed)

  • Hash Cond: (t333_userrolehierarchy_7.c3185_user_role_id = t125_userroledim_7.sid)
97. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t333_userrolehierarchy t333_userrolehierarchy_7 (cost=5,841.24..41,682.84 rows=193,640 width=8) (never executed)

  • Recheck Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
98. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_t333_userrolehierarchy_c3186_ancestor_role_id_end_s50459228 (cost=0.00..5,792.83 rows=193,640 width=0) (never executed)

  • Index Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
99. 0.000 0.000 ↓ 0.0 0

Hash (cost=11,039.33..11,039.33 rows=2,167 width=4) (never executed)

100. 0.000 0.000 ↓ 0.0 0

Seq Scan on t125_userroledim t125_userroledim_7 (cost=0.00..11,039.33 rows=2,167 width=4) (never executed)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c1646_portaltype IS NULL) OR (c1646_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
101. 0.056 0.056 ↓ 0.0 0 14

Index Scan using idx_t105_userdim_sid_end_stamp_start_stamp on t105_userdim t105_userdim_6 (cost=0.42..8.45 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=14)

  • Index Cond: ((sid = t338_adobe_product_to_sales_team_assignment__cfact_3.c3635_sales_team_member__c) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c1256_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
  • Rows Removed by Filter: 1
102. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t105_userdim t105_userdim_7 (cost=245.15..13,035.12 rows=4,358 width=4) (never executed)

  • Recheck Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
103. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_t105_userdim_c1256_userroleid_end_stamp_start_stamp (cost=0.00..244.06 rows=7,964 width=0) (never executed)

  • Index Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
104. 141.216 141.456 ↑ 1.0 39 70,728

Materialize (cost=0.00..40.21 rows=39 width=4) (actual time=0.000..0.002 rows=39 loops=70,728)

105. 0.240 0.240 ↑ 1.0 39 1

Seq Scan on t144_claricurrencytypedim t144_claricurrencytypedim_3 (cost=0.00..40.01 rows=39 width=4) (actual time=0.013..0.240 rows=39 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 1482
106.          

SubPlan (for Nested Loop Left Join)

107. 0.001 0.001 ↑ 1.0 6 1

CTE Scan on picklist_0 cte0_6 (cost=0.00..0.12 rows=6 width=4) (actual time=0.001..0.001 rows=6 loops=1)

108. 0.001 0.001 ↑ 1.0 7 1

CTE Scan on picklist_1 cte0_7 (cost=0.00..0.14 rows=7 width=4) (actual time=0.001..0.001 rows=7 loops=1)

109. 0.025 3,198.364 ↑ 19,392.7 19 1

Hash Left Join (cost=1.32..9,010.21 rows=368,462 width=120) (actual time=3,194.336..3,198.364 rows=19 loops=1)

  • Hash Cond: (cte0.c5 = t373_opportunityforecast_indicator__cpicklistdim.sid)
110. 3,198.325 3,198.325 ↑ 19,392.7 19 1

CTE Scan on bucketac cte0 (cost=0.00..7,369.24 rows=368,462 width=92) (actual time=3,194.304..3,198.325 rows=19 loops=1)

111. 0.007 0.014 ↑ 1.0 14 1

Hash (cost=1.14..1.14 rows=14 width=36) (actual time=0.014..0.014 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
112. 0.007 0.007 ↑ 1.0 14 1

Seq Scan on t373_opportunityforecast_indicator__cpicklistdim (cost=0.00..1.14 rows=14 width=36) (actual time=0.006..0.007 rows=14 loops=1)

113. 0.017 2,329.330 ↑ 13,281.7 26 1

Subquery Scan on *SELECT* 2 (cost=1.32..12,761.04 rows=345,324 width=120) (actual time=2,325.826..2,329.330 rows=26 loops=1)

114. 0.021 2,329.313 ↑ 13,281.7 26 1

Hash Left Join (cost=1.32..8,444.49 rows=345,324 width=144) (actual time=2,325.819..2,329.313 rows=26 loops=1)

  • Hash Cond: (cte0_1.c7 = t373_opportunityforecast_indicator__cpicklistdim_1.sid)
115. 2,329.275 2,329.275 ↑ 13,281.7 26 1

CTE Scan on bucketec cte0_1 (cost=0.00..6,906.48 rows=345,324 width=116) (actual time=2,325.785..2,329.275 rows=26 loops=1)

116. 0.006 0.017 ↑ 1.0 14 1

Hash (cost=1.14..1.14 rows=14 width=36) (actual time=0.017..0.017 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
117. 0.011 0.011 ↑ 1.0 14 1

Seq Scan on t373_opportunityforecast_indicator__cpicklistdim t373_opportunityforecast_indicator__cpicklistdim_1 (cost=0.00..1.14 rows=14 width=36) (actual time=0.008..0.011 rows=14 loops=1)

Planning time : 12.344 ms
Execution time : 5,532.799 ms