explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2MW1

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 1,068,171.958 ↑ 2,885.5 17 1

Append (cost=43,849,113,778.55..43,849,115,382.94 rows=49,054 width=96) (actual time=1,068,156.159..1,068,171.958 rows=17 loops=1)

  • Buffers: shared hit=1,319,094 read=7,971,289, temp read=466 written=467
2.          

CTE picklist_0

3. 4.780 4.780 ↑ 1.0 6 1

Seq Scan on t693_opportunitystagenamepicklistdim (cost=0.00..6.63 rows=6 width=4) (actual time=0.909..4.780 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.037 0.037 ↑ 1.0 7 1

Seq Scan on t693_opportunitystagenamepicklistdim t693_opportunitystagenamepicklistdim_1 (cost=0.00..6.74 rows=7 width=4) (actual time=0.010..0.037 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.725 942,964.153 ↑ 28.6 17,081 1

Nested Loop Left Join (cost=5,797,936,296.31..35,683,241,935.68 rows=488,539 width=34) (actual time=753,089.182..942,964.153 rows=17,081 loops=1)

  • Buffers: shared hit=1,112,462 read=3,937,132, temp read=466 written=467
8. 38.398 752,699.409 ↑ 28.6 17,081 1

GroupAggregate (cost=5,797,875,126.95..5,797,888,690.75 rows=488,539 width=20) (actual time=752,633.857..752,699.409 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=50,977 read=3,348,861, temp read=466 written=467
9. 121.554 752,661.011 ↑ 6.4 90,674 1

Sort (cost=5,797,875,126.95..5,797,876,573.35 rows=578,561 width=28) (actual time=752,633.831..752,661.011 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,728kB
  • Buffers: shared hit=50,977 read=3,348,861, temp read=466 written=467
10. 27.601 752,539.457 ↑ 6.4 90,674 1

Hash Left Join (cost=4,749,422.50..5,797,805,906.55 rows=578,561 width=28) (actual time=685,034.927..752,539.457 rows=90,674 loops=1)

  • Hash Cond: (t922_adobe_product_to_sales_team_assignment__cfact.c8648_currencyisocodesid = t970_claricurrencytypedim.sid)
  • Buffers: shared hit=50,977 read=3,348,861
11. 68,775.063 752,498.765 ↑ 6.4 90,674 1

Bitmap Heap Scan on t922_adobe_product_to_sales_team_assignment__cfact (cost=4,749,375.93..5,797,768,684.73 rows=578,561 width=24) (actual time=685,021.813..752,498.765 rows=90,674 loops=1)

  • Recheck Cond: ((end_stamp > '1585810799999'::bigint) AND (start_stamp <= '1585810799999'::bigint))
  • Rows Removed by Index Recheck: 30,234,914
  • Filter: ((NOT deleted) AND (c8688_opportunity__c_closedate >= 20,200,229) AND (c8688_opportunity__c_closedate <= 20,200,529) 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: 11,210,669
  • Heap Blocks: exact=22,471 lossy=2,489,022
  • Buffers: shared hit=50,977 read=3,348,837
12. 681,478.427 681,478.427 ↑ 3.5 11,804,525 1

Bitmap Index Scan on idx_774_t922_adobe_product_to_sales_team_assignment__cfact_c867 (cost=0.00..4,749,231.13 rows=40,774,520 width=0) (actual time=681,478.427..681,478.427 rows=11,804,525 loops=1)

  • Index Cond: ((end_stamp > '1585810799999'::bigint) AND (start_stamp <= '1585810799999'::bigint))
  • Buffers: shared hit=42,794 read=823,829
13.          

SubPlan (for Bitmap Heap Scan)

14. 0.041 0.041 ↑ 1.0 7 1

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

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

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

16. 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))
17. 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))
18. 29.802 2,245.234 ↓ 7.4 48,651 1

Hash Join (cost=12,924.13..68,634.36 rows=6,591 width=4) (actual time=1,414.123..2,245.234 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
19. 805.804 2,178.472 ↑ 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,377.131..2,178.472 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
20. 1,372.668 1,372.668 ↑ 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,372.668..1,372.668 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
21. 0.184 36.960 ↑ 3.3 950 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 66kB
  • Buffers: shared hit=9 read=500
22. 25.019 36.776 ↑ 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=18.306..36.776 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
23. 11.757 11.757 ↑ 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=11.757..11.757 rows=2,245 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
  • Buffers: shared hit=8 read=31
24. 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))
25. 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))
26. 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))
27. 0.023 13.091 ↑ 1.0 42 1

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

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

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

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

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

  • Filter: (t922_adobe_product_to_sales_team_assignment__cfact.c8649_opportunity__c = aptsta1882_ft1.c1)
  • Buffers: shared hit=1,061,485 read=588,271
