explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ehYi

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 124,429.291 ↑ 295.2 17 1

Append (cost=1,259,077,674.53..1,259,077,839.83 rows=5,018 width=96) (actual time=124,420.014..124,429.291 rows=17 loops=1)

  • Buffers: shared hit=2,162,306 read=1,916,975, temp read=26,961 written=27,006
2.          

CTE picklist_0

3. 6.844 6.844 ↑ 1.0 6 1

Seq Scan on t693_opportunitystagenamepicklistdim (cost=0.00..6.63 rows=6 width=4) (actual time=1.736..6.844 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. 39.151 88,463.948 ↑ 2.9 17,081 1

Nested Loop Left Join (cost=73,073,714.54..1,197,340,490.13 rows=49,556 width=34) (actual time=17,776.987..88,463.948 rows=17,081 loops=1)

  • Buffers: shared hit=1,852,726 read=1,137,819, temp read=11,391 written=11,411
8. 27.861 17,572.805 ↑ 2.9 17,081 1

GroupAggregate (cost=73,051,030.26..73,054,338.94 rows=49,556 width=20) (actual time=17,525.311..17,572.805 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,377 read=763,957, temp read=11,391 written=11,411
9. 76.017 17,544.944 ↑ 2.1 90,674 1

Sort (cost=73,051,030.26..73,051,499.11 rows=187,541 width=28) (actual time=17,525.283..17,544.944 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,377 read=763,957, temp read=11,391 written=11,411
10. 19.977 17,468.927 ↑ 2.1 90,674 1

Hash Left Join (cost=1,911,028.77..73,030,114.13 rows=187,541 width=28) (actual time=17,215.806..17,468.927 rows=90,674 loops=1)

  • Hash Cond: (aptsta1882_ft0.c14213_currencyisocodesid = t970_claricurrencytypedim.sid)
  • Buffers: shared hit=284,377 read=763,957, temp read=10,924 written=10,943
11. 101.921 17,435.204 ↑ 2.1 90,674 1

Subquery Scan on aptsta1882_ft0 (cost=1,910,982.20..73,019,358.96 rows=187,541 width=24) (actual time=17,202.041..17,435.204 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,377 read=763,933, temp read=10,924 written=10,943
12. 111.818 15,210.609 ↑ 3.6 140,658 1

Unique (cost=1,910,982.04..2,087,270.81 rows=500,110 width=662) (actual time=15,056.678..15,210.609 rows=140,658 loops=1)

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

Sort (cost=1,910,982.04..1,912,232.31 rows=500,110 width=662) (actual time=15,056.671..15,098.791 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. 27.587 14,663.375 ↑ 3.6 140,658 1

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

  • Buffers: shared hit=284,361 read=742,041
15. 1,644.174 1,735.383 ↑ 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=101.704..1,735.383 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. 91.209 91.209 ↑ 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=91.209..91.209 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. 3,463.024 3,536.797 ↑ 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=89.331..3,536.797 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. 73.773 73.773 ↓ 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=73.773..73.773 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. 429.940 9,363.608 ↑ 2.3 85,174 1

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

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared read=622,019
20. 8,933.668 8,933.668 ↑ 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=5.687..8,933.668 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 (cost=0.71..133.37 rows=1 width=0) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_673_t903_userrolehierarchy_sid_c8530_ancestor_role_id_end_s on t903_userrolehierarchy (cost=0.43..46.01 rows=11 width=4) (never executed)

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

Index Scan using ""idx_665_t900_userroledim_sid_start_stamp_end_stamp_(c8519_porta"" on t900_userroledim (cost=0.28..7.94 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t903_userrolehierarchy.c8529_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
26. 32.953 2,122.624 ↓ 7.4 48,651 1

Hash Join (cost=12,924.13..68,634.36 rows=6,591 width=4) (actual time=1,490.960..2,122.624 rows=48,651 loops=1)

  • Hash Cond: (t903_userrolehierarchy_1.c8529_user_role_id = t900_userroledim_1.sid)
  • Buffers: shared hit=11 read=21,892
27. 603.537 2,056.305 ↑ 1.1 205,928 1

Bitmap Heap Scan on t903_userrolehierarchy t903_userrolehierarchy_1 (cost=6,881.14..62,003.17 rows=224,069 width=8) (actual time=1,457.551..2,056.305 rows=205,928 loops=1)

  • Recheck Cond: ((c8530_ancestor_role_id = 6,405) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=20,309
  • Buffers: shared hit=2 read=21,392
28. 1,452.768 1,452.768 ↑ 1.1 213,212 1

Bitmap Index Scan on idx_674_t903_userrolehierarchy_c8530_ancestor_role_id_end_stamp (cost=0.00..6,825.12 rows=224,069 width=0) (actual time=1,452.768..1,452.768 rows=213,212 loops=1)

  • Index Cond: ((c8530_ancestor_role_id = 6,405) AND (end_stamp = '32503680000000'::bigint))
  • Buffers: shared hit=1 read=1,084
29. 0.200 33.366 ↑ 3.3 950 1

Hash (cost=6,004.31..6,004.31 rows=3,094 width=4) (actual time=33.366..33.366 rows=950 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 66kB
  • Buffers: shared hit=9 read=500
30. 20.261 33.166 ↑ 3.3 950 1

Bitmap Heap Scan on t900_userroledim t900_userroledim_1 (cost=176.67..6,004.31 rows=3,094 width=4) (actual time=14.693..33.166 rows=950 loops=1)

  • Recheck Cond: ((end_stamp = '32503680000000'::bigint) AND ((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=470
  • Buffers: shared hit=9 read=500
31. 12.905 12.905 ↑ 1.4 2,245 1

Bitmap Index Scan on ""idx_665_t900_userroledim_sid_start_stamp_end_stamp_(c8519_porta"" (cost=0.00..175.90 rows=3,117 width=0) (actual time=12.905..12.905 rows=2,245 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
  • Buffers: shared hit=8 read=31
32. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: ((sid = aptsta1882_ft0.c14300_sales_team_member__c) AND (end_stamp = '32503680000000'::bigint))
  • 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 t899_userdim_1 (cost=239.16..13,513.65 rows=4,645 width=4) (never executed)

  • Recheck Cond: ((c8517_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • 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_stamp_start_stamp_nul (cost=0.00..238.00 rows=7,758 width=0) (never executed)

  • Index Cond: ((c8517_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
35. 0.030 13.746 ↑ 1.0 42 1

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

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

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

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

Subquery Scan on aptsta1882_ft1 (cost=22,684.01..22,687.16 rows=1 width=20) (actual time=4.148..4.148 rows=1 loops=17,081)

  • Filter: (aptsta1882_ft0.c14214_opportunity__c = aptsta1882_ft1.c1)
  • Buffers: shared hit=1,568,349 read=373,862
38. 68.324 70,851.988 ↑ 84.0 1 17,081

GroupAggregate (cost=22,684.01..22,686.11 rows=84 width=20) (actual time=4.148..4.148 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,568,349 read=373,862
39. 85.405 70,783.664 ↑ 10.5 8 17,081

Sort (cost=22,684.01..22,684.22 rows=84 width=28) (actual time=4.144..4.144 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,568,349 read=373,862
40. 68.096 70,698.259 ↑ 10.5 8 17,081

Hash Left Join (cost=6,790.25..22,681.32 rows=84 width=28) (actual time=4.004..4.139 rows=8 loops=17,081)

  • Hash Cond: (aptsta1882_ft1_1.c14213_currencyisocodesid = t970_claricurrencytypedim_1.sid)
  • Buffers: shared hit=1,568,349 read=373,862
41. 67.141 70,629.935 ↑ 10.5 8 17,081

Subquery Scan on aptsta1882_ft1_1 (cost=6,743.67..22,629.81 rows=84 width=24) (actual time=4.003..4.135 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,568,325 read=373,862
42. 683.240 67,879.894 ↑ 14.0 8 17,081

HashAggregate (cost=6,743.67..6,744.79 rows=112 width=662) (actual time=3.961..3.974 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. 85.405 67,196.654 ↑ 14.0 8 17,081

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

  • Buffers: shared hit=623,187 read=372,511
44. 12,281.239 12,281.239 ↓ 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.719..0.719 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,581.082 22,581.082 ↑ 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.727..1.322 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,664.800 32,248.928 ↑ 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.851..1.888 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,584.128 18,584.128 ↑ 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.088..1.088 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. 268.290 2,682.900 ↑ 1.0 1 134,145

Nested Loop (cost=0.71..133.37 rows=1 width=0) (actual time=0.020..0.020 rows=1 loops=134,145)

  • Buffers: shared hit=945,138 read=1,351
50. 2,146.320 2,146.320 ↑ 11.0 1 134,145

Index Scan using idx_673_t903_userrolehierarchy_sid_c8530_ancestor_role_id_end_s on t903_userrolehierarchy t903_userrolehierarchy_2 (cost=0.43..46.01 rows=11 width=4) (actual time=0.016..0.016 rows=1 loops=134,145)

  • Index Cond: ((sid = aptsta1882_ft1_1.c14300_sales_team_member__c) AND (c8530_ancestor_role_id = 6,405) AND (end_stamp = '32503680000000'::bigint))
  • Buffers: shared hit=542,323 read=1,351
51. 268.290 268.290 ↑ 1.0 1 134,145

Index Scan using ""idx_665_t900_userroledim_sid_start_stamp_end_stamp_(c8519_porta"" on t900_userroledim t900_userroledim_2 (cost=0.28..7.94 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=134,145)

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

Hash Join (cost=12,924.13..68,634.36 rows=6,591 width=4) (never executed)

  • Hash Cond: (t903_userrolehierarchy_3.c8529_user_role_id = t900_userroledim_3.sid)
53. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t903_userrolehierarchy t903_userrolehierarchy_3 (cost=6,881.14..62,003.17 rows=224,069 width=8) (never executed)

  • Recheck Cond: ((c8530_ancestor_role_id = 6,405) AND (end_stamp = '32503680000000'::bigint))
54. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_674_t903_userrolehierarchy_c8530_ancestor_role_id_end_stamp (cost=0.00..6,825.12 rows=224,069 width=0) (never executed)

  • Index Cond: ((c8530_ancestor_role_id = 6,405) AND (end_stamp = '32503680000000'::bigint))
55. 0.000 0.000 ↓ 0.0 0

Hash (cost=6,004.31..6,004.31 rows=3,094 width=4) (never executed)

56. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t900_userroledim t900_userroledim_3 (cost=176.67..6,004.31 rows=3,094 width=4) (never executed)

  • Recheck Cond: ((end_stamp = '32503680000000'::bigint) AND ((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_stamp_end_stamp_(c8519_porta"" (cost=0.00..175.90 rows=3,117 width=0) (never executed)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
58. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_670_t899_userdim_sid_end_stamp_start_stamp_null on t899_userdim t899_userdim_2 (cost=0.42..8.45 rows=1 width=0) (never executed)

  • Index Cond: ((sid = aptsta1882_ft1_1.c14300_sales_team_member__c) AND (end_stamp = '32503680000000'::bigint))
  • 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 t899_userdim_3 (cost=239.16..13,513.65 rows=4,645 width=4) (never executed)

  • Recheck Cond: ((c8517_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • 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_stamp_start_stamp_nul (cost=0.00..238.00 rows=7,758 width=0) (never executed)

  • Index Cond: ((c8517_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
61. 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
62. 0.218 0.218 ↑ 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.218 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.002 0.002 ↑ 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.002 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. 17.853 88,501.636 ↓ 85.4 17,081 1

HashAggregate (cost=1,115.01..1,117.01 rows=200 width=4) (actual time=88,498.224..88,501.636 rows=17,081 loops=1)

  • Group Key: cte0_5.c1
  • Buffers: shared hit=1,852,726 read=1,137,819, temp read=11,391 written=11,411
68. 88,483.783 88,483.783 ↑ 2.9 17,081 1

CTE Scan on openwont1 cte0_5 (cost=0.00..991.12 rows=49,556 width=4) (actual time=17,776.989..88,483.783 rows=17,081 loops=1)

  • Buffers: shared hit=1,852,726 read=1,137,819, temp read=11,391 written=11,411
69.          

CTE bucketab

70. 4.495 7.091 ↑ 381.2 13 1

HashAggregate (cost=1,734.46..1,784.02 rows=4,956 width=68) (actual time=7.061..7.091 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.596 2.596 ↑ 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.596 rows=17,081 loops=1)

72.          

CTE picklist_3

73. 0.043 0.043 ↑ 1.0 13 1

Seq Scan on t693_opportunitystagenamepicklistdim t693_opportunitystagenamepicklistdim_2 (cost=0.00..7.44 rows=13 width=4) (actual time=0.008..0.043 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.052 124,421.422 ↑ 15.5 4 1

GroupAggregate (cost=61,734,258.97..61,734,262.56 rows=62 width=120) (actual time=124,420.004..124,421.422 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,162,306 read=1,916,974, temp read=26,961 written=27,006
76. 3.649 124,420.370 ↓ 33.2 6,645 1

Sort (cost=61,734,258.68..61,734,259.18 rows=200 width=52) (actual time=124,419.984..124,420.370 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,162,306 read=1,916,974, temp read=26,961 written=27,006
77. 9.259 124,416.721 ↓ 33.2 6,645 1

Nested Loop Left Join (cost=58,865,128.97..61,734,251.04 rows=200 width=52) (actual time=116,810.618..124,416.721 rows=6,645 loops=1)

  • Buffers: shared hit=2,162,306 read=1,916,974, temp read=26,961 written=27,006
78. 10.960 116,778.955 ↓ 33.2 6,645 1

GroupAggregate (cost=58,850,716.14..58,851,459.08 rows=200 width=12) (actual time=116,761.827..116,778.955 rows=6,645 loops=1)

  • Group Key: aptsta1882_ft0_1.c14214_opportunity__c
  • Buffers: shared hit=1,875,109 read=1,908,324, temp read=26,961 written=27,006
79. 16.024 116,767.995 ↑ 1.9 39,371 1

Sort (cost=58,850,716.14..58,850,901.38 rows=74,094 width=20) (actual time=116,761.817..116,767.995 rows=39,371 loops=1)

  • Sort Key: aptsta1882_ft0_1.c14214_opportunity__c
  • Sort Method: quicksort Memory: 4,022kB
  • Buffers: shared hit=1,875,109 read=1,908,324, temp read=26,961 written=27,006
80. 8.482 116,751.971 ↑ 1.9 39,371 1

Hash Left Join (cost=2,652,130.08..58,844,723.02 rows=74,094 width=20) (actual time=116,461.947..116,751.971 rows=39,371 loops=1)

  • Hash Cond: (aptsta1882_ft0_1.c14213_currencyisocodesid = t970_claricurrencytypedim_2.sid)
  • Buffers: shared hit=1,875,109 read=1,908,324, temp read=26,961 written=27,006
81. 14.051 116,743.251 ↑ 1.9 39,371 1

Hash Semi Join (cost=2,652,083.51..58,840,445.68 rows=74,094 width=16) (actual time=116,461.690..116,743.251 rows=39,371 loops=1)

  • Hash Cond: (aptsta1882_ft0_1.c14254_opportunity__c_stagenamesid = cte0_9.c1)
  • Buffers: shared hit=1,875,085 read=1,908,324, temp read=26,961 written=27,006
82. 127.597 116,722.336 ↑ 1.5 102,191 1

Subquery Scan on aptsta1882_ft0_1 (cost=2,652,083.31..58,839,232.19 rows=148,188 width=20) (actual time=116,406.434..116,722.336 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,875,085 read=1,908,319, temp read=26,961 written=27,006
83. 155.626 27,990.242 ↑ 2.2 181,653 1

Unique (cost=2,652,078.81..2,791,375.53 rows=395,168 width=662) (actual time=27,785.859..27,990.242 rows=181,653 loops=1)

  • Buffers: shared hit=457 read=770,500, temp read=15,570 written=15,595
84. 367.521 27,834.616 ↑ 2.2 181,653 1

Sort (cost=2,652,078.81..2,653,066.73 rows=395,168 width=662) (actual time=27,785.858..27,834.616 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.276 27,467.095 ↑ 2.2 181,653 1

Append (cost=19,963.78..2,383,027.78 rows=395,168 width=662) (actual time=135.998..27,467.095 rows=181,653 loops=1)

  • Buffers: shared hit=457 read=770,500
86. 10,937.812 11,055.765 ↑ 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=135.997..11,055.765 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. 117.953 117.953 ↑ 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=117.953..117.953 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. 8,254.302 8,380.635 ↑ 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=185.251..8,380.635 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. 126.333 126.333 ↑ 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=126.333..126.333 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. 7,862.323 8,007.419 ↑ 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=154.823..8,007.419 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. 145.096 145.096 ↓ 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=145.096..145.096 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. 88,505.360 88,505.360 ↓ 85.4 17,081 1

CTE Scan on openwont1ids cte0_8 (cost=0.00..4.00 rows=200 width=4) (actual time=88,498.225..88,505.360 rows=17,081 loops=1)

  • Buffers: shared hit=1,852,726 read=1,137,819, temp read=11,391 written=11,411
94. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.71..133.37 rows=1 width=0) (never executed)

95. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_673_t903_userrolehierarchy_sid_c8530_ancestor_role_id_end_s on t903_userrolehierarchy t903_userrolehierarchy_4 (cost=0.43..46.01 rows=11 width=4) (never executed)

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

Index Scan using ""idx_665_t900_userroledim_sid_start_stamp_end_stamp_(c8519_porta"" on t900_userroledim t900_userroledim_4 (cost=0.28..7.94 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t903_userrolehierarchy_4.c8529_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
97. 31.713 99.137 ↓ 7.4 48,651 1

Hash Join (cost=12,924.13..68,634.36 rows=6,591 width=4) (actual time=15.996..99.137 rows=48,651 loops=1)

  • Hash Cond: (t903_userrolehierarchy_5.c8529_user_role_id = t900_userroledim_5.sid)
  • Buffers: shared hit=21,902
98. 54.202 66.029 ↑ 1.1 205,928 1

Bitmap Heap Scan on t903_userrolehierarchy t903_userrolehierarchy_5 (cost=6,881.14..62,003.17 rows=224,069 width=8) (actual time=14.579..66.029 rows=205,928 loops=1)

  • Recheck Cond: ((c8530_ancestor_role_id = 6,405) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=20,309
  • Buffers: shared hit=21,394
99. 11.827 11.827 ↑ 1.1 213,212 1

Bitmap Index Scan on idx_674_t903_userrolehierarchy_c8530_ancestor_role_id_end_stamp (cost=0.00..6,825.12 rows=224,069 width=0) (actual time=11.827..11.827 rows=213,212 loops=1)

  • Index Cond: ((c8530_ancestor_role_id = 6,405) AND (end_stamp = '32503680000000'::bigint))
  • Buffers: shared hit=1,085
100. 0.120 1.395 ↑ 3.3 950 1

Hash (cost=6,004.31..6,004.31 rows=3,094 width=4) (actual time=1.395..1.395 rows=950 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 66kB
  • Buffers: shared hit=508
101. 1.030 1.275 ↑ 3.3 950 1

Bitmap Heap Scan on t900_userroledim t900_userroledim_5 (cost=176.67..6,004.31 rows=3,094 width=4) (actual time=0.301..1.275 rows=950 loops=1)

  • Recheck Cond: ((end_stamp = '32503680000000'::bigint) AND ((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=470
  • Buffers: shared hit=508
102. 0.245 0.245 ↑ 1.4 2,236 1

Bitmap Index Scan on ""idx_665_t900_userroledim_sid_start_stamp_end_stamp_(c8519_porta"" (cost=0.00..175.90 rows=3,117 width=0) (actual time=0.245..0.245 rows=2,236 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
  • Buffers: shared hit=38
103. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_670_t899_userdim_sid_end_stamp_start_stamp_null on t899_userdim t899_userdim_4 (cost=0.42..8.45 rows=1 width=0) (never executed)

  • Index Cond: ((sid = aptsta1882_ft0_1.c14300_sales_team_member__c) AND (end_stamp = '32503680000000'::bigint))
  • 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 t899_userdim_5 (cost=239.16..13,513.65 rows=4,645 width=4) (never executed)

  • Recheck Cond: ((c8517_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • 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_stamp_start_stamp_nul (cost=0.00..238.00 rows=7,758 width=0) (never executed)

  • Index Cond: ((c8517_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
106. 0.011 6.864 ↑ 1.0 6 1

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

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

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

  • Buffers: shared read=5
108. 0.015 0.238 ↑ 1.0 42 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=24
109. 0.223 0.223 ↑ 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.223 rows=42 loops=1)

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

Subquery Scan on aptsta1882_ft1_2 (cost=14,412.54..14,413.93 rows=1 width=12) (actual time=1.148..1.148 rows=0 loops=6,645)

  • Filter: (aptsta1882_ft0_1.c14214_opportunity__c = aptsta1882_ft1_2.c1)
  • Buffers: shared hit=287,192 read=8,650
111. 6.645 7,628.460 ↓ 0.0 0 6,645

Group (cost=14,412.54..14,413.16 rows=62 width=12) (actual time=1.148..1.148 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=287,192 read=8,650
112. 13.290 7,621.815 ↑ 62.0 1 6,645

Sort (cost=14,412.54..14,412.69 rows=62 width=12) (actual time=1.147..1.147 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=287,192 read=8,650
113. 26.907 7,608.525 ↑ 62.0 1 6,645

Nested Loop Left Join (cost=2,694.56..14,410.69 rows=62 width=12) (actual time=1.132..1.145 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=287,192 read=8,650
114. 7.626 7,562.010 ↑ 62.0 1 6,645

Subquery Scan on aptsta1882_ft1_3 (cost=2,694.56..14,325.48 rows=62 width=16) (actual time=1.131..1.138 rows=1 loops=6,645)

  • Filter: ((alternatives: SubPlan 24 or hashed SubPlan 25) OR (alternatives: SubPlan 26 or hashed SubPlan 27))
  • Buffers: shared hit=287,168 read=8,650
115. 39.870 7,495.560 ↑ 82.0 1 6,645

HashAggregate (cost=2,694.56..2,695.38 rows=82 width=662) (actual time=1.127..1.128 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. 19.935 7,455.690 ↑ 82.0 1 6,645

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

  • Buffers: shared hit=240,996 read=8,644
117. 1,694.475 1,694.475 ↓ 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.251..0.255 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. 411.990 1,548.285 ↓ 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.233..0.233 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,136.295 1,136.295 ↑ 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.171..0.171 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,192.995 ↑ 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.616..0.631 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. 3,927.195 3,927.195 ↑ 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.591..0.591 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 58.824 ↑ 1.0 1 6,536

Nested Loop (cost=0.71..133.37 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=6,536)

  • Buffers: shared hit=46,172 read=6
124. 32.680 32.680 ↑ 11.0 1 6,536

Index Scan using idx_673_t903_userrolehierarchy_sid_c8530_ancestor_role_id_end_s on t903_userrolehierarchy t903_userrolehierarchy_6 (cost=0.43..46.01 rows=11 width=4) (actual time=0.005..0.005 rows=1 loops=6,536)

  • Index Cond: ((sid = aptsta1882_ft1_3.c14300_sales_team_member__c) AND (c8530_ancestor_role_id = 6,405) AND (end_stamp = '32503680000000'::bigint))
  • Buffers: shared hit=26,563 read=6
125. 13.072 13.072 ↑ 1.0 1 6,536

Index Scan using ""idx_665_t900_userroledim_sid_start_stamp_end_stamp_(c8519_porta"" on t900_userroledim t900_userroledim_6 (cost=0.28..7.94 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=6,536)

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

Hash Join (cost=12,924.13..68,634.36 rows=6,591 width=4) (never executed)

  • Hash Cond: (t903_userrolehierarchy_7.c8529_user_role_id = t900_userroledim_7.sid)
127. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t903_userrolehierarchy t903_userrolehierarchy_7 (cost=6,881.14..62,003.17 rows=224,069 width=8) (never executed)

  • Recheck Cond: ((c8530_ancestor_role_id = 6,405) AND (end_stamp = '32503680000000'::bigint))
128. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_674_t903_userrolehierarchy_c8530_ancestor_role_id_end_stamp (cost=0.00..6,825.12 rows=224,069 width=0) (never executed)

  • Index Cond: ((c8530_ancestor_role_id = 6,405) AND (end_stamp = '32503680000000'::bigint))
129. 0.000 0.000 ↓ 0.0 0

Hash (cost=6,004.31..6,004.31 rows=3,094 width=4) (never executed)

130. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t900_userroledim t900_userroledim_7 (cost=176.67..6,004.31 rows=3,094 width=4) (never executed)

  • Recheck Cond: ((end_stamp = '32503680000000'::bigint) AND ((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_stamp_end_stamp_(c8519_porta"" (cost=0.00..175.90 rows=3,117 width=0) (never executed)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
132. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_670_t899_userdim_sid_end_stamp_start_stamp_null on t899_userdim t899_userdim_6 (cost=0.42..8.45 rows=1 width=0) (never executed)

  • Index Cond: ((sid = aptsta1882_ft1_3.c14300_sales_team_member__c) AND (end_stamp = '32503680000000'::bigint))
  • 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 t899_userdim_7 (cost=239.16..13,513.65 rows=4,645 width=4) (never executed)

  • Recheck Cond: ((c8517_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • 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_stamp_start_stamp_nul (cost=0.00..238.00 rows=7,758 width=0) (never executed)

  • Index Cond: ((c8517_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
135. 19.405 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.203 0.203 ↑ 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.203 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.047 0.047 ↑ 1.0 13 1

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

  • Buffers: shared hit=5
139. 0.009 124,421.436 ↑ 15.5 4 1

Subquery Scan on "*SELECT* 1" (cost=0.00..2.01 rows=62 width=96) (actual time=124,420.014..124,421.436 rows=4 loops=1)

  • Buffers: shared hit=2,162,306 read=1,916,974, temp read=26,961 written=27,006
140. 124,421.427 124,421.427 ↑ 15.5 4 1

CTE Scan on bucketd cte0 (cost=0.00..1.24 rows=62 width=120) (actual time=124,420.007..124,421.427 rows=4 loops=1)

  • Buffers: shared hit=2,162,306 read=1,916,974, temp read=26,961 written=27,006
141. 0.017 7.851 ↑ 381.2 13 1

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

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

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

143. 0.006 0.733 ↑ 1.0 14 1

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

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

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

  • Buffers: shared read=1