explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LWCV

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 137,258.687 ↑ 847.8 17 1

Append (cost=7,972,186,821.86..7,972,187,294.16 rows=14,412 width=96) (actual time=137,249.421..137,258.687 rows=17 loops=1)

  • Buffers: shared hit=1,115,027 read=613,676, temp read=467 written=468
2.          

CTE picklist_0

3. 4.836 4.836 ↑ 1.0 6 1

Seq Scan on t693_opportunitystagenamepicklistdim (cost=0.00..6.63 rows=6 width=4) (actual time=0.750..4.836 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.039 0.039 ↑ 1.0 7 1

Seq Scan on t693_opportunitystagenamepicklistdim t693_opportunitystagenamepicklistdim_1 (cost=0.00..6.74 rows=7 width=4) (actual time=0.012..0.039 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. 44.849 124,194.184 ↑ 8.4 17,081 1

Nested Loop Left Join (cost=168,753,720.84..6,572,162,445.41 rows=143,640 width=34) (actual time=9,828.765..124,194.184 rows=17,081 loops=1)

  • Buffers: shared hit=1,008,607 read=580,476, temp read=467 written=468
8. 28.588 9,809.118 ↑ 8.4 17,081 1

GroupAggregate (cost=168,709,143.19..168,712,868.35 rows=143,640 width=20) (actual time=9,759.109..9,809.118 rows=17,081 loops=1)

  • Group Key: t922_adobe_product_to_sales_team_assignment__cfact_t1.c8649_opportunity__c, t922_adobe_product_to_sales_team_assignment__cfact_t1.c8695_opportunity__c_forecast_indicator__csid, t922_adobe_product_to_sales_team_assignment__cfact_t1.c8682_opportunity__c_stagenamesid
  • Buffers: shared hit=230 read=336,083, temp read=467 written=468
9. 96.140 9,780.530 ↑ 1.7 90,674 1

Sort (cost=168,709,143.19..168,709,524.65 rows=152,584 width=28) (actual time=9,759.081..9,780.530 rows=90,674 loops=1)

  • Sort Key: t922_adobe_product_to_sales_team_assignment__cfact_t1.c8649_opportunity__c, t922_adobe_product_to_sales_team_assignment__cfact_t1.c8695_opportunity__c_forecast_indicator__csid, t922_adobe_product_to_sales_team_assignment__cfact_t1.c8682_opportunity__c_stagenamesid
  • Sort Method: external merge Disk: 3,736kB
  • Buffers: shared hit=230 read=336,083, temp read=467 written=468
10. 23.597 9,684.390 ↑ 1.7 90,674 1

Hash Left Join (cost=28,987.95..168,692,352.28 rows=152,584 width=28) (actual time=1,424.367..9,684.390 rows=90,674 loops=1)

  • Hash Cond: (t922_adobe_product_to_sales_team_assignment__cfact_t1.c8648_currencyisocodesid = t970_claricurrencytypedim.sid)
  • Buffers: shared hit=230 read=336,083
11. 8,265.571 9,645.729 ↑ 1.7 90,674 1

Bitmap Heap Scan on t922_adobe_product_to_sales_team_assignment__cfact_t1 (cost=28,941.38..168,682,445.57 rows=152,584 width=24) (actual time=1,409.283..9,645.729 rows=90,674 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,458,829
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 9)) AND ((alternatives: SubPlan 5 or hashed SubPlan 6) OR (alternatives: SubPlan 7 or hashed SubPlan 8)))
  • Rows Removed by Filter: 1,174,397
  • Heap Blocks: exact=52,697 lossy=265,626
  • Buffers: shared hit=230 read=336,059
12. 180.355 180.355 ↓ 1.1 1,265,071 1

