explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Urs0

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 457.261 ↑ 181.6 10 1

Append (cost=8,875,249.74..8,875,310.15 rows=1,816 width=96) (actual time=456.276..457.261 rows=10 loops=1)

2.          

CTE picklist_0

3. 0.012 0.012 ↑ 1.0 1 1

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

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

CTE picklist_2

5. 0.012 0.012 ↑ 1.0 1 1

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

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

CTE openwont1

7. 3.001 174.141 ↑ 6.6 2,352 1

Nested Loop Left Join (cost=32,169.22..8,683,273.67 rows=15,579 width=34) (actual time=58.059..174.141 rows=2,352 loops=1)

8. 1.934 164.434 ↑ 6.6 2,352 1

Nested Loop Left Join (cost=32,169.17..8,287,145.24 rows=15,579 width=56) (actual time=55.455..164.434 rows=2,352 loops=1)

9. 1.455 153.092 ↑ 6.6 2,352 1

Nested Loop Left Join (cost=32,168.61..8,159,068.79 rows=15,579 width=28) (actual time=55.438..153.092 rows=2,352 loops=1)

10. 100.228 146.933 ↑ 6.6 2,352 1

Bitmap Heap Scan on t123_opp_split_fact (cost=32,168.61..8,153,302.67 rows=15,579 width=28) (actual time=55.419..146.933 rows=2,352 loops=1)

  • Recheck Cond: ((c2022_opp_close_date >= 20,190,501) AND (c2022_opp_close_date <= 20,190,731) AND (start_stamp <= '1561791599999'::bigint) AND (end_stamp > '1561791599999'::bigint))
  • Filter: ((NOT deleted) AND c2032_opp_in_forecast_avp__c 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: 152,651
  • Heap Blocks: exact=39,948
11. 44.647 44.647 ↑ 1.9 155,009 1

Bitmap Index Scan on idx_213_t123_opp_split_fact_c2022_opp_close_date_c2017_opp_stag (cost=0.00..32,164.69 rows=301,110 width=0) (actual time=44.647..44.647 rows=155,009 loops=1)

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

SubPlan (for Bitmap Heap Scan)

13. 0.013 0.013 ↑ 1.0 1 1

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

14. 0.000 0.000 ↓ 0.0 0

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

15. 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 = t123_opp_split_fact.c2027_opportunity_split_ownerid) AND (c762_ancestor_role_id = 157) AND (end_stamp = '32503680000000'::bigint))
16. 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))
17. 0.859 2.045 ↓ 1.0 3,602 1

Hash Join (cost=166.65..560.98 rows=3,546 width=4) (actual time=0.438..2.045 rows=3,602 loops=1)

  • Hash Cond: (t57_userrolehierarchy_3.c761_user_role_id = t41_userroledim_3.sid)
18. 0.794 0.990 ↑ 1.0 3,607 1

Bitmap Heap Scan on t57_userrolehierarchy t57_userrolehierarchy_3 (cost=133.65..518.32 rows=3,645 width=8) (actual time=0.230..0.990 rows=3,607 loops=1)

  • Recheck Cond: ((c762_ancestor_role_id = 157) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=327
19. 0.196 0.196 ↑ 1.0 3,630 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.196..0.196 rows=3,630 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
21. 0.149 0.149 ↑ 1.0 448 1

Seq Scan on t41_userroledim t41_userroledim_3 (cost=0.00..27.19 rows=465 width=4) (actual time=0.006..0.149 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
22. 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 = t123_opp_split_fact.c2027_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))
23. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t37_userdim t37_userdim_3 (cost=132.64..2,586.61 rows=3,024 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))
24. 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.89 rows=3,142 width=0) (never executed)

  • Index Cond: ((c532_usertype = 'Standard'::text) AND (end_stamp = '32503680000000'::bigint))
25. 0.000 4.704 ↑ 2.0 1 2,352

Append (cost=0.00..0.35 rows=2 width=16) (actual time=0.002..0.002 rows=1 loops=2,352)

26. 0.000 0.000 ↓ 0.0 0 2,352

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=2,352)

  • Filter: ((to_iso_code = 'USD'::text) AND (t123_opp_split_fact.c2008_opp_currency_code = sid) AND (t123_opp_split_fact.c2022_opp_close_date = rate_date))
