explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GIuh : optimized mdb

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 3,558.728 ↑ 141.5 12 1

Append (cost=8,234,234.43..8,234,290.98 rows=1,698 width=96) (actual time=3,487.232..3,558.728 rows=12 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.006..0.011 rows=1 loops=1)

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

CTE picklist_2

5. 0.011 0.011 ↑ 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.011 rows=1 loops=1)

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

CTE openwont1

7. 154.267 1,871.726 ↓ 12.9 185,502 1

Nested Loop Left Join (cost=32,453.75..8,040,630.40 rows=14,384 width=34) (actual time=59.400..1,871.726 rows=185,502 loops=1)

8. 110.976 1,344.706 ↓ 12.9 185,502 1

Nested Loop Left Join (cost=32,453.70..7,674,887.33 rows=14,384 width=56) (actual time=57.085..1,344.706 rows=185,502 loops=1)

9. 68.098 677.224 ↓ 12.9 185,502 1

Nested Loop Left Join (cost=32,453.14..7,556,248.89 rows=14,384 width=28) (actual time=57.070..677.224 rows=185,502 loops=1)

10. 188.377 238.122 ↓ 12.9 185,502 1

Bitmap Heap Scan on t123_opp_split_fact (cost=32,453.14..7,550,874.13 rows=14,384 width=28) (actual time=57.054..238.122 rows=185,502 loops=1)

  • Recheck Cond: ((c2022_opp_close_date >= 20,191,101) AND (c2022_opp_close_date <= 20,200,131) AND (start_stamp <= '1577087999999'::bigint) AND (end_stamp > '1577087999999'::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: 15,540
  • Heap Blocks: exact=45,302
11. 47.793 47.793 ↑ 1.4 201,072 1

Bitmap Index Scan on idx_213_t123_opp_split_fact_c2022_opp_close_date_c2017_opp_stag (cost=0.00..32,449.52 rows=278,015 width=0) (actual time=47.793..47.793 rows=201,072 loops=1)

  • Index Cond: ((c2022_opp_close_date >= 20,191,101) AND (c2022_opp_close_date <= 20,200,131) AND (start_stamp <= '1577087999999'::bigint) AND (end_stamp > '1577087999999'::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.007..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.798 1.939 ↓ 1.0 3,602 1

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

  • Hash Cond: (t57_userrolehierarchy_3.c761_user_role_id = t41_userroledim_3.sid)
18. 0.779 0.951 ↑ 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.207..0.951 rows=3,607 loops=1)

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

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

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

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

Seq Scan on t41_userroledim t41_userroledim_3 (cost=0.00..27.19 rows=465 width=4) (actual time=0.003..0.146 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.63..2,585.47 rows=3,023 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.88 rows=3,141 width=0) (never executed)

  • Index Cond: ((c532_usertype = 'Standard'::text) AND (end_stamp = '32503680000000'::bigint))
25. 185.502 371.004 ↑ 2.0 1 185,502

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

26. 0.000 0.000 ↓ 0.0 0 185,502

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=185,502)

  • 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. 185.502 185.502 ↑ 1.0 1 185,502

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.001 rows=1 loops=185,502)

  • 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. 556.506 556.506 ↑ 1.0 1 185,502

Index Scan using t123_opp_split_fact_sidendstampunique on t123_opp_split_fact t123_opp_split_fact_1 (cost=0.56..8.24 rows=1 width=28) (actual time=0.002..0.003 rows=1 loops=185,502)

  • 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. 185.502 371.004 ↑ 2.0 1 185,502

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

30. 0.000 0.000 ↓ 0.0 0 185,502

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=185,502)

  • 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. 185.502 185.502 ↑ 1.0 1 185,502

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.001 rows=1 loops=185,502)

  • 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.000 0.000 ↑ 1.0 1 1

CTE Scan on picklist_0 cte0_2 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 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.714 1.749 ↓ 1.0 3,602 1

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

  • Hash Cond: (t57_userrolehierarchy_1.c761_user_role_id = t41_userroledim_1.sid)
38. 0.668 0.832 ↑ 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.199..0.832 rows=3,607 loops=1)

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
41. 0.150 0.150 ↑ 1.0 448 1

Seq Scan on t41_userroledim t41_userroledim_1 (cost=0.00..27.19 rows=465 width=4) (actual time=0.003..0.150 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.63..2,585.47 rows=3,023 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.88 rows=3,141 width=0) (never executed)

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

46.          

CTE openwont1ids

47. 96.735 2,033.339 ↓ 927.5 185,502 1

HashAggregate (cost=323.64..325.64 rows=200 width=4) (actual time=1,998.616..2,033.339 rows=185,502 loops=1)

  • Group Key: cte0_5.c1
48. 1,936.604 1,936.604 ↓ 12.9 185,502 1

CTE Scan on openwont1 cte0_5 (cost=0.00..287.68 rows=14,384 width=4) (actual time=59.402..1,936.604 rows=185,502 loops=1)

49.          

CTE bucketab

50. 42.973 71.389 ↑ 143.8 10 1