Bitmap Index Scan on idx_777_t922_adobe_p2s_team_assignment__cfact_c868_t1 (cost=0.00..28,903.08 rows=1,200,264 width=0) (actual time=180.355..180.355 rows=1,265,071 loops=1)

  • Index Cond: ((c8688_opportunity__c_closedate >= 20,200,229) AND (c8688_opportunity__c_closedate <= 20,200,529))
  • Buffers: shared hit=219 read=4,440
13.          

SubPlan (for Bitmap Heap Scan)

14. 0.043 0.043 ↑ 1.0 7 1

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

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

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

16. 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 = t922_adobe_product_to_sales_team_assignment__cfact_t1.c8713_sales_team_member__c) AND (c8530_ancestor_role_id = 6,405))
17. 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))
18. 30.762 1,199.760 ↓ 6.3 48,651 1

Hash Semi Join (cost=8,259.00..28,942.94 rows=7,675 width=4) (actual time=900.254..1,199.760 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
19. 271.444 1,145.108 ↑ 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=876.325..1,145.108 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
20. 873.664 873.664 ↑ 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=873.664..873.664 rows=205,928 loops=1)

  • Index Cond: (c8530_ancestor_role_id = 6,405)
  • Buffers: shared hit=1 read=706
21. 0.159 23.890 ↑ 2.2 950 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 66kB
  • Buffers: shared hit=4 read=402
22. 14.439 23.731 ↑ 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=10.396..23.731 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
23. 9.292 9.292 ↓ 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=9.292..9.292 rows=2,190 loops=1)

  • Buffers: shared hit=3 read=9
24. 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 = t922_adobe_product_to_sales_team_assignment__cfact_t1.c8713_sales_team_member__c)
  • Filter: ((NOT deleted) AND (c8517_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c8507_usertype = 'Standard'::text))
25. 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))
26. 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)
27. 0.026 15.064 ↑ 1.0 42 1

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

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

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

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 1,722
  • Buffers: shared read=24
29. 17.081 114,340.214 ↑ 1.0 1 17,081

Subquery Scan on aptsta1882_ft1 (cost=44,577.38..44,579.82 rows=1 width=20) (actual time=6.694..6.694 rows=1 loops=17,081)

  • Filter: (t922_adobe_product_to_sales_team_assignment__cfact_t1.c8649_opportunity__c = aptsta1882_ft1.c1)
  • Buffers: shared hit=1,008,377 read=244,393
30. 68.324 114,323.133 ↑ 65.0 1 17,081

GroupAggregate (cost=44,577.38..44,579.01 rows=65 width=20) (actual time=6.693..6.693 rows=1 loops=17,081)

  • Group Key: t922_adobe_product_to_sales_team_assignment__cfact_t2.c8649_opportunity__c, t922_adobe_product_to_sales_team_assignment__cfact_t2.c8688_opportunity__c_closedate, t922_adobe_product_to_sales_team_assignment__cfact_t2.c8682_opportunity__c_stagenamesid
  • Buffers: shared hit=1,008,377 read=244,393
31. 119.567 114,254.809 ↑ 8.1 8 17,081

Sort (cost=44,577.38..44,577.54 rows=65 width=28) (actual time=6.689..6.689 rows=8 loops=17,081)

  • Sort Key: t922_adobe_product_to_sales_team_assignment__cfact_t2.c8688_opportunity__c_closedate, t922_adobe_product_to_sales_team_assignment__cfact_t2.c8682_opportunity__c_stagenamesid
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1,008,377 read=244,393
32. 485.777 114,135.242 ↑ 8.1 8 17,081

Nested Loop Left Join (cost=0.43..44,575.42 rows=65 width=28) (actual time=2.502..6.682 rows=8 loops=17,081)

  • Join Filter: (t922_adobe_product_to_sales_team_assignment__cfact_t2.c8648_currencyisocodesid = t970_claricurrencytypedim_1.sid)
  • Rows Removed by Join Filter: 322
  • Buffers: shared hit=1,008,377 read=244,393
33. 111,369.000 113,247.030 ↑ 8.1 8 17,081

