explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Te7

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 57,602.318 ↑ 111.6 17 1

Append (cost=4,922,031,152.71..4,922,031,216.04 rows=1,898 width=96) (actual time=57,591.162..57,602.318 rows=17 loops=1)

  • Buffers: shared hit=2,172,914 read=331,073, temp read=467 written=468
2.          

CTE picklist_0

3. 0.033 0.033 ↑ 1.0 6 1

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

Seq Scan on t693_opportunitystagenamepicklistdim t693_opportunitystagenamepicklistdim_1 (cost=0.00..6.74 rows=7 width=4) (actual time=0.012..0.039 rows=7 loops=1)

  • Filter: (correlated_value = ANY ('{Lost,""Closed - Clean Up"",""No Commit"",""Closed Lost"",""Closed - CleanUp"",Closed/Lost,""Closed - Lost""}'::text[]))
  • Rows Removed by Filter: 86
  • Buffers: shared hit=5
6.          

CTE openwont1

7. 13.624 54,864.288 ↑ 1.1 17,081 1

Nested Loop Left Join (cost=252,615,225.28..4,773,570,622.66 rows=18,369 width=34) (actual time=10,986.273..54,864.288 rows=17,081 loops=1)

  • Buffers: shared hit=1,787,766 read=331,073, temp read=467 written=468
8. 21.482 10,798.762 ↑ 1.1 17,081 1

GroupAggregate (cost=252,369,096.20..252,372,035.26 rows=18,369 width=20) (actual time=10,761.257..10,798.762 rows=17,081 loops=1)

  • Group Key: t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8649_opportunity__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8695_opportunity__c_forecast_indicator__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8682_opportunity__c_stagenamesid
  • Buffers: shared hit=539,879 read=138,910, temp read=467 written=468
9. 83.031 10,777.280 ↑ 2.0 90,704 1

Sort (cost=252,369,096.20..252,369,555.43 rows=183,691 width=28) (actual time=10,761.229..10,777.280 rows=90,704 loops=1)

  • Sort Key: t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8649_opportunity__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8695_opportunity__c_forecast_indicator__csid, t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8682_opportunity__c_stagenamesid
  • Sort Method: external merge Disk: 3,736kB
  • Buffers: shared hit=539,879 read=138,910, temp read=467 written=468
10. 19.750 10,694.249 ↑ 2.0 90,704 1

Hash Left Join (cost=277,624.11..252,348,639.25 rows=183,691 width=28) (actual time=660.439..10,694.249 rows=90,704 loops=1)

  • Hash Cond: (t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8648_currencyisocodesid = t970_claricurrencytypedim.sid)
  • Buffers: shared hit=539,879 read=138,910
11. 9.730 10,674.287 ↑ 2.0 90,704 1

Append (cost=277,577.53..252,338,103.92 rows=183,691 width=24) (actual time=660.208..10,674.287 rows=90,704 loops=1)

  • Buffers: shared hit=539,855 read=138,910
12. 3,423.916 4,136.338 ↑ 276.9 256 1