27. 4.704 4.704 ↑ 1.0 1 2,352

Index Scan using t119_clari_conversion_rate_usd_sidratedateunique on t119_clari_conversion_rate_usd (cost=0.29..0.35 rows=1 width=16) (actual time=0.001..0.002 rows=1 loops=2,352)

  • Index Cond: ((t123_opp_split_fact.c2008_opp_currency_code = sid) AND (t123_opp_split_fact.c2022_opp_close_date = rate_date))
  • Filter: (to_iso_code = 'USD'::text)
28. 9.408 9.408 ↑ 1.0 1 2,352

Index Scan using t123_opp_split_fact_sidendstampunique on t123_opp_split_fact t123_opp_split_fact_1 (cost=0.56..8.21 rows=1 width=28) (actual time=0.003..0.004 rows=1 loops=2,352)

  • Index Cond: ((t123_opp_split_fact.sid = sid) AND (t123_opp_split_fact.sid = sid) AND (end_stamp > '1595833199999'::bigint))
  • Filter: ((NOT deleted) AND (start_stamp <= '1595833199999'::bigint))
  • Rows Removed by Filter: 0
29. 0.000 4.704 ↑ 2.0 1 2,352

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

30. 0.000 0.000 ↓ 0.0 0 2,352

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=2,352)

  • Filter: ((to_iso_code = 'USD'::text) AND (t123_opp_split_fact_1.c2008_opp_currency_code = sid) AND (t123_opp_split_fact_1.c2022_opp_close_date = rate_date))
31. 4.704 4.704 ↑ 1.0 1 2,352

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=2,352)

  • Index Cond: ((t123_opp_split_fact_1.c2008_opp_currency_code = sid) AND (t123_opp_split_fact_1.c2022_opp_close_date = rate_date))
  • Filter: (to_iso_code = 'USD'::text)
32.          

SubPlan (for Nested Loop Left Join)

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

34. 0.000 0.000 ↓ 0.0 0

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

35. 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 = t123_opp_split_fact_1.c2027_opportunity_split_ownerid) AND (c762_ancestor_role_id = 157) AND (end_stamp = '32503680000000'::bigint))
36. 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))
37. 0.835 2.000 ↓ 1.0 3,602 1

Hash Join (cost=166.65..560.98 rows=3,546 width=4) (actual time=0.459..2.000 rows=3,602 loops=1)

  • Hash Cond: (t57_userrolehierarchy_1.c761_user_role_id = t41_userroledim_1.sid)
38. 0.764 0.935 ↑ 1.0 3,607 1

Bitmap Heap Scan on t57_userrolehierarchy t57_userrolehierarchy_1 (cost=133.65..518.32 rows=3,645 width=8) (actual time=0.220..0.935 rows=3,607 loops=1)

  • Recheck Cond: ((c762_ancestor_role_id = 157) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=327
39. 0.171 0.171 ↑ 1.0 3,630 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.171..0.171 rows=3,630 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
41. 0.175 0.175 ↑ 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.175 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
42. 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 = t123_opp_split_fact_1.c2027_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))
43. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t37_userdim t37_userdim_1 (cost=132.64..2,586.61 rows=3,024 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))
44. 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.89 rows=3,142 width=0) (never executed)

  • Index Cond: ((c532_usertype = 'Standard'::text) AND (end_stamp = '32503680000000'::bigint))
45. 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.001..0.001 rows=1 loops=1)

46.          

CTE openwont1ids

47. 1.169 176.131 ↓ 11.8 2,352 1

HashAggregate (cost=350.53..352.53 rows=200 width=4) (actual time=175.806..176.131 rows=2,352 loops=1)

  • Group Key: cte0_5.c1
48. 174.962 174.962 ↑ 6.6 2,352 1

CTE Scan on openwont1 cte0_5 (cost=0.00..311.58 rows=15,579 width=4) (actual time=58.061..174.962 rows=2,352 loops=1)

49.          

CTE bucketab

50. 0.573 0.891 ↑ 194.8 8 1

HashAggregate (cost=545.26..560.85 rows=1,558 width=68) (actual time=0.881..0.891 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
51. 0.318 0.318 ↑ 6.6 2,352 1

CTE Scan on openwont1 cte0_6 (cost=0.00..311.58 rows=15,579 width=64) (actual time=0.003..0.318 rows=2,352 loops=1)

52.          

CTE picklist_3

53. 0.013 0.013 ↑ 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.013 rows=2 loops=1)

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

