explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5y5t

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 6,285.368 ↑ 14,921.1 45 1

Append (cost=39,543,403,145.79..39,543,427,079.54 rows=671,448 width=120) (actual time=3,574.442..6,285.368 rows=45 loops=1)

2.          

CTE picklist_0

3. 0.026 0.026 ↑ 1.0 6 1

Seq Scan on t376_opportunitystagenamepicklistdim (cost=0.00..4.54 rows=6 width=4) (actual time=0.009..0.026 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.040 0.040 ↑ 1.0 7 1

Seq Scan on t376_opportunitystagenamepicklistdim t376_opportunitystagenamepicklistdim_1 (cost=0.00..4.65 rows=7 width=4) (actual time=0.006..0.040 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.050 0.050 ↑ 1.0 13 1

Seq Scan on t376_opportunitystagenamepicklistdim t376_opportunitystagenamepicklistdim_2 (cost=0.00..5.31 rows=13 width=4) (actual time=0.019..0.050 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.027 3,578.394 ↑ 18,475.2 19 1

GroupAggregate (cost=13,764,080,079.26..13,764,094,120.38 rows=351,028 width=96) (actual time=3,574.390..3,578.394 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.010 0.010 ↑ 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.009..0.010 rows=1 loops=1)

  • Filter: (value = 'Value Not Assigned'::text)
  • Rows Removed by Filter: 13
12. 6.203 3,575.367 ↑ 28.0 12,541 1

Sort (cost=13,764,080,077.79..13,764,080,955.36 rows=351,028 width=56) (actual time=3,574.384..3,575.367 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.407 3,569.164 ↑ 28.0 12,541 1

Nested Loop Left Join (cost=192,550,687.90..13,764,035,744.46 rows=351,028 width=56) (actual time=1,525.231..3,569.164 rows=12,541 loops=1)

14. 0.465 3,555.688 ↑ 28.0 12,541 1

Nested Loop Left Join (cost=192,550,687.60..13,764,023,458.18 rows=351,028 width=44) (actual time=1,525.213..3,555.688 rows=12,541 loops=1)

15. 4.300 1,573.745 ↑ 28.0 12,541 1

Subquery Scan on aptsta1882_ft0 (cost=192,512,031.07..192,541,868.45 rows=351,028 width=20) (actual time=1,524.651..1,573.745 rows=12,541 loops=1)

  • Filter: (NOT (hashed SubPlan 7))
  • Rows Removed by Filter: 3817
16. 27.946 1,569.392 ↑ 42.9 16,358 1

GroupAggregate (cost=192,512,030.78..192,533,092.46 rows=702,056 width=28) (actual time=1,524.573..1,569.392 rows=16,358 loops=1)

  • Group Key: t197_adobe_product_to_sales_team_assignment__cfact.c2665_opportunity__c, t197_adobe_product_to_sales_team_assignment__cfact.c2728_opportunity__c_forecast_indicator__csid, t197_adobe_product_to_sales_team_assignment__cfact.c2719_opportunity__c_closedate, t197_adobe_product_to_sales_team_assignment__cfact.c2705_opportunity__c_stagenamesid, t373_opportunityforecast_indicator__cpicklistdim_3.correlated_app_order
17. 83.296 1,541.446 ↑ 7.2 98,104 1

Sort (cost=192,512,030.78..192,513,785.92 rows=702,056 width=36) (actual time=1,524.554..1,541.446 rows=98,104 loops=1)

  • Sort Key: t197_adobe_product_to_sales_team_assignment__cfact.c2665_opportunity__c, t197_adobe_product_to_sales_team_assignment__cfact.c2728_opportunity__c_forecast_indicator__csid, t197_adobe_product_to_sales_team_assignment__cfact.c2719_opportunity__c_closedate, t197_adobe_product_to_sales_team_assignment__cfact.c2705_opportunity__c_stagenamesid, t373_opportunityforecast_indicator__cpicklistdim_3.correlated_app_order
  • Sort Method: external merge Disk: 4784kB
18. 93.642 1,458.150 ↑ 7.2 98,104 1

Nested Loop Left Join (cost=0.56..192,424,659.33 rows=702,056 width=36) (actual time=173.526..1,458.150 rows=98,104 loops=1)

  • Join Filter: (t197_adobe_product_to_sales_team_assignment__cfact.c2728_opportunity__c_forecast_indicator__csid = t373_opportunityforecast_indicator__cpicklistdim_3.sid)
  • Rows Removed by Join Filter: 1275386
19. 292.148 1,266.404 ↑ 7.2 98,104 1

Nested Loop Left Join (cost=0.56..192,277,226.40 rows=702,056 width=32) (actual time=173.508..1,266.404 rows=98,104 loops=1)

  • Join Filter: (t197_adobe_product_to_sales_team_assignment__cfact.c2664_currencyisocodesid = t144_claricurrencytypedim.sid)
  • Rows Removed by Join Filter: 3727998
20. 605.603 778.048 ↑ 7.2 98,104 1

Index Scan using ix_t197_closedate_stagename_timestamp on t197_adobe_product_to_sales_team_assignment__cfact (cost=0.56..191,866,483.53 rows=702,056 width=28) (actual time=173.233..778.048 rows=98,104 loops=1)

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

SubPlan (forIndex 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 = t197_adobe_product_to_sales_team_assignment__cfact.c2751_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. 29.365 162.002 ↓ 4.3 43,566 1

Hash Join (cost=16,900.58..53,507.96 rows=10,116 width=4) (actual time=87.945..162.002 rows=43,566 loops=1)

  • Hash Cond: (t333_userrolehierarchy_1.c3185_user_role_id = t125_userroledim_1.sid)
26. 47.169 64.284 ↓ 1.0 194,690 1

Bitmap Heap Scan on t333_userrolehierarchy t333_userrolehierarchy_1 (cost=5,837.24..41,678.84 rows=193,640 width=8) (actual time=19.151..64.284 rows=194,690 loops=1)

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

Bitmap Index Scan on idx_t333_userrolehierarchy_c3186_ancestor_role_id_end_s50459228 (cost=0.00..5,788.83 rows=193,640 width=0) (actual time=17.115..17.115 rows=194,852 loops=1)

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

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

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

Seq Scan on t125_userroledim t125_userroledim_1 (cost=0.00..11,036.25 rows=2,167 width=4) (actual time=2.310..68.165 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: 214618
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 = t197_adobe_product_to_sales_team_assignment__cfact.c2751_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.482 10.443 ↓ 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.418..10.443 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: 2762
  • Heap Blocks: exact=3398
32. 0.961 0.961 ↓ 1.0 8,331 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.961..0.961 rows=8,331 loops=1)

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

Seq Scan on t144_claricurrencytypedim (cost=0.00..40.01 rows=39 width=12) (actual time=0.016..0.254 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.007..0.009 rows=14 loops=1)

37.          

SubPlan (forSubquery 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.021..0.053 rows=13 loops=1)

39. 12.541 1,981.478 ↑ 1.0 1 12,541

Subquery Scan on aptsta1882_ft1 (cost=38,656.54..38,662.10 rows=1 width=24) (actual time=0.157..0.158 rows=1 loops=12,541)

  • Filter: (aptsta1882_ft0.c1 = aptsta1882_ft1.c1)
40. 25.082 1,968.937 ↑ 139.0 1 12,541

GroupAggregate (cost=38,656.54..38,660.36 rows=139 width=24) (actual time=0.157..0.157 rows=1 loops=12,541)

  • Group Key: t197_adobe_product_to_sales_team_assignment__cfact_1.c2665_opportunity__c, t197_adobe_product_to_sales_team_assignment__cfact_1.c2719_opportunity__c_closedate, t197_adobe_product_to_sales_team_assignment__cfact_1.c2705_opportunity__c_stagenamesid, t373_opportunityforecast_indicator__cpicklistdim_4.correlated_app_order
41. 50.164 1,943.855 ↑ 17.4 8 12,541

Sort (cost=38,656.54..38,656.88 rows=139 width=32) (actual time=0.154..0.155 rows=8 loops=12,541)

  • Sort Key: t197_adobe_product_to_sales_team_assignment__cfact_1.c2719_opportunity__c_closedate, t197_adobe_product_to_sales_team_assignment__cfact_1.c2705_opportunity__c_stagenamesid, t373_opportunityforecast_indicator__cpicklistdim_4.correlated_app_order
  • Sort Method: quicksort Memory: 25kB
42. 135.327 1,893.691 ↑ 17.4 8 12,541

Nested Loop Left Join (cost=0.56..38,651.59 rows=139 width=32) (actual time=0.042..0.151 rows=8 loops=12,541)

  • Join Filter: (t197_adobe_product_to_sales_team_assignment__cfact_1.c2728_opportunity__c_forecast_indicator__csid = t373_opportunityforecast_indicator__cpicklistdim_4.sid)
  • Rows Removed by Join Filter: 107
43. 320.818 1,655.412 ↑ 17.4 8 12,541

Nested Loop Left Join (cost=0.56..38,621.22 rows=139 width=32) (actual time=0.040..0.132 rows=8 loops=12,541)

  • Join Filter: (t197_adobe_product_to_sales_team_assignment__cfact_1.c2664_currencyisocodesid = t144_claricurrencytypedim_1.sid)
  • Rows Removed by Join Filter: 312
44. 716.865 1,128.690 ↑ 17.4 8 12,541

Index Scan using t197_adobe_product_to_sales_team_assignment__cfact_14214_timest on t197_adobe_product_to_sales_team_assignment__cfact t197_adobe_product_to_sales_team_assignment__cfact_1 (cost=0.56..38,499.80 rows=139 width=28) (actual time=0.035..0.090 rows=8 loops=12,541)

  • Index Cond: ((aptsta1882_ft0.c1 = c2665_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 (forIndex 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 = t197_adobe_product_to_sales_team_assignment__cfact_1.c2751_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,900.58..53,507.96 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,837.24..41,678.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,788.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,036.25..11,036.25 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,036.25 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.013 0.013 ↓ 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.013..0.013 rows=0 loops=1)

  • Index Cond: ((sid = t197_adobe_product_to_sales_team_assignment__cfact_1.c2751_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.670 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.234 0.234 ↑ 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.234 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.004..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 (forNested Loop Left Join)

63. 0.027 0.027 ↑ 1.0 6 1

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

64. 0.042 0.042 ↑ 1.0 7 1

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

65.          

CTE bucketec

66. 2.538 2,706.825 ↑ 12,323.8 26 1

GroupAggregate (cost=25,779,297,794.90..25,779,309,009.60 rows=320,420 width=120) (actual time=2,703.411..2,706.825 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), t197_adobe_product_to_sales_team_assignment__cfact_2.c2728_opportunity__c_forecast_indicator__csid
67. 6.874 2,704.287 ↑ 25.3 12,657 1

Sort (cost=25,779,297,794.60..25,779,298,595.65 rows=320,420 width=80) (actual time=2,703.321..2,704.287 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), t197_adobe_product_to_sales_team_assignment__cfact_2.c2728_opportunity__c_forecast_indicator__csid
  • Sort Method: quicksort Memory: 1373kB
68. 14.234 2,697.413 ↑ 25.3 12,657 1

Nested Loop Left Join (cost=166,725,424.54..25,779,254,254.83 rows=320,420 width=80) (actual time=1,506.683..2,697.413 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.735 1,565.187 ↑ 24.2 23,787 1

GroupAggregate (cost=166,680,841.89..166,697,224.30 rows=574,458 width=24) (actual time=1,506.199..1,565.187 rows=23,787 loops=1)

  • Group Key: t197_adobe_product_to_sales_team_assignment__cfact_2.c2665_opportunity__c, t197_adobe_product_to_sales_team_assignment__cfact_2.c2705_opportunity__c_stagenamesid, t197_adobe_product_to_sales_team_assignment__cfact_2.c2728_opportunity__c_forecast_indicator__csid, t197_adobe_product_to_sales_team_assignment__cfact_2.c2719_opportunity__c_closedate
70. 95.803 1,528.452 ↑ 4.7 129,521 1

Sort (cost=166,680,841.89..166,682,361.58 rows=607,876 width=32) (actual time=1,506.183..1,528.452 rows=129,521 loops=1)

  • Sort Key: t197_adobe_product_to_sales_team_assignment__cfact_2.c2665_opportunity__c, t197_adobe_product_to_sales_team_assignment__cfact_2.c2705_opportunity__c_stagenamesid, t197_adobe_product_to_sales_team_assignment__cfact_2.c2728_opportunity__c_forecast_indicator__csid, t197_adobe_product_to_sales_team_assignment__cfact_2.c2719_opportunity__c_closedate
  • Sort Method: external merge Disk: 5312kB
71. 378.588 1,432.649 ↑ 4.7 129,521 1

Nested Loop Left Join (cost=0.56..166,607,899.02 rows=607,876 width=32) (actual time=173.374..1,432.649 rows=129,521 loops=1)

  • Join Filter: (t197_adobe_product_to_sales_team_assignment__cfact_2.c2664_currencyisocodesid = t144_claricurrencytypedim_2.sid)
  • Rows Removed by Join Filter: 4921839
72. 622.549 795.019 ↑ 4.7 129,521 1

Index Scan using ix_t197_closedate_stagename_timestamp on t197_adobe_product_to_sales_team_assignment__cfact t197_adobe_product_to_sales_team_assignment__cfact_2 (cost=0.56..166,252,251.45 rows=607,876 width=28) (actual time=173.073..795.019 rows=129,521 loops=1)

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

SubPlan (forIndex 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 = t197_adobe_product_to_sales_team_assignment__cfact_2.c2751_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.898 161.888 ↓ 4.3 43,566 1

Hash Join (cost=16,900.58..53,507.96 rows=10,116 width=4) (actual time=88.035..161.888 rows=43,566 loops=1)

  • Hash Cond: (t333_userrolehierarchy_5.c3185_user_role_id = t125_userroledim_5.sid)
78. 47.511 64.306 ↓ 1.0 194,690 1

Bitmap Heap Scan on t333_userrolehierarchy t333_userrolehierarchy_5 (cost=5,837.24..41,678.84 rows=193,640 width=8) (actual time=18.937..64.306 rows=194,690 loops=1)

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

Bitmap Index Scan on idx_t333_userrolehierarchy_c3186_ancestor_role_id_end_s50459228 (cost=0.00..5,788.83 rows=193,640 width=0) (actual time=16.795..16.795 rows=194,852 loops=1)

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

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

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

Seq Scan on t125_userroledim t125_userroledim_5 (cost=0.00..11,036.25 rows=2,167 width=4) (actual time=2.334..68.488 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: 214618
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 = t197_adobe_product_to_sales_team_assignment__cfact_2.c2751_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.546 10.582 ↓ 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.546..10.582 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: 2762
  • Heap Blocks: exact=3398
84. 1.036 1.036 ↓ 1.0 8,331 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.036..1.036 rows=8,331 loops=1)

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

Seq Scan on t144_claricurrencytypedim t144_claricurrencytypedim_2 (cost=0.00..40.01 rows=39 width=12) (actual time=0.014..0.287 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=44,582.35..44,585.57 rows=1 width=8) (actual time=0.047..0.047 rows=1 loops=23,787)

  • Filter: (t197_adobe_product_to_sales_team_assignment__cfact_2.c2665_opportunity__c = aptsta1882_ft1_1.c1)
88. 23.787 1,117.989 ↑ 161.0 1 23,787

Group (cost=44,582.35..44,583.56 rows=161 width=8) (actual time=0.046..0.047 rows=1 loops=23,787)

  • Group Key: t197_adobe_product_to_sales_team_assignment__cfact_3.c2665_opportunity__c, t197_adobe_product_to_sales_team_assignment__cfact_3.c2719_opportunity__c_closedate
89. 23.787 1,094.202 ↑ 53.7 3 23,787

Sort (cost=44,582.35..44,582.76 rows=161 width=8) (actual time=0.046..0.046 rows=3 loops=23,787)

  • Sort Key: t197_adobe_product_to_sales_team_assignment__cfact_3.c2719_opportunity__c_closedate
  • Sort Method: quicksort Memory: 25kB
90. 215.349 1,070.415 ↑ 53.7 3 23,787

Nested Loop Left Join (cost=0.56..44,576.45 rows=161 width=8) (actual time=0.015..0.045 rows=3 loops=23,787)

  • Join Filter: (t197_adobe_product_to_sales_team_assignment__cfact_3.c2664_currencyisocodesid = t144_claricurrencytypedim_3.sid)
  • Rows Removed by Join Filter: 113
91. 430.572 713.610 ↑ 53.7 3 23,787

Index Scan using t197_adobe_product_to_sales_team_assignment__cfact_14214_timest on t197_adobe_product_to_sales_team_assignment__cfact t197_adobe_product_to_sales_team_assignment__cfact_3 (cost=0.56..44,442.16 rows=161 width=12) (actual time=0.013..0.030 rows=3 loops=23,787)

  • Index Cond: ((t197_adobe_product_to_sales_team_assignment__cfact_2.c2665_opportunity__c = c2665_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 (forIndex 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 = t197_adobe_product_to_sales_team_assignment__cfact_3.c2751_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,900.58..53,507.96 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,837.24..41,678.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,788.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,036.25..11,036.25 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,036.25 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.070 0.070 ↓ 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.005..0.005 rows=0 loops=14)

  • Index Cond: ((sid = t197_adobe_product_to_sales_team_assignment__cfact_3.c2751_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.204 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.252 0.252 ↑ 1.0 39 1

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

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

SubPlan (forNested 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.038 3,578.461 ↑ 18,475.2 19 1

Hash Left Join (cost=1.32..8,583.95 rows=351,028 width=120) (actual time=3,574.442..3,578.461 rows=19 loops=1)

  • Hash Cond: (cte0.c5 = t373_opportunityforecast_indicator__cpicklistdim.sid)
110. 3,578.405 3,578.405 ↑ 18,475.2 19 1

CTE Scan on bucketac cte0 (cost=0.00..7,020.56 rows=351,028 width=92) (actual time=3,574.392..3,578.405 rows=19 loops=1)

111. 0.006 0.018 ↑ 1.0 14 1

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

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

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

113. 0.021 2,706.901 ↑ 12,323.8 26 1

Subquery Scan on *SELECT* 2 (cost=1.32..11,840.83 rows=320,420 width=120) (actual time=2,703.459..2,706.901 rows=26 loops=1)

114. 0.025 2,706.880 ↑ 12,323.8 26 1

Hash Left Join (cost=1.32..7,835.58 rows=320,420 width=144) (actual time=2,703.448..2,706.880 rows=26 loops=1)

  • Hash Cond: (cte0_1.c7 = t373_opportunityforecast_indicator__cpicklistdim_1.sid)
115. 2,706.837 2,706.837 ↑ 12,323.8 26 1

CTE Scan on bucketec cte0_1 (cost=0.00..6,408.40 rows=320,420 width=116) (actual time=2,703.414..2,706.837 rows=26 loops=1)

116. 0.006 0.018 ↑ 1.0 14 1

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

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

Planning time : 11.786 ms
Execution time : 6,290.282 ms