30. 85.405 190,196.935 ↑ 101.0 1 17,081

GroupAggregate (cost=61,169.09..61,171.62 rows=101 width=20) (actual time=11.135..11.135 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,061,485 read=588,271
31. 153.729 190,111.530 ↑ 12.6 8 17,081

Sort (cost=61,169.09..61,169.35 rows=101 width=28) (actual time=11.129..11.130 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,061,485 read=588,271
32. 502.858 189,957.801 ↑ 12.6 8 17,081

Nested Loop Left Join (cost=0.57..61,165.73 rows=101 width=28) (actual time=8.020..11.121 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,061,485 read=588,271
33. 185,296.448 189,052.508 ↑ 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.011..11.068 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,061,470 read=588,262
34.          

SubPlan (for Index Scan)

35. 268.290 3,756.060 ↑ 1.0 1 134,145

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

  • Buffers: shared hit=944,962 read=1,527
36. 3,085.335 3,085.335 ↑ 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.023..0.023 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,163 read=1,511
37. 402.435 402.435 ↑ 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.003..0.003 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,427 read=16
38. 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)
39. 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))
40. 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))
41. 0.000 0.000 ↓ 0.0 0

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

42. 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))
43. 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)
44. 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))
45. 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))
46. 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))
47. 391.597 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=15 read=9
48. 10.838 10.838 ↑ 1.0 42 1

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

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

SubPlan (for Nested Loop Left Join)

50. 0.001 0.001 ↑ 1.0 6 1

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

51. 0.002 0.002 ↑ 1.0 6 1

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

52.          

CTE openwont1ids

53. 26.819 943,019.339 ↓ 85.4 17,081 1

HashAggregate (cost=10,992.13..10,994.13 rows=200 width=4) (actual time=943,016.068..943,019.339 rows=17,081 loops=1)

  • Group Key: cte0_5.c1
  • Buffers: shared hit=1,112,462 read=3,937,132, temp read=466 written=467
54. 942,992.520 942,992.520 ↑ 28.6 17,081 1

CTE Scan on openwont1 cte0_5 (cost=0.00..9,770.78 rows=488,539 width=4) (actual time=753,089.184..942,992.520 rows=17,081 loops=1)

  • Buffers: shared hit=1,112,462 read=3,937,132, temp read=466 written=467
55.          

CTE bucketab

56. 3.336 12.670 ↑ 3,758.0 13 1

GroupAggregate (cost=74,301.11..83,339.08 rows=48,854 width=68) (actual time=8.626..12.670 rows=13 loops=1)

  • Group Key: cte0_6.c5, (CASE WHEN cte0_6.c6 THEN CASE WHEN cte0_6.c4 THEN 'convertedWon'::text ELSE 'convertedOpen'::text END ELSE CASE WHEN cte0_6.c4 THEN 'unconvertedWon'::text ELSE 'unconvertedOpen'::text END END), cte0_6.c3
57. 6.797 9.334 ↑ 28.6 17,081 1

Sort (cost=74,301.11..75,522.46 rows=488,539 width=64) (actual time=8.235..9.334 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
58. 2.537 2.537 ↑ 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.537 rows=17,081 loops=1)

59.          

CTE picklist_3

60. 6.147 6.147 ↑ 1.0 13 1

