explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Wr44 : SingleTimePoint

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 29,867.222 ↑ 1,491.1 10 1

Append (cost=13,051,097.54..13,051,585.57 rows=14,911 width=96) (actual time=29,866.214..29,867.222 rows=10 loops=1)

2.          

CTE picklist_0

3. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on t100_opportunitystagenamepicklistdim (cost=0.00..2.60 rows=1 width=4) (actual time=0.005..0.011 rows=1 loops=1)

  • Filter: (correlated_value = 'Closed Won'::text)
  • Rows Removed by Filter: 47
4.          

CTE picklist_2

5. 0.017 0.017 ↑ 1.0 1 1

Seq Scan on t100_opportunitystagenamepicklistdim t100_opportunitystagenamepicklistdim_1 (cost=0.00..2.60 rows=1 width=4) (actual time=0.011..0.017 rows=1 loops=1)

  • Filter: (correlated_value = 'Closed Lost'::text)
  • Rows Removed by Filter: 47
6.          

CTE openwont1

7. 54.483 7,775.773 ↑ 54.6 2,352 1

Hash Join (cost=368,661.56..12,726,069.53 rows=128,329 width=34) (actual time=3,684.683..7,775.773 rows=2,352 loops=1)

  • Hash Cond: (t108_opp_split_fact.c1800_opportunity_sid = t78_oppdim.sid)
8. 80.318 5,705.000 ↓ 1.3 148,027 1

Nested Loop Left Join (cost=34,543.72..9,090,681.62 rows=114,521 width=64) (actual time=1,056.851..5,705.000 rows=148,027 loops=1)

9. 139.870 5,328.628 ↓ 1.3 148,027 1

Nested Loop Left Join (cost=34,543.72..9,053,159.12 rows=114,521 width=60) (actual time=1,056.835..5,328.628 rows=148,027 loops=1)

10. 80.229 1,784.137 ↓ 1.3 148,027 1

Nested Loop Left Join (cost=34,543.16..8,273,668.82 rows=114,521 width=32) (actual time=1,056.813..1,784.137 rows=148,027 loops=1)

11. 356.024 1,407.854 ↓ 1.3 148,027 1

Bitmap Heap Scan on t108_opp_split_fact (cost=34,543.16..8,235,503.20 rows=114,521 width=32) (actual time=1,056.789..1,407.854 rows=148,027 loops=1)

  • Recheck Cond: ((c1825_opp_close_date >= 20,190,501) AND (c1825_opp_close_date <= 20,190,731) AND (start_stamp <= '1561791599999'::bigint) AND (end_stamp > '1561791599999'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 13)) AND ((alternatives: SubPlan 9 or hashed SubPlan 10) OR (alternatives: SubPlan 11 or hashed SubPlan 12)))
  • Rows Removed by Filter: 6,976
  • Heap Blocks: exact=22,715
12. 1,049.874 1,049.874 ↑ 1.9 159,107 1

Bitmap Index Scan on idx_171_t108_opp_split_fact_c1825_opp_close_date_c1816_opp_stag (cost=0.00..34,514.51 rows=305,389 width=0) (actual time=1,049.874..1,049.874 rows=159,107 loops=1)

  • Index Cond: ((c1825_opp_close_date >= 20,190,501) AND (c1825_opp_close_date <= 20,190,731) AND (start_stamp <= '1561791599999'::bigint) AND (end_stamp > '1561791599999'::bigint))
13.          

SubPlan (for Bitmap Heap Scan)

14. 0.019 0.019 ↑ 1.0 1 1

CTE Scan on picklist_2 cte0_4 (cost=0.00..0.02 rows=1 width=4) (actual time=0.013..0.019 rows=1 loops=1)

15. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.56..16.64 rows=1 width=0) (never executed)

16. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_24_t57_userrolehierarchy_sid_c762_ancestor_role_id_end_stam on t57_userrolehierarchy t57_userrolehierarchy_2 (cost=0.29..8.31 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t108_opp_split_fact.c1830_opportunity_split_ownerid) AND (c762_ancestor_role_id = 157) AND (end_stamp = '32503680000000'::bigint))
17. 0.000 0.000 ↓ 0.0 0

