explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aysy

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 9,433.056 ↑ 2,885.5 17 1

Append (cost=36,654,685,761.22..36,654,687,365.61 rows=49,054 width=96) (actual time=9,418.694..9,433.056 rows=17 loops=1)

  • Buffers: shared hit=1,834,503 read=2, temp read=467 written=468
2.          

CTE picklist_0

3. 0.036 0.036 ↑ 1.0 6 1

Seq Scan on t693_opportunitystagenamepicklistdim (cost=0.00..6.63 rows=6 width=4) (actual time=0.008..0.036 rows=6 loops=1)

  • Filter: (correlated_value = ANY ('{""Closed - Booked"",""Closed- Booked"",""Closed - Booked (sales order no. require"",Booked/Won,""Closed Booked"",""Budget Won/Live""}'::text[]))
  • Rows Removed by Filter: 87
  • Buffers: shared hit=5
4.          

CTE picklist_2

5. 0.036 0.036 ↑ 1.0 7 1

Seq Scan on t693_opportunitystagenamepicklistdim t693_opportunitystagenamepicklistdim_1 (cost=0.00..6.74 rows=7 width=4) (actual time=0.011..0.036 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. 14.590 5,007.802 ↑ 28.6 17,081 1

Nested Loop Left Join (cost=692,099,129.27..30,577,404,768.64 rows=488,539 width=34) (actual time=2,473.983..5,007.802 rows=17,081 loops=1)

  • Buffers: shared hit=1,245,171 read=1, temp read=467 written=468
8. 17.819 2,499.384 ↑ 28.6 17,081 1

GroupAggregate (cost=692,037,959.90..692,051,523.71 rows=488,539 width=20) (actual time=2,470.082..2,499.384 rows=17,081 loops=1)

  • Group Key: t922_adobe_product_to_sales_team_assignment__cfact.c8649_opportunity__c, t922_adobe_product_to_sales_team_assignment__cfact.c8695_opportunity__c_forecast_indicator__csid, t922_adobe_product_to_sales_team_assignment__cfact.c8682_opportunity__c_stagenamesid
  • Buffers: shared hit=119,756 read=1, temp read=467 written=468
9. 79.876 2,481.565 ↑ 6.4 90,674 1

Sort (cost=692,037,959.90..692,039,406.30 rows=578,561 width=28) (actual time=2,470.042..2,481.565 rows=90,674 loops=1)

  • Sort Key: t922_adobe_product_to_sales_team_assignment__cfact.c8649_opportunity__c, t922_adobe_product_to_sales_team_assignment__cfact.c8695_opportunity__c_forecast_indicator__csid, t922_adobe_product_to_sales_team_assignment__cfact.c8682_opportunity__c_stagenamesid
  • Sort Method: external merge Disk: 3,736kB
  • Buffers: shared hit=119,756 read=1, temp read=467 written=468
10. 322.263 2,401.689 ↑ 6.4 90,674 1

Nested Loop Left Join (cost=0.73..691,968,739.51 rows=578,561 width=28) (actual time=102.696..2,401.689 rows=90,674 loops=1)

  • Join Filter: (t922_adobe_product_to_sales_team_assignment__cfact.c8648_currencyisocodesid = t970_claricurrencytypedim.sid)
  • Rows Removed by Join Filter: 3,717,634
  • Buffers: shared hit=119,756 read=1
11. 1,806.719 1,898.078 ↑ 6.4 90,674 1

Index Scan using idx_778_t922_adobe_product_to_sales_team_assignment__cfact_c868 on t922_adobe_product_to_sales_team_assignment__cfact (cost=0.73..691,604,199.93 rows=578,561 width=24) (actual time=102.475..1,898.078 rows=90,674 loops=1)

  • Index Cond: ((c8688_opportunity__c_closedate >= 20,200,229) AND (c8688_opportunity__c_closedate <= 20,200,529) AND (end_stamp > '1585810799999'::bigint) AND (start_stamp <= '1585810799999'::bigint))
  • 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
  • Buffers: shared hit=119,732 read=1
12.          

SubPlan (for Index Scan)

13. 0.039 0.039 ↑ 1.0 7 1

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

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

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

15. 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 = t922_adobe_product_to_sales_team_assignment__cfact.c8713_sales_team_member__c) AND (c8530_ancestor_role_id = 6,405) AND (end_stamp = '32503680000000'::bigint))
16. 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))
17. 29.909 91.320 ↓ 7.4 48,651 1

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

  • Hash Cond: (t903_userrolehierarchy_1.c8529_user_role_id = t900_userroledim_1.sid)
  • Buffers: shared hit=21,902