Bitmap Heap Scan on t922_adobe_product_to_sales_team_assignment__cfact_2020u (cost=277,577.53..71,206,620.37 rows=70,895 width=24) (actual time=660.207..4,136.338 rows=256 loops=1)

  • Recheck Cond: ((c8688_opportunity__c_closedate >= 20,200,229) AND (c8688_opportunity__c_closedate <= 20,200,529) AND (end_stamp > '1585810799999'::bigint))
  • Rows Removed by Index Recheck: 3,179,700
  • Filter: ((NOT deleted) AND (start_stamp <= '1585810799999'::bigint) 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: 130,839
  • Heap Blocks: exact=38,423 lossy=185,737
  • Buffers: shared hit=209,178 read=75,905
13. 10.226 483.435 ↓ 0.0 0 1

BitmapAnd (cost=277,577.38..277,577.38 rows=502,849 width=0) (actual time=483.435..483.435 rows=0 loops=1)

  • Buffers: shared hit=50,043
14. 72.009 72.009 ↓ 1.0 946,778 1

Bitmap Index Scan on idx_778_t922_adobe_p2s_team_assignment__cfact_c868_2020u (cost=0.00..19,411.10 rows=925,467 width=0) (actual time=72.009..72.009 rows=946,778 loops=1)

  • Index Cond: ((c8688_opportunity__c_closedate >= 20,200,229) AND (c8688_opportunity__c_closedate <= 20,200,529))
  • Buffers: shared hit=2,630
15. 401.200 401.200 ↓ 1.0 5,104,271 1

Bitmap Index Scan on t922_adobe_p2s_team_assignment__cfact_sidendstampu_2020u (cost=0.00..258,130.57 rows=5,069,363 width=0) (actual time=401.200..401.200 rows=5,104,271 loops=1)

  • Index Cond: (end_stamp > '1585810799999'::bigint)
  • Buffers: shared hit=47,413
16.          

SubPlan (for Bitmap Heap Scan)

17. 0.048 0.048 ↑ 1.0 7 3

CTE Scan on picklist_2 cte0_4 (cost=0.00..0.14 rows=7 width=4) (actual time=0.006..0.016 rows=7 loops=3)

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

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

19. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_673_t903_userrolehier_sid_c8530_ancestor_role_id_end_s_curr on t903_userrolehierarchy_current (cost=0.43..41.21 rows=12 width=4) (never executed)

  • Index Cond: ((sid = t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8713_sales_team_member__c) AND (c8530_ancestor_role_id = 6,405))
20. 0.000 0.000 ↓ 0.0 0

Index Scan using ""idx_665_t900_userroledim_sid_start_s_end_s_(c8519_porta_curr"" on t900_userroledim_current (cost=0.28..6.97 rows=1 width=4) (never executed)

  • Index Cond: (sid = t903_userrolehierarchy_current.c8529_user_role_id)
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
21. 87.772 228.939 ↓ 6.3 48,651 3

Hash Semi Join (cost=8,259.00..28,942.94 rows=7,675 width=4) (actual time=9.812..76.313 rows=48,651 loops=3)

  • Hash Cond: (t903_userrolehierarchy_current_1.c8529_user_role_id = t900_userroledim_current_1.sid)
  • Buffers: shared hit=39,094
22. 116.559 140.124 ↑ 1.1 205,928 3

Bitmap Heap Scan on t903_userrolehierarchy_current t903_userrolehierarchy_current_1 (cost=4,757.79..24,788.22 rows=216,434 width=8) (actual time=9.451..46.708 rows=205,928 loops=3)

  • Recheck Cond: (c8530_ancestor_role_id = 6,405)
  • Heap Blocks: exact=36,567
  • Buffers: shared hit=38,688
23. 23.565 23.565 ↑ 1.1 205,928 3

Bitmap Index Scan on idx_674_t903_userrolehier_c8530_ancestor_role_id_end_stamp_curr (cost=0.00..4,703.68 rows=216,434 width=0) (actual time=7.854..7.855 rows=205,928 loops=3)

  • Index Cond: (c8530_ancestor_role_id = 6,405)
  • Buffers: shared hit=2,121
24. 0.129 1.043 ↑ 2.2 950 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 66kB
  • Buffers: shared hit=406
25. 0.800 0.914 ↑ 2.2 950 1

Bitmap Heap Scan on t900_userroledim_current t900_userroledim_current_1 (cost=51.18..3,475.59 rows=2,049 width=4) (actual time=0.155..0.914 rows=950 loops=1)

  • Recheck Cond: ((c8519_portaltype IS NULL) OR (c8519_portaltype = 'None'::text))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
  • Rows Removed by Filter: 1,240
  • Heap Blocks: exact=394
  • Buffers: shared hit=406
26. 0.114 0.114 ↓ 1.1 2,190 1

Bitmap Index Scan on ""idx_665_t900_userroledim_sid_start_s_end_s_(c8519_porta_curr"" (cost=0.00..50.66 rows=2,077 width=0) (actual time=0.114..0.114 rows=2,190 loops=1)

  • Buffers: shared hit=12
27. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_670_t899_userdim_sid_end_stamp_start_stamp_null_curr on t899_userdim_current (cost=0.42..8.44 rows=1 width=0) (never executed)

  • Index Cond: (sid = t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8713_sales_team_member__c)
  • Filter: ((NOT deleted) AND (c8517_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c8507_usertype = 'Standard'::text))
28. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t899_userdim_current t899_userdim_current_1 (cost=199.98..8,537.13 rows=4,617 width=4) (never executed)

  • Recheck Cond: (c8517_userroleid IS NULL)
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c8507_usertype = 'Standard'::text))
29. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_672_t899_userdim_c8517_userroleid_end_s_start_s_nul_curr (cost=0.00..198.82 rows=8,871 width=0) (never executed)

  • Index Cond: (c8517_userroleid IS NULL)
30. 3,941.086 3,941.086 ↑ 3.5 12,249 1

Index Scan using idx_777_t922_adobe_p2s_team_assignment__cfact_c868_april2020u on t922_adobe_product_to_sales_team_assignment__cfact_april2020u (cost=0.59..71,098,907.27 rows=43,191 width=24) (actual time=108.396..3,941.086 rows=12,249 loops=1)

  • Index Cond: ((c8688_opportunity__c_closedate >= 20,200,229) AND (c8688_opportunity__c_closedate <= 20,200,529))
  • Filter: ((NOT deleted) AND (start_stamp <= '1585810799999'::bigint) AND (end_stamp > '1585810799999'::bigint) 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: 489,069
  • Buffers: shared hit=14,303 read=62,837
31. 1,528.831 2,587.133 ↓ 1.1 78,199 1

Bitmap Heap Scan on t922_adobe_product_to_sales_team_assignment__cfact_april2020m (cost=470,910.13..110,032,576.28 rows=69,605 width=24) (actual time=1,150.980..2,587.133 rows=78,199 loops=1)

  • Recheck Cond: ((start_stamp <= '1585810799999'::bigint) AND (c8688_opportunity__c_closedate >= 20,200,229) AND (c8688_opportunity__c_closedate <= 20,200,529) AND (end_stamp > '1585810799999'::bigint))
  • Rows Removed by Index Recheck: 2,802,938
  • 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: 559,960
  • Heap Blocks: exact=24,988 lossy=195,210
  • Buffers: shared hit=316,374 read=168
32. 14.012 1,058.302 ↓ 0.0 0 1

BitmapAnd (cost=470,909.97..470,909.97 rows=777,709 width=0) (actual time=1,058.302..1,058.302 rows=0 loops=1)

  • Buffers: shared hit=83,280 read=168
33. 167.758 167.758 ↑ 1.0 2,692,474 1

Bitmap Index Scan on t922_adobe_p2s_team_assignment__cfact_startstampsi_april2020m (cost=0.00..79,159.10 rows=2,725,672 width=0) (actual time=167.758..167.758 rows=2,692,474 loops=1)

  • Index Cond: (start_stamp <= '1585810799999'::bigint)
  • Buffers: shared hit=14,251 read=79
34. 227.299 227.299 ↓ 1.0 3,401,260 1

Bitmap Index Scan on idx_777_t922_adobe_p2s_team_assignment__cfact_c868_april2020m (cost=0.00..80,091.46 rows=3,338,703 width=0) (actual time=227.299..227.299 rows=3,401,260 loops=1)

  • Index Cond: ((c8688_opportunity__c_closedate >= 20,200,229) AND (c8688_opportunity__c_closedate <= 20,200,529))
  • Buffers: shared hit=11,867 read=77
35. 649.233 649.233 ↓ 1.0 10,915,576 1

Bitmap Index Scan on t922_adobe_p2s_team_assignment__cfact_sidendstampu_april2020m (cost=0.00..311,606.70 rows=10,904,283 width=0) (actual time=649.233..649.233 rows=10,915,576 loops=1)

  • Index Cond: (end_stamp > '1585810799999'::bigint)
  • Buffers: shared hit=57,162 read=12
36. 0.009 0.212 ↑ 1.0 42 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=24
37. 0.203 0.203 ↑ 1.0 42 1

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

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 1,722
  • Buffers: shared hit=24
38. 17.081 44,051.899 ↑ 1.0 1 17,081

Subquery Scan on aptsta1882_ft1 (cost=246,128.81..246,131.96 rows=1 width=20) (actual time=2.578..2.579 rows=1 loops=17,081)

  • Filter: (t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8649_opportunity__c = aptsta1882_ft1.c1)
  • Buffers: shared hit=1,247,887 read=192,163
39. 34.162 44,034.818 ↑ 84.0 1 17,081

GroupAggregate (cost=246,128.81..246,130.91 rows=84 width=20) (actual time=2.578..2.578 rows=1 loops=17,081)

  • Group Key: t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8649_opportunity__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8688_opportunity__c_closedate, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8682_opportunity__c_stagenamesid
  • Buffers: shared hit=1,247,887 read=192,163
40. 85.405 44,000.656 ↑ 10.5 8 17,081

Sort (cost=246,128.81..246,129.02 rows=84 width=28) (actual time=2.575..2.576 rows=8 loops=17,081)

  • Sort Key: t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8688_opportunity__c_closedate, t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8682_opportunity__c_stagenamesid
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1,247,887 read=192,163
41. 534.517 43,915.251 ↑ 10.5 8 17,081

Nested Loop Left Join (cost=0.43..246,126.13 rows=84 width=28) (actual time=0.852..2.571 rows=8 loops=17,081)

  • Join Filter: (t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8648_currencyisocodesid = t970_claricurrencytypedim_1.sid)
  • Rows Removed by Join Filter: 322
  • Buffers: shared hit=1,247,887 read=192,163
42. 85.405 43,112.444 ↑ 10.5 8 17,081

Append (cost=0.43..246,027.05 rows=84 width=24) (actual time=0.847..2.524 rows=8 loops=17,081)

  • Buffers: shared hit=1,247,863 read=192,163
43. 6,896.158 7,566.883 ↓ 0.0 0 17,081

Index Scan using idx_771_t922_adobe_p2s_team_assignment__cfact_c864_2020u on t922_adobe_product_to_sales_team_assignment__cfact_2020u t922_adobe_product_to_sales_team_assignment__cfact_2020u_1 (cost=0.43..23,164.02 rows=16 width=24) (actual time=0.443..0.443 rows=0 loops=17,081)

  • Index Cond: (t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8649_opportunity__c = c8649_opportunity__c)
  • Filter: ((NOT deleted) AND (start_stamp <= '1601535599999'::bigint) AND (end_stamp > '1601535599999'::bigint) AND ((alternatives: SubPlan 10 or hashed SubPlan 11) OR (alternatives: SubPlan 12 or hashed SubPlan 13)))
  • Rows Removed by Filter: 20
  • Buffers: shared hit=89,399 read=18,494
44.          

SubPlan (for Index Scan)

45. 268.290 670.725 ↑ 1.0 1 134,145

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

  • Buffers: shared hit=939,517
46. 268.290 268.290 ↑ 12.0 1 134,145

Index Scan using idx_673_t903_userrolehier_sid_c8530_ancestor_role_id_end_s_curr on t903_userrolehierarchy_current t903_userrolehierarchy_current_2 (cost=0.43..41.21 rows=12 width=4) (actual time=0.002..0.002 rows=1 loops=134,145)

  • Index Cond: ((sid = t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8713_sales_team_member__c) AND (c8530_ancestor_role_id = 6,405))
  • Buffers: shared hit=536,890
47. 134.145 134.145 ↑ 1.0 1 134,145

Index Scan using ""idx_665_t900_userroledim_sid_start_s_end_s_(c8519_porta_curr"" on t900_userroledim_current t900_userroledim_current_2 (cost=0.28..6.97 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=134,145)

  • Index Cond: (sid = t903_userrolehierarchy_current_2.c8529_user_role_id)
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
  • Buffers: shared hit=402,627
48. 0.000 0.000 ↓ 0.0 0

Hash Semi Join (cost=8,259.00..28,942.94 rows=7,675 width=4) (never executed)

  • Hash Cond: (t903_userrolehierarchy_current_3.c8529_user_role_id = t900_userroledim_current_3.sid)
49. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t903_userrolehierarchy_current t903_userrolehierarchy_current_3 (cost=4,757.79..24,788.22 rows=216,434 width=8) (never executed)

  • Recheck Cond: (c8530_ancestor_role_id = 6,405)
50. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_674_t903_userrolehier_c8530_ancestor_role_id_end_stamp_curr (cost=0.00..4,703.68 rows=216,434 width=0) (never executed)

  • Index Cond: (c8530_ancestor_role_id = 6,405)
51. 0.000 0.000 ↓ 0.0 0

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

52. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t900_userroledim_current t900_userroledim_current_3 (cost=51.18..3,475.59 rows=2,049 width=4) (never executed)

  • Recheck Cond: ((c8519_portaltype IS NULL) OR (c8519_portaltype = 'None'::text))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
53. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ""idx_665_t900_userroledim_sid_start_s_end_s_(c8519_porta_curr"" (cost=0.00..50.66 rows=2,077 width=0) (never executed)

54. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_670_t899_userdim_sid_end_stamp_start_stamp_null_curr on t899_userdim_current t899_userdim_current_2 (cost=0.42..8.44 rows=1 width=0) (never executed)

  • Index Cond: (sid = t922_adobe_product_to_sales_team_assignment__cfact_2020u_1.c8713_sales_team_member__c)
  • Filter: ((NOT deleted) AND (c8517_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c8507_usertype = 'Standard'::text))
55. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t899_userdim_current t899_userdim_current_3 (cost=199.98..8,537.13 rows=4,617 width=4) (never executed)

  • Recheck Cond: (c8517_userroleid IS NULL)
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c8507_usertype = 'Standard'::text))
56. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_672_t899_userdim_c8517_userroleid_end_s_start_s_nul_curr (cost=0.00..198.82 rows=8,871 width=0) (never executed)

  • Index Cond: (c8517_userroleid IS NULL)
57. 9,326.226 9,326.226 ↑ 6.5 4 17,081

Index Scan using idx_771_t922_adobe_p2s_team_assignment__cfact_c864_sept2020u on t922_adobe_product_to_sales_team_assignment__cfact_sept2020u (cost=0.43..20,714.82 rows=26 width=24) (actual time=0.239..0.546 rows=4 loops=17,081)

  • Index Cond: (t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8649_opportunity__c = c8649_opportunity__c)
  • Filter: ((NOT deleted) AND (start_stamp <= '1601535599999'::bigint) AND (end_stamp > '1601535599999'::bigint) AND ((alternatives: SubPlan 10 or hashed SubPlan 11) OR (alternatives: SubPlan 12 or hashed SubPlan 13)))
  • Rows Removed by Filter: 27
  • Buffers: shared hit=595,717 read=22,159
58. 26,133.930 26,133.930 ↑ 10.5 4 17,081

Index Scan using idx_771_t922_adobe_p2s_team_assignment__cfact_c864_sept2020m on t922_adobe_product_to_sales_team_assignment__cfact_sept2020m (cost=0.44..202,148.21 rows=42 width=24) (actual time=0.176..1.530 rows=4 loops=17,081)

  • Index Cond: (t922_adobe_product_to_sales_team_assignment__cfact_2020u.c8649_opportunity__c = c8649_opportunity__c)
  • Filter: ((NOT deleted) AND (start_stamp <= '1601535599999'::bigint) AND (end_stamp > '1601535599999'::bigint) AND ((alternatives: SubPlan 10 or hashed SubPlan 11) OR (alternatives: SubPlan 12 or hashed SubPlan 13)))
  • Rows Removed by Filter: 162
  • Buffers: shared hit=562,747 read=151,510
59. 268.084 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
60. 0.206 0.206 ↑ 1.0 42 1

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

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

SubPlan (for Nested Loop Left Join)

62. 0.002 0.002 ↑ 1.0 6 1

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

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

64.          

CTE openwont1ids

65. 14.079 54,891.247 ↓ 85.4 17,081 1

HashAggregate (cost=413.30..415.30 rows=200 width=4) (actual time=54,887.840..54,891.247 rows=17,081 loops=1)

  • Group Key: cte0_5.c1
  • Buffers: shared hit=1,787,766 read=331,073, temp read=467 written=468
66. 54,877.168 54,877.168 ↑ 1.1 17,081 1

CTE Scan on openwont1 cte0_5 (cost=0.00..367.38 rows=18,369 width=4) (actual time=10,986.275..54,877.168 rows=17,081 loops=1)

  • Buffers: shared hit=1,787,766 read=331,073, temp read=467 written=468
67.          

CTE bucketab

68. 6.100 9.545 ↑ 141.3 13 1

HashAggregate (cost=642.91..661.28 rows=1,837 width=68) (actual time=9.536..9.545 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
69. 3.445 3.445 ↑ 1.1 17,081 1

CTE Scan on openwont1 cte0_6 (cost=0.00..367.38 rows=18,369 width=64) (actual time=0.002..3.445 rows=17,081 loops=1)

70.          

CTE picklist_3

71. 0.040 0.040 ↑ 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.040 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
72.          

CTE bucketd

73. 1.156 57,592.714 ↑ 15.2 4 1

GroupAggregate (cost=148,459,429.07..148,459,432.64 rows=61 width=120) (actual time=57,591.156..57,592.714 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=2,172,913 read=331,073, temp read=467 written=468
74. 2.028 57,591.558 ↓ 33.2 6,645 1

Sort (cost=148,459,428.78..148,459,429.28 rows=200 width=52) (actual time=57,591.142..57,591.558 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=2,172,913 read=331,073, temp read=467 written=468
75. 1.863 57,589.530 ↓ 33.2 6,645 1

Nested Loop Left Join (cost=128,447,941.29..148,459,421.14 rows=200 width=52) (actual time=57,117.589..57,589.530 rows=6,645 loops=1)

  • Buffers: shared hit=2,172,913 read=331,073, temp read=467 written=468
76. 7.374 57,129.118 ↓ 33.2 6,645 1

GroupAggregate (cost=128,347,385.96..128,348,133.80 rows=200 width=12) (actual time=57,116.818..57,129.118 rows=6,645 loops=1)

  • Group Key: t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8649_opportunity__c
  • Buffers: shared hit=2,034,561 read=331,073, temp read=467 written=468
77. 15.651 57,121.744 ↑ 1.9 39,371 1

Sort (cost=128,347,385.96..128,347,572.42 rows=74,584 width=20) (actual time=57,116.809..57,121.744 rows=39,371 loops=1)

  • Sort Key: t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8649_opportunity__c
  • Sort Method: quicksort Memory: 4,022kB
  • Buffers: shared hit=2,034,561 read=331,073, temp read=467 written=468
78. 7.744 57,106.093 ↑ 1.9 39,371 1

Hash Left Join (cost=277,589.81..128,341,349.66 rows=74,584 width=20) (actual time=55,735.321..57,106.093 rows=39,371 loops=1)

  • Hash Cond: (t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8648_currencyisocodesid = t970_claricurrencytypedim_2.sid)
  • Buffers: shared hit=2,034,561 read=331,073, temp read=467 written=468
79. 4.722 57,098.142 ↑ 1.9 39,371 1

Nested Loop (cost=277,543.24..128,337,044.34 rows=74,584 width=16) (actual time=55,735.100..57,098.142 rows=39,371 loops=1)

  • Buffers: shared hit=2,034,537 read=331,073, temp read=467 written=468
80. 0.013 0.048 ↑ 1.0 6 1

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

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

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

  • Buffers: shared hit=5
82. 4.632 57,093.372 ↑ 2.1 6,562 6

Append (cost=277,543.10..21,389,372.19 rows=13,517 width=20) (actual time=9,304.598..9,515.562 rows=6,562 loops=6)

  • Buffers: shared hit=2,034,532 read=331,073, temp read=467 written=468
83. 0.000 55,410.432 ↓ 0.0 0 6

Bitmap Heap Scan on t922_adobe_product_to_sales_team_assignment__cfact_2020u t922_adobe_product_to_sales_team_assignment__cfact_2020u_2 (cost=277,543.10..4,420,065.90 rows=4,088 width=20) (actual time=9,235.072..9,235.072 rows=0 loops=6)

  • Recheck 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 (end_stamp > '1601535599999'::bigint))
  • Rows Removed by Index Recheck: 4
  • Filter: ((NOT deleted) AND (start_stamp <= '1601535599999'::bigint) 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: 5
  • Heap Blocks: exact=34
  • Buffers: shared hit=1,850,177 read=331,073, temp read=467 written=468
84. 0.642 512.250 ↓ 0.0 0 6

BitmapAnd (cost=277,538.60..277,538.60 rows=28,999 width=0) (actual time=85.375..85.375 rows=0 loops=6)

  • Buffers: shared hit=62,377
85. 156.588 156.588 ↑ 5,224.0 16 6

Bitmap Index Scan on idx_778_t922_adobe_p2s_team_assignment__cfact_c868_2020u (cost=0.00..19,395.44 rows=83,584 width=0) (actual time=26.098..26.098 rows=16 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))
  • Buffers: shared hit=14,964
86. 355.020 355.020 ↑ 1.0 3,214,735 1

Bitmap Index Scan on t922_adobe_p2s_team_assignment__cfact_sidendstampu_2020u (cost=0.00..258,130.57 rows=3,236,952 width=0) (actual time=355.020..355.020 rows=3,214,735 loops=1)

  • Index Cond: (end_stamp > '1601535599999'::bigint)
  • Buffers: shared hit=47,413
87.          

SubPlan (for Bitmap Heap Scan)

88. 54,897.756 54,897.756 ↓ 85.4 17,081 3

CTE Scan on openwont1ids cte0_8 (cost=0.00..4.00 rows=200 width=4) (actual time=18,295.948..18,299.252 rows=17,081 loops=3)

  • Buffers: shared hit=1,787,766 read=331,073, temp read=467 written=468
89. 0.000 0.000 ↓ 0.0 0

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

90. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_673_t903_userrolehier_sid_c8530_ancestor_role_id_end_s_curr on t903_userrolehierarchy_current t903_userrolehierarchy_current_4 (cost=0.43..41.21 rows=12 width=4) (never executed)

  • Index Cond: ((sid = t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8713_sales_team_member__c) AND (c8530_ancestor_role_id = 6,405))
91. 0.000 0.000 ↓ 0.0 0

Index Scan using ""idx_665_t900_userroledim_sid_start_s_end_s_(c8519_porta_curr"" on t900_userroledim_current t900_userroledim_current_4 (cost=0.28..6.97 rows=1 width=4) (never executed)

  • Index Cond: (sid = t903_userrolehierarchy_current_4.c8529_user_role_id)
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
92. 60.344 155.796 ↓ 6.3 48,651 2

Hash Semi Join (cost=8,259.00..28,942.94 rows=7,675 width=4) (actual time=9.799..77.898 rows=48,651 loops=2)

  • Hash Cond: (t903_userrolehierarchy_current_5.c8529_user_role_id = t900_userroledim_current_5.sid)
  • Buffers: shared hit=26,198
93. 78.964 94.446 ↑ 1.1 205,928 2

Bitmap Heap Scan on t903_userrolehierarchy_current t903_userrolehierarchy_current_5 (cost=4,757.79..24,788.22 rows=216,434 width=8) (actual time=9.284..47.223 rows=205,928 loops=2)

  • Recheck Cond: (c8530_ancestor_role_id = 6,405)
  • Heap Blocks: exact=24,378
  • Buffers: shared hit=25,792
94. 15.482 15.482 ↑ 1.1 205,928 2

Bitmap Index Scan on idx_674_t903_userrolehier_c8530_ancestor_role_id_end_stamp_curr (cost=0.00..4,703.68 rows=216,434 width=0) (actual time=7.741..7.741 rows=205,928 loops=2)

  • Index Cond: (c8530_ancestor_role_id = 6,405)
  • Buffers: shared hit=1,414
95. 0.121 1.006 ↑ 2.2 950 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 66kB
  • Buffers: shared hit=406
96. 0.785 0.885 ↑ 2.2 950 1

Bitmap Heap Scan on t900_userroledim_current t900_userroledim_current_5 (cost=51.18..3,475.59 rows=2,049 width=4) (actual time=0.141..0.885 rows=950 loops=1)

  • Recheck Cond: ((c8519_portaltype IS NULL) OR (c8519_portaltype = 'None'::text))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
  • Rows Removed by Filter: 1,240
  • Heap Blocks: exact=394
  • Buffers: shared hit=406
97. 0.100 0.100 ↓ 1.1 2,190 1

Bitmap Index Scan on ""idx_665_t900_userroledim_sid_start_s_end_s_(c8519_porta_curr"" (cost=0.00..50.66 rows=2,077 width=0) (actual time=0.100..0.100 rows=2,190 loops=1)

  • Buffers: shared hit=12
98. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_670_t899_userdim_sid_end_stamp_start_stamp_null_curr on t899_userdim_current t899_userdim_current_4 (cost=0.42..8.44 rows=1 width=0) (never executed)

  • Index Cond: (sid = t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8713_sales_team_member__c)
  • Filter: ((NOT deleted) AND (c8517_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c8507_usertype = 'Standard'::text))
99. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t899_userdim_current t899_userdim_current_5 (cost=199.98..8,537.13 rows=4,617 width=4) (never executed)

  • Recheck Cond: (c8517_userroleid IS NULL)
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c8507_usertype = 'Standard'::text))
100. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_672_t899_userdim_c8517_userroleid_end_s_start_s_nul_curr (cost=0.00..198.82 rows=8,871 width=0) (never executed)

  • Index Cond: (c8517_userroleid IS NULL)
101. 497.850 497.850 ↑ 1.4 5,417 6

Index Scan using idx_778_t922_adobe_p2s_team_assignment__cfact_c868_sept2020u on t922_adobe_product_to_sales_team_assignment__cfact_sept2020u t922_adobe_product_to_sales_team_assignment__cfact_sept2020u_1 (cost=4.93..11,871,112.09 rows=7,625 width=20) (actual time=36.486..82.975 rows=5,417 loops=6)

  • Index Cond: ((c8688_opportunity__c_closedate >= 20,200,229) AND (c8688_opportunity__c_closedate <= 20,200,529) AND (c8682_opportunity__c_stagenamesid = cte0_9.c1))
  • Filter: ((NOT deleted) AND (start_stamp <= '1601535599999'::bigint) AND (end_stamp > '1601535599999'::bigint) 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: 47,275
  • Buffers: shared hit=27,591
102. 251.802 1,180.458 ↑ 1.6 1,145 6

Bitmap Heap Scan on t922_adobe_product_to_sales_team_assignment__cfact_sept2020m t922_adobe_product_to_sales_team_assignment__cfact_sept2020m_1 (cost=556,692.57..5,098,194.20 rows=1,804 width=20) (actual time=170.698..196.743 rows=1,145 loops=6)

  • Recheck 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 (end_stamp > '1601535599999'::bigint))
  • Rows Removed by Index Recheck: 15,545
  • Filter: ((NOT deleted) AND (start_stamp <= '1601535599999'::bigint) 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: 35,237
  • Heap Blocks: exact=12,081 lossy=12,299
  • Buffers: shared hit=156,764
103. 8.641 928.656 ↓ 0.0 0 6

BitmapAnd (cost=556,688.07..556,688.07 rows=31,705 width=0) (actual time=154.776..154.776 rows=0 loops=6)

  • Buffers: shared hit=119,488
104. 271.716 271.716 ↑ 1.1 114,157 6

Bitmap Index Scan on idx_778_t922_adobe_p2s_team_assignment__cfact_c868_sept2020m (cost=0.00..28,880.32 rows=121,678 width=0) (actual time=45.286..45.286 rows=114,157 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))
  • Buffers: shared hit=22,443
105. 648.299 648.299 ↓ 1.0 4,981,925 1

Bitmap Index Scan on t922_adobe_p2s_team_assignment__cfact_sidendstampu_sept2020m (cost=0.00..527,802.08 rows=4,952,947 width=0) (actual time=648.299..648.299 rows=4,981,925 loops=1)

  • Index Cond: (end_stamp > '1601535599999'::bigint)
  • Buffers: shared hit=97,045
106. 0.008 0.207 ↑ 1.0 42 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=24
107. 0.199 0.199 ↑ 1.0 42 1

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

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

Subquery Scan on aptsta1882_ft1_1 (cost=100,555.04..100,556.41 rows=1 width=12) (actual time=0.069..0.069 rows=0 loops=6,645)

  • Filter: (t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8649_opportunity__c = aptsta1882_ft1_1.c1)
  • Buffers: shared hit=138,347
109. 6.645 458.505 ↓ 0.0 0 6,645

Group (cost=100,555.04..100,555.65 rows=61 width=12) (actual time=0.069..0.069 rows=0 loops=6,645)

  • Group Key: t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8649_opportunity__c, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8682_opportunity__c_stagenamesid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8688_opportunity__c_closedate
  • Buffers: shared hit=138,347
110. 6.645 451.860 ↑ 61.0 1 6,645

Sort (cost=100,555.04..100,555.19 rows=61 width=12) (actual time=0.068..0.068 rows=1 loops=6,645)

  • Sort Key: t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8682_opportunity__c_stagenamesid, t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8688_opportunity__c_closedate
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=138,347
111. 26.798 445.215 ↑ 61.0 1 6,645

Nested Loop Left Join (cost=0.43..100,553.23 rows=61 width=12) (actual time=0.051..0.067 rows=1 loops=6,645)

  • Join Filter: (t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8648_currencyisocodesid = t970_claricurrencytypedim_3.sid)
  • Rows Removed by Join Filter: 40
  • Buffers: shared hit=138,347
112. 13.290 405.345 ↑ 61.0 1 6,645

Append (cost=0.43..100,468.64 rows=61 width=16) (actual time=0.050..0.061 rows=1 loops=6,645)

  • Buffers: shared hit=138,323
113. 0.436 26.580 ↓ 0.0 0 6,645

Index Scan using idx_771_t922_adobe_p2s_team_assignment__cfact_c864_2020u on t922_adobe_product_to_sales_team_assignment__cfact_2020u t922_adobe_product_to_sales_team_assignment__cfact_2020u_3 (cost=0.43..23,164.02 rows=25 width=16) (actual time=0.004..0.004 rows=0 loops=6,645)

  • Index Cond: (t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8649_opportunity__c = c8649_opportunity__c)
  • Filter: ((NOT deleted) AND (start_stamp <= '1585810799999'::bigint) AND (end_stamp > '1585810799999'::bigint) AND ((alternatives: SubPlan 24 or hashed SubPlan 25) OR (alternatives: SubPlan 26 or hashed SubPlan 27)))
  • Rows Removed by Filter: 3
  • Buffers: shared hit=23,071
114.          

SubPlan (for Index Scan)

115. 6.536 26.144 ↑ 1.0 1 6,536

Nested Loop Semi Join (cost=0.71..131.50 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=6,536)

  • Buffers: shared hit=45,774
116. 13.072 13.072 ↑ 12.0 1 6,536

Index Scan using idx_673_t903_userrolehier_sid_c8530_ancestor_role_id_end_s_curr on t903_userrolehierarchy_current t903_userrolehierarchy_current_6 (cost=0.43..41.21 rows=12 width=4) (actual time=0.002..0.002 rows=1 loops=6,536)

  • Index Cond: ((sid = t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8713_sales_team_member__c) AND (c8530_ancestor_role_id = 6,405))
  • Buffers: shared hit=26,158
117. 6.536 6.536 ↑ 1.0 1 6,536

Index Scan using ""idx_665_t900_userroledim_sid_start_s_end_s_(c8519_porta_curr"" on t900_userroledim_current t900_userroledim_current_6 (cost=0.28..6.97 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=6,536)

  • Index Cond: (sid = t903_userrolehierarchy_current_6.c8529_user_role_id)
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
  • Buffers: shared hit=19,616
118. 0.000 0.000 ↓ 0.0 0

Hash Semi Join (cost=8,259.00..28,942.94 rows=7,675 width=4) (never executed)

  • Hash Cond: (t903_userrolehierarchy_current_7.c8529_user_role_id = t900_userroledim_current_7.sid)
119. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t903_userrolehierarchy_current t903_userrolehierarchy_current_7 (cost=4,757.79..24,788.22 rows=216,434 width=8) (never executed)

  • Recheck Cond: (c8530_ancestor_role_id = 6,405)
120. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_674_t903_userrolehier_c8530_ancestor_role_id_end_stamp_curr (cost=0.00..4,703.68 rows=216,434 width=0) (never executed)

  • Index Cond: (c8530_ancestor_role_id = 6,405)
121. 0.000 0.000 ↓ 0.0 0

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

122. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t900_userroledim_current t900_userroledim_current_7 (cost=51.18..3,475.59 rows=2,049 width=4) (never executed)

  • Recheck Cond: ((c8519_portaltype IS NULL) OR (c8519_portaltype = 'None'::text))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
123. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ""idx_665_t900_userroledim_sid_start_s_end_s_(c8519_porta_curr"" (cost=0.00..50.66 rows=2,077 width=0) (never executed)

124. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_670_t899_userdim_sid_end_stamp_start_stamp_null_curr on t899_userdim_current t899_userdim_current_6 (cost=0.42..8.44 rows=1 width=0) (never executed)

  • Index Cond: (sid = t922_adobe_product_to_sales_team_assignment__cfact_2020u_3.c8713_sales_team_member__c)
  • Filter: ((NOT deleted) AND (c8517_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c8507_usertype = 'Standard'::text))
125. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t899_userdim_current t899_userdim_current_7 (cost=199.98..8,537.13 rows=4,617 width=4) (never executed)

  • Recheck Cond: (c8517_userroleid IS NULL)
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c8507_usertype = 'Standard'::text))
126. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_672_t899_userdim_c8517_userroleid_end_s_start_s_nul_curr (cost=0.00..198.82 rows=8,871 width=0) (never executed)

  • Index Cond: (c8517_userroleid IS NULL)
127. 19.935 19.935 ↓ 0.0 0 6,645

Index Scan using idx_771_t922_adobe_p2s_team_assignment__cfact_c864_april2020u on t922_adobe_product_to_sales_team_assignment__cfact_april2020u t922_adobe_product_to_sales_team_assignment__cfact_april2020u_1 (cost=0.43..15,264.42 rows=18 width=16) (actual time=0.003..0.003 rows=0 loops=6,645)

  • Index Cond: (t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8649_opportunity__c = c8649_opportunity__c)
  • Filter: ((NOT deleted) AND (start_stamp <= '1585810799999'::bigint) AND (end_stamp > '1585810799999'::bigint) AND ((alternatives: SubPlan 24 or hashed SubPlan 25) OR (alternatives: SubPlan 26 or hashed SubPlan 27)))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=22,270
128. 345.540 345.540 ↑ 18.0 1 6,645

Index Scan using idx_771_t922_adobe_p2s_team_assignment__cfact_c864_april2020m on t922_adobe_product_to_sales_team_assignment__cfact_april2020m t922_adobe_product_to_sales_team_assignment__cfact_april2020m_1 (cost=0.43..62,040.19 rows=18 width=16) (actual time=0.041..0.052 rows=1 loops=6,645)

  • Index Cond: (t922_adobe_product_to_sales_team_assignment__cfact_2020u_2.c8649_opportunity__c = c8649_opportunity__c)
  • Filter: ((NOT deleted) AND (start_stamp <= '1585810799999'::bigint) AND (end_stamp > '1585810799999'::bigint) AND ((alternatives: SubPlan 24 or hashed SubPlan 25) OR (alternatives: SubPlan 26 or hashed SubPlan 27)))
  • Rows Removed by Filter: 57
  • Buffers: shared hit=92,982
129. 12.871 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
130. 0.201 0.201 ↑ 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.201 rows=42 loops=1)

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

SubPlan (for Nested Loop Left Join)

132. 0.044 0.044 ↑ 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.044 rows=13 loops=1)

  • Buffers: shared hit=5
133. 0.007 57,592.727 ↑ 15.2 4 1

Subquery Scan on "*SELECT* 1" (cost=0.00..1.98 rows=61 width=96) (actual time=57,591.162..57,592.727 rows=4 loops=1)

  • Buffers: shared hit=2,172,913 read=331,073, temp read=467 written=468
134. 57,592.720 57,592.720 ↑ 15.2 4 1

CTE Scan on bucketd cte0 (cost=0.00..1.22 rows=61 width=120) (actual time=57,591.158..57,592.720 rows=4 loops=1)

  • Buffers: shared hit=2,172,913 read=331,073, temp read=467 written=468
135. 0.019 9.587 ↑ 141.3 13 1

Hash Left Join (cost=1.32..42.98 rows=1,837 width=76) (actual time=9.570..9.587 rows=13 loops=1)

  • Hash Cond: (cte0_1.c6 = t691_opportunityforecast_indicator__cpicklistdim.sid)
  • Buffers: shared hit=1
136. 9.552 9.552 ↑ 141.3 13 1

CTE Scan on bucketab cte0_1 (cost=0.00..36.74 rows=1,837 width=68) (actual time=9.538..9.552 rows=13 loops=1)

137. 0.005 0.016 ↑ 1.0 14 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
138. 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