Index Scan using idx_771_t922_adobe_p2s_team_assignment__cfact_c864_t2 on t922_adobe_product_to_sales_team_assignment__cfact_t2 (cost=0.43..44,488.32 rows=65 width=24) (actual time=2.494..6.630 rows=8 loops=17,081)

  • Index Cond: (t922_adobe_product_to_sales_team_assignment__cfact_t1.c8649_opportunity__c = c8649_opportunity__c)
  • Filter: ((NOT deleted) AND (start_stamp <= '1601535599999'::bigint) AND (end_stamp > '1601535599999'::bigint) AND ((alternatives: SubPlan 10 or hashed SubPlan 11) OR (alternatives: SubPlan 12 or hashed SubPlan 13)))
  • Rows Removed by Filter: 64
  • Buffers: shared hit=1,008,353 read=244,393
34.          

SubPlan (for Index Scan)

35. 268.290 1,878.030 ↑ 1.0 1 134,145

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

  • Buffers: shared hit=938,858 read=659
36. 1,341.450 1,341.450 ↑ 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.010..0.010 rows=1 loops=134,145)

  • Index Cond: ((sid = t922_adobe_product_to_sales_team_assignment__cfact_t2.c8713_sales_team_member__c) AND (c8530_ancestor_role_id = 6,405))
  • Buffers: shared hit=536,231 read=659
37. 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
38. 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)
39. 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)
40. 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)
41. 0.000 0.000 ↓ 0.0 0

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

42. 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))
43. 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)

44. 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 = t922_adobe_product_to_sales_team_assignment__cfact_t2.c8713_sales_team_member__c)
  • Filter: ((NOT deleted) AND (c8517_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c8507_usertype = 'Standard'::text))
45. 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))
46. 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)
47. 402.225 402.435 ↑ 1.0 42 134,145

Materialize (cost=0.00..46.26 rows=42 width=12) (actual time=0.000..0.003 rows=42 loops=134,145)

  • Buffers: shared hit=24
48. 0.210 0.210 ↑ 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.210 rows=42 loops=1)

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

SubPlan (for Nested Loop Left Join)

50. 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)

51. 0.001 0.001 ↑ 1.0 6 1

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

52.          

CTE openwont1ids

53. 21.187 124,238.254 ↓ 85.4 17,081 1

HashAggregate (cost=3,231.90..3,233.90 rows=200 width=4) (actual time=124,235.028..124,238.254 rows=17,081 loops=1)

  • Group Key: cte0_5.c1
  • Buffers: shared hit=1,008,607 read=580,476, temp read=467 written=468
54. 124,217.067 124,217.067 ↑ 8.4 17,081 1

CTE Scan on openwont1 cte0_5 (cost=0.00..2,872.80 rows=143,640 width=4) (actual time=9,828.767..124,217.067 rows=17,081 loops=1)

  • Buffers: shared hit=1,008,607 read=580,476, temp read=467 written=468
55.          

CTE bucketab

56. 4.424 6.979 ↑ 1,104.9 13 1

HashAggregate (cost=5,027.40..5,171.04 rows=14,364 width=68) (actual time=6.925..6.979 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
57. 2.555 2.555 ↑ 8.4 17,081 1

CTE Scan on openwont1 cte0_6 (cost=0.00..2,872.80 rows=143,640 width=64) (actual time=0.002..2.555 rows=17,081 loops=1)

58.          

CTE picklist_3

59. 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
60.          

CTE bucketd

61. 1.141 137,250.949 ↑ 12.0 4 1

GroupAggregate (cost=1,400,015,353.37..1,400,015,950.70 rows=48 width=120) (actual time=137,249.413..137,250.949 rows=4 loops=1)

  • Group Key: '1585810799999'::bigint, (CASE WHEN (aptsta1882_ft1_1.c1 IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 18)) THEN CASE WHEN (aptsta1882_ft1_1.c3 > 20200529) THEN 'pulledIn'::text WHEN (aptsta1882_ft1_1.c3 < 20200229) THEN 'pushedIn'::text ELSE 'other'::text END ELSE 'other'::text END)
  • Buffers: shared hit=1,115,027 read=613,675, temp read=467 written=468