18. 48.651 60.082 ↑ 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=14.233..60.082 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
19. 11.431 11.431 ↑ 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.431..11.431 rows=213,212 loops=1)

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

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 66kB
  • Buffers: shared hit=508
21. 0.931 1.195 ↑ 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=0.317..1.195 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
22. 0.264 0.264 ↑ 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.264..0.264 rows=2,236 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
  • Buffers: shared hit=38
23. 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 = t922_adobe_product_to_sales_team_assignment__cfact.c8713_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))
24. 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))
25. 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))
26. 181.143 181.348 ↑ 1.0 42 90,674

Materialize (cost=0.00..46.26 rows=42 width=12) (actual time=0.000..0.002 rows=42 loops=90,674)

  • Buffers: shared hit=24
27. 0.205 0.205 ↑ 1.0 42 1

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

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

Subquery Scan on aptsta1882_ft1 (cost=61,169.09..61,172.88 rows=1 width=20) (actual time=0.146..0.146 rows=1 loops=17,081)

  • Filter: (t922_adobe_product_to_sales_team_assignment__cfact.c8649_opportunity__c = aptsta1882_ft1.c1)
  • Buffers: shared hit=1,125,415
29. 34.162 2,493.826 ↑ 101.0 1 17,081

GroupAggregate (cost=61,169.09..61,171.62 rows=101 width=20) (actual time=0.146..0.146 rows=1 loops=17,081)

  • Group Key: t922_adobe_product_to_sales_team_assignment__cfact_1.c8649_opportunity__c, t922_adobe_product_to_sales_team_assignment__cfact_1.c8688_opportunity__c_closedate, t922_adobe_product_to_sales_team_assignment__cfact_1.c8682_opportunity__c_stagenamesid
  • Buffers: shared hit=1,125,415
30. 51.243 2,459.664 ↑ 12.6 8 17,081

Sort (cost=61,169.09..61,169.35 rows=101 width=28) (actual time=0.144..0.144 rows=8 loops=17,081)

  • Sort Key: t922_adobe_product_to_sales_team_assignment__cfact_1.c8688_opportunity__c_closedate, t922_adobe_product_to_sales_team_assignment__cfact_1.c8682_opportunity__c_stagenamesid
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1,125,415
31. 500.355 2,408.421 ↑ 12.6 8 17,081

Nested Loop Left Join (cost=0.57..61,165.73 rows=101 width=28) (actual time=0.033..0.141 rows=8 loops=17,081)

  • Join Filter: (t922_adobe_product_to_sales_team_assignment__cfact_1.c8648_currencyisocodesid = t970_claricurrencytypedim_1.sid)
  • Rows Removed by Join Filter: 322
  • Buffers: shared hit=1,125,415
32. 1,103.196 1,639.776 ↑ 12.6 8 17,081

Index Scan using idx_771_t922_adobe_product_to_sales_team_assignment__cfact_c864 on t922_adobe_product_to_sales_team_assignment__cfact t922_adobe_product_to_sales_team_assignment__cfact_1 (cost=0.57..61,055.95 rows=101 width=24) (actual time=0.028..0.096 rows=8 loops=17,081)

  • Index Cond: ((t922_adobe_product_to_sales_team_assignment__cfact.c8649_opportunity__c = c8649_opportunity__c) AND (end_stamp > '1601535599999'::bigint) AND (start_stamp <= '1601535599999'::bigint))
  • Filter: ((NOT deleted) 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,125,391
33.          

SubPlan (for Index Scan)

34. 134.145 536.580 ↑ 1.0 1 134,145

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

  • Buffers: shared hit=945,619
35. 268.290 268.290 ↑ 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.002..0.002 rows=1 loops=134,145)

  • Index Cond: ((sid = t922_adobe_product_to_sales_team_assignment__cfact_1.c8713_sales_team_member__c) AND (c8530_ancestor_role_id = 6,405) AND (end_stamp = '32503680000000'::bigint))
  • Buffers: shared hit=543,184