HashAggregate (cost=503.44..517.82 rows=1,438 width=68) (actual time=71.379..71.389 rows=10 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. 28.416 28.416 ↓ 12.9 185,502 1

CTE Scan on openwont1 cte0_6 (cost=0.00..287.68 rows=14,384 width=64) (actual time=0.013..28.416 rows=185,502 loops=1)

52.          

CTE picklist_3

53. 0.015 0.015 ↑ 1.0 2 1

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

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

CTE bucketd

55. 20.724 3,487.295 ↑ 130.0 2 1

GroupAggregate (cost=186,221.06..192,752.76 rows=260 width=120) (actual time=3,487.226..3,487.295 rows=2 loops=1)

  • Group Key: '1577087999999'::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 > 20200131) THEN 'pulledIn'::text WHEN (t123_opp_split_fact_3.c2022_opp_close_date < 20191101) THEN 'pushedIn'::text WHEN ((t123_opp_split_fact_3.c2022_opp_close_date >= 20,191,101) AND (t123_opp_split_fact_3.c2022_opp_close_date <= 20,200,131) 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. 50.249 3,466.571 ↓ 496.3 129,045 1

Sort (cost=186,221.02..186,221.67 rows=260 width=60) (actual time=3,457.309..3,466.571 rows=129,045 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 > 20200131) THEN 'pulledIn'::text WHEN (t123_opp_split_fact_3.c2022_opp_close_date < 20191101) THEN 'pushedIn'::text WHEN ((t123_opp_split_fact_3.c2022_opp_close_date >= 20,191,101) AND (t123_opp_split_fact_3.c2022_opp_close_date <= 20,200,131) 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: external sort Disk: 6,320kB
57. 78.959 3,416.322 ↓ 496.3 129,045 1

Nested Loop Left Join (cost=5.69..186,210.59 rows=260 width=60) (actual time=2,158.534..3,416.322 rows=129,045 loops=1)

58. 55.213 2,821.166 ↓ 496.3 129,045 1

Nested Loop Left Join (cost=5.08..177,515.19 rows=260 width=20) (actual time=2,158.492..2,821.166 rows=129,045 loops=1)

59. 14.101 2,507.863 ↓ 496.3 129,045 1

Nested Loop (cost=5.08..177,412.97 rows=260 width=20) (actual time=2,158.473..2,507.863 rows=129,045 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. 421.928 2,493.747 ↓ 496.3 129,045 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..177,410.34 rows=260 width=24) (actual time=2,158.456..2,493.747 rows=129,045 loops=1)

  • Index Cond: ((c2022_opp_close_date >= 20,191,101) AND (c2022_opp_close_date <= 20,200,131) 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: 184,224
63.          

SubPlan (for Index Scan)

64. 2,069.842 2,069.842 ↓ 927.5 185,502 1

CTE Scan on openwont1ids cte0_8 (cost=0.00..4.00 rows=200 width=4) (actual time=1,998.618..2,069.842 rows=185,502 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.784 1.977 ↓ 1.0 3,602 1

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

  • Hash Cond: (t57_userrolehierarchy_7.c761_user_role_id = t41_userroledim_7.sid)
69. 0.800 0.984 ↑ 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.221..0.984 rows=3,607 loops=1)

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
72. 0.154 0.154 ↑ 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.154 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.63..2,585.47 rows=3,023 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.88 rows=3,141 width=0) (never executed)

  • Index Cond: ((c532_usertype = 'Standard'::text) AND (end_stamp = '32503680000000'::bigint))
76. 129.045 258.090 ↑ 2.0 1 129,045

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

77. 0.000 0.000 ↓ 0.0 0 129,045

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=129,045)

  • 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. 129.045 129.045 ↑ 1.0 1 129,045

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.001 rows=1 loops=129,045)

  • 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. 516.180 516.180 ↑ 1.0 1 129,045

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.002..0.004 rows=1 loops=129,045)

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

SubPlan (for Nested Loop Left Join)

81. 0.017 0.017 ↑ 1.0 2 1

CTE Scan on picklist_3 cte0_7 (cost=0.00..0.04 rows=2 width=4) (actual time=0.010..0.017 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.63..2,585.47 rows=3,023 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.88 rows=3,141 width=0) (never executed)

  • Index Cond: ((c532_usertype = 'Standard'::text) AND (end_stamp = '32503680000000'::bigint))
93. 0.005 3,487.304 ↑ 130.0 2 1

Subquery Scan on "*SELECT* 1" (cost=0.00..8.45 rows=260 width=96) (actual time=3,487.232..3,487.304 rows=2 loops=1)

94. 3,487.299 3,487.299 ↑ 130.0 2 1

CTE Scan on bucketd cte0 (cost=0.00..5.20 rows=260 width=120) (actual time=3,487.228..3,487.299 rows=2 loops=1)

95. 0.013 71.422 ↑ 143.8 10 1

Hash Left Join (cost=1.11..33.73 rows=1,438 width=96) (actual time=71.406..71.422 rows=10 loops=1)

  • Hash Cond: (cte0_1.c6 = t101_opportunityforecastcategorypicklistdim.sid)
96. 71.395 71.395 ↑ 143.8 10 1

CTE Scan on bucketab cte0_1 (cost=0.00..28.76 rows=1,438 width=68) (actual time=71.381..71.395 rows=10 loops=1)

97. 0.007 0.014 ↑ 1.0 5 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
98. 0.007 0.007 ↑ 1.0 5 1

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

Planning time : 11.468 ms
Execution time : 3,565.916 ms