explain.depesz.com

PostgreSQL's explain analyze made readable

Result: khH2

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Append (cost=48,918,618.45..49,254,308.33 rows=345,667 width=120) (actual rows= loops=)

2.          

CTE picklist_0

3. 0.000 0.000 ↓ 0.0

Seq Scan on t35_opportunitystagenamepicklistdim (cost=0.00..70.54 rows=6 width=4) (actual rows= loops=)

  • Filter: (correlated_value = ANY ('{"06 - Won, Deploy & Expand","6 - Won, Deploy & Expand","Closed - Won","06 - Won. Deploy & Expand",Won,"06 - Won, Deploy & Expand"}'::text[]))
4.          

CTE picklist_1

5. 0.000 0.000 ↓ 0.0

Seq Scan on t35_opportunitystagenamepicklistdim t35_opportunitystagenamepicklistdim_1 (cost=0.00..72.08 rows=7 width=4) (actual rows= loops=)

  • Filter: (correlated_value = ANY ('{"HPE Not Pursued",Lost,Close:Duplicate,"HP Not Pursued",Duplicate,Error,"Closed - Lost"}'::text[]))
6.          

CTE picklist_4

7. 0.000 0.000 ↓ 0.0

Seq Scan on t35_opportunitystagenamepicklistdim t35_opportunitystagenamepicklistdim_2 (cost=0.00..81.31 rows=13 width=4) (actual rows= loops=)

  • Filter: (correlated_value = ANY ('{"06 - Won, Deploy & Expand",Lost,"6 - Won, Deploy & Expand",Close:Duplicate,"HP Not Pursued","06 - Won, Deploy & Expand",Error,"HPE Not Pursued","Closed - Won","06 - Won. Deploy & Expand",Won,Duplicate,"Closed - Lost"}'::text[]))
8.          

CTE bucketac

9. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=14,642,233.42..15,001,246.87 rows=7,558,178 width=92) (actual rows= loops=)

  • Group Key: t21_opp_line_item_fact.c320_opp_forecastcategory, (CASE WHEN (t21_opp_line_item_fact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t21_opp_line_item_fact_1.c270_opp_close_date < 20181101) OR (t21_opp_line_item_fact_1.c270_opp_close_date > 20190131)) THEN 'slipped'::text WHEN (hashed SubPlan 4) THEN CASE WHEN (hashed SubPlan 5) THEN 'won'::text WHEN (hashed SubPlan 6) THEN 'lost'::text ELSE CASE WHEN (t34_opportunityforecastcategorypicklistdim_2.correlated_app_order = t34_opportunityforecastcategorypicklistdim_3.correlated_app_order) THEN 'unchanged'::text WHEN (t34_opportunityforecastcategorypicklistdim_2.correlated_app_order > t34_opportunityforecastcategorypicklistdim_3.correlated_app_order) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
10. 0.000 0.000 ↓ 0.0

Sort (cost=14,211,822.70..14,230,718.15 rows=7,558,178 width=72) (actual rows= loops=)

  • Sort Key: t21_opp_line_item_fact.c320_opp_forecastcategory, (CASE WHEN (t21_opp_line_item_fact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t21_opp_line_item_fact_1.c270_opp_close_date < 20181101) OR (t21_opp_line_item_fact_1.c270_opp_close_date > 20190131)) THEN 'slipped'::text WHEN (hashed SubPlan 4) THEN CASE WHEN (hashed SubPlan 5) THEN 'won'::text WHEN (hashed SubPlan 6) THEN 'lost'::text ELSE CASE WHEN (t34_opportunityforecastcategorypicklistdim_2.correlated_app_order = t34_opportunityforecastcategorypicklistdim_3.correlated_app_order) THEN 'unchanged'::text WHEN (t34_opportunityforecastcategorypicklistdim_2.correlated_app_order > t34_opportunityforecastcategorypicklistdim_3.correlated_app_order) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
11. 0.000 0.000 ↓ 0.0

Hash Join (cost=12,036,620.00..12,728,304.72 rows=7,558,178 width=72) (actual rows= loops=)

  • Hash Cond: ((t21_opp_line_item_fact.c270_opp_close_date = distinctclosedateowner_1.closedate) AND (t21_opp_line_item_fact.c268_opp_ownerid = t166_userrolehierarchy_1.sid))
12. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=11,157,250.56..11,458,499.90 rows=8,322,069 width=72) (actual rows= loops=)

  • Hash Cond: (t21_opp_line_item_fact.c320_opp_forecastcategory = t34_opportunityforecastcategorypicklistdim_2.sid)
13. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=11,157,249.45..11,347,981.71 rows=8,322,069 width=68) (actual rows= loops=)

  • Merge Cond: ((t21_opp_line_item_fact.c270_opp_close_date = cur177_t0_r0.rate_date) AND (t21_opp_line_item_fact.c323_opp_currency_code = cur177_t0_r0.sid))
14. 0.000 0.000 ↓ 0.0

Sort (cost=11,141,326.62..11,159,010.62 rows=7,073,600 width=64) (actual rows= loops=)

  • Sort Key: t21_opp_line_item_fact.c270_opp_close_date, t21_opp_line_item_fact.c323_opp_currency_code
15. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=9,658,224.38..9,804,660.68 rows=7,073,600 width=64) (actual rows= loops=)

  • Merge Cond: ((cur177_t1_r1.rate_date = t21_opp_line_item_fact_1.c270_opp_close_date) AND (cur177_t1_r1.sid = t21_opp_line_item_fact_1.c323_opp_currency_code))
16. 0.000 0.000 ↓ 0.0

Merge Append (cost=0.31..8,226.02 rows=100,865 width=16) (actual rows= loops=)

  • Sort Key: cur177_t1_r1.rate_date, cur177_t1_r1.sid
17. 0.000 0.000 ↓ 0.0

Sort (cost=0.01..0.02 rows=1 width=16) (actual rows= loops=)

  • Sort Key: cur177_t1_r1.rate_date, cur177_t1_r1.sid
18. 0.000 0.000 ↓ 0.0

Seq Scan on clari_conversion_rate cur177_t1_r1 (cost=0.00..0.00 rows=1 width=16) (actual rows= loops=)

  • Filter: (to_iso_code = 'USD'::text)
19. 0.000 0.000 ↓ 0.0

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur177_t1_r1_1 (cost=0.29..6,965.19 rows=100,864 width=16) (actual rows= loops=)

  • Filter: (to_iso_code = 'USD'::text)
20. 0.000 0.000 ↓ 0.0

Materialize (cost=9,657,111.27..9,687,173.39 rows=6,012,425 width=60) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Sort (cost=9,657,111.27..9,672,142.33 rows=6,012,425 width=60) (actual rows= loops=)

  • Sort Key: t21_opp_line_item_fact_1.c270_opp_close_date, t21_opp_line_item_fact_1.c323_opp_currency_code
22. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=8,300,048.92..8,528,017.78 rows=6,012,425 width=60) (actual rows= loops=)

  • Hash Cond: (t21_opp_line_item_fact_1.c320_opp_forecastcategory = t34_opportunityforecastcategorypicklistdim_3.sid)
23. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=8,300,047.81..8,448,171.66 rows=6,012,425 width=60) (actual rows= loops=)

  • Merge Cond: ((t21_opp_line_item_fact_1.sid = t21_opp_line_item_fact.sid) AND (t21_opp_line_item_fact_1.sid = t21_opp_line_item_fact.sid))
24. 0.000 0.000 ↓ 0.0

Sort (cost=4,596,443.57..4,625,776.62 rows=11,733,219 width=32) (actual rows= loops=)

  • Sort Key: t21_opp_line_item_fact_1.sid
25. 0.000 0.000 ↓ 0.0