36. 134.145 134.145 ↑ 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.001..0.001 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,435
37. 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)
38. 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))
39. 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))
40. 0.000 0.000 ↓ 0.0 0

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

41. 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))
42. 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)
43. 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 = t922_adobe_product_to_sales_team_assignment__cfact_1.c8713_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))
44. 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))
45. 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))
46. 268.078 268.290 ↑ 1.0 42 134,145

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

  • Buffers: shared hit=24
47. 0.212 0.212 ↑ 1.0 42 1

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

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

SubPlan (for Nested Loop Left Join)

49. 0.001 0.001 ↑ 1.0 6 1

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

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

51.          

CTE openwont1ids

52. 11.502 5,027.225 ↓ 85.4 17,081 1

HashAggregate (cost=10,992.13..10,994.13 rows=200 width=4) (actual time=5,023.940..5,027.225 rows=17,081 loops=1)

  • Group Key: cte0_5.c1
  • Buffers: shared hit=1,245,171 read=1, temp read=467 written=468
53. 5,015.723 5,015.723 ↑ 28.6 17,081 1

CTE Scan on openwont1 cte0_5 (cost=0.00..9,770.78 rows=488,539 width=4) (actual time=2,473.985..5,015.723 rows=17,081 loops=1)

  • Buffers: shared hit=1,245,171 read=1, temp read=467 written=468
54.          

CTE bucketab

55. 3.244 12.881 ↑ 3,758.0 13 1

