explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UC7U

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 13,862.847 ↑ 295.9 17 1

Append (cost=1,251,654,402.43..1,251,654,568.11 rows=5,030 width=96) (actual time=13,854.177..13,862.847 rows=17 loops=1)

  • Buffers: shared hit=4,025,191, temp read=26,962 written=27,007
2.          

CTE picklist_0

3. 0.035 0.035 ↑ 1.0 6 1

Seq Scan on t693_opportunitystagenamepicklistdim (cost=0.00..6.63 rows=6 width=4) (actual time=0.008..0.035 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: 87
  • Buffers: shared hit=5
4.          

CTE picklist_2

5. 0.046 0.046 ↑ 1.0 7 1

Seq Scan on t693_opportunitystagenamepicklistdim t693_opportunitystagenamepicklistdim_1 (cost=0.00..6.74 rows=7 width=4) (actual time=0.017..0.046 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: 86
  • Buffers: shared hit=5
6.          

CTE openwont1

7. 15.897 7,986.132 ↑ 2.9 17,081 1

Nested Loop Left Join (cost=72,317,280.27..1,190,315,106.76 rows=49,684 width=34) (actual time=4,565.747..7,986.132 rows=17,081 loops=1)

  • Buffers: shared hit=2,947,456, temp read=11,392 written=11,412
8. 18.815 4,588.195 ↑ 2.9 17,081 1

GroupAggregate (cost=72,294,780.62..72,298,097.82 rows=49,684 width=20) (actual time=4,556.272..4,588.195 rows=17,081 loops=1)

  • Group Key: aptsta1882_ft0.c14214_opportunity__c, aptsta1882_ft0.c14277_opportunity__c_forecast_indicator__csid, aptsta1882_ft0.c14254_opportunity__c_stagenamesid
  • Buffers: shared hit=1,039,674, temp read=11,392 written=11,412
9. 63.689 4,569.380 ↑ 2.1 90,674 1

Sort (cost=72,294,780.62..72,295,250.68 rows=188,024 width=28) (actual time=4,556.236..4,569.380 rows=90,674 loops=1)

  • Sort Key: aptsta1882_ft0.c14214_opportunity__c, aptsta1882_ft0.c14277_opportunity__c_forecast_indicator__csid, aptsta1882_ft0.c14254_opportunity__c_stagenamesid
  • Sort Method: external merge Disk: 3,736kB
  • Buffers: shared hit=1,039,674, temp read=11,392 written=11,412
10. 20.572 4,505.691 ↑ 2.1 90,674 1

Hash Left Join (cost=1,914,045.37..72,273,808.19 rows=188,024 width=28) (actual time=4,254.937..4,505.691 rows=90,674 loops=1)

  • Hash Cond: (aptsta1882_ft0.c14213_currencyisocodesid = t970_claricurrencytypedim.sid)
  • Buffers: shared hit=1,039,674, temp read=10,925 written=10,944
11. 98.486 4,484.893 ↑ 2.1 90,674 1

Subquery Scan on aptsta1882_ft0 (cost=1,913,998.80..72,263,025.45 rows=188,024 width=24) (actual time=4,254.693..4,484.893 rows=90,674 loops=1)

  • Filter: ((NOT (hashed SubPlan 9)) AND ((alternatives: SubPlan 5 or hashed SubPlan 6) OR (alternatives: SubPlan 7 or hashed SubPlan 8)))
  • Rows Removed by Filter: 49,984
  • Buffers: shared hit=1,039,650, temp read=10,925 written=10,944
12. 111.612 4,306.094 ↑ 3.6 140,658 1

Unique (cost=1,913,998.64..2,090,741.43 rows=501,398 width=662) (actual time=4,154.446..4,306.094 rows=140,658 loops=1)

  • Buffers: shared hit=1,026,343, temp read=10,925 written=10,944
13. 303.000 4,194.482 ↑ 3.6 140,658 1

Sort (cost=1,913,998.64..1,915,252.13 rows=501,398 width=662) (actual time=4,154.437..4,194.482 rows=140,658 loops=1)

  • Sort Key: t922_adobe_product_to_sales_team_assignment__cfact_2020u.vid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.sid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.start_stamp, t922_adobe_product_to_sales_team_assignment__cfact_2020u.end_stamp, t922_adobe_product_to_sales_team_assignment__cfact_2020u.load_stamp, t922_adobe_product_to_sales_team_assignment__cfact_2020u.load_id, t922_adobe_product_to_sales_team_assignment__cfact_2020u.first, t922_adobe_product_to_sales_team_assignment__cfact_2020u.deleted, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8640_product_sell_type__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8641_gross_asv_booking__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8642_multi_year_renewal_bookings__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8643_assigned_tacv_fx_close_date__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8644_first_year_subscription__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8645_sales_team_member__c_managerid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8646_sales_team_member__c_managerid_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8648_currencyisocodesid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8649_opportunity__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8650_opportunity__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8651_opportunity__c_vp_hedge__csid, (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8652_opportunity__c_value__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8653_opportunity__c_ownerid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8654_opportunity__c_ownerid_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8655_opportunity__c_ownerid_userroleid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8656_opportunity__c_ownerid_userroleid_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8657_opportunity__c_close_date_change_reason__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8658_opportunity__c_partner_sourcing__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8659_opportunity__c_partner_sourcing__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8660_opportunity__c_created_by_partner_manager__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8661_opportunity__c_adobe_pov_status__csid, (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8662_opportunity__c_forecastcategorynamesid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8663_opportunity__c_sales_campaign__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8664_opportunity__c_sales_campaign__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8665_opportunity__c_services_sales_play__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8666_opportunity__c_contract_driver__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8667_opportunity__c_contract_driver__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8668_opp__c_contract_driver__c_adobe_role_type__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8669_opportunity__c_ags_consulting_close_date__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8670_opportunity__c_selling_partner__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8671_opportunity__c_selling_partner__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8672_opportunity__c_accept_into_salespipeline__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8673_opportunity__c_business_tech_win_driver__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8674_opportunity__c_business_tech_win_driver__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8675_opp__c_businetechwindrive__c_adobe_role_type__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8676_opportunity__c_etla_type__csid, (NULL::integer), (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8677_opportunity__c_technical_case_status__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8678_opportunity__c_pssi_name__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8679_opportunity__c_pssi_name__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8680_opportunity__c_ags_adls_close_date__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8681_opportunity__c_power__csid, (NULL::integer), (NULL::integer), (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8682_opportunity__c_stagenamesid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8683_opportunity__c_approver_desktop__c, (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8684_opportunity__c_proposal_status__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8685_opportunity__c_sales_play__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8686_opportunity__c_licensing_program__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8687_opportunity__c_geo_hedge__csid, (NULL::integer), (NULL::integer), (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8688_opportunity__c_closedate, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8689_opportunity__c_accountid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8690_opportunity__c_accountid_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8691_opportunity__c_plan__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8692_opportunity__c_isr_involvement__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8693_opportunity__c_stage_date__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8694_opportunity__c_vision_match__csid, (NULL::integer), (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8695_opportunity__c_forecast_indicator__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8696_opportunity__c_date_last_inspected_by_manager__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8697_opportunity__c_contract_status__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8698_opportunity__c_ise_driven__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8699_opportunity__c_risk_status__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8700_opportunity__c_business_case_status__csid, (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8701_opportunity__c_accountid_dx_sub_region__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8702_opportunity__c_manager_forecast_notes__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8703_opportunity__c_accountid_dx_territory__csid, (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8704_renewal_bookings_usd_adjusted__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8705_adobe_role__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8706_adobe_role__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8707_adobe_role__c_adobe_role_type__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8708_gross_asv_booking_usd_adjusted__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8709_gross_tcv_booking_usd_adjusted__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8710_new_m_s__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8711_product_on_line__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8712_product_on_line__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8713_sales_team_member__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8714_sales_team_member__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8715_sales_team_member__c_userroleid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8716_sales_team_member__c_userroleid_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8717_perpetual_license__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8718_adobe_territory__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8719_adobe_territory__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8720_adobe_territory__c_sub_region__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8721_assigned_tacv__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8722_sales_team_member_role__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8723_subscription_attrition__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8724_assigned_consulting_acv__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8725_gross_tcv_booking__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8726_gross_tsv_booking_usd_adjusted__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8727_reportable_company_acv__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8728_adobe_territory__c_region__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8729_gross_acv_booking_usd_adjusted__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8731_ratable_subscription_bookings__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8733_adobe_territory__c_territory__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8734_outlook_product_group__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8735_adobe_territory__c_global_region__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8736_adobe_sales_team__c, (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8737_gross_tsv_booking__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8738_renewal_bookings__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8739_assigned_attrition__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8740_m_s_attrition__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8741_gross_acv_booking__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8730_opportunity__c_power_status__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8732_opportunity__c_mutual_plan_status__csid
  • Sort Method: external merge Disk: 63,936kB
  • Buffers: shared hit=1,026,343, temp read=10,925 written=10,944
14. 15.628 3,891.482 ↑ 3.6 140,658 1

Append (cost=19,458.37..1,571,757.29 rows=501,398 width=662) (actual time=82.648..3,891.482 rows=140,658 loops=1)

  • Buffers: shared hit=1,026,343
15. 1,586.251 1,659.857 ↑ 16.0 11,838 1

Bitmap Heap Scan on t922_adobe_product_to_sales_team_assignment__cfact_2020u (cost=19,458.37..590,477.16 rows=189,055 width=662) (actual time=82.647..1,659.857 rows=11,838 loops=1)

  • Recheck Cond: ((c8688_opportunity__c_closedate >= 20,200,229) AND (c8688_opportunity__c_closedate <= 20,200,529))
  • Rows Removed by Index Recheck: 3,195,830
  • Filter: ((NOT deleted) AND (start_stamp <= '1585810799999'::bigint) AND (end_stamp > '1585810799999'::bigint))
  • Rows Removed by Filter: 934,940
  • Heap Blocks: exact=49,376 lossy=232,275
  • Buffers: shared hit=284,281
16. 73.606 73.606 ↓ 1.0 946,778 1

Bitmap Index Scan on idx_778_t922_adobe_p2s_team_assignment__cfact_c868_2020u (cost=0.00..19,411.10 rows=925,467 width=0) (actual time=73.606..73.606 rows=946,778 loops=1)

  • Index Cond: ((c8688_opportunity__c_closedate >= 20,200,229) AND (c8688_opportunity__c_closedate <= 20,200,529))
  • Buffers: shared hit=2,630
17. 566.932 616.512 ↑ 2.6 43,646 1

Bitmap Heap Scan on t922_adobe_product_to_sales_team_assignment__cfact_april2020u (cost=10,555.96..247,452.44 rows=115,177 width=662) (actual time=60.196..616.512 rows=43,646 loops=1)

  • Recheck Cond: ((c8688_opportunity__c_closedate >= 20,200,229) AND (c8688_opportunity__c_closedate <= 20,200,529))
  • Rows Removed by Index Recheck: 838,942
  • Filter: ((NOT deleted) AND (start_stamp <= '1585810799999'::bigint) AND (end_stamp > '1585810799999'::bigint))
  • Rows Removed by Filter: 457,672
  • Heap Blocks: exact=52,042 lossy=66,603
  • Buffers: shared hit=120,043
18. 49.580 49.580 ↑ 1.0 501,318 1

Bitmap Index Scan on idx_778_t922_adobe_p2s_team_assignment__cfact_c868_april2020u (cost=0.00..10,527.17 rows=501,874 width=0) (actual time=49.580..49.580 rows=501,318 loops=1)

  • Index Cond: ((c8688_opportunity__c_closedate >= 20,200,229) AND (c8688_opportunity__c_closedate <= 20,200,529))
  • Buffers: shared hit=1,398
19. 0.000 1,599.485 ↑ 2.3 85,174 1

Gather (cost=1,000.00..728,813.70 rows=197,166 width=662) (actual time=3.316..1,599.485 rows=85,174 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=622,019
20. 1,607.048 1,607.048 ↑ 2.9 28,391 3 / 3

Parallel Seq Scan on t922_adobe_product_to_sales_team_assignment__cfact_april2020m (cost=0.00..708,097.10 rows=82,152 width=662) (actual time=0.075..1,607.048 rows=28,391 loops=3)

  • Filter: ((NOT deleted) AND (start_stamp <= '1585810799999'::bigint) AND (end_stamp > '1585810799999'::bigint) AND (c8688_opportunity__c_closedate >= 20,200,229) AND (c8688_opportunity__c_closedate <= 20,200,529))
  • Rows Removed by Filter: 3,415,293
  • Buffers: shared hit=622,019
21.          

SubPlan (for Subquery Scan)

22. 0.049 0.049 ↑ 1.0 7 1

CTE Scan on picklist_2 cte0_4 (cost=0.00..0.14 rows=7 width=4) (actual time=0.019..0.049 rows=7 loops=1)

  • Buffers: shared hit=5
23. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.71..131.50 rows=1 width=0) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_673_t903_userrolehier_sid_c8530_ancestor_role_id_end_s_curr on t903_userrolehierarchy_current (cost=0.43..41.21 rows=12 width=4) (never executed)

  • Index Cond: ((sid = aptsta1882_ft0.c14300_sales_team_member__c) AND (c8530_ancestor_role_id = 6,405))
25. 0.000 0.000 ↓ 0.0 0

Index Scan using ""idx_665_t900_userroledim_sid_start_s_end_s_(c8519_porta_curr"" on t900_userroledim_current (cost=0.28..6.97 rows=1 width=4) (never executed)

  • Index Cond: (sid = t903_userrolehierarchy_current.c8529_user_role_id)
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
26. 29.312 80.264 ↓ 6.3 48,651 1

Hash Semi Join (cost=8,259.00..28,942.94 rows=7,675 width=4) (actual time=10.798..80.264 rows=48,651 loops=1)

  • Hash Cond: (t903_userrolehierarchy_current_1.c8529_user_role_id = t900_userroledim_current_1.sid)
  • Buffers: shared hit=13,302
27. 41.694 49.786 ↑ 1.1 205,928 1

Bitmap Heap Scan on t903_userrolehierarchy_current t903_userrolehierarchy_current_1 (cost=4,757.79..24,788.22 rows=216,434 width=8) (actual time=9.589..49.786 rows=205,928 loops=1)

  • Recheck Cond: (c8530_ancestor_role_id = 6,405)
  • Heap Blocks: exact=12,189
  • Buffers: shared hit=12,896
28. 8.092 8.092 ↑ 1.1 205,928 1

Bitmap Index Scan on idx_674_t903_userrolehier_c8530_ancestor_role_id_end_stamp_curr (cost=0.00..4,703.68 rows=216,434 width=0) (actual time=8.092..8.092 rows=205,928 loops=1)

  • Index Cond: (c8530_ancestor_role_id = 6,405)
  • Buffers: shared hit=707
29. 0.126 1.166 ↑ 2.2 950 1

Hash (cost=3,475.59..3,475.59 rows=2,049 width=4) (actual time=1.166..1.166 rows=950 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 66kB
  • Buffers: shared hit=406
30. 0.925 1.040 ↑ 2.2 950 1

Bitmap Heap Scan on t900_userroledim_current t900_userroledim_current_1 (cost=51.18..3,475.59 rows=2,049 width=4) (actual time=0.157..1.040 rows=950 loops=1)

  • Recheck Cond: ((c8519_portaltype IS NULL) OR (c8519_portaltype = 'None'::text))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
  • Rows Removed by Filter: 1,240
  • Heap Blocks: exact=394
  • Buffers: shared hit=406
31. 0.115 0.115 ↓ 1.1 2,190 1

Bitmap Index Scan on ""idx_665_t900_userroledim_sid_start_s_end_s_(c8519_porta_curr"" (cost=0.00..50.66 rows=2,077 width=0) (actual time=0.115..0.115 rows=2,190 loops=1)

  • Buffers: shared hit=12
32. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_670_t899_userdim_sid_end_stamp_start_stamp_null_curr on t899_userdim_current (cost=0.42..8.44 rows=1 width=0) (never executed)

  • Index Cond: (sid = aptsta1882_ft0.c14300_sales_team_member__c)
  • Filter: ((NOT deleted) AND (c8517_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c8507_usertype = 'Standard'::text))
33. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t899_userdim_current t899_userdim_current_1 (cost=199.98..8,537.13 rows=4,617 width=4) (never executed)

  • Recheck Cond: (c8517_userroleid IS NULL)
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c8507_usertype = 'Standard'::text))
34. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_672_t899_userdim_c8517_userroleid_end_s_start_s_nul_curr (cost=0.00..198.82 rows=8,871 width=0) (never executed)

  • Index Cond: (c8517_userroleid IS NULL)
35. 0.011 0.226 ↑ 1.0 42 1

Hash (cost=46.05..46.05 rows=42 width=12) (actual time=0.225..0.226 rows=42 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=24
36. 0.215 0.215 ↑ 1.0 42 1

Seq Scan on t970_claricurrencytypedim (cost=0.00..46.05 rows=42 width=12) (actual time=0.010..0.215 rows=42 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 1,722
  • Buffers: shared hit=24
37. 0.000 3,382.038 ↑ 1.0 1 17,081

Subquery Scan on aptsta1882_ft1 (cost=22,499.38..22,502.53 rows=1 width=20) (actual time=0.198..0.198 rows=1 loops=17,081)

  • Filter: (aptsta1882_ft0.c14214_opportunity__c = aptsta1882_ft1.c1)
  • Buffers: shared hit=1,907,782
38. 34.162 3,382.038 ↑ 84.0 1 17,081

GroupAggregate (cost=22,499.38..22,501.48 rows=84 width=20) (actual time=0.198..0.198 rows=1 loops=17,081)

  • Group Key: aptsta1882_ft1_1.c14214_opportunity__c, aptsta1882_ft1_1.c14268_opportunity__c_closedate, aptsta1882_ft1_1.c14254_opportunity__c_stagenamesid
  • Buffers: shared hit=1,907,782
39. 51.243 3,347.876 ↑ 10.5 8 17,081

Sort (cost=22,499.38..22,499.59 rows=84 width=28) (actual time=0.195..0.196 rows=8 loops=17,081)

  • Sort Key: aptsta1882_ft1_1.c14268_opportunity__c_closedate, aptsta1882_ft1_1.c14254_opportunity__c_stagenamesid
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1,907,782
40. 51.031 3,296.633 ↑ 10.5 8 17,081

Hash Left Join (cost=6,816.15..22,496.69 rows=84 width=28) (actual time=0.146..0.193 rows=8 loops=17,081)

  • Hash Cond: (aptsta1882_ft1_1.c14213_currencyisocodesid = t970_claricurrencytypedim_1.sid)
  • Buffers: shared hit=1,907,782
41. 129.579 3,245.390 ↑ 10.5 8 17,081

Subquery Scan on aptsta1882_ft1_1 (cost=6,769.57..22,445.18 rows=84 width=24) (actual time=0.146..0.190 rows=8 loops=17,081)

  • Filter: ((alternatives: SubPlan 10 or hashed SubPlan 11) OR (alternatives: SubPlan 12 or hashed SubPlan 13))
  • Buffers: shared hit=1,907,758
42. 580.754 2,579.231 ↑ 14.0 8 17,081

HashAggregate (cost=6,769.57..6,770.69 rows=112 width=662) (actual time=0.139..0.151 rows=8 loops=17,081)

  • Group Key: t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.vid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.sid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.start_stamp, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.end_stamp, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.load_stamp, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.load_id, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.first, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.deleted, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8640_product_sell_type__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8641_gross_asv_booking__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8642_multi_year_renewal_bookings__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8643_assigned_tacv_fx_close_date__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8644_first_year_subscription__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8645_sales_team_member__c_managerid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8646_sales_team_member__c_managerid_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8648_currencyisocodesid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8649_opportunity__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8650_opportunity__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8651_opportunity__c_vp_hedge__csid, (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8652_opportunity__c_value__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8653_opportunity__c_ownerid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8654_opportunity__c_ownerid_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8655_opportunity__c_ownerid_userroleid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8656_opportunity__c_ownerid_userroleid_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8657_opportunity__c_close_date_change_reason__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8658_opportunity__c_partner_sourcing__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8659_opportunity__c_partner_sourcing__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8660_opportunity__c_created_by_partner_manager__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8661_opportunity__c_adobe_pov_status__csid, (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8662_opportunity__c_forecastcategorynamesid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8663_opportunity__c_sales_campaign__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8664_opportunity__c_sales_campaign__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8665_opportunity__c_services_sales_play__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8666_opportunity__c_contract_driver__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8667_opportunity__c_contract_driver__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8668_opp__c_contract_driver__c_adobe_role_type__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8669_opportunity__c_ags_consulting_close_date__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8670_opportunity__c_selling_partner__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8671_opportunity__c_selling_partner__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8672_opportunity__c_accept_into_salespipeline__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8673_opportunity__c_business_tech_win_driver__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8674_opportunity__c_business_tech_win_driver__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8675_opp__c_businetechwindrive__c_adobe_role_type__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8676_opportunity__c_etla_type__csid, (NULL::integer), (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8677_opportunity__c_technical_case_status__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8678_opportunity__c_pssi_name__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8679_opportunity__c_pssi_name__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8680_opportunity__c_ags_adls_close_date__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8681_opportunity__c_power__csid, (NULL::integer), (NULL::integer), (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8682_opportunity__c_stagenamesid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8683_opportunity__c_approver_desktop__c, (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8684_opportunity__c_proposal_status__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8685_opportunity__c_sales_play__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8686_opportunity__c_licensing_program__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8687_opportunity__c_geo_hedge__csid, (NULL::integer), (NULL::integer), (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8688_opportunity__c_closedate, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8689_opportunity__c_accountid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8690_opportunity__c_accountid_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8691_opportunity__c_plan__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8692_opportunity__c_isr_involvement__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8693_opportunity__c_stage_date__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8694_opportunity__c_vision_match__csid, (NULL::integer), (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8695_opportunity__c_forecast_indicator__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8696_opportunity__c_date_last_inspected_by_manager__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8697_opportunity__c_contract_status__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8698_opportunity__c_ise_driven__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8699_opportunity__c_risk_status__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8700_opportunity__c_business_case_status__csid, (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8701_opportunity__c_accountid_dx_sub_region__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8702_opportunity__c_manager_forecast_notes__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8703_opportunity__c_accountid_dx_territory__csid, (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8704_renewal_bookings_usd_adjusted__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8705_adobe_role__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8706_adobe_role__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8707_adobe_role__c_adobe_role_type__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8708_gross_asv_booking_usd_adjusted__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8709_gross_tcv_booking_usd_adjusted__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8710_new_m_s__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8711_product_on_line__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8712_product_on_line__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8713_sales_team_member__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8714_sales_team_member__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8715_sales_team_member__c_userroleid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8716_sales_team_member__c_userroleid_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8717_perpetual_license__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8718_adobe_territory__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8719_adobe_territory__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8720_adobe_territory__c_sub_region__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8721_assigned_tacv__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8722_sales_team_member_role__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8723_subscription_attrition__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8724_assigned_consulting_acv__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8725_gross_tcv_booking__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8726_gross_tsv_booking_usd_adjusted__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8727_reportable_company_acv__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8728_adobe_territory__c_region__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8729_gross_acv_booking_usd_adjusted__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8731_ratable_subscription_bookings__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8733_adobe_territory__c_territory__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8734_outlook_product_group__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8735_adobe_territory__c_global_region__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8736_adobe_sales_team__c, (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8737_gross_tsv_booking__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8738_renewal_bookings__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8739_assigned_attrition__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8740_m_s_attrition__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8741_gross_acv_booking__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8730_opportunity__c_power_status__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8732_opportunity__c_mutual_plan_status__csid
  • Buffers: shared hit=968,241
43. 51.243 1,998.477 ↑ 14.0 8 17,081

Append (cost=0.43..6,730.37 rows=112 width=662) (actual time=0.086..0.117 rows=8 loops=17,081)

  • Buffers: shared hit=968,241
44. 239.134 239.134 ↓ 0.0 0 17,081

Index Scan using idx_771_t922_adobe_p2s_team_assignment__cfact_c864_2020u on t922_adobe_product_to_sales_team_assignment__cfact_2020u t922_adobe_product_to_sales_team_assignment__cfact_2020u_1 (cost=0.43..633.56 rows=21 width=662) (actual time=0.014..0.014 rows=0 loops=17,081)

  • Index Cond: (aptsta1882_ft0.c14214_opportunity__c = c8649_opportunity__c)
  • Filter: ((NOT deleted) AND (start_stamp <= '1601535599999'::bigint) AND (end_stamp > '1601535599999'::bigint))
  • Rows Removed by Filter: 20
  • Buffers: shared hit=91,408
45. 427.025 427.025 ↑ 8.8 4 17,081

Index Scan using idx_771_t922_adobe_p2s_team_assignment__cfact_c864_sept2020u on t922_adobe_product_to_sales_team_assignment__cfact_sept2020u (cost=0.43..563.35 rows=35 width=662) (actual time=0.009..0.025 rows=4 loops=17,081)

  • Index Cond: (aptsta1882_ft0.c14214_opportunity__c = c8649_opportunity__c)
  • Filter: ((NOT deleted) AND (start_stamp <= '1601535599999'::bigint) AND (end_stamp > '1601535599999'::bigint))
  • Rows Removed by Filter: 27
  • Buffers: shared hit=133,007
46. 1,110.265 1,281.075 ↑ 14.0 4 17,081

Bitmap Heap Scan on t922_adobe_product_to_sales_team_assignment__cfact_sept2020m (cost=34.98..5,532.34 rows=56 width=662) (actual time=0.065..0.075 rows=4 loops=17,081)

  • Recheck Cond: (aptsta1882_ft0.c14214_opportunity__c = c8649_opportunity__c)
  • Filter: ((NOT deleted) AND (start_stamp <= '1601535599999'::bigint) AND (end_stamp > '1601535599999'::bigint))
  • Rows Removed by Filter: 162
  • Heap Blocks: exact=680,393
  • Buffers: shared hit=743,826
47. 170.810 170.810 ↑ 8.5 166 17,081

Bitmap Index Scan on idx_771_t922_adobe_p2s_team_assignment__cfact_c864_sept2020m (cost=0.00..34.97 rows=1,404 width=0) (actual time=0.010..0.010 rows=166 loops=17,081)

  • Index Cond: (aptsta1882_ft0.c14214_opportunity__c = c8649_opportunity__c)
  • Buffers: shared hit=63,433
48.          

SubPlan (for Subquery Scan)

49. 134.145 536.580 ↑ 1.0 1 134,145

Nested Loop Semi Join (cost=0.71..131.50 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=134,145)

  • Buffers: shared hit=939,517
50. 268.290 268.290 ↑ 12.0 1 134,145

Index Scan using idx_673_t903_userrolehier_sid_c8530_ancestor_role_id_end_s_curr on t903_userrolehierarchy_current t903_userrolehierarchy_current_2 (cost=0.43..41.21 rows=12 width=4) (actual time=0.002..0.002 rows=1 loops=134,145)

  • Index Cond: ((sid = aptsta1882_ft1_1.c14300_sales_team_member__c) AND (c8530_ancestor_role_id = 6,405))
  • Buffers: shared hit=536,890
51. 134.145 134.145 ↑ 1.0 1 134,145

Index Scan using ""idx_665_t900_userroledim_sid_start_s_end_s_(c8519_porta_curr"" on t900_userroledim_current t900_userroledim_current_2 (cost=0.28..6.97 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=134,145)

  • Index Cond: (sid = t903_userrolehierarchy_current_2.c8529_user_role_id)
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
  • Buffers: shared hit=402,627
52. 0.000 0.000 ↓ 0.0 0

Hash Semi Join (cost=8,259.00..28,942.94 rows=7,675 width=4) (never executed)

  • Hash Cond: (t903_userrolehierarchy_current_3.c8529_user_role_id = t900_userroledim_current_3.sid)
53. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t903_userrolehierarchy_current t903_userrolehierarchy_current_3 (cost=4,757.79..24,788.22 rows=216,434 width=8) (never executed)

  • Recheck Cond: (c8530_ancestor_role_id = 6,405)
54. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_674_t903_userrolehier_c8530_ancestor_role_id_end_stamp_curr (cost=0.00..4,703.68 rows=216,434 width=0) (never executed)

  • Index Cond: (c8530_ancestor_role_id = 6,405)
55. 0.000 0.000 ↓ 0.0 0

Hash (cost=3,475.59..3,475.59 rows=2,049 width=4) (never executed)

56. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t900_userroledim_current t900_userroledim_current_3 (cost=51.18..3,475.59 rows=2,049 width=4) (never executed)

  • Recheck Cond: ((c8519_portaltype IS NULL) OR (c8519_portaltype = 'None'::text))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
57. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ""idx_665_t900_userroledim_sid_start_s_end_s_(c8519_porta_curr"" (cost=0.00..50.66 rows=2,077 width=0) (never executed)

58. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_670_t899_userdim_sid_end_stamp_start_stamp_null_curr on t899_userdim_current t899_userdim_current_2 (cost=0.42..8.44 rows=1 width=0) (never executed)

  • Index Cond: (sid = aptsta1882_ft1_1.c14300_sales_team_member__c)
  • Filter: ((NOT deleted) AND (c8517_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c8507_usertype = 'Standard'::text))
59. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t899_userdim_current t899_userdim_current_3 (cost=199.98..8,537.13 rows=4,617 width=4) (never executed)

  • Recheck Cond: (c8517_userroleid IS NULL)
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c8507_usertype = 'Standard'::text))
60. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_672_t899_userdim_c8517_userroleid_end_s_start_s_nul_curr (cost=0.00..198.82 rows=8,871 width=0) (never executed)

  • Index Cond: (c8517_userroleid IS NULL)
61. 0.009 0.212 ↑ 1.0 42 1

Hash (cost=46.05..46.05 rows=42 width=12) (actual time=0.211..0.212 rows=42 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=24
62. 0.203 0.203 ↑ 1.0 42 1

Seq Scan on t970_claricurrencytypedim t970_claricurrencytypedim_1 (cost=0.00..46.05 rows=42 width=12) (actual time=0.008..0.203 rows=42 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 1,722
  • Buffers: shared hit=24
63.          

SubPlan (for Nested Loop Left Join)

64. 0.001 0.001 ↑ 1.0 6 1

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

65. 0.001 0.001 ↑ 1.0 6 1

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

66.          

CTE openwont1ids

67. 12.262 8,007.687 ↓ 85.4 17,081 1

HashAggregate (cost=1,117.89..1,119.89 rows=200 width=4) (actual time=8,004.115..8,007.687 rows=17,081 loops=1)

  • Group Key: cte0_5.c1
  • Buffers: shared hit=2,947,456, temp read=11,392 written=11,412
68. 7,995.425 7,995.425 ↑ 2.9 17,081 1

CTE Scan on openwont1 cte0_5 (cost=0.00..993.68 rows=49,684 width=4) (actual time=4,565.749..7,995.425 rows=17,081 loops=1)

  • Buffers: shared hit=2,947,456, temp read=11,392 written=11,412
69.          

CTE bucketab

70. 4.519 7.144 ↑ 382.2 13 1

HashAggregate (cost=1,738.94..1,788.62 rows=4,968 width=68) (actual time=7.110..7.144 rows=13 loops=1)

  • Group Key: cte0_6.c5, CASE WHEN cte0_6.c6 THEN CASE WHEN cte0_6.c4 THEN 'convertedWon'::text ELSE 'convertedOpen'::text END ELSE CASE WHEN cte0_6.c4 THEN 'unconvertedWon'::text ELSE 'unconvertedOpen'::text END END, cte0_6.c3
71. 2.625 2.625 ↑ 2.9 17,081 1

CTE Scan on openwont1 cte0_6 (cost=0.00..993.68 rows=49,684 width=64) (actual time=0.002..2.625 rows=17,081 loops=1)

72.          

CTE picklist_3

73. 0.041 0.041 ↑ 1.0 13 1

Seq Scan on t693_opportunitystagenamepicklistdim t693_opportunitystagenamepicklistdim_2 (cost=0.00..7.44 rows=13 width=4) (actual time=0.006..0.041 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: 80
  • Buffers: shared hit=5
74.          

CTE bucketd

75. 1.053 13,855.635 ↑ 15.5 4 1

GroupAggregate (cost=61,336,362.75..61,336,366.34 rows=62 width=120) (actual time=13,854.168..13,855.635 rows=4 loops=1)

  • Group Key: '1585810799999'::bigint, (CASE WHEN (aptsta1882_ft1_2.c1 IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 18)) THEN CASE WHEN (aptsta1882_ft1_2.c3 > 20200529) THEN 'pulledIn'::text WHEN (aptsta1882_ft1_2.c3 < 20200229) THEN 'pushedIn'::text ELSE 'other'::text END ELSE 'other'::text END)
  • Buffers: shared hit=4,025,190, temp read=26,962 written=27,007
76. 2.190 13,854.582 ↓ 33.2 6,645 1

Sort (cost=61,336,362.46..61,336,362.96 rows=200 width=52) (actual time=13,854.153..13,854.582 rows=6,645 loops=1)

  • Sort Key: (CASE WHEN (aptsta1882_ft1_2.c1 IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 18)) THEN CASE WHEN (aptsta1882_ft1_2.c3 > 20200529) THEN 'pulledIn'::text WHEN (aptsta1882_ft1_2.c3 < 20200229) THEN 'pushedIn'::text ELSE 'other'::text END ELSE 'other'::text END)
  • Sort Method: quicksort Memory: 712kB
  • Buffers: shared hit=4,025,190, temp read=26,962 written=27,007
77. 3.183 13,852.392 ↓ 33.2 6,645 1

Nested Loop Left Join (cost=58,492,395.32..61,336,354.82 rows=200 width=52) (actual time=13,391.847..13,852.392 rows=6,645 loops=1)

  • Buffers: shared hit=4,025,187, temp read=26,962 written=27,007
78. 7.701 13,403.950 ↓ 33.2 6,645 1

GroupAggregate (cost=58,478,108.97..58,478,856.81 rows=200 width=12) (actual time=13,390.992..13,403.950 rows=6,645 loops=1)

  • Group Key: aptsta1882_ft0_1.c14214_opportunity__c
  • Buffers: shared hit=3,731,384, temp read=26,962 written=27,007
79. 14.833 13,396.249 ↑ 1.9 39,371 1

Sort (cost=58,478,108.97..58,478,295.43 rows=74,584 width=20) (actual time=13,390.983..13,396.249 rows=39,371 loops=1)

  • Sort Key: aptsta1882_ft0_1.c14214_opportunity__c
  • Sort Method: quicksort Memory: 4,022kB
  • Buffers: shared hit=3,731,384, temp read=26,962 written=27,007
80. 8.506 13,381.416 ↑ 1.9 39,371 1

Hash Left Join (cost=2,655,487.41..58,472,072.67 rows=74,584 width=20) (actual time=13,094.775..13,381.416 rows=39,371 loops=1)

  • Hash Cond: (aptsta1882_ft0_1.c14213_currencyisocodesid = t970_claricurrencytypedim_2.sid)
  • Buffers: shared hit=3,731,384, temp read=26,962 written=27,007
81. 14.354 13,372.687 ↑ 1.9 39,371 1

Hash Semi Join (cost=2,655,440.84..58,467,767.34 rows=74,584 width=16) (actual time=13,094.528..13,372.687 rows=39,371 loops=1)

  • Hash Cond: (aptsta1882_ft0_1.c14254_opportunity__c_stagenamesid = cte0_9.c1)
  • Buffers: shared hit=3,731,360, temp read=26,962 written=27,007
82. 125.702 13,358.286 ↑ 1.5 102,191 1

Subquery Scan on aptsta1882_ft0_1 (cost=2,655,440.64..58,466,545.83 rows=149,168 width=20) (actual time=13,046.740..13,358.286 rows=102,191 loops=1)

  • Filter: ((NOT (hashed SubPlan 23)) AND ((alternatives: SubPlan 19 or hashed SubPlan 20) OR (alternatives: SubPlan 21 or hashed SubPlan 22)))
  • Rows Removed by Filter: 79,462
  • Buffers: shared hit=3,731,355, temp read=26,962 written=27,007
83. 155.208 5,139.374 ↑ 2.2 181,653 1

Unique (cost=2,655,436.14..2,795,654.30 rows=397,782 width=662) (actual time=4,936.956..5,139.374 rows=181,653 loops=1)

  • Buffers: shared hit=770,597, temp read=15,570 written=15,595
84. 348.220 4,984.166 ↑ 2.2 181,653 1

Sort (cost=2,655,436.14..2,656,430.60 rows=397,782 width=662) (actual time=4,936.955..4,984.166 rows=181,653 loops=1)

  • Sort Key: t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.vid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.sid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.start_stamp, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.end_stamp, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.load_stamp, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.load_id, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.first, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.deleted, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8640_product_sell_type__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8641_gross_asv_booking__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8642_multi_year_renewal_bookings__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8643_assigned_tacv_fx_close_date__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8644_first_year_subscription__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8645_sales_team_member__c_managerid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8646_sales_team_member__c_managerid_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8648_currencyisocodesid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8649_opportunity__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8650_opportunity__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8651_opportunity__c_vp_hedge__csid, (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8652_opportunity__c_value__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8653_opportunity__c_ownerid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8654_opportunity__c_ownerid_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8655_opportunity__c_ownerid_userroleid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8656_opportunity__c_ownerid_userroleid_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8657_opportunity__c_close_date_change_reason__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8658_opportunity__c_partner_sourcing__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8659_opportunity__c_partner_sourcing__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8660_opportunity__c_created_by_partner_manager__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8661_opportunity__c_adobe_pov_status__csid, (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8662_opportunity__c_forecastcategorynamesid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8663_opportunity__c_sales_campaign__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8664_opportunity__c_sales_campaign__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8665_opportunity__c_services_sales_play__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8666_opportunity__c_contract_driver__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8667_opportunity__c_contract_driver__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8668_opp__c_contract_driver__c_adobe_role_type__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8669_opportunity__c_ags_consulting_close_date__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8670_opportunity__c_selling_partner__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8671_opportunity__c_selling_partner__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8672_opportunity__c_accept_into_salespipeline__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8673_opportunity__c_business_tech_win_driver__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8674_opportunity__c_business_tech_win_driver__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8675_opp__c_businetechwindrive__c_adobe_role_type__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8676_opportunity__c_etla_type__csid, (NULL::integer), (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8677_opportunity__c_technical_case_status__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8678_opportunity__c_pssi_name__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8679_opportunity__c_pssi_name__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8680_opportunity__c_ags_adls_close_date__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8681_opportunity__c_power__csid, (NULL::integer), (NULL::integer), (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8682_opportunity__c_stagenamesid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8683_opportunity__c_approver_desktop__c, (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8684_opportunity__c_proposal_status__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8685_opportunity__c_sales_play__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8686_opportunity__c_licensing_program__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8687_opportunity__c_geo_hedge__csid, (NULL::integer), (NULL::integer), (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8688_opportunity__c_closedate, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8689_opportunity__c_accountid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8690_opportunity__c_accountid_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8691_opportunity__c_plan__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8692_opportunity__c_isr_involvement__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8693_opportunity__c_stage_date__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8694_opportunity__c_vision_match__csid, (NULL::integer), (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8695_opportunity__c_forecast_indicator__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8696_opportunity__c_date_last_inspected_by_manager__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8697_opportunity__c_contract_status__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8698_opportunity__c_ise_driven__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8699_opportunity__c_risk_status__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8700_opportunity__c_business_case_status__csid, (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8701_opportunity__c_accountid_dx_sub_region__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8702_opportunity__c_manager_forecast_notes__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8703_opportunity__c_accountid_dx_territory__csid, (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8704_renewal_bookings_usd_adjusted__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8705_adobe_role__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8706_adobe_role__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8707_adobe_role__c_adobe_role_type__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8708_gross_asv_booking_usd_adjusted__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8709_gross_tcv_booking_usd_adjusted__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8710_new_m_s__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8711_product_on_line__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8712_product_on_line__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8713_sales_team_member__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8714_sales_team_member__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8715_sales_team_member__c_userroleid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8716_sales_team_member__c_userroleid_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8717_perpetual_license__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8718_adobe_territory__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8719_adobe_territory__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8720_adobe_territory__c_sub_region__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8721_assigned_tacv__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8722_sales_team_member_role__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8723_subscription_attrition__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8724_assigned_consulting_acv__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8725_gross_tcv_booking__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8726_gross_tsv_booking_usd_adjusted__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8727_reportable_company_acv__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8728_adobe_territory__c_region__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8729_gross_acv_booking_usd_adjusted__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8731_ratable_subscription_bookings__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8733_adobe_territory__c_territory__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8734_outlook_product_group__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8735_adobe_territory__c_global_region__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8736_adobe_sales_team__c, (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8737_gross_tsv_booking__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8738_renewal_bookings__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8739_assigned_attrition__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8740_m_s_attrition__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8741_gross_acv_booking__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8730_opportunity__c_power_status__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8732_opportunity__c_mutual_plan_status__csid
  • Sort Method: external merge Disk: 82,104kB
  • Buffers: shared hit=770,597, temp read=15,570 written=15,595
85. 21.188 4,635.946 ↑ 2.2 181,653 1

Append (cost=19,441.28..2,384,583.20 rows=397,782 width=662) (actual time=97.139..4,635.946 rows=181,653 loops=1)

  • Buffers: shared hit=770,597
86. 1,563.238 1,650.229 ↑ 26.9 4,482 1

Bitmap Heap Scan on t922_adobe_product_to_sales_team_assignment__cfact_2020u t922_adobe_product_to_sales_team_assignment__cfact_2020u_2 (cost=19,441.28..590,460.08 rows=120,717 width=662) (actual time=97.138..1,650.229 rows=4,482 loops=1)

  • Recheck Cond: ((c8688_opportunity__c_closedate >= 20,200,229) AND (c8688_opportunity__c_closedate <= 20,200,529))
  • Rows Removed by Index Recheck: 3,195,830
  • Filter: ((NOT deleted) AND (start_stamp <= '1601535599999'::bigint) AND (end_stamp > '1601535599999'::bigint))
  • Rows Removed by Filter: 942,296
  • Heap Blocks: exact=49,376 lossy=232,275
  • Buffers: shared hit=284,281
87. 86.991 86.991 ↓ 1.0 946,778 1

Bitmap Index Scan on idx_778_t922_adobe_p2s_team_assignment__cfact_c868_2020u (cost=0.00..19,411.10 rows=925,467 width=0) (actual time=86.991..86.991 rows=946,778 loops=1)

  • Index Cond: ((c8688_opportunity__c_closedate >= 20,200,229) AND (c8688_opportunity__c_closedate <= 20,200,529))
  • Buffers: shared hit=2,630
88. 1,439.938 1,513.476 ↑ 1.7 129,309 1

Bitmap Heap Scan on t922_adobe_product_to_sales_team_assignment__cfact_sept2020u t922_adobe_product_to_sales_team_assignment__cfact_sept2020u_1 (cost=19,299.67..465,545.11 rows=224,090 width=662) (actual time=80.726..1,513.476 rows=129,309 loops=1)

  • Recheck Cond: ((c8688_opportunity__c_closedate >= 20,200,229) AND (c8688_opportunity__c_closedate <= 20,200,529))
  • Rows Removed by Index Recheck: 2,736,681
  • Filter: ((NOT deleted) AND (start_stamp <= '1601535599999'::bigint) AND (end_stamp > '1601535599999'::bigint))
  • Rows Removed by Filter: 759,071
  • Heap Blocks: exact=39,150 lossy=198,921
  • Buffers: shared hit=240,538
89. 73.538 73.538 ↑ 1.0 888,380 1

Bitmap Index Scan on idx_778_t922_adobe_p2s_team_assignment__cfact_c868_sept2020u (cost=0.00..19,243.65 rows=917,522 width=0) (actual time=73.538..73.538 rows=888,380 loops=1)

  • Index Cond: ((c8688_opportunity__c_closedate >= 20,200,229) AND (c8688_opportunity__c_closedate <= 20,200,529))
  • Buffers: shared hit=2,467
90. 1,350.714 1,451.053 ↑ 1.1 47,862 1

Bitmap Heap Scan on t922_adobe_product_to_sales_team_assignment__cfact_sept2020m t922_adobe_product_to_sales_team_assignment__cfact_sept2020m_1 (cost=28,109.85..1,324,600.19 rows=52,975 width=662) (actual time=108.908..1,451.053 rows=47,862 loops=1)

  • Recheck Cond: ((c8688_opportunity__c_closedate >= 20,200,229) AND (c8688_opportunity__c_closedate <= 20,200,529))
  • Rows Removed by Index Recheck: 2,058,682
  • Filter: ((NOT deleted) AND (start_stamp <= '1601535599999'::bigint) AND (end_stamp > '1601535599999'::bigint))
  • Rows Removed by Filter: 1,320,777
  • Heap Blocks: exact=41,002 lossy=200,981
  • Buffers: shared hit=245,778
91. 100.339 100.339 ↓ 1.0 1,368,639 1

Bitmap Index Scan on idx_778_t922_adobe_p2s_team_assignment__cfact_c868_sept2020m (cost=0.00..28,096.61 rows=1,339,617 width=0) (actual time=100.339..100.339 rows=1,368,639 loops=1)

  • Index Cond: ((c8688_opportunity__c_closedate >= 20,200,229) AND (c8688_opportunity__c_closedate <= 20,200,529))
  • Buffers: shared hit=3,795
92.          

SubPlan (for Subquery Scan)

93. 8,011.432 8,011.432 ↓ 85.4 17,081 1

CTE Scan on openwont1ids cte0_8 (cost=0.00..4.00 rows=200 width=4) (actual time=8,004.117..8,011.432 rows=17,081 loops=1)

  • Buffers: shared hit=2,947,456, temp read=11,392 written=11,412
94. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.71..131.50 rows=1 width=0) (never executed)

95. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_673_t903_userrolehier_sid_c8530_ancestor_role_id_end_s_curr on t903_userrolehierarchy_current t903_userrolehierarchy_current_4 (cost=0.43..41.21 rows=12 width=4) (never executed)

  • Index Cond: ((sid = aptsta1882_ft0_1.c14300_sales_team_member__c) AND (c8530_ancestor_role_id = 6,405))
96. 0.000 0.000 ↓ 0.0 0

Index Scan using ""idx_665_t900_userroledim_sid_start_s_end_s_(c8519_porta_curr"" on t900_userroledim_current t900_userroledim_current_4 (cost=0.28..6.97 rows=1 width=4) (never executed)

  • Index Cond: (sid = t903_userrolehierarchy_current_4.c8529_user_role_id)
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
97. 29.638 81.778 ↓ 6.3 48,651 1

Hash Semi Join (cost=8,259.00..28,942.94 rows=7,675 width=4) (actual time=12.052..81.778 rows=48,651 loops=1)

  • Hash Cond: (t903_userrolehierarchy_current_5.c8529_user_role_id = t900_userroledim_current_5.sid)
  • Buffers: shared hit=13,302
98. 41.646 50.060 ↑ 1.1 205,928 1

Bitmap Heap Scan on t903_userrolehierarchy_current t903_userrolehierarchy_current_5 (cost=4,757.79..24,788.22 rows=216,434 width=8) (actual time=9.946..50.060 rows=205,928 loops=1)

  • Recheck Cond: (c8530_ancestor_role_id = 6,405)
  • Heap Blocks: exact=12,189
  • Buffers: shared hit=12,896
99. 8.414 8.414 ↑ 1.1 205,928 1

Bitmap Index Scan on idx_674_t903_userrolehier_c8530_ancestor_role_id_end_stamp_curr (cost=0.00..4,703.68 rows=216,434 width=0) (actual time=8.414..8.414 rows=205,928 loops=1)

  • Index Cond: (c8530_ancestor_role_id = 6,405)
  • Buffers: shared hit=707
100. 0.622 2.080 ↑ 2.2 950 1

Hash (cost=3,475.59..3,475.59 rows=2,049 width=4) (actual time=2.080..2.080 rows=950 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 66kB
  • Buffers: shared hit=406
101. 1.301 1.458 ↑ 2.2 950 1

Bitmap Heap Scan on t900_userroledim_current t900_userroledim_current_5 (cost=51.18..3,475.59 rows=2,049 width=4) (actual time=0.212..1.458 rows=950 loops=1)

  • Recheck Cond: ((c8519_portaltype IS NULL) OR (c8519_portaltype = 'None'::text))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
  • Rows Removed by Filter: 1,240
  • Heap Blocks: exact=394
  • Buffers: shared hit=406
102. 0.157 0.157 ↓ 1.1 2,190 1

Bitmap Index Scan on ""idx_665_t900_userroledim_sid_start_s_end_s_(c8519_porta_curr"" (cost=0.00..50.66 rows=2,077 width=0) (actual time=0.157..0.157 rows=2,190 loops=1)

  • Buffers: shared hit=12
103. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_670_t899_userdim_sid_end_stamp_start_stamp_null_curr on t899_userdim_current t899_userdim_current_4 (cost=0.42..8.44 rows=1 width=0) (never executed)

  • Index Cond: (sid = aptsta1882_ft0_1.c14300_sales_team_member__c)
  • Filter: ((NOT deleted) AND (c8517_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c8507_usertype = 'Standard'::text))
104. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t899_userdim_current t899_userdim_current_5 (cost=199.98..8,537.13 rows=4,617 width=4) (never executed)

  • Recheck Cond: (c8517_userroleid IS NULL)
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c8507_usertype = 'Standard'::text))
105. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_672_t899_userdim_c8517_userroleid_end_s_start_s_nul_curr (cost=0.00..198.82 rows=8,871 width=0) (never executed)

  • Index Cond: (c8517_userroleid IS NULL)
106. 0.004 0.047 ↑ 1.0 6 1

Hash (cost=0.12..0.12 rows=6 width=4) (actual time=0.047..0.047 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=5
107. 0.043 0.043 ↑ 1.0 6 1

CTE Scan on picklist_0 cte0_9 (cost=0.00..0.12 rows=6 width=4) (actual time=0.014..0.043 rows=6 loops=1)

  • Buffers: shared hit=5
108. 0.012 0.223 ↑ 1.0 42 1

Hash (cost=46.05..46.05 rows=42 width=12) (actual time=0.223..0.223 rows=42 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=24
109. 0.211 0.211 ↑ 1.0 42 1

Seq Scan on t970_claricurrencytypedim t970_claricurrencytypedim_2 (cost=0.00..46.05 rows=42 width=12) (actual time=0.015..0.211 rows=42 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 1,722
  • Buffers: shared hit=24
110. 0.000 445.215 ↓ 0.0 0 6,645

Subquery Scan on aptsta1882_ft1_2 (cost=14,286.07..14,287.46 rows=1 width=12) (actual time=0.067..0.067 rows=0 loops=6,645)

  • Filter: (aptsta1882_ft0_1.c14214_opportunity__c = aptsta1882_ft1_2.c1)
  • Buffers: shared hit=293,798
111. 6.645 445.215 ↓ 0.0 0 6,645

Group (cost=14,286.07..14,286.69 rows=62 width=12) (actual time=0.067..0.067 rows=0 loops=6,645)

  • Group Key: aptsta1882_ft1_3.c14214_opportunity__c, aptsta1882_ft1_3.c14254_opportunity__c_stagenamesid, aptsta1882_ft1_3.c14268_opportunity__c_closedate
  • Buffers: shared hit=293,798
112. 6.645 438.570 ↑ 62.0 1 6,645

Sort (cost=14,286.07..14,286.22 rows=62 width=12) (actual time=0.066..0.066 rows=1 loops=6,645)

  • Sort Key: aptsta1882_ft1_3.c14254_opportunity__c_stagenamesid, aptsta1882_ft1_3.c14268_opportunity__c_closedate
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=293,798
113. 20.262 431.925 ↑ 62.0 1 6,645

Nested Loop Left Join (cost=2,722.22..14,284.22 rows=62 width=12) (actual time=0.055..0.065 rows=1 loops=6,645)

  • Join Filter: (aptsta1882_ft1_3.c14213_currencyisocodesid = t970_claricurrencytypedim_3.sid)
  • Rows Removed by Join Filter: 40
  • Buffers: shared hit=293,798
114. 7.190 392.055 ↑ 62.0 1 6,645

Subquery Scan on aptsta1882_ft1_3 (cost=2,722.22..14,199.01 rows=62 width=16) (actual time=0.053..0.059 rows=1 loops=6,645)

  • Filter: ((alternatives: SubPlan 24 or hashed SubPlan 25) OR (alternatives: SubPlan 26 or hashed SubPlan 27))
  • Buffers: shared hit=293,774
115. 33.225 352.185 ↑ 82.0 1 6,645

HashAggregate (cost=2,722.22..2,723.04 rows=82 width=662) (actual time=0.052..0.053 rows=1 loops=6,645)

  • Group Key: t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.vid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.sid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.start_stamp, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.end_stamp, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.load_stamp, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.load_id, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.first, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.deleted, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8640_product_sell_type__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8641_gross_asv_booking__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8642_multi_year_renewal_bookings__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8643_assigned_tacv_fx_close_date__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8644_first_year_subscription__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8645_sales_team_member__c_managerid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8646_sales_team_member__c_managerid_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8648_currencyisocodesid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8649_opportunity__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8650_opportunity__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8651_opportunity__c_vp_hedge__csid, (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8652_opportunity__c_value__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8653_opportunity__c_ownerid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8654_opportunity__c_ownerid_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8655_opportunity__c_ownerid_userroleid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8656_opportunity__c_ownerid_userroleid_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8657_opportunity__c_close_date_change_reason__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8658_opportunity__c_partner_sourcing__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8659_opportunity__c_partner_sourcing__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8660_opportunity__c_created_by_partner_manager__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8661_opportunity__c_adobe_pov_status__csid, (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8662_opportunity__c_forecastcategorynamesid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8663_opportunity__c_sales_campaign__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8664_opportunity__c_sales_campaign__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8665_opportunity__c_services_sales_play__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8666_opportunity__c_contract_driver__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8667_opportunity__c_contract_driver__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8668_opp__c_contract_driver__c_adobe_role_type__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8669_opportunity__c_ags_consulting_close_date__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8670_opportunity__c_selling_partner__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8671_opportunity__c_selling_partner__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8672_opportunity__c_accept_into_salespipeline__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8673_opportunity__c_business_tech_win_driver__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8674_opportunity__c_business_tech_win_driver__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8675_opp__c_businetechwindrive__c_adobe_role_type__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8676_opportunity__c_etla_type__csid, (NULL::integer), (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8677_opportunity__c_technical_case_status__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8678_opportunity__c_pssi_name__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8679_opportunity__c_pssi_name__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8680_opportunity__c_ags_adls_close_date__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8681_opportunity__c_power__csid, (NULL::integer), (NULL::integer), (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8682_opportunity__c_stagenamesid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8683_opportunity__c_approver_desktop__c, (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8684_opportunity__c_proposal_status__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8685_opportunity__c_sales_play__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8686_opportunity__c_licensing_program__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8687_opportunity__c_geo_hedge__csid, (NULL::integer), (NULL::integer), (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8688_opportunity__c_closedate, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8689_opportunity__c_accountid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8690_opportunity__c_accountid_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8691_opportunity__c_plan__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8692_opportunity__c_isr_involvement__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8693_opportunity__c_stage_date__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8694_opportunity__c_vision_match__csid, (NULL::integer), (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8695_opportunity__c_forecast_indicator__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8696_opportunity__c_date_last_inspected_by_manager__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8697_opportunity__c_contract_status__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8698_opportunity__c_ise_driven__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8699_opportunity__c_risk_status__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8700_opportunity__c_business_case_status__csid, (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8701_opportunity__c_accountid_dx_sub_region__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8702_opportunity__c_manager_forecast_notes__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8703_opportunity__c_accountid_dx_territory__csid, (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8704_renewal_bookings_usd_adjusted__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8705_adobe_role__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8706_adobe_role__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8707_adobe_role__c_adobe_role_type__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8708_gross_asv_booking_usd_adjusted__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8709_gross_tcv_booking_usd_adjusted__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8710_new_m_s__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8711_product_on_line__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8712_product_on_line__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8713_sales_team_member__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8714_sales_team_member__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8715_sales_team_member__c_userroleid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8716_sales_team_member__c_userroleid_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8717_perpetual_license__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8718_adobe_territory__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8719_adobe_territory__c_safe, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8720_adobe_territory__c_sub_region__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8721_assigned_tacv__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8722_sales_team_member_role__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8723_subscription_attrition__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8724_assigned_consulting_acv__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8725_gross_tcv_booking__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8726_gross_tsv_booking_usd_adjusted__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8727_reportable_company_acv__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8728_adobe_territory__c_region__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8729_gross_acv_booking_usd_adjusted__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8731_ratable_subscription_bookings__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8733_adobe_territory__c_territory__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8734_outlook_product_group__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8735_adobe_territory__c_global_region__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8736_adobe_sales_team__c, (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), (NULL::integer), t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8737_gross_tsv_booking__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8738_renewal_bookings__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8739_assigned_attrition__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8740_m_s_attrition__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8741_gross_acv_booking__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8730_opportunity__c_power_status__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8732_opportunity__c_mutual_plan_status__csid
  • Buffers: shared hit=248,000
116. 6.645 318.960 ↑ 82.0 1 6,645

Append (cost=0.43..2,693.52 rows=82 width=662) (actual time=0.034..0.048 rows=1 loops=6,645)

  • Buffers: shared hit=248,000
117. 26.580 26.580 ↓ 0.0 0 6,645

Index Scan using idx_771_t922_adobe_p2s_team_assignment__cfact_c864_2020u on t922_adobe_product_to_sales_team_assignment__cfact_2020u t922_adobe_product_to_sales_team_assignment__cfact_2020u_3 (cost=0.43..633.56 rows=33 width=662) (actual time=0.004..0.004 rows=0 loops=6,645)

  • Index Cond: (aptsta1882_ft0_1.c14214_opportunity__c = c8649_opportunity__c)
  • Filter: ((NOT deleted) AND (start_stamp <= '1585810799999'::bigint) AND (end_stamp > '1585810799999'::bigint))
  • Rows Removed by Filter: 3
  • Buffers: shared hit=22,875
118. 6.645 19.935 ↓ 0.0 0 6,645

Bitmap Heap Scan on t922_adobe_product_to_sales_team_assignment__cfact_april2020u t922_adobe_product_to_sales_team_assignment__cfact_april2020u_1 (cost=5.23..424.21 rows=24 width=662) (actual time=0.003..0.003 rows=0 loops=6,645)

  • Recheck Cond: (aptsta1882_ft0_1.c14214_opportunity__c = c8649_opportunity__c)
  • Filter: ((NOT deleted) AND (start_stamp <= '1585810799999'::bigint) AND (end_stamp > '1585810799999'::bigint))
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=5,467
  • Buffers: shared hit=25,487
119. 13.290 13.290 ↑ 53.0 2 6,645

Bitmap Index Scan on idx_771_t922_adobe_p2s_team_assignment__cfact_c864_april2020u (cost=0.00..5.22 rows=106 width=0) (actual time=0.002..0.002 rows=2 loops=6,645)

  • Index Cond: (aptsta1882_ft0_1.c14214_opportunity__c = c8649_opportunity__c)
  • Buffers: shared hit=20,020
120. 232.575 265.800 ↑ 25.0 1 6,645

Bitmap Heap Scan on t922_adobe_product_to_sales_team_assignment__cfact_april2020m t922_adobe_product_to_sales_team_assignment__cfact_april2020m_1 (cost=11.53..1,634.93 rows=25 width=662) (actual time=0.026..0.040 rows=1 loops=6,645)

  • Recheck Cond: (aptsta1882_ft0_1.c14214_opportunity__c = c8649_opportunity__c)
  • Filter: ((NOT deleted) AND (start_stamp <= '1585810799999'::bigint) AND (end_stamp > '1585810799999'::bigint))
  • Rows Removed by Filter: 53
  • Heap Blocks: exact=177,758
  • Buffers: shared hit=199,638
121. 33.225 33.225 ↑ 7.6 54 6,645

Bitmap Index Scan on idx_771_t922_adobe_p2s_team_assignment__cfact_c864_april2020m (cost=0.00..11.53 rows=412 width=0) (actual time=0.005..0.005 rows=54 loops=6,645)

  • Index Cond: (aptsta1882_ft0_1.c14214_opportunity__c = c8649_opportunity__c)
  • Buffers: shared hit=21,880
122.          

SubPlan (for Subquery Scan)

123. 13.072 32.680 ↑ 1.0 1 6,536

Nested Loop Semi Join (cost=0.71..131.50 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=6,536)

  • Buffers: shared hit=45,774
124. 13.072 13.072 ↑ 12.0 1 6,536

Index Scan using idx_673_t903_userrolehier_sid_c8530_ancestor_role_id_end_s_curr on t903_userrolehierarchy_current t903_userrolehierarchy_current_6 (cost=0.43..41.21 rows=12 width=4) (actual time=0.002..0.002 rows=1 loops=6,536)

  • Index Cond: ((sid = aptsta1882_ft1_3.c14300_sales_team_member__c) AND (c8530_ancestor_role_id = 6,405))
  • Buffers: shared hit=26,158
125. 6.536 6.536 ↑ 1.0 1 6,536

Index Scan using ""idx_665_t900_userroledim_sid_start_s_end_s_(c8519_porta_curr"" on t900_userroledim_current t900_userroledim_current_6 (cost=0.28..6.97 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=6,536)

  • Index Cond: (sid = t903_userrolehierarchy_current_6.c8529_user_role_id)
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
  • Buffers: shared hit=19,616
126. 0.000 0.000 ↓ 0.0 0

Hash Semi Join (cost=8,259.00..28,942.94 rows=7,675 width=4) (never executed)

  • Hash Cond: (t903_userrolehierarchy_current_7.c8529_user_role_id = t900_userroledim_current_7.sid)
127. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t903_userrolehierarchy_current t903_userrolehierarchy_current_7 (cost=4,757.79..24,788.22 rows=216,434 width=8) (never executed)

  • Recheck Cond: (c8530_ancestor_role_id = 6,405)
128. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_674_t903_userrolehier_c8530_ancestor_role_id_end_stamp_curr (cost=0.00..4,703.68 rows=216,434 width=0) (never executed)

  • Index Cond: (c8530_ancestor_role_id = 6,405)
129. 0.000 0.000 ↓ 0.0 0

Hash (cost=3,475.59..3,475.59 rows=2,049 width=4) (never executed)

130. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t900_userroledim_current t900_userroledim_current_7 (cost=51.18..3,475.59 rows=2,049 width=4) (never executed)

  • Recheck Cond: ((c8519_portaltype IS NULL) OR (c8519_portaltype = 'None'::text))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
131. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ""idx_665_t900_userroledim_sid_start_s_end_s_(c8519_porta_curr"" (cost=0.00..50.66 rows=2,077 width=0) (never executed)

132. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_670_t899_userdim_sid_end_stamp_start_stamp_null_curr on t899_userdim_current t899_userdim_current_6 (cost=0.42..8.44 rows=1 width=0) (never executed)

  • Index Cond: (sid = aptsta1882_ft1_3.c14300_sales_team_member__c)
  • Filter: ((NOT deleted) AND (c8517_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c8507_usertype = 'Standard'::text))
133. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t899_userdim_current t899_userdim_current_7 (cost=199.98..8,537.13 rows=4,617 width=4) (never executed)

  • Recheck Cond: (c8517_userroleid IS NULL)
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c8507_usertype = 'Standard'::text))
134. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_672_t899_userdim_c8517_userroleid_end_s_start_s_nul_curr (cost=0.00..198.82 rows=8,871 width=0) (never executed)

  • Index Cond: (c8517_userroleid IS NULL)
135. 19.402 19.608 ↑ 1.0 42 6,536

Materialize (cost=0.00..46.26 rows=42 width=4) (actual time=0.000..0.003 rows=42 loops=6,536)

  • Buffers: shared hit=24
136. 0.206 0.206 ↑ 1.0 42 1

Seq Scan on t970_claricurrencytypedim t970_claricurrencytypedim_3 (cost=0.00..46.05 rows=42 width=4) (actual time=0.006..0.206 rows=42 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 1,722
  • Buffers: shared hit=24
137.          

SubPlan (for Nested Loop Left Join)

138. 0.044 0.044 ↑ 1.0 13 1

CTE Scan on picklist_3 cte0_7 (cost=0.00..0.26 rows=13 width=4) (actual time=0.007..0.044 rows=13 loops=1)

  • Buffers: shared hit=5
139. 0.007 13,855.647 ↑ 15.5 4 1

Subquery Scan on "*SELECT* 1" (cost=0.00..2.01 rows=62 width=96) (actual time=13,854.176..13,855.647 rows=4 loops=1)

  • Buffers: shared hit=4,025,190, temp read=26,962 written=27,007
140. 13,855.640 13,855.640 ↑ 15.5 4 1

CTE Scan on bucketd cte0 (cost=0.00..1.24 rows=62 width=120) (actual time=13,854.171..13,855.640 rows=4 loops=1)

  • Buffers: shared hit=4,025,190, temp read=26,962 written=27,007
141. 0.027 7.197 ↑ 382.2 13 1

Hash Left Join (cost=1.32..113.99 rows=4,968 width=76) (actual time=7.154..7.197 rows=13 loops=1)

  • Hash Cond: (cte0_1.c6 = t691_opportunityforecast_indicator__cpicklistdim.sid)
  • Buffers: shared hit=1
142. 7.152 7.152 ↑ 382.2 13 1

CTE Scan on bucketab cte0_1 (cost=0.00..99.36 rows=4,968 width=68) (actual time=7.113..7.152 rows=13 loops=1)

143. 0.007 0.018 ↑ 1.0 14 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
144. 0.011 0.011 ↑ 1.0 14 1

Seq Scan on t691_opportunityforecast_indicator__cpicklistdim (cost=0.00..1.14 rows=14 width=16) (actual time=0.007..0.011 rows=14 loops=1)

  • Buffers: shared hit=1