explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3Qtx

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

Append (cost=36,654,685,761.22..36,654,687,365.61 rows=49,054 width=96) (actual time=367,074.623..367,091.174 rows=17 loops=1)

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

CTE picklist_0

3. 4.385 4.385 ↑ 1.0 6 1

Seq Scan on t693_opportunitystagenamepicklistdim (cost=0.00..6.63 rows=6 width=4) (actual time=0.814..4.385 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.040 0.040 ↑ 1.0 7 1

Seq Scan on t693_opportunitystagenamepicklistdim t693_opportunitystagenamepicklistdim_1 (cost=0.00..6.74 rows=7 width=4) (actual time=0.013..0.040 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. 35.137 258,192.498 ↑ 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=73,606.665..258,192.498 rows=17,081 loops=1)

  • Buffers: shared hit=1,040,946 read=1,282,212, temp read=467 written=468
8. 28.267 73,323.858 ↑ 28.6 17,081 1

GroupAggregate (cost=692,037,959.90..692,051,523.71 rows=488,539 width=20) (actual time=73,275.733..73,323.858 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. 106.111 73,295.591 ↑ 6.4 90,674 1

Sort (cost=692,037,959.90..692,039,406.30 rows=578,561 width=28) (actual time=73,275.706..73,295.591 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. 259.727 73,189.480 ↑ 6.4 90,674 1

Nested Loop Left Join (cost=0.73..691,968,739.51 rows=578,561 width=28) (actual time=1,833.355..73,189.480 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. 70,858.146 72,657.731 ↑ 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,820.710..72,657.731 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.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.015..0.043 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.384 1,799.542 ↓ 7.4 48,651 1

Hash Join (cost=12,924.13..68,634.36 rows=6,591 width=4) (actual time=1,314.449..1,799.542 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. 460.229 1,741.378 ↑ 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,285.628..1,741.378 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,281.149 1,281.149 ↑ 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,281.149..1,281.149 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.156 28.780 ↑ 3.3 950 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 66kB
  • Buffers: shared hit=9 read=500
21. 15.349 28.624 ↑ 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=15.004..28.624 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. 13.275 13.275 ↑ 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=13.275..13.275 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. 259.408 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. 12.614 12.614 ↑ 1.0 42 1

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

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

Subquery Scan on aptsta1882_ft1 (cost=61,169.09..61,172.88 rows=1 width=20) (actual time=10.821..10.821 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 184,816.420 ↑ 101.0 1 17,081

GroupAggregate (cost=61,169.09..61,171.62 rows=101 width=20) (actual time=10.820..10.820 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. 119.567 184,748.096 ↑ 12.6 8 17,081

Sort (cost=61,169.09..61,169.35 rows=101 width=28) (actual time=10.815..10.816 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. 485.777 184,628.529 ↑ 12.6 8 17,081

Nested Loop Left Join (cost=0.57..61,165.73 rows=101 width=28) (actual time=7.782..10.809 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. 180,386.692 183,740.317 ↑ 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=7.775..10.757 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. 268.290 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,817.045 2,817.045 ↑ 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.021..0.021 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.232 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.203 0.203 ↑ 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.203 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. 19.841 258,234.740 ↓ 85.4 17,081 1

HashAggregate (cost=10,992.13..10,994.13 rows=200 width=4) (actual time=258,231.502..258,234.740 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. 258,214.899 258,214.899 ↑ 28.6 17,081 1

CTE Scan on openwont1 cte0_5 (cost=0.00..9,770.78 rows=488,539 width=4) (actual time=73,606.667..258,214.899 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.377 13.362 ↑ 3,758.0 13 1

GroupAggregate (cost=74,301.11..83,339.08 rows=48,854 width=68) (actual time=9.229..13.362 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.369 9.985 ↑ 28.6 17,081 1

Sort (cost=74,301.11..75,522.46 rows=488,539 width=64) (actual time=8.838..9.985 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.616 2.616 ↑ 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.616 rows=17,081 loops=1)

58.          

CTE picklist_3

59. 0.067 0.067 ↑ 1.0 13 1

Seq Scan on t693_opportunitystagenamepicklistdim t693_opportunitystagenamepicklistdim_2 (cost=0.00..7.44 rows=13 width=4) (actual time=0.014..0.067 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.064 367,076.146 ↑ 50.0 4 1

GroupAggregate (cost=6,077,186,022.44..6,077,186,638.56 rows=200 width=120) (actual time=367,074.612..367,076.146 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. 5.023 367,075.082 ↑ 7.4 6,645 1

Sort (cost=6,077,186,022.15..6,077,186,144.67 rows=49,010 width=52) (actual time=367,074.598..367,075.082 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. 9.748 367,070.059 ↑ 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=350,191.049..367,070.059 rows=6,645 loops=1)

  • Buffers: shared hit=1,532,297 read=1,425,716, temp read=467 written=468
64. 13.188 350,188.583 ↑ 5.6 6,645 1

GroupAggregate (cost=148,237,248.20..148,238,829.24 rows=37,270 width=12) (actual time=350,169.129..350,188.583 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. 19.384 350,175.395 ↑ 3.1 39,371 1

Sort (cost=148,237,248.20..148,237,550.28 rows=120,834 width=20) (actual time=350,169.119..350,175.395 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. 114.420 350,156.011 ↑ 3.1 39,371 1

Nested Loop Left Join (cost=5.21..148,224,566.70 rows=120,834 width=20) (actual time=342,908.928..350,156.011 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.271 349,923.478 ↑ 3.1 39,371 1

Nested Loop (cost=5.21..148,148,395.12 rows=120,834 width=16) (actual time=342,908.696..349,923.478 rows=39,371 loops=1)

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

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

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

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

  • Buffers: shared read=5
70. 91,581.269 349,912.794 ↑ 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=57,240.470..58,318.799 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. 258,238.306 258,238.306 ↓ 85.4 17,081 1

CTE Scan on openwont1ids cte0_8 (cost=0.00..4.00 rows=200 width=4) (actual time=258,231.504..258,238.306 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. 30.779 93.219 ↓ 7.4 48,651 1

Hash Join (cost=12,924.13..68,634.36 rows=6,591 width=4) (actual time=15.495..93.219 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.740 61.104 ↑ 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.129..61.104 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.364 11.364 ↑ 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.364..11.364 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.132 1.336 ↑ 3.3 950 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 66kB
  • Buffers: shared hit=508
80. 0.971 1.204 ↑ 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.286..1.204 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.233 0.233 ↑ 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.233..0.233 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.895 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.218 0.218 ↑ 1.0 42 1

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

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 1,722
  • Buffers: shared hit=24
87. 0.000 16,871.655 ↓ 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.539..2.539 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,871.655 ↓ 0.0 0 6,645

Group (cost=159,074.91..159,077.54 rows=263 width=12) (actual time=2.539..2.539 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. 19.935 16,865.010 ↑ 263.0 1 6,645

Sort (cost=159,074.91..159,075.56 rows=263 width=12) (actual time=2.538..2.538 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,845.075 ↑ 263.0 1 6,645

Nested Loop Left Join (cost=0.57..159,064.33 rows=263 width=12) (actual time=1.413..2.535 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,693.875 16,791.915 ↑ 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.410..2.527 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 98.040 ↑ 1.0 1 6,536

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

  • Buffers: shared hit=46,172 read=6
94. 71.896 71.896 ↑ 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.011..0.011 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.398 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.210 0.210 ↑ 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.210 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.073 0.073 ↑ 1.0 13 1

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

  • Buffers: shared hit=5
109. 0.010 367,076.161 ↑ 50.0 4 1

Subquery Scan on "*SELECT* 1" (cost=0.00..6.50 rows=200 width=96) (actual time=367,074.622..367,076.161 rows=4 loops=1)

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

CTE Scan on bucketd cte0 (cost=0.00..4.00 rows=200 width=120) (actual time=367,074.615..367,076.151 rows=4 loops=1)

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

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

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

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

113. 0.011 1.606 ↑ 1.0 14 1

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

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

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

  • Buffers: shared read=1