explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JUHO

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 115,142.122 ↑ 295.2 17 1

Append (cost=1,246,931,054.67..1,246,931,219.96 rows=5,018 width=96) (actual time=115,132.722..115,142.122 rows=17 loops=1)

  • Buffers: shared hit=2,147,022 read=1,907,682, temp read=26,961 written=27,006
2.          

CTE picklist_0

3. 6.070 6.070 ↑ 1.0 6 1

Seq Scan on t693_opportunitystagenamepicklistdim (cost=0.00..6.63 rows=6 width=4) (actual time=1.630..6.070 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 read=5
4.          

CTE picklist_2

5. 0.047 0.047 ↑ 1.0 7 1

Seq Scan on t693_opportunitystagenamepicklistdim t693_opportunitystagenamepicklistdim_1 (cost=0.00..6.74 rows=7 width=4) (actual time=0.018..0.047 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. 38.933 85,624.856 ↑ 2.9 17,081 1

Nested Loop Left Join (cost=72,133,440.09..1,185,967,500.52 rows=49,556 width=34) (actual time=16,581.240..85,624.856 rows=17,081 loops=1)

  • Buffers: shared hit=1,846,441 read=1,128,531, temp read=11,391 written=11,411
8. 28.519 16,424.951 ↑ 2.9 17,081 1

GroupAggregate (cost=72,110,966.34..72,114,275.01 rows=49,556 width=20) (actual time=16,375.238..16,424.951 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=284,372 read=755,361, temp read=11,391 written=11,411
9. 79.353 16,396.432 ↑ 2.1 90,674 1

Sort (cost=72,110,966.34..72,111,435.19 rows=187,541 width=28) (actual time=16,375.211..16,396.432 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=284,372 read=755,361, temp read=11,391 written=11,411
10. 20.825 16,317.079 ↑ 2.1 90,674 1

Hash Left Join (cost=1,911,028.77..72,090,050.20 rows=187,541 width=28) (actual time=16,059.939..16,317.079 rows=90,674 loops=1)

  • Hash Cond: (aptsta1882_ft0.c14213_currencyisocodesid = t970_claricurrencytypedim.sid)
  • Buffers: shared hit=284,372 read=755,361, temp read=10,924 written=10,943
11. 100.340 16,282.507 ↑ 2.1 90,674 1

Subquery Scan on aptsta1882_ft0 (cost=1,910,982.20..72,079,295.03 rows=187,541 width=24) (actual time=16,046.168..16,282.507 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=284,372 read=755,337, temp read=10,924 written=10,943
12. 113.898 14,996.690 ↑ 3.6 140,658 1

Unique (cost=1,910,982.04..2,087,270.81 rows=500,110 width=662) (actual time=14,840.636..14,996.690 rows=140,658 loops=1)

  • Buffers: shared hit=284,361 read=742,041, temp read=10,924 written=10,943
13. 400.553 14,882.792 ↑ 3.6 140,658 1

Sort (cost=1,910,982.04..1,912,232.31 rows=500,110 width=662) (actual time=14,840.626..14,882.792 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=284,361 read=742,041, temp read=10,924 written=10,943
14. 26.357 14,482.239 ↑ 3.6 140,658 1

Append (cost=19,981.65..1,569,627.91 rows=500,110 width=662) (actual time=91.231..14,482.239 rows=140,658 loops=1)

  • Buffers: shared hit=284,361 read=742,041
15. 1,614.593 1,696.094 ↑ 16.1 11,838 1

Bitmap Heap Scan on t922_adobe_product_to_sales_team_assignment__cfact_2020u (cost=19,981.65..589,025.61 rows=191,142 width=662) (actual time=91.229..1,696.094 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,238
16. 81.501 81.501 ↑ 1.0 946,778 1

Bitmap Index Scan on idx_778_t922_adobe_p2s_team_assignment__cfact_c868_2020u (cost=0.00..19,933.87 rows=950,543 width=0) (actual time=81.501..81.501 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,587
17. 2,947.243 3,014.262 ↑ 2.7 43,646 1

Bitmap Heap Scan on t922_adobe_product_to_sales_team_assignment__cfact_april2020u (cost=10,457.70..247,260.90 rows=116,676 width=662) (actual time=79.928..3,014.262 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=123 read=120,022
18. 67.019 67.019 ↓ 1.0 501,318 1

Bitmap Index Scan on idx_778_t922_adobe_p2s_team_assignment__cfact_c868_april2020u (cost=0.00..10,428.53 rows=497,210 width=0) (actual time=67.019..67.019 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=122 read=1,378
19. 173.450 9,745.526 ↑ 2.3 85,174 1

Gather (cost=1,000.00..728,340.30 rows=192,292 width=662) (actual time=4.822..9,745.526 rows=85,174 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared read=622,019
20. 9,572.076 9,572.076 ↑ 2.8 28,391 3 / 3

Parallel Seq Scan on t922_adobe_product_to_sales_team_assignment__cfact_april2020m (cost=0.00..708,111.10 rows=80,122 width=662) (actual time=15.147..9,572.076 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 read=622,019
21.          

SubPlan (for Subquery Scan)

22. 0.050 0.050 ↑ 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.050 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. 30.714 1,185.427 ↓ 6.3 48,651 1

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

  • Hash Cond: (t903_userrolehierarchy_current_1.c8529_user_role_id = t900_userroledim_current_1.sid)
  • Buffers: shared hit=6 read=13,296
27. 259.295 1,125.097 ↑ 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=869.091..1,125.097 rows=205,928 loops=1)

  • Recheck Cond: (c8530_ancestor_role_id = 6,405)
  • Heap Blocks: exact=12,189
  • Buffers: shared hit=2 read=12,894
28. 865.802 865.802 ↑ 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=865.802..865.802 rows=205,928 loops=1)

  • Index Cond: (c8530_ancestor_role_id = 6,405)
  • Buffers: shared hit=1 read=706
29. 0.178 29.616 ↑ 2.2 950 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 66kB
  • Buffers: shared hit=4 read=402
30. 17.744 29.438 ↑ 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=14.738..29.438 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=4 read=402
31. 11.694 11.694 ↓ 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=11.694..11.694 rows=2,190 loops=1)

  • Buffers: shared hit=3 read=9
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.029 13.747 ↑ 1.0 42 1

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

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

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

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

Subquery Scan on aptsta1882_ft1 (cost=22,473.48..22,476.63 rows=1 width=20) (actual time=4.049..4.049 rows=1 loops=17,081)

  • Filter: (aptsta1882_ft0.c14214_opportunity__c = aptsta1882_ft1.c1)
  • Buffers: shared hit=1,562,069 read=373,170
38. 68.324 69,160.969 ↑ 84.0 1 17,081

GroupAggregate (cost=22,473.48..22,475.58 rows=84 width=20) (actual time=4.048..4.049 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,562,069 read=373,170
39. 102.486 69,092.645 ↑ 10.5 8 17,081

Sort (cost=22,473.48..22,473.69 rows=84 width=28) (actual time=4.045..4.045 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,562,069 read=373,170
40. 68.099 68,990.159 ↑ 10.5 8 17,081

Hash Left Join (cost=6,790.25..22,470.79 rows=84 width=28) (actual time=3.948..4.039 rows=8 loops=17,081)

  • Hash Cond: (aptsta1882_ft1_1.c14213_currencyisocodesid = t970_claricurrencytypedim_1.sid)
  • Buffers: shared hit=1,562,069 read=373,170
41. 149.603 68,921.835 ↑ 10.5 8 17,081

Subquery Scan on aptsta1882_ft1_1 (cost=6,743.67..22,419.28 rows=84 width=24) (actual time=3.946..4.035 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,562,045 read=373,170
42. 666.159 67,162.492 ↑ 14.0 8 17,081

HashAggregate (cost=6,743.67..6,744.79 rows=112 width=662) (actual time=3.920..3.932 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=623,187 read=372,511
43. 102.486 66,496.333 ↑ 14.0 8 17,081

Append (cost=0.43..6,704.47 rows=112 width=662) (actual time=2.953..3.893 rows=8 loops=17,081)

  • Buffers: shared hit=623,187 read=372,511
44. 12,042.105 12,042.105 ↓ 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..621.51 rows=20 width=662) (actual time=0.705..0.705 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=84,582 read=19,195
45. 22,546.920 22,546.920 ↑ 9.0 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..572.73 rows=36 width=662) (actual time=0.726..1.320 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=117,812 read=30,232
46. 13,135.289 31,804.822 ↑ 14.0 4 17,081

Bitmap Heap Scan on t922_adobe_product_to_sales_team_assignment__cfact_sept2020m (cost=34.94..5,509.11 rows=56 width=662) (actual time=1.826..1.862 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=420,793 read=323,084
47. 18,669.533 18,669.533 ↑ 8.4 166 17,081

Bitmap Index Scan on idx_771_t922_adobe_p2s_team_assignment__cfact_c864_sept2020m (cost=0.00..34.92 rows=1,398 width=0) (actual time=1.093..1.093 rows=166 loops=17,081)

  • Index Cond: (aptsta1882_ft0.c14214_opportunity__c = c8649_opportunity__c)
  • Buffers: shared hit=48,211 read=15,273
48.          

SubPlan (for Subquery Scan)

49. 134.145 1,609.740 ↑ 1.0 1 134,145

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

  • Buffers: shared hit=938,858 read=659
50. 1,207.305 1,207.305 ↑ 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.009..0.009 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,231 read=659
51. 268.290 268.290 ↑ 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.002..0.002 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.010 0.225 ↑ 1.0 42 1

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

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

Seq Scan on t970_claricurrencytypedim t970_claricurrencytypedim_1 (cost=0.00..46.05 rows=42 width=12) (actual time=0.009..0.215 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.002 0.002 ↑ 1.0 6 1

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

66.          

CTE openwont1ids

67. 19.154 85,664.835 ↓ 85.4 17,081 1

HashAggregate (cost=1,115.01..1,117.01 rows=200 width=4) (actual time=85,661.430..85,664.835 rows=17,081 loops=1)

  • Group Key: cte0_5.c1
  • Buffers: shared hit=1,846,441 read=1,128,531, temp read=11,391 written=11,411
68. 85,645.681 85,645.681 ↑ 2.9 17,081 1

CTE Scan on openwont1 cte0_5 (cost=0.00..991.12 rows=49,556 width=4) (actual time=16,581.242..85,645.681 rows=17,081 loops=1)

  • Buffers: shared hit=1,846,441 read=1,128,531, temp read=11,391 written=11,411
69.          

CTE bucketab

70. 4.468 7.011 ↑ 381.2 13 1

HashAggregate (cost=1,734.46..1,784.02 rows=4,956 width=68) (actual time=6.976..7.011 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.543 2.543 ↑ 2.9 17,081 1

CTE Scan on openwont1 cte0_6 (cost=0.00..991.12 rows=49,556 width=64) (actual time=0.002..2.543 rows=17,081 loops=1)

72.          

CTE picklist_3

73. 0.042 0.042 ↑ 1.0 13 1

Seq Scan on t693_opportunitystagenamepicklistdim t693_opportunitystagenamepicklistdim_2 (cost=0.00..7.44 rows=13 width=4) (actual time=0.007..0.042 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.043 115,134.128 ↑ 15.5 4 1

GroupAggregate (cost=60,960,628.71..60,960,632.30 rows=62 width=120) (actual time=115,132.689..115,134.128 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=2,147,022 read=1,907,681, temp read=26,961 written=27,006
76. 3.495 115,133.085 ↓ 33.2 6,645 1

Sort (cost=60,960,628.42..60,960,628.92 rows=200 width=52) (actual time=115,132.674..115,133.085 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=2,147,022 read=1,907,681, temp read=26,961 written=27,006
77. 5.105 115,129.590 ↓ 33.2 6,645 1

Nested Loop Left Join (cost=58,122,171.89..60,960,620.78 rows=200 width=52) (actual time=107,625.957..115,129.590 rows=6,645 loops=1)

  • Buffers: shared hit=2,147,022 read=1,907,681, temp read=26,961 written=27,006
78. 10.823 107,608.944 ↓ 33.2 6,645 1

GroupAggregate (cost=58,107,913.20..58,108,656.14 rows=200 width=12) (actual time=107,592.037..107,608.944 rows=6,645 loops=1)

  • Group Key: aptsta1882_ft0_1.c14214_opportunity__c
  • Buffers: shared hit=1,860,224 read=1,899,036, temp read=26,961 written=27,006
79. 15.318 107,598.121 ↑ 1.9 39,371 1

Sort (cost=58,107,913.20..58,108,098.43 rows=74,094 width=20) (actual time=107,592.028..107,598.121 rows=39,371 loops=1)

  • Sort Key: aptsta1882_ft0_1.c14214_opportunity__c
  • Sort Method: quicksort Memory: 4,022kB
  • Buffers: shared hit=1,860,224 read=1,899,036, temp read=26,961 written=27,006
80. 8.371 107,582.803 ↑ 1.9 39,371 1

Hash Left Join (cost=2,652,130.08..58,101,920.08 rows=74,094 width=20) (actual time=107,297.510..107,582.803 rows=39,371 loops=1)

  • Hash Cond: (aptsta1882_ft0_1.c14213_currencyisocodesid = t970_claricurrencytypedim_2.sid)
  • Buffers: shared hit=1,860,224 read=1,899,036, temp read=26,961 written=27,006
81. 14.507 107,574.204 ↑ 1.9 39,371 1

Hash Semi Join (cost=2,652,083.51..58,097,642.73 rows=74,094 width=16) (actual time=107,297.260..107,574.204 rows=39,371 loops=1)

  • Hash Cond: (aptsta1882_ft0_1.c14254_opportunity__c_stagenamesid = cte0_9.c1)
  • Buffers: shared hit=1,860,200 read=1,899,036, temp read=26,961 written=27,006
82. 122.417 107,553.614 ↑ 1.5 102,191 1

Subquery Scan on aptsta1882_ft0_1 (cost=2,652,083.31..58,096,429.25 rows=148,188 width=20) (actual time=107,243.657..107,553.614 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=1,860,200 read=1,899,031, temp read=26,961 written=27,006
83. 153.718 21,682.248 ↑ 2.2 181,653 1

Unique (cost=2,652,078.81..2,791,375.53 rows=395,168 width=662) (actual time=21,480.549..21,682.248 rows=181,653 loops=1)

  • Buffers: shared hit=457 read=770,500, temp read=15,570 written=15,595
84. 379.293 21,528.530 ↑ 2.2 181,653 1

Sort (cost=2,652,078.81..2,653,066.73 rows=395,168 width=662) (actual time=21,480.547..21,528.530 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=457 read=770,500, temp read=15,570 written=15,595
85. 23.509 21,149.237 ↑ 2.2 181,653 1

Append (cost=19,963.78..2,383,027.78 rows=395,168 width=662) (actual time=147.401..21,149.237 rows=181,653 loops=1)

  • Buffers: shared hit=457 read=770,500
86. 7,296.994 7,421.679 ↑ 26.7 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,963.78..589,007.75 rows=119,677 width=662) (actual time=147.400..7,421.679 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=75 read=284,250
87. 124.685 124.685 ↑ 1.0 946,778 1

Bitmap Index Scan on idx_778_t922_adobe_p2s_team_assignment__cfact_c868_2020u (cost=0.00..19,933.87 rows=950,543 width=0) (actual time=124.685..124.685 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=74 read=2,600
88. 6,790.625 6,895.575 ↑ 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=18,863.40..464,693.68 rows=221,321 width=662) (actual time=113.621..6,895.575 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=167 read=240,510
89. 104.950 104.950 ↑ 1.0 888,380 1

Bitmap Index Scan on idx_778_t922_adobe_p2s_team_assignment__cfact_c868_sept2020u (cost=0.00..18,808.07 rows=896,764 width=0) (actual time=104.950..104.950 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=167 read=2,439
90. 6,651.503 6,808.474 ↑ 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,236.35..1,325,374.67 rows=54,170 width=662) (actual time=173.676..6,808.474 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=215 read=245,740
91. 156.971 156.971 ↓ 1.0 1,368,639 1

Bitmap Index Scan on idx_778_t922_adobe_p2s_team_assignment__cfact_c868_sept2020m (cost=0.00..28,222.81 rows=1,345,837 width=0) (actual time=156.971..156.971 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=215 read=3,757
92.          

SubPlan (for Subquery Scan)

93. 85,668.541 85,668.541 ↓ 85.4 17,081 1

CTE Scan on openwont1ids cte0_8 (cost=0.00..4.00 rows=200 width=4) (actual time=85,661.432..85,668.541 rows=17,081 loops=1)

  • Buffers: shared hit=1,846,441 read=1,128,531, temp read=11,391 written=11,411
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. 30.043 80.408 ↓ 6.3 48,651 1

Hash Semi Join (cost=8,259.00..28,942.94 rows=7,675 width=4) (actual time=10.693..80.408 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.185 49.263 ↑ 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.575..49.263 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.078 8.078 ↑ 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.078..8.078 rows=205,928 loops=1)

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

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 66kB
  • Buffers: shared hit=406
101. 0.878 0.984 ↑ 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.147..0.984 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.106 0.106 ↓ 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.106..0.106 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.006 6.083 ↑ 1.0 6 1

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

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

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

  • Buffers: shared read=5
108. 0.010 0.228 ↑ 1.0 42 1

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

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

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

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

Subquery Scan on aptsta1882_ft1_2 (cost=14,258.40..14,259.79 rows=1 width=12) (actual time=1.131..1.131 rows=0 loops=6,645)

  • Filter: (aptsta1882_ft0_1.c14214_opportunity__c = aptsta1882_ft1_2.c1)
  • Buffers: shared hit=286,793 read=8,645
111. 0.000 7,508.850 ↓ 0.0 0 6,645

Group (cost=14,258.40..14,259.02 rows=62 width=12) (actual time=1.130..1.130 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=286,793 read=8,645
112. 19.935 7,508.850 ↑ 62.0 1 6,645

Sort (cost=14,258.40..14,258.55 rows=62 width=12) (actual time=1.130..1.130 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=286,793 read=8,645
113. 20.262 7,488.915 ↑ 62.0 1 6,645

Nested Loop Left Join (cost=2,694.56..14,256.55 rows=62 width=12) (actual time=1.116..1.127 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=286,793 read=8,645
114. 7.408 7,449.045 ↑ 62.0 1 6,645

Subquery Scan on aptsta1882_ft1_3 (cost=2,694.56..14,171.34 rows=62 width=16) (actual time=1.115..1.121 rows=1 loops=6,645)

  • Filter: ((alternatives: SubPlan 24 or hashed SubPlan 25) OR (alternatives: SubPlan 26 or hashed SubPlan 27))
  • Buffers: shared hit=286,769 read=8,645
115. 46.515 7,395.885 ↑ 82.0 1 6,645

HashAggregate (cost=2,694.56..2,695.38 rows=82 width=662) (actual time=1.111..1.113 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=240,996 read=8,644
116. 13.290 7,349.370 ↑ 82.0 1 6,645

Append (cost=0.43..2,665.86 rows=82 width=662) (actual time=1.075..1.106 rows=1 loops=6,645)

  • Buffers: shared hit=240,996 read=8,644
117. 1,541.640 1,541.640 ↓ 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..621.51 rows=32 width=662) (actual time=0.228..0.232 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,061 read=2,454
118. 471.795 1,501.770 ↓ 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.24..428.14 rows=25 width=662) (actual time=0.226..0.226 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=22,665 read=2,822
119. 1,029.975 1,029.975 ↑ 53.5 2 6,645

Bitmap Index Scan on idx_771_t922_adobe_p2s_team_assignment__cfact_c864_april2020u (cost=0.00..5.23 rows=107 width=0) (actual time=0.155..0.155 rows=2 loops=6,645)

  • Index Cond: (aptsta1882_ft0_1.c14214_opportunity__c = c8649_opportunity__c)
  • Buffers: shared hit=19,081 read=939
120. 265.800 4,292.670 ↑ 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.49..1,615.38 rows=25 width=662) (actual time=0.631..0.646 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=196,270 read=3,368
121. 4,026.870 4,026.870 ↑ 7.5 54 6,645

Bitmap Index Scan on idx_771_t922_adobe_p2s_team_assignment__cfact_c864_april2020m (cost=0.00..11.49 rows=407 width=0) (actual time=0.606..0.606 rows=54 loops=6,645)

  • Index Cond: (aptsta1882_ft0_1.c14214_opportunity__c = c8649_opportunity__c)
  • Buffers: shared hit=18,512 read=3,368
122.          

SubPlan (for Subquery Scan)

123. 13.072 45.752 ↑ 1.0 1 6,536

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

  • Buffers: shared hit=45,773 read=1
124. 19.608 19.608 ↑ 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.003..0.003 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,157 read=1
125. 13.072 13.072 ↑ 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.002..0.002 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.403 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.205 0.205 ↑ 1.0 42 1

Seq Scan on t970_claricurrencytypedim t970_claricurrencytypedim_3 (cost=0.00..46.05 rows=42 width=4) (actual time=0.008..0.205 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.046 0.046 ↑ 1.0 13 1

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

  • Buffers: shared hit=5
139. 0.032 115,134.165 ↑ 15.5 4 1

Subquery Scan on "*SELECT* 1" (cost=0.00..2.01 rows=62 width=96) (actual time=115,132.721..115,134.165 rows=4 loops=1)

  • Buffers: shared hit=2,147,022 read=1,907,681, temp read=26,961 written=27,006
140. 115,134.133 115,134.133 ↑ 15.5 4 1

CTE Scan on bucketd cte0 (cost=0.00..1.24 rows=62 width=120) (actual time=115,132.691..115,134.133 rows=4 loops=1)

  • Buffers: shared hit=2,147,022 read=1,907,681, temp read=26,961 written=27,006
141. 0.020 7.954 ↑ 381.2 13 1

Hash Left Join (cost=1.32..113.72 rows=4,956 width=76) (actual time=7.910..7.954 rows=13 loops=1)

  • Hash Cond: (cte0_1.c6 = t691_opportunityforecast_indicator__cpicklistdim.sid)
  • Buffers: shared read=1
142. 7.018 7.018 ↑ 381.2 13 1

CTE Scan on bucketab cte0_1 (cost=0.00..99.12 rows=4,956 width=68) (actual time=6.978..7.018 rows=13 loops=1)

143. 0.007 0.916 ↑ 1.0 14 1

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

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

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

  • Buffers: shared read=1