CTE bucketd

55. 0.360 456.314 ↑ 129.0 2 1

GroupAggregate (cost=184,573.43..191,054.89 rows=258 width=120) (actual time=456.270..456.314 rows=2 loops=1)

  • Group Key: '1561791599999'::bigint, (CASE WHEN (t123_opp_split_fact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 18)) THEN CASE WHEN (t123_opp_split_fact_3.c2022_opp_close_date > 20190731) THEN 'pulledIn'::text WHEN (t123_opp_split_fact_3.c2022_opp_close_date < 20190501) THEN 'pushedIn'::text WHEN ((t123_opp_split_fact_3.c2022_opp_close_date >= 20,190,501) AND (t123_opp_split_fact_3.c2022_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)
56. 0.594 455.954 ↓ 8.7 2,240 1

Sort (cost=184,573.39..184,574.03 rows=258 width=60) (actual time=455.861..455.954 rows=2,240 loops=1)

  • Sort Key: (CASE WHEN (t123_opp_split_fact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 18)) THEN CASE WHEN (t123_opp_split_fact_3.c2022_opp_close_date > 20190731) THEN 'pulledIn'::text WHEN (t123_opp_split_fact_3.c2022_opp_close_date < 20190501) THEN 'pushedIn'::text WHEN ((t123_opp_split_fact_3.c2022_opp_close_date >= 20,190,501) AND (t123_opp_split_fact_3.c2022_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
57. 2.931 455.360 ↓ 8.7 2,240 1

Nested Loop Left Join (cost=5.69..184,563.05 rows=258 width=60) (actual time=425.704..455.360 rows=2,240 loops=1)

58. 1.086 438.974 ↓ 8.7 2,240 1

Nested Loop Left Join (cost=5.08..175,934.00 rows=258 width=20) (actual time=425.666..438.974 rows=2,240 loops=1)

59. 0.255 433.408 ↓ 8.7 2,240 1

Nested Loop (cost=5.08..175,832.41 rows=258 width=20) (actual time=425.652..433.408 rows=2,240 loops=1)

60. 0.002 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
61. 0.013 0.013 ↑ 1.0 1 1

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

62. 254.604 433.138 ↓ 8.7 2,240 1

Index Scan using idx_213_t123_opp_split_fact_c2022_opp_close_date_c2017_opp_stag on t123_opp_split_fact t123_opp_split_fact_2 (cost=5.06..175,829.80 rows=258 width=24) (actual time=425.635..433.138 rows=2,240 loops=1)

  • Index Cond: ((c2022_opp_close_date >= 20,190,501) AND (c2022_opp_close_date <= 20,190,731) AND (c2017_opp_stagename = cte0_9.c1) AND (start_stamp <= '1595833199999'::bigint) AND (end_stamp > '1595833199999'::bigint))
  • Filter: ((NOT deleted) AND c2032_opp_in_forecast_avp__c 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: 251,664
63.          

SubPlan (for Index Scan)

64. 176.563 176.563 ↓ 11.8 2,352 1

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

65. 0.000 0.000 ↓ 0.0 0

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

66. 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 = t123_opp_split_fact_2.c2027_opportunity_split_ownerid) AND (c762_ancestor_role_id = 157) AND (end_stamp = '32503680000000'::bigint))
67. 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))
68. 0.782 1.971 ↓ 1.0 3,602 1

Hash Join (cost=166.65..560.98 rows=3,546 width=4) (actual time=0.468..1.971 rows=3,602 loops=1)

  • Hash Cond: (t57_userrolehierarchy_7.c761_user_role_id = t41_userroledim_7.sid)
69. 0.769 0.941 ↑ 1.0 3,607 1

Bitmap Heap Scan on t57_userrolehierarchy t57_userrolehierarchy_7 (cost=133.65..518.32 rows=3,645 width=8) (actual time=0.209..0.941 rows=3,607 loops=1)

  • Recheck Cond: ((c762_ancestor_role_id = 157) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=327
70. 0.172 0.172 ↑ 1.0 3,630 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.172..0.172 rows=3,630 loops=1)

  • Index Cond: ((c762_ancestor_role_id = 157) AND (end_stamp = '32503680000000'::bigint))
