explain.depesz.com

PostgreSQL's explain analyze made readable

Result: I512

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

Append (cost=36,654,685,761.22..36,654,687,365.61 rows=49,054 width=96) (actual time=382,717.978..382,733.123 rows=17 loops=1)

  • Buffers: shared hit=1,532,297 read=1,425,717, temp read=467 written=468
2.          

CTE picklist_0

3. 6.143 6.143 ↑ 1.0 6 1

Seq Scan on t693_opportunitystagenamepicklistdim (cost=0.00..6.63 rows=6 width=4) (actual time=1.344..6.143 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.042 0.042 ↑ 1.0 7 1

Seq Scan on t693_opportunitystagenamepicklistdim t693_opportunitystagenamepicklistdim_1 (cost=0.00..6.74 rows=7 width=4) (actual time=0.016..0.042 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. 50.152 262,867.331 ↑ 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=71,297.744..262,867.331 rows=17,081 loops=1)

  • Buffers: shared hit=1,040,946 read=1,282,212, temp read=467 written=468
8. 31.181 71,031.709 ↑ 28.6 17,081 1

GroupAggregate (cost=692,037,959.90..692,051,523.71 rows=488,539 width=20) (actual time=70,977.741..71,031.709 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=2,741 read=644,847, temp read=467 written=468
9. 119.458 71,000.528 ↑ 6.4 90,674 1

Sort (cost=692,037,959.90..692,039,406.30 rows=578,561 width=28) (actual time=70,977.713..71,000.528 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=2,741 read=644,847, temp read=467 written=468
10. 276.333 70,881.070 ↑ 6.4 90,674 1

Nested Loop Left Join (cost=0.73..691,968,739.51 rows=578,561 width=28) (actual time=1,935.223..70,881.070 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=2,741 read=644,847
11. 68,432.833 70,332.715 ↑ 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=1,922.031..70,332.715 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=2,741 read=644,823
12.          

SubPlan (for Index Scan)

13. 0.046 0.046 ↑ 1.0 7 1

CTE Scan on picklist_2 cte0_4 (cost=0.00..0.14 rows=7 width=4) (actual time=0.018..0.046 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. 30.731 1,899.836 ↓ 7.4 48,651 1

Hash Join (cost=12,924.13..68,634.36 rows=6,591 width=4) (actual time=1,329.033..1,899.836 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
18. 544.305 1,831.570 ↑ 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,291.458..1,831.570 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
19. 1,287.265 1,287.265 ↑ 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,287.265..1,287.265 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
20. 0.241 37.535 ↑ 3.3 950 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 66kB
  • Buffers: shared hit=9 read=500
21. 27.072 37.294 ↑ 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=11.809..37.294 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
22. 10.222 10.222 ↑ 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=10.222..10.222 rows=2,245 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
  • Buffers: shared hit=8 read=31
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. 258.869 272.022 ↑ 1.0 42 90,674

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

  • Buffers: shared read=24
27. 13.153 13.153 ↑ 1.0 42 1

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

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

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

  • Filter: (t922_adobe_product_to_sales_team_assignment__cfact.c8649_opportunity__c = aptsta1882_ft1.c1)
  • Buffers: shared hit=1,038,205 read=637,365
29. 68.324 191,768.387 ↑ 101.0 1 17,081

GroupAggregate (cost=61,169.09..61,171.62 rows=101 width=20) (actual time=11.227..11.227 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,038,205 read=637,365
30. 136.648 191,700.063 ↑ 12.6 8 17,081

Sort (cost=61,169.09..61,169.35 rows=101 width=28) (actual time=11.223..11.223 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,038,205 read=637,365
31. 468.696 191,563.415 ↑ 12.6 8 17,081

Nested Loop Left Join (cost=0.57..61,165.73 rows=101 width=28) (actual time=8.067..11.215 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,038,205 read=637,365
32. 187,338.659 190,692.284 ↑ 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=8.059..11.164 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,038,181 read=637,365
33.          

SubPlan (for Index Scan)

34. 402.435 3,353.625 ↑ 1.0 1 134,145

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

  • Buffers: shared hit=945,138 read=1,351
35. 2,682.900 2,682.900 ↑ 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.020..0.020 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=542,323 read=1,351
36. 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
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. 402.223 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
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.011..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.000..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. 23.029 262,914.302 ↓ 85.4 17,081 1

HashAggregate (cost=10,992.13..10,994.13 rows=200 width=4) (actual time=262,910.889..262,914.302 rows=17,081 loops=1)

  • Group Key: cte0_5.c1
  • Buffers: shared hit=1,040,946 read=1,282,212, temp read=467 written=468
53. 262,891.273 262,891.273 ↑ 28.6 17,081 1

CTE Scan on openwont1 cte0_5 (cost=0.00..9,770.78 rows=488,539 width=4) (actual time=71,297.746..262,891.273 rows=17,081 loops=1)

  • Buffers: shared hit=1,040,946 read=1,282,212, temp read=467 written=468
54.          

CTE bucketab

55. 3.299 12.972 ↑ 3,758.0 13 1

GroupAggregate (cost=74,301.11..83,339.08 rows=48,854 width=68) (actual time=8.991..12.972 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
56. 7.143 9.673 ↑ 28.6 17,081 1

Sort (cost=74,301.11..75,522.46 rows=488,539 width=64) (actual time=8.604..9.673 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
57. 2.530 2.530 ↑ 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.530 rows=17,081 loops=1)

58.          

CTE picklist_3

59. 0.054 0.054 ↑ 1.0 13 1

Seq Scan on t693_opportunitystagenamepicklistdim t693_opportunitystagenamepicklistdim_2 (cost=0.00..7.44 rows=13 width=4) (actual time=0.018..0.054 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.059 382,719.394 ↑ 50.0 4 1

GroupAggregate (cost=6,077,186,022.44..6,077,186,638.56 rows=200 width=120) (actual time=382,717.968..382,719.394 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,532,297 read=1,425,716, temp read=467 written=468
62. 4.748 382,718.335 ↑ 7.4 6,645 1

Sort (cost=6,077,186,022.15..6,077,186,144.67 rows=49,010 width=52) (actual time=382,717.955..382,718.335 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,532,297 read=1,425,716, temp read=467 written=468
63. 7.408 382,713.587 ↑ 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=365,962.021..382,713.587 rows=6,645 loops=1)

  • Buffers: shared hit=1,532,297 read=1,425,716, temp read=467 written=468
64. 12.686 365,947.430 ↑ 5.6 6,645 1

GroupAggregate (cost=148,237,248.20..148,238,829.24 rows=37,270 width=12) (actual time=365,928.663..365,947.430 rows=6,645 loops=1)

  • Group Key: t922_adobe_product_to_sales_team_assignment__cfact_2.c8649_opportunity__c
  • Buffers: shared hit=1,461,523 read=1,399,464, temp read=467 written=468
65. 20.314 365,934.744 ↑ 3.1 39,371 1

Sort (cost=148,237,248.20..148,237,550.28 rows=120,834 width=20) (actual time=365,928.650..365,934.744 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,461,523 read=1,399,464, temp read=467 written=468
66. 148.838 365,914.430 ↑ 3.1 39,371 1

Nested Loop Left Join (cost=5.21..148,224,566.70 rows=120,834 width=20) (actual time=358,175.517..365,914.430 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,461,523 read=1,399,464, temp read=467 written=468
67. 6.373 365,686.850 ↑ 3.1 39,371 1

Nested Loop (cost=5.21..148,148,395.12 rows=120,834 width=16) (actual time=358,175.295..365,686.850 rows=39,371 loops=1)

  • Buffers: shared hit=1,461,499 read=1,399,464, temp read=467 written=468
68. 0.023 6.175 ↑ 1.0 6 1

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

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

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

  • Buffers: shared read=5
70. 102,659.743 365,674.302 ↑ 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=59,789.054..60,945.717 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,461,499 read=1,399,459, temp read=467 written=468
71.          

SubPlan (for Index Scan)

72. 262,917.963 262,917.963 ↓ 85.4 17,081 1

CTE Scan on openwont1ids cte0_8 (cost=0.00..4.00 rows=200 width=4) (actual time=262,910.890..262,917.963 rows=17,081 loops=1)

  • Buffers: shared hit=1,040,946 read=1,282,212, 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. 31.042 96.596 ↓ 7.4 48,651 1

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

  • Hash Cond: (t903_userrolehierarchy_5.c8529_user_role_id = t900_userroledim_5.sid)
  • Buffers: shared hit=21,902
77. 52.516 64.128 ↑ 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.467..64.128 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.612 11.612 ↑ 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.611..11.612 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.129 1.426 ↑ 3.3 950 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 66kB
  • Buffers: shared hit=508
80. 1.035 1.297 ↑ 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.317..1.297 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.262 0.262 ↑ 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.261..0.262 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. 78.537 78.742 ↑ 1.0 42 39,371

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

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

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

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

Subquery Scan on aptsta1882_ft1_1 (cost=159,074.91..159,080.82 rows=1 width=12) (actual time=2.522..2.522 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=70,769 read=26,252
88. 6.645 16,752.045 ↓ 0.0 0 6,645

Group (cost=159,074.91..159,077.54 rows=263 width=12) (actual time=2.521..2.521 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=70,769 read=26,252
89. 13.290 16,745.400 ↑ 263.0 1 6,645

Sort (cost=159,074.91..159,075.56 rows=263 width=12) (actual time=2.520..2.520 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=70,769 read=26,252
90. 33.552 16,732.110 ↑ 263.0 1 6,645

Nested Loop Left Join (cost=0.57..159,064.33 rows=263 width=12) (actual time=1.359..2.518 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=70,769 read=26,252
91. 16,554.766 16,678.950 ↑ 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=1.356..2.510 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=70,745 read=26,252
92.          

SubPlan (for Index Scan)

93. 13.072 124.184 ↑ 1.0 1 6,536

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

  • Buffers: shared hit=46,172 read=6
94. 98.040 98.040 ↑ 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.015..0.015 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,563 read=6
95. 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
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. 19.399 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. 0.209 0.209 ↑ 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.209 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.059 0.059 ↑ 1.0 13 1

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

  • Buffers: shared hit=5
109. 0.010 382,719.408 ↑ 50.0 4 1

Subquery Scan on "*SELECT* 1" (cost=0.00..6.50 rows=200 width=96) (actual time=382,717.978..382,719.408 rows=4 loops=1)

  • Buffers: shared hit=1,532,297 read=1,425,716, temp read=467 written=468
110. 382,719.398 382,719.398 ↑ 50.0 4 1

CTE Scan on bucketd cte0 (cost=0.00..4.00 rows=200 width=120) (actual time=382,717.971..382,719.398 rows=4 loops=1)

  • Buffers: shared hit=1,532,297 read=1,425,716, temp read=467 written=468
111. 0.021 13.712 ↑ 3,758.0 13 1

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

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

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

113. 0.007 0.711 ↑ 1.0 14 1

Hash (cost=1.14..1.14 rows=14 width=16) (actual time=0.711..0.711 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