Index Scan using ""idx_14_t41_userroledim_sid_start_stamp_end_stamp_(c514_portalty"" on t41_userroledim t41_userroledim_2 (cost=0.28..8.30 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t57_userrolehierarchy_2.c761_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
18. 0.780 1.937 ↓ 1.0 3,594 1

Hash Join (cost=166.65..560.98 rows=3,546 width=4) (actual time=0.429..1.937 rows=3,594 loops=1)

  • Hash Cond: (t57_userrolehierarchy_3.c761_user_role_id = t41_userroledim_3.sid)
19. 0.775 0.951 ↑ 1.0 3,599 1

Bitmap Heap Scan on t57_userrolehierarchy t57_userrolehierarchy_3 (cost=133.65..518.32 rows=3,645 width=8) (actual time=0.210..0.951 rows=3,599 loops=1)

  • Recheck Cond: ((c762_ancestor_role_id = 157) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=327
20. 0.176 0.176 ↑ 1.0 3,616 1

Bitmap Index Scan on idx_22_t57_userrolehierarchy_c762_ancestor_role_id_end_stamp_st (cost=0.00..132.74 rows=3,645 width=0) (actual time=0.176..0.176 rows=3,616 loops=1)

  • Index Cond: ((c762_ancestor_role_id = 157) AND (end_stamp = '32503680000000'::bigint))
21. 0.055 0.206 ↑ 1.0 448 1

Hash (cost=27.19..27.19 rows=465 width=4) (actual time=0.206..0.206 rows=448 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
22. 0.151 0.151 ↑ 1.0 448 1

Seq Scan on t41_userroledim t41_userroledim_3 (cost=0.00..27.19 rows=465 width=4) (actual time=0.005..0.151 rows=448 loops=1)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c514_portaltype IS NULL) OR (c514_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 127
23. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_18_t37_userdim_sid_end_stamp_start_stamp_null on t37_userdim t37_userdim_2 (cost=0.42..8.44 rows=1 width=0) (never executed)

  • Index Cond: ((sid = t108_opp_split_fact.c1830_opportunity_split_ownerid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c501_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c532_usertype = 'Standard'::text))
24. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t37_userdim t37_userdim_3 (cost=132.50..2,575.68 rows=3,011 width=4) (never executed)

  • Recheck Cond: ((c532_usertype = 'Standard'::text) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c501_userroleid IS NULL) AND (sid <> '-2'::integer))
25. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_21_t37_userdim_c532_usertype_c496_userroleidnid_start_stamp (cost=0.00..131.75 rows=3,128 width=0) (never executed)

  • Index Cond: ((c532_usertype = 'Standard'::text) AND (end_stamp = '32503680000000'::bigint))
26. 0.000 296.054 ↑ 2.0 1 148,027

Append (cost=0.00..0.31 rows=2 width=16) (actual time=0.002..0.002 rows=1 loops=148,027)

27. 0.000 0.000 ↓ 0.0 0 148,027

Seq Scan on t119_clari_conversion_rate (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=148,027)

  • Filter: ((to_iso_code = 'USD'::text) AND (t108_opp_split_fact.c1798_opp_currency_code = sid) AND (t108_opp_split_fact.c1825_opp_close_date = rate_date))
28. 296.054 296.054 ↑ 1.0 1 148,027

Index Scan using t119_clari_conversion_rate_usd_sidratedateunique on t119_clari_conversion_rate_usd (cost=0.29..0.31 rows=1 width=16) (actual time=0.001..0.002 rows=1 loops=148,027)

  • Index Cond: ((t108_opp_split_fact.c1798_opp_currency_code = sid) AND (t108_opp_split_fact.c1825_opp_close_date = rate_date))
  • Filter: (to_iso_code = 'USD'::text)
29. 3,404.621 3,404.621 ↑ 1.0 1 148,027

Index Scan using t108_opp_split_fact_sidendstampunique on t108_opp_split_fact t108_opp_split_fact_1 (cost=0.56..6.80 rows=1 width=28) (actual time=0.017..0.023 rows=1 loops=148,027)

  • Index Cond: ((t108_opp_split_fact.sid = sid) AND (t108_opp_split_fact.sid = sid) AND (end_stamp > '1595833199999'::bigint))
  • Filter: ((NOT deleted) AND (start_stamp <= '1595833199999'::bigint))
  • Rows Removed by Filter: 0
30. 0.000 296.054 ↑ 2.0 1 148,027

Append (cost=0.00..0.31 rows=2 width=16) (actual time=0.002..0.002 rows=1 loops=148,027)

31. 0.000 0.000 ↓ 0.0 0 148,027

Seq Scan on t119_clari_conversion_rate t119_clari_conversion_rate_1 (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=148,027)

  • Filter: ((to_iso_code = 'USD'::text) AND (t108_opp_split_fact_1.c1798_opp_currency_code = sid) AND (t108_opp_split_fact_1.c1825_opp_close_date = rate_date))
32. 296.054 296.054 ↑ 1.0 1 148,027

Index Scan using t119_clari_conversion_rate_usd_sidratedateunique on t119_clari_conversion_rate_usd t119_clari_conversion_rate_usd_1 (cost=0.29..0.31 rows=1 width=16) (actual time=0.001..0.002 rows=1 loops=148,027)

  • Index Cond: ((t108_opp_split_fact_1.c1798_opp_currency_code = sid) AND (t108_opp_split_fact_1.c1825_opp_close_date = rate_date))
  • Filter: (to_iso_code = 'USD'::text)
33. 0.357 2,014.351 ↑ 128.2 1,909 1

Hash (cost=330,102.99..330,102.99 rows=244,704 width=4) (actual time=2,014.351..2,014.351 rows=1,909 loops=1)

  • Buckets: 131,072 Batches: 4 Memory Usage: 1,048kB
34. 2.979 2,013.994 ↑ 128.2 1,909 1

Bitmap Heap Scan on t78_oppdim (cost=16,662.94..330,102.99 rows=244,704 width=4) (actual time=2,011.247..2,013.994 rows=1,909 loops=1)

  • Recheck Cond: ((end_stamp > '1561791599999'::bigint) AND (start_stamp <= '1561791599999'::bigint))
  • Filter: ((NOT deleted) AND c1216_in_forecast_avp__c)
  • Heap Blocks: exact=1,837
35. 2,011.015 2,011.015 ↑ 128.2 1,909 1

Bitmap Index Scan on idx_85_t78_oppdim_c1216_in_forecast_avp__c_end_stamp_start_stam (cost=0.00..16,601.77 rows=244,704 width=0) (actual time=2,011.015..2,011.015 rows=1,909 loops=1)

  • Index Cond: ((c1216_in_forecast_avp__c = true) AND (end_stamp > '1561791599999'::bigint) AND (start_stamp <= '1561791599999'::bigint))
36.          

SubPlan (for Hash Join)

37. 0.001 0.001 ↑ 1.0 1 1

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

38. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.56..16.64 rows=1 width=0) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_24_t57_userrolehierarchy_sid_c762_ancestor_role_id_end_stam on t57_userrolehierarchy (cost=0.29..8.31 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t108_opp_split_fact_1.c1830_opportunity_split_ownerid) AND (c762_ancestor_role_id = 157) AND (end_stamp = '32503680000000'::bigint))
40. 0.000 0.000 ↓ 0.0 0

Index Scan using ""idx_14_t41_userroledim_sid_start_stamp_end_stamp_(c514_portalty"" on t41_userroledim (cost=0.28..8.30 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t57_userrolehierarchy.c761_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
41. 0.770 1.937 ↓ 1.0 3,594 1

Hash Join (cost=166.65..560.98 rows=3,546 width=4) (actual time=0.424..1.937 rows=3,594 loops=1)

  • Hash Cond: (t57_userrolehierarchy_1.c761_user_role_id = t41_userroledim_1.sid)
42. 0.791 0.969 ↑ 1.0 3,599 1

Bitmap Heap Scan on t57_userrolehierarchy t57_userrolehierarchy_1 (cost=133.65..518.32 rows=3,645 width=8) (actual time=0.214..0.969 rows=3,599 loops=1)

  • Recheck Cond: ((c762_ancestor_role_id = 157) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=327
43. 0.178 0.178 ↑ 1.0 3,616 1

Bitmap Index Scan on idx_22_t57_userrolehierarchy_c762_ancestor_role_id_end_stamp_st (cost=0.00..132.74 rows=3,645 width=0) (actual time=0.178..0.178 rows=3,616 loops=1)

  • Index Cond: ((c762_ancestor_role_id = 157) AND (end_stamp = '32503680000000'::bigint))
44. 0.047 0.198 ↑ 1.0 448 1

Hash (cost=27.19..27.19 rows=465 width=4) (actual time=0.198..0.198 rows=448 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
45. 0.151 0.151 ↑ 1.0 448 1

Seq Scan on t41_userroledim t41_userroledim_1 (cost=0.00..27.19 rows=465 width=4) (actual time=0.005..0.151 rows=448 loops=1)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c514_portaltype IS NULL) OR (c514_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 127
46. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: ((sid = t108_opp_split_fact_1.c1830_opportunity_split_ownerid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c501_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c532_usertype = 'Standard'::text))
47. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t37_userdim t37_userdim_1 (cost=132.50..2,575.68 rows=3,011 width=4) (never executed)

  • Recheck Cond: ((c532_usertype = 'Standard'::text) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c501_userroleid IS NULL) AND (sid <> '-2'::integer))
48. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_21_t37_userdim_c532_usertype_c496_userroleidnid_start_stamp (cost=0.00..131.75 rows=3,128 width=0) (never executed)

  • Index Cond: ((c532_usertype = 'Standard'::text) AND (end_stamp = '32503680000000'::bigint))
49. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on picklist_0 cte0_3 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=1)

50.          

CTE openwont1ids

51. 1.076 7,777.669 ↓ 11.8 2,352 1

HashAggregate (cost=2,887.40..2,889.40 rows=200 width=4) (actual time=7,777.403..7,777.669 rows=2,352 loops=1)

  • Group Key: cte0_5.c1
52. 7,776.593 7,776.593 ↑ 54.6 2,352 1

CTE Scan on openwont1 cte0_5 (cost=0.00..2,566.58 rows=128,329 width=4) (actual time=3,684.685..7,776.593 rows=2,352 loops=1)

53.          

CTE bucketab

54. 0.611 0.923 ↑ 1,604.1 8 1

HashAggregate (cost=4,491.51..4,619.84 rows=12,833 width=68) (actual time=0.871..0.923 rows=8 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
55. 0.312 0.312 ↑ 54.6 2,352 1

CTE Scan on openwont1 cte0_6 (cost=0.00..2,566.58 rows=128,329 width=64) (actual time=0.002..0.312 rows=2,352 loops=1)

56.          

CTE picklist_3

57. 0.014 0.014 ↑ 1.0 2 1

Seq Scan on t100_opportunitystagenamepicklistdim t100_opportunitystagenamepicklistdim_2 (cost=0.00..2.60 rows=2 width=4) (actual time=0.007..0.014 rows=2 loops=1)

  • Filter: (correlated_value = ANY ('{""Closed Lost"",""Closed Won""}'::text[]))
  • Rows Removed by Filter: 46
58.          

CTE bucketd

59. 0.361 29,866.253 ↑ 1,039.0 2 1

GroupAggregate (cost=265,307.58..317,510.96 rows=2,078 width=120) (actual time=29,866.209..29,866.253 rows=2 loops=1)

  • Group Key: '1561791599999'::bigint, (CASE WHEN (t108_opp_split_fact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 18)) THEN CASE WHEN (t108_opp_split_fact_3.c1825_opp_close_date > 20190731) THEN 'pulledIn'::text WHEN (t108_opp_split_fact_3.c1825_opp_close_date < 20190501) THEN 'pushedIn'::text WHEN ((t108_opp_split_fact_3.c1825_opp_close_date >= 20,190,501) AND (t108_opp_split_fact_3.c1825_opp_close_date <= 20,190,731) AND ((alternatives: SubPlan 19 or hashed SubPlan 20) OR (alternatives: SubPlan 21 or hashed SubPlan 22))) THEN 'other'::text ELSE 'ownerChange'::text END ELSE 'other'::text END)
60. 0.707 29,865.892 ↓ 1.1 2,240 1

Sort (cost=265,307.54..265,312.73 rows=2,078 width=60) (actual time=29,865.798..29,865.892 rows=2,240 loops=1)

  • Sort Key: (CASE WHEN (t108_opp_split_fact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 18)) THEN CASE WHEN (t108_opp_split_fact_3.c1825_opp_close_date > 20190731) THEN 'pulledIn'::text WHEN (t108_opp_split_fact_3.c1825_opp_close_date < 20190501) THEN 'pushedIn'::text WHEN ((t108_opp_split_fact_3.c1825_opp_close_date >= 20,190,501) AND (t108_opp_split_fact_3.c1825_opp_close_date <= 20,190,731) AND ((alternatives: SubPlan 19 or hashed SubPlan 20) OR (alternatives: SubPlan 21 or hashed SubPlan 22))) THEN 'other'::text ELSE 'ownerChange'::text END ELSE 'other'::text END)
  • Sort Method: quicksort Memory: 272kB
61. 123.260 29,865.185 ↓ 1.1 2,240 1

Nested Loop (cost=6.12..265,193.03 rows=2,078 width=60) (actual time=28,977.473..29,865.185 rows=2,240 loops=1)

62. 206.881 27,469.166 ↓ 138.8 252,527 1

Nested Loop Left Join (cost=5.65..188,374.77 rows=1,820 width=40) (actual time=7,781.355..27,469.166 rows=252,527 loops=1)

63. 148.997 9,080.341 ↓ 138.8 252,527 1

Nested Loop Left Join (cost=5.08..175,010.15 rows=1,820 width=24) (actual time=7,781.341..9,080.341 rows=252,527 loops=1)

64. 29.887 8,426.290 ↓ 138.8 252,527 1

Nested Loop (cost=5.08..174,397.00 rows=1,820 width=24) (actual time=7,781.327..8,426.290 rows=252,527 loops=1)

65. 0.003 0.015 ↑ 1.0 1 1

HashAggregate (cost=0.02..0.03 rows=1 width=4) (actual time=0.014..0.015 rows=1 loops=1)

  • Group Key: cte0_9.c1
66. 0.012 0.012 ↑ 1.0 1 1

CTE Scan on picklist_0 cte0_9 (cost=0.00..0.02 rows=1 width=4) (actual time=0.006..0.012 rows=1 loops=1)

67. 616.160 8,396.388 ↓ 138.8 252,527 1

Index Scan using idx_171_t108_opp_split_fact_c1825_opp_close_date_c1816_opp_stag on t108_opp_split_fact t108_opp_split_fact_2 (cost=5.06..174,378.77 rows=1,820 width=28) (actual time=7,781.310..8,396.388 rows=252,527 loops=1)

  • Index Cond: ((c1825_opp_close_date >= 20,190,501) AND (c1825_opp_close_date <= 20,190,731) AND (c1816_opp_stagename = cte0_9.c1) AND (start_stamp <= '1595833199999'::bigint) AND (end_stamp > '1595833199999'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 27)) AND ((alternatives: SubPlan 23 or hashed SubPlan 24) OR (alternatives: SubPlan 25 or hashed SubPlan 26)))
  • Rows Removed by Filter: 1,377
68.          

SubPlan (for Index Scan)

69. 7,778.068 7,778.068 ↓ 11.8 2,352 1

CTE Scan on openwont1ids cte0_8 (cost=0.00..4.00 rows=200 width=4) (actual time=7,777.404..7,778.068 rows=2,352 loops=1)

70. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.56..16.64 rows=1 width=0) (never executed)

71. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_24_t57_userrolehierarchy_sid_c762_ancestor_role_id_end_stam on t57_userrolehierarchy t57_userrolehierarchy_6 (cost=0.29..8.31 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t108_opp_split_fact_2.c1830_opportunity_split_ownerid) AND (c762_ancestor_role_id = 157) AND (end_stamp = '32503680000000'::bigint))
72. 0.000 0.000 ↓ 0.0 0

Index Scan using ""idx_14_t41_userroledim_sid_start_stamp_end_stamp_(c514_portalty"" on t41_userroledim t41_userroledim_6 (cost=0.28..8.30 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t57_userrolehierarchy_6.c761_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
73. 0.921 2.160 ↓ 1.0 3,594 1

Hash Join (cost=166.65..560.98 rows=3,546 width=4) (actual time=0.416..2.160 rows=3,594 loops=1)

  • Hash Cond: (t57_userrolehierarchy_7.c761_user_role_id = t41_userroledim_7.sid)
74. 0.872 1.041 ↑ 1.0 3,599 1

Bitmap Heap Scan on t57_userrolehierarchy t57_userrolehierarchy_7 (cost=133.65..518.32 rows=3,645 width=8) (actual time=0.207..1.041 rows=3,599 loops=1)

  • Recheck Cond: ((c762_ancestor_role_id = 157) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=327
75. 0.169 0.169 ↑ 1.0 3,616 1

Bitmap Index Scan on idx_22_t57_userrolehierarchy_c762_ancestor_role_id_end_stamp_st (cost=0.00..132.74 rows=3,645 width=0) (actual time=0.169..0.169 rows=3,616 loops=1)

  • Index Cond: ((c762_ancestor_role_id = 157) AND (end_stamp = '32503680000000'::bigint))
76. 0.046 0.198 ↑ 1.0 448 1

Hash (cost=27.19..27.19 rows=465 width=4) (actual time=0.198..0.198 rows=448 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
77. 0.152 0.152 ↑ 1.0 448 1

Seq Scan on t41_userroledim t41_userroledim_7 (cost=0.00..27.19 rows=465 width=4) (actual time=0.007..0.152 rows=448 loops=1)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c514_portaltype IS NULL) OR (c514_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 127
78. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_18_t37_userdim_sid_end_stamp_start_stamp_null on t37_userdim t37_userdim_6 (cost=0.42..8.44 rows=1 width=0) (never executed)

  • Index Cond: ((sid = t108_opp_split_fact_2.c1830_opportunity_split_ownerid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c501_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c532_usertype = 'Standard'::text))
79. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t37_userdim t37_userdim_7 (cost=132.50..2,575.68 rows=3,011 width=4) (never executed)

  • Recheck Cond: ((c532_usertype = 'Standard'::text) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c501_userroleid IS NULL) AND (sid <> '-2'::integer))
80. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_21_t37_userdim_c532_usertype_c496_userroleidnid_start_stamp (cost=0.00..131.75 rows=3,128 width=0) (never executed)

  • Index Cond: ((c532_usertype = 'Standard'::text) AND (end_stamp = '32503680000000'::bigint))
81. 0.000 505.054 ↑ 2.0 1 252,527

Append (cost=0.00..0.32 rows=2 width=16) (actual time=0.002..0.002 rows=1 loops=252,527)

82. 0.000 0.000 ↓ 0.0 0 252,527

Seq Scan on t119_clari_conversion_rate t119_clari_conversion_rate_2 (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=252,527)

  • Filter: ((to_iso_code = 'USD'::text) AND (t108_opp_split_fact_2.c1798_opp_currency_code = sid) AND (t108_opp_split_fact_2.c1825_opp_close_date = rate_date))
83. 505.054 505.054 ↑ 1.0 1 252,527

Index Scan using t119_clari_conversion_rate_usd_sidratedateunique on t119_clari_conversion_rate_usd t119_clari_conversion_rate_usd_2 (cost=0.29..0.32 rows=1 width=16) (actual time=0.001..0.002 rows=1 loops=252,527)

  • Index Cond: ((t108_opp_split_fact_2.c1798_opp_currency_code = sid) AND (t108_opp_split_fact_2.c1825_opp_close_date = rate_date))
  • Filter: (to_iso_code = 'USD'::text)
84. 18,181.944 18,181.944 ↑ 1.0 1 252,527

Index Scan using t108_opp_split_fact_sidendstampunique on t108_opp_split_fact t108_opp_split_fact_3 (cost=0.56..7.33 rows=1 width=16) (actual time=0.024..0.072 rows=1 loops=252,527)

  • Index Cond: ((t108_opp_split_fact_2.sid = sid) AND (t108_opp_split_fact_2.sid = sid) AND (end_stamp > '1561791599999'::bigint))
  • Filter: ((NOT deleted) AND (start_stamp <= '1561791599999'::bigint))
  • Rows Removed by Filter: 2
85. 2,272.743 2,272.743 ↓ 0.0 0 252,527

Index Scan using idx_72_t78_oppdim_sid_end_stamp_start_stamp_null on t78_oppdim t78_oppdim_1 (cost=0.43..13.54 rows=1 width=4) (actual time=0.009..0.009 rows=0 loops=252,527)

  • Index Cond: ((sid = t108_opp_split_fact_2.c1800_opportunity_sid) AND (end_stamp > '1595833199999'::bigint) AND (start_stamp <= '1595833199999'::bigint))
  • Filter: ((NOT deleted) AND c1216_in_forecast_avp__c)
  • Rows Removed by Filter: 1
86.          

SubPlan (for Nested Loop)

87. 0.016 0.016 ↑ 1.0 2 1

CTE Scan on picklist_3 cte0_7 (cost=0.00..0.04 rows=2 width=4) (actual time=0.008..0.016 rows=2 loops=1)

88. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.56..16.64 rows=1 width=0) (never executed)

89. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_24_t57_userrolehierarchy_sid_c762_ancestor_role_id_end_stam on t57_userrolehierarchy t57_userrolehierarchy_4 (cost=0.29..8.31 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t108_opp_split_fact_3.c1830_opportunity_split_ownerid) AND (c762_ancestor_role_id = 157) AND (end_stamp = '32503680000000'::bigint))
90. 0.000 0.000 ↓ 0.0 0

Index Scan using ""idx_14_t41_userroledim_sid_start_stamp_end_stamp_(c514_portalty"" on t41_userroledim t41_userroledim_4 (cost=0.28..8.30 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t57_userrolehierarchy_4.c761_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
91. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=166.65..560.98 rows=3,546 width=4) (never executed)

  • Hash Cond: (t57_userrolehierarchy_5.c761_user_role_id = t41_userroledim_5.sid)
92. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t57_userrolehierarchy t57_userrolehierarchy_5 (cost=133.65..518.32 rows=3,645 width=8) (never executed)

  • Recheck Cond: ((c762_ancestor_role_id = 157) AND (end_stamp = '32503680000000'::bigint))
93. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_22_t57_userrolehierarchy_c762_ancestor_role_id_end_stamp_st (cost=0.00..132.74 rows=3,645 width=0) (never executed)

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

Hash (cost=27.19..27.19 rows=465 width=4) (never executed)

95. 0.000 0.000 ↓ 0.0 0

Seq Scan on t41_userroledim t41_userroledim_5 (cost=0.00..27.19 rows=465 width=4) (never executed)

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

Index Scan using idx_18_t37_userdim_sid_end_stamp_start_stamp_null on t37_userdim t37_userdim_4 (cost=0.42..8.44 rows=1 width=0) (never executed)

  • Index Cond: ((sid = t108_opp_split_fact_3.c1830_opportunity_split_ownerid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c501_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c532_usertype = 'Standard'::text))
97. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t37_userdim t37_userdim_5 (cost=132.50..2,575.68 rows=3,011 width=4) (never executed)

  • Recheck Cond: ((c532_usertype = 'Standard'::text) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c501_userroleid IS NULL) AND (sid <> '-2'::integer))
98. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_21_t37_userdim_c532_usertype_c496_userroleidnid_start_stamp (cost=0.00..131.75 rows=3,128 width=0) (never executed)

  • Index Cond: ((c532_usertype = 'Standard'::text) AND (end_stamp = '32503680000000'::bigint))
99. 0.005 29,866.261 ↑ 1,039.0 2 1

Subquery Scan on "*SELECT* 1" (cost=0.00..67.53 rows=2,078 width=96) (actual time=29,866.214..29,866.261 rows=2 loops=1)

100. 29,866.256 29,866.256 ↑ 1,039.0 2 1

CTE Scan on bucketd cte0 (cost=0.00..41.56 rows=2,078 width=120) (actual time=29,866.211..29,866.256 rows=2 loops=1)

101. 0.021 0.959 ↑ 1,604.1 8 1

Hash Left Join (cost=1.11..292.17 rows=12,833 width=96) (actual time=0.900..0.959 rows=8 loops=1)

  • Hash Cond: (cte0_1.c6 = t101_opportunityforecastcategorypicklistdim.sid)
102. 0.929 0.929 ↑ 1,604.1 8 1

CTE Scan on bucketab cte0_1 (cost=0.00..256.66 rows=12,833 width=68) (actual time=0.872..0.929 rows=8 loops=1)

103. 0.003 0.009 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=36) (actual time=0.009..0.009 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
104. 0.006 0.006 ↑ 1.0 5 1

Seq Scan on t101_opportunityforecastcategorypicklistdim (cost=0.00..1.05 rows=5 width=36) (actual time=0.005..0.006 rows=5 loops=1)

Planning time : 13.796 ms
Execution time : 29,868.968 ms