62. 2.484 137,249.808 ↑ 7.2 6,645 1

Sort (cost=1,400,015,353.08..1,400,015,472.37 rows=47,719 width=52) (actual time=137,249.400..137,249.808 rows=6,645 loops=1)

  • Sort Key: (CASE WHEN (aptsta1882_ft1_1.c1 IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 18)) THEN CASE WHEN (aptsta1882_ft1_1.c3 > 20200529) THEN 'pulledIn'::text WHEN (aptsta1882_ft1_1.c3 < 20200229) THEN 'pushedIn'::text ELSE 'other'::text END ELSE 'other'::text END)
  • Sort Method: quicksort Memory: 712kB
  • Buffers: shared hit=1,115,027 read=613,675, temp read=467 written=468
63. 6.398 137,247.324 ↑ 7.2 6,645 1

Nested Loop Left Join (cost=104,599,826.59..1,400,011,644.77 rows=47,719 width=52) (actual time=132,715.792..137,247.324 rows=6,645 loops=1)

  • Buffers: shared hit=1,115,027 read=613,675, temp read=467 written=468
64. 8.744 132,708.990 ↑ 7.2 6,645 1

GroupAggregate (cost=104,572,680.20..104,573,932.15 rows=47,719 width=12) (actual time=132,695.277..132,708.990 rows=6,645 loops=1)

  • Group Key: t922_adobe_product_to_sales_team_assignment__cfact_t2_1.c8649_opportunity__c
  • Buffers: shared hit=1,045,401 read=606,066, temp read=467 written=468
65. 18.131 132,700.246 ↑ 2.0 39,371 1

Sort (cost=104,572,680.20..104,572,873.89 rows=77,476 width=20) (actual time=132,695.264..132,700.246 rows=39,371 loops=1)

  • Sort Key: t922_adobe_product_to_sales_team_assignment__cfact_t2_1.c8649_opportunity__c
  • Sort Method: quicksort Memory: 4,022kB
  • Buffers: shared hit=1,045,401 read=606,066, temp read=467 written=468
66. 118.315 132,682.115 ↑ 2.0 39,371 1

Nested Loop Left Join (cost=5.07..104,566,388.58 rows=77,476 width=20) (actual time=129,605.020..132,682.115 rows=39,371 loops=1)

  • Join Filter: (t922_adobe_product_to_sales_team_assignment__cfact_t2_1.c8648_currencyisocodesid = t970_claricurrencytypedim_2.sid)
  • Rows Removed by Join Filter: 1,614,211
  • Buffers: shared hit=1,045,401 read=606,066, temp read=467 written=468
67. 5.791 132,445.687 ↑ 2.0 39,371 1

Nested Loop (cost=5.07..104,517,532.55 rows=77,476 width=16) (actual time=129,604.808..132,445.687 rows=39,371 loops=1)

  • Buffers: shared hit=1,045,377 read=606,066, temp read=467 written=468
68. 0.021 4.866 ↑ 1.0 6 1

HashAggregate (cost=0.14..0.20 rows=6 width=4) (actual time=4.850..4.866 rows=6 loops=1)

  • Group Key: cte0_9.c1
  • Buffers: shared read=5
69. 4.845 4.845 ↑ 1.0 6 1

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

  • Buffers: shared read=5
70. 8,115.317 132,435.030 ↑ 2.0 6,562 6

