explain.depesz.com

PostgreSQL's explain analyze made readable

Result: g5Gk

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 5,560.317 ↑ 15,860.2 45 1

Append (cost=43,973,617,196.08..43,973,642,647.89 rows=713,709 width=120) (actual time=3,201.167..5,560.317 rows=45 loops=1)

2.          

CTE picklist_0

3. 0.022 0.022 ↑ 1.0 6 1

Seq Scan on t376_opportunitystagenamepicklistdim (cost=0.00..4.54 rows=6 width=4) (actual time=0.006..0.022 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.022 0.022 ↑ 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.022 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.049 0.049 ↑ 1.0 13 1

Seq Scan on t376_opportunitystagenamepicklistdim t376_opportunitystagenamepicklistdim_2 (cost=0.00..5.31 rows=13 width=4) (actual time=0.018..0.049 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. 2.969 3,204.930 ↑ 19,390.6 19 1

GroupAggregate (cost=15,292,457,040.55..15,292,471,777.43 rows=368,422 width=96) (actual time=3,201.135..3,204.930 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.008 0.008 ↑ 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.005..0.008 rows=1 loops=1)

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

Sort (cost=15,292,457,039.08..15,292,457,960.13 rows=368,422 width=56) (actual time=3,201.129..3,201.961 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. 14.007 3,195.695 ↑ 29.4 12,541 1

Nested Loop Left Join (cost=116,967,846.12..15,292,410,383.62 rows=368,422 width=56) (actual time=1,177.173..3,195.695 rows=12,541 loops=1)

14. 10.568 3,181.636 ↑ 29.4 12,541 1

Nested Loop Left Join (cost=116,967,845.82..15,292,397,488.55 rows=368,422 width=44) (actual time=1,177.162..3,181.636 rows=12,541 loops=1)

15. 5.173 1,227.213 ↑ 29.4 12,541 1

Subquery Scan on aptsta1882_ft0 (cost=116,926,661.42..116,957,977.25 rows=368,422 width=20) (actual time=1,176.641..1,227.213 rows=12,541 loops=1)

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

GroupAggregate (cost=116,926,661.13..116,948,766.42 rows=736,843 width=28) (actual time=1,176.566..1,221.986 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.110 1,193.233 ↑ 7.5 98,104 1

Sort (cost=116,926,661.13..116,928,503.24 rows=736,843 width=36) (actual time=1,176.550..1,193.233 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. 113.755 1,112.123 ↑ 7.5 98,104 1

Nested Loop Left Join (cost=0.56..116,834,702.60 rows=736,843 width=36) (actual time=167.446..1,112.123 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. 289.599 900.264 ↑ 7.5 98,104 1

Nested Loop Left Join (cost=0.56..116,679,964.40 rows=736,843 width=32) (actual time=167.429..900.264 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. 249.395 414.457 ↑ 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,248,871.13 rows=736,843 width=28) (actual time=167.155..414.457 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.425 154.992 ↓ 4.3 43,595 1

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

  • Hash Cond: (t333_userrolehierarchy_1.c3185_user_role_id = t125_userroledim_1.sid)
26. 43.297 60.762 ↓ 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=19.599..60.762 rows=194,755 loops=1)

  • Recheck Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=12474
27. 17.465 17.465 ↓ 1.0 194,997 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.465..17.465 rows=194,997 loops=1)

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

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

  • Buckets: 4096 Batches: 1 Memory Usage: 70kB
29. 65.631 65.631 ↑ 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.221..65.631 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: 214655
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.092 10.070 ↓ 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.459..10.070 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. 0.978 0.978 ↓ 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=0.978..0.978 rows=8,333 loops=1)

  • Index Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
33. 195.947 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.261 0.261 ↑ 1.0 39 1

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

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 1482
35. 98.097 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.007 0.007 ↑ 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.007 rows=14 loops=1)

37.          

SubPlan (for Subquery Scan)

38. 0.054 0.054 ↑ 1.0 13 1

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

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

Subquery Scan on aptsta1882_ft1 (cost=41,184.40..41,190.36 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.40..41,188.50 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.40..41,184.78 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.02 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.56 rows=149 width=32) (actual time=0.041..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.28 rows=149 width=28) (actual time=0.036..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.663 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.241 0.241 ↑ 1.0 39 1

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

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 1482
59. 102.944 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.008 0.008 ↑ 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.008 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.028 0.028 ↑ 1.0 6 1

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

64. 0.024 0.024 ↑ 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.024 rows=7 loops=1)

65.          

CTE bucketec

66. 2.618 2,355.264 ↑ 13,280.3 26 1

GroupAggregate (cost=28,681,133,317.80..28,681,145,402.84 rows=345,287 width=120) (actual time=2,351.808..2,355.264 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.654 2,352.646 ↑ 27.3 12,657 1

Sort (cost=28,681,133,317.50..28,681,134,180.72 rows=345,287 width=80) (actual time=2,351.746..2,352.646 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. 18.106 2,345.992 ↑ 27.3 12,657 1

Nested Loop Left Join (cost=104,150,752.24..28,681,086,211.53 rows=345,287 width=80) (actual time=1,150.567..2,345.992 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. 37.031 1,209.894 ↑ 26.0 23,787 1

GroupAggregate (cost=104,104,592.04..104,122,273.46 rows=619,041 width=24) (actual time=1,150.109..1,209.894 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.616 1,172.863 ↑ 5.1 129,521 1

Sort (cost=104,104,592.04..104,106,233.61 rows=656,629 width=32) (actual time=1,150.092..1,172.863 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. 387.305 1,076.247 ↑ 5.1 129,521 1

Nested Loop Left Join (cost=0.56..104,025,434.68 rows=656,629 width=32) (actual time=166.506..1,076.247 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.874 429.900 ↑ 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,641,266.61 rows=656,629 width=28) (actual time=166.241..429.900 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. 29.175 154.915 ↓ 4.3 43,595 1

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

  • Hash Cond: (t333_userrolehierarchy_5.c3185_user_role_id = t125_userroledim_5.sid)
78. 43.395 60.629 ↓ 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.273..60.629 rows=194,755 loops=1)

  • Recheck Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=12474
79. 17.234 17.234 ↓ 1.0 194,997 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.234..17.234 rows=194,997 loops=1)

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

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

  • Buckets: 4096 Batches: 1 Memory Usage: 70kB
81. 64.943 64.943 ↑ 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.197..64.943 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: 214655
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.079 10.111 ↓ 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.526..10.111 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.032 1.032 ↓ 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.032..1.032 rows=8,333 loops=1)

  • Index Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
85. 258.795 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.247 0.247 ↑ 1.0 39 1

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

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

Subquery Scan on aptsta1882_ft1_1 (cost=46,159.91..46,163.25 rows=1 width=8) (actual time=0.047..0.047 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,117.989 ↑ 167.0 1 23,787

Group (cost=46,159.91..46,161.16 rows=167 width=8) (actual time=0.046..0.047 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,094.202 ↑ 55.7 3 23,787

Sort (cost=46,159.91..46,160.33 rows=167 width=8) (actual time=0.046..0.046 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,070.415 ↑ 55.7 3 23,787

Nested Loop Left Join (cost=0.56..46,153.75 rows=167 width=8) (actual time=0.016..0.045 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. 359.844 713.610 ↑ 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.94 rows=167 width=12) (actual time=0.013..0.030 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. 70.770 353.710 ↑ 1.0 1 70,742

Nested Loop Semi Join (cost=0.85..107.19 rows=1 width=0) (actual time=0.005..0.005 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.193 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.263 0.263 ↑ 1.0 39 1

Seq Scan on t144_claricurrencytypedim t144_claricurrencytypedim_3 (cost=0.00..40.01 rows=39 width=4) (actual time=0.015..0.263 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.002 0.002 ↑ 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.002 rows=7 loops=1)

109. 0.020 3,204.977 ↑ 19,390.6 19 1

Hash Left Join (cost=1.32..9,009.24 rows=368,422 width=120) (actual time=3,201.167..3,204.977 rows=19 loops=1)

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

CTE Scan on bucketac cte0 (cost=0.00..7,368.44 rows=368,422 width=92) (actual time=3,201.137..3,204.941 rows=19 loops=1)

111. 0.007 0.016 ↑ 1.0 14 1

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

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

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

113. 0.016 2,355.337 ↑ 13,280.3 26 1

Subquery Scan on *SELECT* 2 (cost=1.32..12,759.67 rows=345,287 width=120) (actual time=2,351.852..2,355.337 rows=26 loops=1)

114. 0.024 2,355.321 ↑ 13,280.3 26 1

Hash Left Join (cost=1.32..8,443.58 rows=345,287 width=144) (actual time=2,351.846..2,355.321 rows=26 loops=1)

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

CTE Scan on bucketec cte0_1 (cost=0.00..6,905.74 rows=345,287 width=116) (actual time=2,351.813..2,355.281 rows=26 loops=1)

116. 0.006 0.016 ↑ 1.0 14 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
117. 0.010 0.010 ↑ 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.010 rows=14 loops=1)

Planning time : 12.237 ms
Execution time : 5,565.613 ms