71. 0.062 0.248 ↑ 1.0 448 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
72. 0.186 0.186 ↑ 1.0 448 1

Seq Scan on t41_userroledim t41_userroledim_7 (cost=0.00..27.19 rows=465 width=4) (actual time=0.006..0.186 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
73. 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 = t123_opp_split_fact_2.c2027_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))
74. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t37_userdim t37_userdim_7 (cost=132.64..2,586.61 rows=3,024 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))
75. 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.89 rows=3,142 width=0) (never executed)

  • Index Cond: ((c532_usertype = 'Standard'::text) AND (end_stamp = '32503680000000'::bigint))
76. 0.000 4.480 ↑ 2.0 1 2,240

Append (cost=0.00..0.37 rows=2 width=16) (actual time=0.002..0.002 rows=1 loops=2,240)

77. 0.000 0.000 ↓ 0.0 0 2,240

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=2,240)

  • Filter: ((to_iso_code = 'USD'::text) AND (t123_opp_split_fact_2.c2008_opp_currency_code = sid) AND (t123_opp_split_fact_2.c2022_opp_close_date = rate_date))
78. 4.480 4.480 ↑ 1.0 1 2,240

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.37 rows=1 width=16) (actual time=0.001..0.002 rows=1 loops=2,240)

  • Index Cond: ((t123_opp_split_fact_2.c2008_opp_currency_code = sid) AND (t123_opp_split_fact_2.c2022_opp_close_date = rate_date))
  • Filter: (to_iso_code = 'USD'::text)
79. 13.440 13.440 ↑ 1.0 1 2,240

Index Scan using t123_opp_split_fact_sidendstampunique on t123_opp_split_fact t123_opp_split_fact_3 (cost=0.56..8.34 rows=1 width=16) (actual time=0.003..0.006 rows=1 loops=2,240)

  • Index Cond: ((t123_opp_split_fact_2.sid = sid) AND (t123_opp_split_fact_2.sid = sid) AND (end_stamp > '1561791599999'::bigint))
  • Filter: ((NOT deleted) AND (start_stamp <= '1561791599999'::bigint))
  • Rows Removed by Filter: 5
80.          

SubPlan (for Nested Loop Left Join)

81. 0.015 0.015 ↑ 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.015 rows=2 loops=1)

82. 0.000 0.000 ↓ 0.0 0

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

83. 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 = t123_opp_split_fact_3.c2027_opportunity_split_ownerid) AND (c762_ancestor_role_id = 157) AND (end_stamp = '32503680000000'::bigint))
84. 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))
85. 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)
86. 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))
87. 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))
88. 0.000 0.000 ↓ 0.0 0

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

89. 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))
90. 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 = t123_opp_split_fact_3.c2027_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))
91. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t37_userdim t37_userdim_5 (cost=132.64..2,586.61 rows=3,024 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))
92. 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.89 rows=3,142 width=0) (never executed)

  • Index Cond: ((c532_usertype = 'Standard'::text) AND (end_stamp = '32503680000000'::bigint))
93. 0.022 456.339 ↑ 129.0 2 1

Subquery Scan on "*SELECT* 1" (cost=0.00..8.38 rows=258 width=96) (actual time=456.275..456.339 rows=2 loops=1)

94. 456.317 456.317 ↑ 129.0 2 1

CTE Scan on bucketd cte0 (cost=0.00..5.16 rows=258 width=120) (actual time=456.271..456.317 rows=2 loops=1)

95. 0.017 0.920 ↑ 194.8 8 1

Hash Left Join (cost=1.11..36.45 rows=1,558 width=96) (actual time=0.907..0.920 rows=8 loops=1)

  • Hash Cond: (cte0_1.c6 = t101_opportunityforecastcategorypicklistdim.sid)
96. 0.895 0.895 ↑ 194.8 8 1

CTE Scan on bucketab cte0_1 (cost=0.00..31.16 rows=1,558 width=68) (actual time=0.883..0.895 rows=8 loops=1)

97. 0.002 0.008 ↑ 1.0 5 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
98. 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 : 12.562 ms
Execution time : 458.771 ms