Index Scan using idx_778_t922_adobe_p2s_team_assignment__cfact_c868_t2 on t922_adobe_product_to_sales_team_assignment__cfact_t2 t922_adobe_product_to_sales_team_assignment__cfact_t2_1 (cost=4.93..17,419,459.60 rows=12,913 width=20) (actual time=21,606.937..22,072.505 rows=6,562 loops=6)

  • Index Cond: ((c8688_opportunity__c_closedate >= 20,200,229) AND (c8688_opportunity__c_closedate <= 20,200,529) AND (c8682_opportunity__c_stagenamesid = cte0_9.c1))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 23)) AND ((alternatives: SubPlan 19 or hashed SubPlan 20) OR (alternatives: SubPlan 21 or hashed SubPlan 22)))
  • Rows Removed by Filter: 82,517
  • Buffers: shared hit=1,045,377 read=606,061, temp read=467 written=468
71.          

SubPlan (for Index Scan)

72. 124,241.771 124,241.771 ↓ 85.4 17,081 1

CTE Scan on openwont1ids cte0_8 (cost=0.00..4.00 rows=200 width=4) (actual time=124,235.030..124,241.771 rows=17,081 loops=1)

  • Buffers: shared hit=1,008,607 read=580,476, temp read=467 written=468
73. 0.000 0.000 ↓ 0.0 0

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

74. 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 = t922_adobe_product_to_sales_team_assignment__cfact_t2_1.c8713_sales_team_member__c) AND (c8530_ancestor_role_id = 6,405))
75. 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))
76. 28.836 77.942 ↓ 6.3 48,651 1

Hash Semi Join (cost=8,259.00..28,942.94 rows=7,675 width=4) (actual time=10.776..77.942 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
77. 39.856 48.056 ↑ 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.698..48.056 rows=205,928 loops=1)

  • Recheck Cond: (c8530_ancestor_role_id = 6,405)
  • Heap Blocks: exact=12,189
  • Buffers: shared hit=12,896
78. 8.200 8.200 ↑ 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.200..8.200 rows=205,928 loops=1)

  • Index Cond: (c8530_ancestor_role_id = 6,405)
  • Buffers: shared hit=707
79. 0.116 1.050 ↑ 2.2 950 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 66kB
  • Buffers: shared hit=406
80. 0.833 0.934 ↑ 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.144..0.934 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
81. 0.101 0.101 ↓ 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.101..0.101 rows=2,190 loops=1)

  • Buffers: shared hit=12
82. 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 = t922_adobe_product_to_sales_team_assignment__cfact_t2_1.c8713_sales_team_member__c)
  • Filter: ((NOT deleted) AND (c8517_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c8507_usertype = 'Standard'::text))
83. 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))
84. 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)
85. 117.914 118.113 ↑ 1.0 42 39,371

Materialize (cost=0.00..46.26 rows=42 width=12) (actual time=0.000..0.003 rows=42 loops=39,371)

  • Buffers: shared hit=24
86. 0.199 0.199 ↑ 1.0 42 1

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

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

Subquery Scan on aptsta1882_ft1_1 (cost=27,146.10..27,147.18 rows=1 width=12) (actual time=0.682..0.682 rows=0 loops=6,645)

  • Filter: (t922_adobe_product_to_sales_team_assignment__cfact_t2_1.c8649_opportunity__c = aptsta1882_ft1_1.c1)
  • Buffers: shared hit=69,621 read=7,609
88. 6.645 4,531.890 ↓ 0.0 0 6,645

Group (cost=27,146.10..27,146.58 rows=48 width=12) (actual time=0.682..0.682 rows=0 loops=6,645)

  • Group Key: t922_adobe_product_to_sales_team_assignment__cfact_t1_1.c8649_opportunity__c, t922_adobe_product_to_sales_team_assignment__cfact_t1_1.c8682_opportunity__c_stagenamesid, t922_adobe_product_to_sales_team_assignment__cfact_t1_1.c8688_opportunity__c_closedate
  • Buffers: shared hit=69,621 read=7,609
89. 6.645 4,525.245 ↑ 48.0 1 6,645