Seq Scan on t693_opportunitystagenamepicklistdim t693_opportunitystagenamepicklistdim_2 (cost=0.00..7.44 rows=13 width=4) (actual time=0.885..6.147 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 read=5
61.          

CTE bucketd

62. 1.760 1,068,158.474 ↑ 50.0 4 1

GroupAggregate (cost=8,165,776,872.72..8,165,777,488.85 rows=200 width=120) (actual time=1,068,156.148..1,068,158.474 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,319,094 read=7,971,288, temp read=466 written=467
63. 5.348 1,068,156.714 ↑ 7.4 6,645 1

Sort (cost=8,165,776,872.43..8,165,776,994.96 rows=49,010 width=52) (actual time=1,068,156.126..1,068,156.714 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,319,094 read=7,971,288, temp read=466 written=467
64. 10.698 1,068,151.366 ↑ 7.4 6,645 1

Nested Loop Left Join (cost=2,236,987,173.68..8,165,773,054.36 rows=49,010 width=52) (actual time=1,047,643.762..1,068,151.366 rows=6,645 loops=1)

  • Buffers: shared hit=1,319,094 read=7,971,288, temp read=466 written=467
65. 13.209 1,047,601.458 ↑ 5.6 6,645 1

GroupAggregate (cost=2,236,828,098.48..2,236,829,679.52 rows=37,270 width=12) (actual time=1,047,581.766..1,047,601.458 rows=6,645 loops=1)

  • Group Key: t922_adobe_product_to_sales_team_assignment__cfact_2.c8649_opportunity__c
  • Buffers: shared hit=1,247,140 read=7,940,501, temp read=466 written=467
66. 26.584 1,047,588.249 ↑ 3.1 39,371 1

Sort (cost=2,236,828,098.48..2,236,828,400.57 rows=120,834 width=20) (actual time=1,047,581.757..1,047,588.249 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,247,140 read=7,940,501, temp read=466 written=467
67. 13.311 1,047,561.665 ↑ 3.1 39,371 1

Hash Left Join (cost=3,907,957.36..2,236,815,416.98 rows=120,834 width=20) (actual time=960,738.469..1,047,561.665 rows=39,371 loops=1)

  • Hash Cond: (t922_adobe_product_to_sales_team_assignment__cfact_2.c8648_currencyisocodesid = t970_claricurrencytypedim_2.sid)
  • Buffers: shared hit=1,247,140 read=7,940,501, temp read=466 written=467
68. 26.199 1,047,548.133 ↑ 3.1 39,371 1

Hash Semi Join (cost=3,907,910.78..2,236,807,606.26 rows=120,834 width=16) (actual time=960,738.218..1,047,548.133 rows=39,371 loops=1)

  • Hash Cond: (t922_adobe_product_to_sales_team_assignment__cfact_2.c8682_opportunity__c_stagenamesid = cte0_9.c1)
  • Buffers: shared hit=1,247,116 read=7,940,501, temp read=466 written=467
69. 87,759.335 1,047,517.141 ↑ 2.2 102,191 1

Bitmap Heap Scan on t922_adobe_product_to_sales_team_assignment__cfact t922_adobe_product_to_sales_team_assignment__cfact_2 (cost=3,907,910.59..2,236,805,678.01 rows=222,389 width=20) (actual time=959,957.950..1,047,517.141 rows=102,191 loops=1)

  • Recheck Cond: (end_stamp > '1601535599999'::bigint)
  • Rows Removed by Index Recheck: 34,114,399
  • Filter: ((NOT deleted) AND (start_stamp <= '1601535599999'::bigint) AND (c8688_opportunity__c_closedate >= 20,200,229) AND (c8688_opportunity__c_closedate <= 20,200,529) 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: 15,598,146
  • Heap Blocks: exact=33,528 lossy=3,645,534
  • Buffers: shared hit=1,247,116 read=7,940,496, temp read=466 written=467
70. 14,834.314 14,834.314 ↓ 1.6 25,837,852 1

Bitmap Index Scan on t922_adobe_product_to_sales_team_assignment__cfact_sidendstampu (cost=0.00..3,907,850.49 rows=15,673,053 width=0) (actual time=14,834.314..14,834.314 rows=25,837,852 loops=1)

  • Index Cond: (end_stamp > '1601535599999'::bigint)
  • Buffers: shared hit=4,082 read=704,955
71.          

SubPlan (for Bitmap Heap Scan)

72. 943,022.877 943,022.877 ↓ 85.4 17,081 1

CTE Scan on openwont1ids cte0_8 (cost=0.00..4.00 rows=200 width=4) (actual time=943,016.070..943,022.877 rows=17,081 loops=1)

  • Buffers: shared hit=1,112,462 read=3,937,132, temp read=466 written=467
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. 29.925 1,900.615 ↓ 7.4 48,651 1

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

  • Hash Cond: (t903_userrolehierarchy_5.c8529_user_role_id = t900_userroledim_5.sid)
  • Buffers: shared hit=773 read=21,129
77. 510.357 1,853.754 ↑ 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=1,347.794..1,853.754 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=631 read=20,763
78. 1,343.397 1,343.397 ↑ 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,343.397..1,343.397 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
79. 0.154 16.936 ↑ 3.3 950 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 66kB
  • Buffers: shared hit=142 read=366
80. 15.007 16.782 ↑ 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=3.553..16.782 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=142 read=366
81. 1.775 1.775 ↑ 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=1.775..1.775 rows=2,236 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
  • Buffers: shared hit=31 read=7
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. 0.008 4.793 ↑ 1.0 6 1

Hash (cost=0.12..0.12 rows=6 width=4) (actual time=4.793..4.793 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared read=5
86. 4.785 4.785 ↑ 1.0 6 1

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

  • Buffers: shared read=5
87. 0.009 0.221 ↑ 1.0 42 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=24
88. 0.212 0.212 ↑ 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.212 rows=42 loops=1)

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

Subquery Scan on aptsta1882_ft1_1 (cost=159,074.91..159,080.82 rows=1 width=12) (actual time=3.090..3.090 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=71,954 read=30,782
90. 6.645 20,533.050 ↓ 0.0 0 6,645

Group (cost=159,074.91..159,077.54 rows=263 width=12) (actual time=3.090..3.090 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=71,954 read=30,782
91. 19.935 20,526.405 ↑ 263.0 1 6,645

Sort (cost=159,074.91..159,075.56 rows=263 width=12) (actual time=3.089..3.089 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=71,954 read=30,782
92. 33.879 20,506.470 ↑ 263.0 1 6,645

Nested Loop Left Join (cost=0.57..159,064.33 rows=263 width=12) (actual time=1.704..3.086 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=71,954 read=30,782
93. 20,309.191 20,433.375 ↑ 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.698..3.075 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=71,954 read=30,758
94.          

SubPlan (for Index Scan)

95. 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,164 read=20
96. 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,552 read=20
97. 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
98. 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)
99. 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))
100. 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))
101. 0.000 0.000 ↓ 0.0 0

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

102. 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))
103. 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)
104. 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))
105. 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))
106. 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))
107. 18.883 39.216 ↑ 1.0 42 6,536

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

  • Buffers: shared read=24
108. 20.333 20.333 ↑ 1.0 42 1

Seq Scan on t970_claricurrencytypedim t970_claricurrencytypedim_3 (cost=0.00..46.05 rows=42 width=4) (actual time=1.385..20.333 rows=42 loops=1)

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

SubPlan (for Nested Loop Left Join)

110. 6.160 6.160 ↑ 1.0 13 1

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

  • Buffers: shared read=5
111. 0.011 1,068,158.492 ↑ 50.0 4 1

Subquery Scan on "*SELECT* 1" (cost=0.00..6.50 rows=200 width=96) (actual time=1,068,156.158..1,068,158.492 rows=4 loops=1)

  • Buffers: shared hit=1,319,094 read=7,971,288, temp read=466 written=467
112. 1,068,158.481 1,068,158.481 ↑ 50.0 4 1

CTE Scan on bucketd cte0 (cost=0.00..4.00 rows=200 width=120) (actual time=1,068,156.151..1,068,158.481 rows=4 loops=1)

  • Buffers: shared hit=1,319,094 read=7,971,288, temp read=466 written=467
113. 0.020 13.462 ↑ 3,758.0 13 1

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

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

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

115. 0.008 0.763 ↑ 1.0 14 1

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

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

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

  • Buffers: shared read=1