Seq Scan on t21_opp_line_item_fact t21_opp_line_item_fact_1 (cost=0.00..2,657,267.39 rows=11,733,219 width=32) (actual rows= loops=)

  • Filter: ((NOT deleted) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
26. 0.000 0.000 ↓ 0.0

Materialize (cost=3,703,604.24..3,733,666.36 rows=6,012,425 width=28) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Sort (cost=3,703,604.24..3,718,635.30 rows=6,012,425 width=28) (actual rows= loops=)

  • Sort Key: t21_opp_line_item_fact.sid
28. 0.000 0.000 ↓ 0.0

Seq Scan on t21_opp_line_item_fact (cost=0.29..2,738,912.75 rows=6,012,425 width=28) (actual rows= loops=)

  • Filter: ((NOT deleted) AND (start_stamp <= '1541055600000'::bigint) AND (end_stamp > '1541055600000'::bigint) AND (NOT (hashed SubPlan 7)))
29.          

SubPlan (forSeq Scan)

30. 0.000 0.000 ↓ 0.0

CTE Scan on picklist_4 cte0_4 (cost=0.00..0.26 rows=13 width=4) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Hash (cost=1.05..1.05 rows=5 width=8) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Seq Scan on t34_opportunityforecastcategorypicklistdim t34_opportunityforecastcategorypicklistdim_3 (cost=0.00..1.05 rows=5 width=8) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Sort (cost=10,586.72..10,838.89 rows=100,865 width=16) (actual rows= loops=)

  • Sort Key: cur177_t0_r0.rate_date, cur177_t0_r0.sid
34. 0.000 0.000 ↓ 0.0

Append (cost=0.00..2,203.80 rows=100,865 width=16) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Seq Scan on clari_conversion_rate cur177_t0_r0 (cost=0.00..0.00 rows=1 width=16) (actual rows= loops=)

  • Filter: (to_iso_code = 'USD'::text)
36. 0.000 0.000 ↓ 0.0

Seq Scan on usd_clari_conversion_rate cur177_t0_r0_1 (cost=0.00..2,203.80 rows=100,864 width=16) (actual rows= loops=)

  • Filter: (to_iso_code = 'USD'::text)
37. 0.000 0.000 ↓ 0.0

Hash (cost=1.05..1.05 rows=5 width=8) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Seq Scan on t34_opportunityforecastcategorypicklistdim t34_opportunityforecastcategorypicklistdim_2 (cost=0.00..1.05 rows=5 width=8) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Hash (cost=446,288.27..446,288.27 rows=134,297 width=12) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Unique (cost=445,165.15..446,288.27 rows=134,297 width=12) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Sort (cost=445,165.15..445,539.52 rows=149,749 width=12) (actual rows= loops=)

  • Sort Key: distinctclosedateowner_1.closedate, distinctclosedateowner_1.ownerid
42. 0.000 0.000 ↓ 0.0

Hash Join (cost=396,407.50..429,730.59 rows=149,749 width=12) (actual rows= loops=)

  • Hash Cond: (distinctclosedateowner_1.ownerid = t166_userrolehierarchy_1.sid)
43. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on distinctclosedateowner distinctclosedateowner_1 (cost=3,179.36..32,515.59 rows=149,749 width=8) (actual rows= loops=)

  • Recheck Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
44. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ix_closedate_distinctclosedateowner (cost=0.00..3,141.92 rows=149,749 width=0) (actual rows= loops=)

  • Index Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
45. 0.000 0.000 ↓ 0.0

Hash (cost=390,019.91..390,019.91 rows=195,539 width=4) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Unique (cost=383,611.68..390,019.91 rows=195,539 width=4) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Sort (cost=383,611.68..386,815.79 rows=1,281,645 width=4) (actual rows= loops=)

  • Sort Key: t166_userrolehierarchy_1.sid
48. 0.000 0.000 ↓ 0.0

Hash Join (cost=59,587.81..236,067.08 rows=1,281,645 width=4) (actual rows= loops=)

  • Hash Cond: (t166_userrolehierarchy_1.c662_user_role_id = t67_userroledim_1.sid)
49. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on t166_userrolehierarchy t166_userrolehierarchy_1 (cost=37,189.99..194,344.67 rows=1,281,645 width=8) (actual rows= loops=)

  • Recheck Cond: ((c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
50. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..36,869.58 rows=1,281,645 width=0) (actual rows= loops=)

  • Index Cond: ((c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
51. 0.000 0.000 ↓ 0.0

Hash (cost=21,117.18..21,117.18 rows=78,051 width=4) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on t67_userroledim t67_userroledim_1 (cost=4,261.99..21,117.18 rows=78,051 width=4) (actual rows= loops=)

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

Bitmap Index Scan on ix_t67_userroledim_sid_end_start_timestamp_partial (cost=0.00..4,242.48 rows=78,925 width=0) (actual rows= loops=)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
54.          

SubPlan (forHash Join)

55. 0.000 0.000 ↓ 0.0

HashAggregate (cost=430,104.96..430,349.33 rows=24,437 width=4) (actual rows= loops=)

  • Group Key: distinctclosedateowner.ownerid
56. 0.000 0.000 ↓ 0.0

Hash Join (cost=396,407.50..429,730.59 rows=149,749 width=4) (actual rows= loops=)

  • Hash Cond: (distinctclosedateowner.ownerid = t166_userrolehierarchy.sid)
57. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on distinctclosedateowner (cost=3,179.36..32,515.59 rows=149,749 width=4) (actual rows= loops=)

  • Recheck Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
58. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ix_closedate_distinctclosedateowner (cost=0.00..3,141.92 rows=149,749 width=0) (actual rows= loops=)

  • Index Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
59. 0.000 0.000 ↓ 0.0

Hash (cost=390,019.91..390,019.91 rows=195,539 width=4) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Unique (cost=383,611.68..390,019.91 rows=195,539 width=4) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Sort (cost=383,611.68..386,815.79 rows=1,281,645 width=4) (actual rows= loops=)

  • Sort Key: t166_userrolehierarchy.sid
62. 0.000 0.000 ↓ 0.0

Hash Join (cost=59,587.81..236,067.08 rows=1,281,645 width=4) (actual rows= loops=)

  • Hash Cond: (t166_userrolehierarchy.c662_user_role_id = t67_userroledim.sid)
63. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on t166_userrolehierarchy (cost=37,189.99..194,344.67 rows=1,281,645 width=8) (actual rows= loops=)

  • Recheck Cond: ((c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
64. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..36,869.58 rows=1,281,645 width=0) (actual rows= loops=)

  • Index Cond: ((c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
65. 0.000 0.000 ↓ 0.0

Hash (cost=21,117.18..21,117.18 rows=78,051 width=4) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on t67_userroledim (cost=4,261.99..21,117.18 rows=78,051 width=4) (actual rows= loops=)

  • Recheck Cond: ((end_stamp = '32503680000000'::bigint) AND ((c377_portaltype IS NULL) OR (c377_portaltype = 'None'::text)))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
67. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ix_t67_userroledim_sid_end_start_timestamp_partial (cost=0.00..4,242.48 rows=78,925 width=0) (actual rows= loops=)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
68. 0.000 0.000 ↓ 0.0

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

69. 0.000 0.000 ↓ 0.0

CTE Scan on picklist_1 cte0_3 (cost=0.00..0.14 rows=7 width=4) (actual rows= loops=)

70.          

CTE bucketec

71. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=33,650,735.19..33,917,146.53 rows=6,268,502 width=116) (actual rows= loops=)

  • Group Key: (CASE WHEN (t21_opp_line_item_fact_3.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 9)) THEN CASE WHEN (t21_opp_line_item_fact_3.c270_opp_close_date > 20190131) THEN 'pulledInBucket'::text WHEN (t21_opp_line_item_fact_3.c270_opp_close_date < 20181101) THEN 'pushedInBucket'::text WHEN (hashed SubPlan 10) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END ELSE 'otherBucket'::text END), (CASE WHEN (hashed SubPlan 11) THEN 'won'::text WHEN (hashed SubPlan 12) THEN 'lost'::text ELSE 'newOpen'::text END), t21_opp_line_item_fact_2.c320_opp_forecastcategory
72. 0.000 0.000 ↓ 0.0

Sort (cost=33,220,324.18..33,235,995.44 rows=6,268,502 width=88) (actual rows= loops=)

  • Sort Key: (CASE WHEN (t21_opp_line_item_fact_3.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 9)) THEN CASE WHEN (t21_opp_line_item_fact_3.c270_opp_close_date > 20190131) THEN 'pulledInBucket'::text WHEN (t21_opp_line_item_fact_3.c270_opp_close_date < 20181101) THEN 'pushedInBucket'::text WHEN (hashed SubPlan 10) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END ELSE 'otherBucket'::text END), (CASE WHEN (hashed SubPlan 11) THEN 'won'::text WHEN (hashed SubPlan 12) THEN 'lost'::text ELSE 'newOpen'::text END), t21_opp_line_item_fact_2.c320_opp_forecastcategory
73. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=8,103,589.58..31,912,699.04 rows=6,268,502 width=88) (actual rows= loops=)

  • Hash Cond: ((t21_opp_line_item_fact_2.c323_opp_currency_code = cur177_t0_r0_2.sid) AND (t21_opp_line_item_fact_2.c270_opp_close_date = cur177_t0_r0_2.rate_date))
74. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=7,668,968.80..30,081,669.48 rows=5,328,108 width=44) (actual rows= loops=)

  • Merge Cond: ((t21_opp_line_item_fact_3.sid = t21_opp_line_item_fact_2.sid) AND (t21_opp_line_item_fact_3.sid = t21_opp_line_item_fact_2.sid))
  • Filter: ((t21_opp_line_item_fact_3.sid IS NULL) OR (NOT (SubPlan 13)))
75. 0.000 0.000 ↓ 0.0

Sort (cost=4,482,372.62..4,512,434.75 rows=12,024,850 width=16) (actual rows= loops=)

  • Sort Key: t21_opp_line_item_fact_3.sid
76. 0.000 0.000 ↓ 0.0

Seq Scan on t21_opp_line_item_fact t21_opp_line_item_fact_3 (cost=0.00..2,657,267.39 rows=12,024,850 width=16) (actual rows= loops=)

  • Filter: ((NOT deleted) AND (start_stamp <= '1541055600000'::bigint) AND (end_stamp > '1541055600000'::bigint))
77. 0.000 0.000 ↓ 0.0

Materialize (cost=3,183,415.84..3,236,696.91 rows=10,656,215 width=28) (actual rows= loops=)

78. 0.000 0.000 ↓ 0.0

Sort (cost=3,183,415.84..3,210,056.38 rows=10,656,215 width=28) (actual rows= loops=)

  • Sort Key: t21_opp_line_item_fact_2.sid
79. 0.000 0.000 ↓ 0.0

Nested Loop (cost=445,165.71..1,429,636.92 rows=10,656,215 width=28) (actual rows= loops=)

  • Join Filter: (t21_opp_line_item_fact_2.c268_opp_ownerid = t166_userrolehierarchy_4.sid)
80. 0.000 0.000 ↓ 0.0

Unique (cost=445,165.15..446,288.27 rows=134,297 width=12) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Sort (cost=445,165.15..445,539.52 rows=149,749 width=12) (actual rows= loops=)

  • Sort Key: distinctclosedateowner_4.closedate, distinctclosedateowner_4.ownerid
82. 0.000 0.000 ↓ 0.0

Hash Join (cost=396,407.50..429,730.59 rows=149,749 width=12) (actual rows= loops=)

  • Hash Cond: (distinctclosedateowner_4.ownerid = t166_userrolehierarchy_4.sid)
83. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on distinctclosedateowner distinctclosedateowner_4 (cost=3,179.36..32,515.59 rows=149,749 width=8) (actual rows= loops=)

  • Recheck Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
84. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ix_closedate_distinctclosedateowner (cost=0.00..3,141.92 rows=149,749 width=0) (actual rows= loops=)

  • Index Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
85. 0.000 0.000 ↓ 0.0

Hash (cost=390,019.91..390,019.91 rows=195,539 width=4) (actual rows= loops=)

86. 0.000 0.000 ↓ 0.0

Unique (cost=383,611.68..390,019.91 rows=195,539 width=4) (actual rows= loops=)

87. 0.000 0.000 ↓ 0.0

Sort (cost=383,611.68..386,815.79 rows=1,281,645 width=4) (actual rows= loops=)

  • Sort Key: t166_userrolehierarchy_4.sid
88. 0.000 0.000 ↓ 0.0

Hash Join (cost=59,587.81..236,067.08 rows=1,281,645 width=4) (actual rows= loops=)

  • Hash Cond: (t166_userrolehierarchy_4.c662_user_role_id = t67_userroledim_4.sid)
89. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on t166_userrolehierarchy t166_userrolehierarchy_4 (cost=37,189.99..194,344.67 rows=1,281,645 width=8) (actual rows= loops=)

  • Recheck Cond: ((c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
90. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..36,869.58 rows=1,281,645 width=0) (actual rows= loops=)

  • Index Cond: ((c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
91. 0.000 0.000 ↓ 0.0

Hash (cost=21,117.18..21,117.18 rows=78,051 width=4) (actual rows= loops=)

92. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on t67_userroledim t67_userroledim_4 (cost=4,261.99..21,117.18 rows=78,051 width=4) (actual rows= loops=)

  • Recheck Cond: ((end_stamp = '32503680000000'::bigint) AND ((c377_portaltype IS NULL) OR (c377_portaltype = 'None'::text)))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
93. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ix_t67_userroledim_sid_end_start_timestamp_partial (cost=0.00..4,242.48 rows=78,925 width=0) (actual rows= loops=)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
94. 0.000 0.000 ↓ 0.0

Index Scan using ix_t21_opp_line_item_fact_owner_closedate_timestamp on t21_opp_line_item_fact t21_opp_line_item_fact_2 (cost=0.56..7.31 rows=1 width=32) (actual rows= loops=)

  • Index Cond: ((c268_opp_ownerid = distinctclosedateowner_4.ownerid) AND (c270_opp_close_date = distinctclosedateowner_4.closedate) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
  • Filter: (NOT deleted)
95.          

SubPlan (forMerge Right Join)

96. 0.000 0.000 ↓ 0.0

Materialize (cost=3,180.34..538,130.09 rows=149,749 width=8) (actual rows= loops=)

97. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=3,180.34..536,796.34 rows=149,749 width=8) (actual rows= loops=)

98. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on distinctclosedateowner distinctclosedateowner_3 (cost=3,179.36..32,515.59 rows=149,749 width=8) (actual rows= loops=)

  • Recheck Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
99. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ix_closedate_distinctclosedateowner (cost=0.00..3,141.92 rows=149,749 width=0) (actual rows= loops=)

  • Index Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
100. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=0.98..9.90 rows=7 width=4) (actual rows= loops=)

101. 0.000 0.000 ↓ 0.0

Index Scan using t166_userrolehierarchy_sidendstamp on t166_userrolehierarchy t166_userrolehierarchy_3 (cost=0.56..6.37 rows=7 width=8) (actual rows= loops=)

  • Index Cond: ((sid = distinctclosedateowner_3.ownerid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: (c663_ancestor_role_id = ANY ('{436,247157}'::integer[]))
102. 0.000 0.000 ↓ 0.0

Index Scan using ix_t67_userroledim_sid_end_start_timestamp_partial on t67_userroledim t67_userroledim_3 (cost=0.42..0.49 rows=1 width=4) (actual rows= loops=)

  • Index Cond: ((sid = t166_userrolehierarchy_3.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
103. 0.000 0.000 ↓ 0.0

Hash (cost=2,203.80..2,203.80 rows=100,865 width=16) (actual rows= loops=)

104. 0.000 0.000 ↓ 0.0

Append (cost=0.00..2,203.80 rows=100,865 width=16) (actual rows= loops=)

105. 0.000 0.000 ↓ 0.0

Seq Scan on clari_conversion_rate cur177_t0_r0_2 (cost=0.00..0.00 rows=1 width=16) (actual rows= loops=)

  • Filter: (to_iso_code = 'USD'::text)
106. 0.000 0.000 ↓ 0.0

Seq Scan on usd_clari_conversion_rate cur177_t0_r0_3 (cost=0.00..2,203.80 rows=100,864 width=16) (actual rows= loops=)

  • Filter: (to_iso_code = 'USD'::text)
107.          

SubPlan (forHash Left Join)

108. 0.000 0.000 ↓ 0.0

CTE Scan on picklist_4 cte0_5 (cost=0.00..0.26 rows=13 width=4) (actual rows= loops=)

109. 0.000 0.000 ↓ 0.0

HashAggregate (cost=430,104.96..430,349.33 rows=24,437 width=4) (actual rows= loops=)

  • Group Key: distinctclosedateowner_2.ownerid
110. 0.000 0.000 ↓ 0.0

Hash Join (cost=396,407.50..429,730.59 rows=149,749 width=4) (actual rows= loops=)

  • Hash Cond: (distinctclosedateowner_2.ownerid = t166_userrolehierarchy_2.sid)
111. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on distinctclosedateowner distinctclosedateowner_2 (cost=3,179.36..32,515.59 rows=149,749 width=4) (actual rows= loops=)

  • Recheck Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
112. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ix_closedate_distinctclosedateowner (cost=0.00..3,141.92 rows=149,749 width=0) (actual rows= loops=)

  • Index Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
113. 0.000 0.000 ↓ 0.0

Hash (cost=390,019.91..390,019.91 rows=195,539 width=4) (actual rows= loops=)

114. 0.000 0.000 ↓ 0.0

Unique (cost=383,611.68..390,019.91 rows=195,539 width=4) (actual rows= loops=)

115. 0.000 0.000 ↓ 0.0

Sort (cost=383,611.68..386,815.79 rows=1,281,645 width=4) (actual rows= loops=)

  • Sort Key: t166_userrolehierarchy_2.sid
116. 0.000 0.000 ↓ 0.0

Hash Join (cost=59,587.81..236,067.08 rows=1,281,645 width=4) (actual rows= loops=)

  • Hash Cond: (t166_userrolehierarchy_2.c662_user_role_id = t67_userroledim_2.sid)
117. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on t166_userrolehierarchy t166_userrolehierarchy_2 (cost=37,189.99..194,344.67 rows=1,281,645 width=8) (actual rows= loops=)

  • Recheck Cond: ((c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
118. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..36,869.58 rows=1,281,645 width=0) (actual rows= loops=)

  • Index Cond: ((c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
119. 0.000 0.000 ↓ 0.0

Hash (cost=21,117.18..21,117.18 rows=78,051 width=4) (actual rows= loops=)

120. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on t67_userroledim t67_userroledim_2 (cost=4,261.99..21,117.18 rows=78,051 width=4) (actual rows= loops=)

  • Recheck Cond: ((end_stamp = '32503680000000'::bigint) AND ((c377_portaltype IS NULL) OR (c377_portaltype = 'None'::text)))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
121. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ix_t67_userroledim_sid_end_start_timestamp_partial (cost=0.00..4,242.48 rows=78,925 width=0) (actual rows= loops=)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
122. 0.000 0.000 ↓ 0.0

CTE Scan on picklist_0 cte0_6 (cost=0.00..0.12 rows=6 width=4) (actual rows= loops=)

123. 0.000 0.000 ↓ 0.0

CTE Scan on picklist_1 cte0_7 (cost=0.00..0.14 rows=7 width=4) (actual rows= loops=)

124. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.11..181,397.38 rows=188,954 width=96) (actual rows= loops=)

  • Hash Cond: (cte0.c4 = t34_opportunityforecastcategorypicklistdim.sid)
125. 0.000 0.000 ↓ 0.0

CTE Scan on bucketac cte0 (cost=0.00..151,163.56 rows=7,558,178 width=92) (actual rows= loops=)

126. 0.000 0.000 ↓ 0.0

Hash (cost=1.05..1.05 rows=5 width=12) (actual rows= loops=)

127. 0.000 0.000 ↓ 0.0

Seq Scan on t34_opportunityforecastcategorypicklistdim (cost=0.00..1.05 rows=5 width=12) (actual rows= loops=)

128. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=1.11..152,404.08 rows=156,713 width=96) (actual rows= loops=)

129. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.11..150,445.17 rows=156,713 width=120) (actual rows= loops=)

  • Hash Cond: (cte0_1.c6 = t34_opportunityforecastcategorypicklistdim_1.sid)
130. 0.000 0.000 ↓ 0.0

CTE Scan on bucketec cte0_1 (cost=0.00..125,370.04 rows=6,268,502 width=116) (actual rows= loops=)

131. 0.000 0.000 ↓ 0.0

Hash (cost=1.05..1.05 rows=5 width=12) (actual rows= loops=)

132. 0.000 0.000 ↓ 0.0

Seq Scan on t34_opportunityforecastcategorypicklistdim t34_opportunityforecastcategorypicklistdim_1 (cost=0.00..1.05 rows=5 width=12) (actual rows= loops=)