Sort (cost=27,146.10..27,146.22 rows=48 width=12) (actual time=0.681..0.681 rows=1 loops=6,645)

  • Sort Key: t922_adobe_product_to_sales_team_assignment__cfact_t1_1.c8682_opportunity__c_stagenamesid, t922_adobe_product_to_sales_team_assignment__cfact_t1_1.c8688_opportunity__c_closedate
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=69,621 read=7,609
90. 26.907 4,518.600 ↑ 48.0 1 6,645

Nested Loop Left Join (cost=0.43..27,144.76 rows=48 width=12) (actual time=0.417..0.680 rows=1 loops=6,645)

  • Join Filter: (t922_adobe_product_to_sales_team_assignment__cfact_t1_1.c8648_currencyisocodesid = t970_claricurrencytypedim_3.sid)
  • Rows Removed by Join Filter: 40
  • Buffers: shared hit=69,621 read=7,609
91. 4,426.333 4,472.085 ↑ 48.0 1 6,645

Index Scan using idx_771_t922_adobe_p2s_team_assignment__cfact_c864_t1 on t922_adobe_product_to_sales_team_assignment__cfact_t1 t922_adobe_product_to_sales_team_assignment__cfact_t1_1 (cost=0.43..27,068.37 rows=48 width=16) (actual time=0.415..0.673 rows=1 loops=6,645)

  • Index Cond: (t922_adobe_product_to_sales_team_assignment__cfact_t2_1.c8649_opportunity__c = c8649_opportunity__c)
  • Filter: ((NOT deleted) AND ((alternatives: SubPlan 24 or hashed SubPlan 25) OR (alternatives: SubPlan 26 or hashed SubPlan 27)))
  • Rows Removed by Filter: 7
  • Buffers: shared hit=69,597 read=7,609
92.          

SubPlan (for Index Scan)

93. 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
94. 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 = t922_adobe_product_to_sales_team_assignment__cfact_t1_1.c8713_sales_team_member__c) AND (c8530_ancestor_role_id = 6,405))
  • Buffers: shared hit=26,157 read=1
95. 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
96. 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)
97. 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)
98. 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)
99. 0.000 0.000 ↓ 0.0 0

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

100. 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))
101. 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)

102. 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 = t922_adobe_product_to_sales_team_assignment__cfact_t1_1.c8713_sales_team_member__c)
  • Filter: ((NOT deleted) AND (c8517_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c8507_usertype = 'Standard'::text))
103. 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))
104. 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)
105. 18.092 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
106. 1.516 1.516 ↑ 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..1.516 rows=42 loops=1)

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

SubPlan (for Nested Loop Left Join)

108. 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
109. 0.007 137,250.961 ↑ 12.0 4 1

Subquery Scan on "*SELECT* 1" (cost=0.00..1.56 rows=48 width=96) (actual time=137,249.420..137,250.961 rows=4 loops=1)

  • Buffers: shared hit=1,115,027 read=613,675, temp read=467 written=468
110. 137,250.954 137,250.954 ↑ 12.0 4 1

CTE Scan on bucketd cte0 (cost=0.00..0.96 rows=48 width=120) (actual time=137,249.415..137,250.954 rows=4 loops=1)

  • Buffers: shared hit=1,115,027 read=613,675, temp read=467 written=468
111. 0.023 7.722 ↑ 1,104.9 13 1

Hash Left Join (cost=1.32..327.10 rows=14,364 width=76) (actual time=7.657..7.722 rows=13 loops=1)

  • Hash Cond: (cte0_1.c6 = t691_opportunityforecast_indicator__cpicklistdim.sid)
  • Buffers: shared read=1
112. 6.987 6.987 ↑ 1,104.9 13 1

CTE Scan on bucketab cte0_1 (cost=0.00..287.28 rows=14,364 width=68) (actual time=6.926..6.987 rows=13 loops=1)

113. 0.008 0.712 ↑ 1.0 14 1

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

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

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

  • Buffers: shared read=1