GroupAggregate (cost=74,301.11..83,339.08 rows=48,854 width=68) (actual time=8.995..12.881 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
  • Buffers: shared hit=3
56. 7.125 9.637 ↑ 28.6 17,081 1

Sort (cost=74,301.11..75,522.46 rows=488,539 width=64) (actual time=8.610..9.637 rows=17,081 loops=1)

  • Sort 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
  • Sort Method: quicksort Memory: 2,103kB
  • Buffers: shared hit=3
57. 2.512 2.512 ↑ 28.6 17,081 1

CTE Scan on openwont1 cte0_6 (cost=0.00..9,770.78 rows=488,539 width=64) (actual time=0.002..2.512 rows=17,081 loops=1)

58.          

CTE picklist_3

59. 0.041 0.041 ↑ 1.0 13 1

Seq Scan on t693_opportunitystagenamepicklistdim t693_opportunitystagenamepicklistdim_2 (cost=0.00..7.44 rows=13 width=4) (actual time=0.006..0.041 rows=13 loops=1)

  • Filter: (correlated_value = ANY ('{Lost,""Closed - Clean Up"",Booked/Won,""Closed - CleanUp"",""Closed Booked"",""Budget Won/Live"",""Closed - Booked"",""Closed- Booked"",""No Commit"",""Closed Lost"",""Closed - Booked (sales order no. require"",Closed/Lost,""Closed - Lost""}'::text[]))
  • Rows Removed by Filter: 80
  • Buffers: shared hit=5
60.          

CTE bucketd

61. 1.042 9,420.106 ↑ 50.0 4 1

GroupAggregate (cost=6,077,186,022.44..6,077,186,638.56 rows=200 width=120) (actual time=9,418.686..9,420.106 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,834,499 read=2, temp read=467 written=468
62. 2.034 9,419.064 ↑ 7.4 6,645 1

Sort (cost=6,077,186,022.15..6,077,186,144.67 rows=49,010 width=52) (actual time=9,418.672..9,419.064 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,834,499 read=2, temp read=467 written=468
63. 4.316 9,417.030 ↑ 7.4 6,645 1

Nested Loop Left Join (cost=148,396,323.40..6,077,182,204.07 rows=49,010 width=52) (actual time=9,202.246..9,417.030 rows=6,645 loops=1)

  • Buffers: shared hit=1,834,496 read=2, temp read=467 written=468
64. 7.152 9,213.319 ↑ 5.6 6,645 1

GroupAggregate (cost=148,237,248.20..148,238,829.24 rows=37,270 width=12) (actual time=9,201.520..9,213.319 rows=6,645 loops=1)

  • Group Key: t922_adobe_product_to_sales_team_assignment__cfact_2.c8649_opportunity__c
  • Buffers: shared hit=1,747,330 read=2, temp read=467 written=468
65. 14.593 9,206.167 ↑ 3.1 39,371 1

Sort (cost=148,237,248.20..148,237,550.28 rows=120,834 width=20) (actual time=9,201.510..9,206.167 rows=39,371 loops=1)

  • Sort Key: t922_adobe_product_to_sales_team_assignment__cfact_2.c8649_opportunity__c
  • Sort Method: quicksort Memory: 4,022kB
  • Buffers: shared hit=1,747,330 read=2, temp read=467 written=468
66. 112.569 9,191.574 ↑ 3.1 39,371 1

Nested Loop Left Join (cost=5.21..148,224,566.70 rows=120,834 width=20) (actual time=7,367.975..9,191.574 rows=39,371 loops=1)

  • Join Filter: (t922_adobe_product_to_sales_team_assignment__cfact_2.c8648_currencyisocodesid = t970_claricurrencytypedim_2.sid)
  • Rows Removed by Join Filter: 1,614,211
  • Buffers: shared hit=1,747,327 read=2, temp read=467 written=468
67. 4.829 8,960.892 ↑ 3.1 39,371 1

Nested Loop (cost=5.21..148,148,395.12 rows=120,834 width=16) (actual time=7,367.759..8,960.892 rows=39,371 loops=1)

  • Buffers: shared hit=1,747,303 read=2, temp read=467 written=468
68. 0.021 0.061 ↑ 1.0 6 1

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

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

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

  • Buffers: shared hit=5
70. 3,832.484 8,956.002 ↑ 3.1 6,562 6

Index Scan using idx_777_t922_adobe_product_to_sales_team_assignment__cfact_c868 on t922_adobe_product_to_sales_team_assignment__cfact t922_adobe_product_to_sales_team_assignment__cfact_2 (cost=5.07..24,691,197.76 rows=20,139 width=20) (actual time=1,319.642..1,492.667 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) AND (start_stamp <= '1601535599999'::bigint) AND (end_stamp > '1601535599999'::bigint))
  • 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,747,298 read=2, temp read=467 written=468
71.          

SubPlan (for Index Scan)

72. 5,030.766 5,030.766 ↓ 85.4 17,081 1

CTE Scan on openwont1ids cte0_8 (cost=0.00..4.00 rows=200 width=4) (actual time=5,023.942..5,030.766 rows=17,081 loops=1)

  • Buffers: shared hit=1,245,171 read=1, temp read=467 written=468
73. 0.000 0.000 ↓ 0.0 0

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

74. 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 = t922_adobe_product_to_sales_team_assignment__cfact_2.c8713_sales_team_member__c) AND (c8530_ancestor_role_id = 6,405) AND (end_stamp = '32503680000000'::bigint))
75. 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))
76. 30.596 92.752 ↓ 7.4 48,651 1

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

  • Hash Cond: (t903_userrolehierarchy_5.c8529_user_role_id = t900_userroledim_5.sid)
  • Buffers: shared hit=21,902
77. 49.192 60.823 ↑ 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.316..60.823 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
78. 11.631 11.631 ↑ 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.631..11.631 rows=213,212 loops=1)

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

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 66kB
  • Buffers: shared hit=508
80. 0.970 1.216 ↑ 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.216 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
81. 0.246 0.246 ↑ 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.246..0.246 rows=2,236 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
  • Buffers: shared hit=38
82. 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 = t922_adobe_product_to_sales_team_assignment__cfact_2.c8713_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))
83. 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))
84. 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))
85. 117.910 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.203 0.203 ↑ 1.0 42 1

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

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

Subquery Scan on aptsta1882_ft1_1 (cost=159,074.91..159,080.82 rows=1 width=12) (actual time=0.030..0.030 rows=0 loops=6,645)

  • Filter: (t922_adobe_product_to_sales_team_assignment__cfact_2.c8649_opportunity__c = aptsta1882_ft1_1.c1)
  • Buffers: shared hit=87,161
88. 0.000 199.350 ↓ 0.0 0 6,645

Group (cost=159,074.91..159,077.54 rows=263 width=12) (actual time=0.030..0.030 rows=0 loops=6,645)

  • Group Key: t922_adobe_product_to_sales_team_assignment__cfact_3.c8649_opportunity__c, t922_adobe_product_to_sales_team_assignment__cfact_3.c8682_opportunity__c_stagenamesid, t922_adobe_product_to_sales_team_assignment__cfact_3.c8688_opportunity__c_closedate
  • Buffers: shared hit=87,161
89. 6.645 199.350 ↑ 263.0 1 6,645

Sort (cost=159,074.91..159,075.56 rows=263 width=12) (actual time=0.030..0.030 rows=1 loops=6,645)

  • Sort Key: t922_adobe_product_to_sales_team_assignment__cfact_3.c8682_opportunity__c_stagenamesid, t922_adobe_product_to_sales_team_assignment__cfact_3.c8688_opportunity__c_closedate
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=87,161
90. 33.443 192.705 ↑ 263.0 1 6,645

Nested Loop Left Join (cost=0.57..159,064.33 rows=263 width=12) (actual time=0.010..0.029 rows=1 loops=6,645)

  • Join Filter: (t922_adobe_product_to_sales_team_assignment__cfact_3.c8648_currencyisocodesid = t970_claricurrencytypedim_3.sid)
  • Rows Removed by Join Filter: 40
  • Buffers: shared hit=87,161
91. 113.510 146.190 ↑ 263.0 1 6,645

Index Scan using idx_771_t922_adobe_product_to_sales_team_assignment__cfact_c864 on t922_adobe_product_to_sales_team_assignment__cfact t922_adobe_product_to_sales_team_assignment__cfact_3 (cost=0.57..158,852.49 rows=263 width=16) (actual time=0.009..0.022 rows=1 loops=6,645)

  • Index Cond: ((t922_adobe_product_to_sales_team_assignment__cfact_2.c8649_opportunity__c = c8649_opportunity__c) AND (end_stamp > '1585810799999'::bigint) AND (start_stamp <= '1585810799999'::bigint))
  • 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=87,137
92.          

SubPlan (for Index Scan)

93. 13.072 32.680 ↑ 1.0 1 6,536

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

  • Buffers: shared hit=46,176
94. 13.072 13.072 ↑ 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.002..0.002 rows=1 loops=6,536)

  • Index Cond: ((sid = t922_adobe_product_to_sales_team_assignment__cfact_3.c8713_sales_team_member__c) AND (c8530_ancestor_role_id = 6,405) AND (end_stamp = '32503680000000'::bigint))
  • Buffers: shared hit=26,568
95. 6.536 6.536 ↑ 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.001..0.001 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
96. 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)
97. 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))
98. 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))
99. 0.000 0.000 ↓ 0.0 0

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

100. 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))
101. 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)
102. 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 = t922_adobe_product_to_sales_team_assignment__cfact_3.c8713_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))
103. 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))
104. 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))
105. 12.868 13.072 ↑ 1.0 42 6,536

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

  • Buffers: shared hit=24
106. 0.204 0.204 ↑ 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.204 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.045 0.045 ↑ 1.0 13 1

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

  • Buffers: shared hit=5
109. 0.006 9,420.117 ↑ 50.0 4 1

Subquery Scan on "*SELECT* 1" (cost=0.00..6.50 rows=200 width=96) (actual time=9,418.693..9,420.117 rows=4 loops=1)

  • Buffers: shared hit=1,834,499 read=2, temp read=467 written=468
110. 9,420.111 9,420.111 ↑ 50.0 4 1

CTE Scan on bucketd cte0 (cost=0.00..4.00 rows=200 width=120) (actual time=9,418.689..9,420.111 rows=4 loops=1)

  • Buffers: shared hit=1,834,499 read=2, temp read=467 written=468
111. 0.029 12.936 ↑ 3,758.0 13 1

Hash Left Join (cost=1.32..1,109.35 rows=48,854 width=76) (actual time=9.039..12.936 rows=13 loops=1)

  • Hash Cond: (cte0_1.c6 = t691_opportunityforecast_indicator__cpicklistdim.sid)
  • Buffers: shared hit=4
112. 12.889 12.889 ↑ 3,758.0 13 1

CTE Scan on bucketab cte0_1 (cost=0.00..977.08 rows=48,854 width=68) (actual time=8.998..12.889 rows=13 loops=1)

  • Buffers: shared hit=3
113. 0.007 0.018 ↑ 1.0 14 1

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

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

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

  • Buffers: shared hit=1