explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DY83y : DS query

Settings
# exclusive inclusive rows x rows loops node
1. 0.074 121,452.362 ↑ 301.9 150 1

Nested Loop (cost=4,115,112.65..49,383,659.09 rows=45,288 width=96) (actual time=73,444.074..121,452.362 rows=150 loops=1)

2.          

CTE multitimecte

3. 0.025 0.025 ↑ 1.0 12 1

Values Scan on ""*VALUES*"" (cost=0.00..0.15 rows=12 width=8) (actual time=0.002..0.025 rows=12 loops=1)

4.          

CTE picklist_0

5. 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
6.          

CTE picklist_2

7. 0.014 0.014 ↑ 1.0 1 1

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

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

CTE picklist_3

9. 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.009..0.015 rows=2 loops=1)

  • Filter: (correlated_value = ANY ('{""Closed Lost"",""Closed Won""}'::text[]))
  • Rows Removed by Filter: 46
10. 0.036 0.036 ↑ 1.0 12 1

CTE Scan on multitimecte multitimecte_alias (cost=0.00..0.24 rows=12 width=8) (actual time=0.003..0.036 rows=12 loops=1)

11. 0.036 121,452.252 ↑ 314.5 12 12

Append (cost=4,115,104.70..4,115,228.76 rows=3,774 width=96) (actual time=10,048.996..10,121.021 rows=12 loops=12)

12.          

CTE openwont1

13. 2,131.401 44,841.408 ↓ 11.3 186,842 12

Nested Loop Left Join (cost=243,381.00..3,789,319.04 rows=16,474 width=34) (actual time=379.243..3,736.784 rows=186,842 loops=12)

14. 1,723.052 38,223.840 ↓ 11.3 186,842 12

Nested Loop Left Join (cost=243,380.95..3,370,433.26 rows=16,474 width=56) (actual time=379.019..3,185.320 rows=186,842 loops=12)

15. 1,050.172 14,079.708 ↓ 11.3 186,842 12

Nested Loop Left Join (cost=243,380.39..3,242,237.36 rows=16,474 width=28) (actual time=378.940..1,173.309 rows=186,842 loops=12)

16. 984.768 8,545.320 ↓ 11.3 186,842 12

Hash Join (cost=243,380.39..3,236,571.17 rows=16,474 width=28) (actual time=378.772..712.110 rows=186,842 loops=12)

  • Hash Cond: (t108_opp_split_fact.c1800_opportunity_sid = t78_oppdim.sid)
17. 3,098.262 4,848.240 ↓ 4.9 196,353 12

Bitmap Heap Scan on t108_opp_split_fact (cost=35,296.71..3,000,937.27 rows=40,128 width=32) (actual time=152.424..404.020 rows=196,353 loops=12)

  • Recheck Cond: ((c1825_opp_close_date >= 20,191,101) AND (c1825_opp_close_date <= 20,200,131) AND (start_stamp <= $11) AND (end_stamp > $11))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 15)) AND ((alternatives: SubPlan 11 or hashed SubPlan 12) OR (alternatives: SubPlan 13 or hashed SubPlan 14)))
  • Rows Removed by Filter: 6,237
  • Heap Blocks: exact=380,203
18. 1,747.980 1,747.980 ↓ 1.9 203,261 12

Bitmap Index Scan on idx_171_t108_opp_split_fact_c1825_opp_close_date_c1816_opp_stag (cost=0.00..35,286.66 rows=107,008 width=0) (actual time=145.665..145.665 rows=203,261 loops=12)

  • Index Cond: ((c1825_opp_close_date >= 20,191,101) AND (c1825_opp_close_date <= 20,200,131) AND (start_stamp <= $11) AND (end_stamp > $11))
19.          

SubPlan (for Bitmap Heap Scan)

20. 0.018 0.018 ↑ 1.0 1 1

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

21. 0.000 0.000 ↓ 0.0 0

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

22. 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))
23. 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))
24. 0.779 1.980 ↓ 1.0 3,593 1

Hash Join (cost=166.65..560.98 rows=3,546 width=4) (actual time=0.430..1.980 rows=3,593 loops=1)

  • Hash Cond: (t57_userrolehierarchy_3.c761_user_role_id = t41_userroledim_3.sid)
25. 0.817 0.996 ↑ 1.0 3,598 1

Bitmap Heap Scan on t57_userrolehierarchy t57_userrolehierarchy_3 (cost=133.65..518.32 rows=3,645 width=8) (actual time=0.213..0.996 rows=3,598 loops=1)

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
28. 0.152 0.152 ↑ 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.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
29. 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))
30. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t37_userdim t37_userdim_3 (cost=132.49..2,574.61 rows=3,009 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))
31. 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.74 rows=3,127 width=0) (never executed)

  • Index Cond: ((c532_usertype = 'Standard'::text) AND (end_stamp = '32503680000000'::bigint))
32. 161.388 2,712.312 ↑ 1.2 76,628 12

Hash (cost=206,612.05..206,612.05 rows=89,650 width=4) (actual time=226.026..226.026 rows=76,628 loops=12)

  • Buckets: 131,072 Batches: 2 Memory Usage: 2,681kB
33. 1,052.172 2,550.924 ↑ 1.2 76,628 12

Bitmap Heap Scan on t78_oppdim (cost=8,776.72..206,612.05 rows=89,650 width=4) (actual time=131.601..212.577 rows=76,628 loops=12)

  • Recheck Cond: ((end_stamp > $11) AND (start_stamp <= $11))
  • Filter: ((NOT deleted) AND c1233_in_forecast_gvp__c)
  • Heap Blocks: exact=509,658
34. 1,498.752 1,498.752 ↑ 1.2 76,628 12

Bitmap Index Scan on idx_74_t78_oppdim_c1233_in_forecast_gvp__c_end_stamp_start_stam (cost=0.00..8,754.31 rows=89,650 width=0) (actual time=124.896..124.896 rows=76,628 loops=12)

  • Index Cond: ((c1233_in_forecast_gvp__c = true) AND (end_stamp > $11) AND (start_stamp <= $11))
35. 0.000 4,484.216 ↑ 2.0 1 2,242,108

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

36. 0.000 0.000 ↓ 0.0 0 2,242,108

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,242,108)

  • 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))
37. 4,484.216 4,484.216 ↑ 1.0 1 2,242,108

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

  • 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)
38. 22,421.080 22,421.080 ↑ 1.0 1 2,242,108

Index Scan using t108_opp_split_fact_sidendstampunique on t108_opp_split_fact t108_opp_split_fact_1 (cost=0.56..7.77 rows=1 width=28) (actual time=0.008..0.010 rows=1 loops=2,242,108)

  • 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
39. 0.000 4,484.216 ↑ 2.0 1 2,242,108

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

40. 0.000 0.000 ↓ 0.0 0 2,242,108

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,242,108)

  • 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))
41. 4,484.216 4,484.216 ↑ 1.0 1 2,242,108

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,242,108)

  • 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)
42.          

SubPlan (for Nested Loop Left Join)

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

44. 0.000 0.000 ↓ 0.0 0

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

45. 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))
46. 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))
47. 0.785 1.949 ↓ 1.0 3,593 1

Hash Join (cost=166.65..560.98 rows=3,546 width=4) (actual time=0.420..1.949 rows=3,593 loops=1)

  • Hash Cond: (t57_userrolehierarchy_1.c761_user_role_id = t41_userroledim_1.sid)
48. 0.790 0.966 ↑ 1.0 3,598 1

Bitmap Heap Scan on t57_userrolehierarchy t57_userrolehierarchy_1 (cost=133.65..518.32 rows=3,645 width=8) (actual time=0.212..0.966 rows=3,598 loops=1)

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

  • Index Cond: ((c762_ancestor_role_id = 157) AND (end_stamp = '32503680000000'::bigint))
50. 0.050 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
51. 0.148 0.148 ↑ 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.148 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
52. 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))
53. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t37_userdim t37_userdim_1 (cost=132.49..2,574.61 rows=3,009 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))
54. 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.74 rows=3,127 width=0) (never executed)

  • Index Cond: ((c532_usertype = 'Standard'::text) AND (end_stamp = '32503680000000'::bigint))
55. 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)

56.          

CTE openwont1ids

57. 1,268.172 46,889.184 ↓ 934.2 186,842 12

HashAggregate (cost=370.67..372.67 rows=200 width=4) (actual time=3,869.893..3,907.432 rows=186,842 loops=12)

  • Group Key: cte0_5.c1
58. 45,621.012 45,621.012 ↓ 11.3 186,842 12

CTE Scan on openwont1 cte0_5 (cost=0.00..329.48 rows=16,474 width=4) (actual time=379.262..3,801.751 rows=186,842 loops=12)

59.          

CTE bucketab

60. 521.316 863.172 ↑ 164.7 10 12

HashAggregate (cost=576.59..593.06 rows=1,647 width=68) (actual time=71.923..71.931 rows=10 loops=12)

  • 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
61. 341.856 341.856 ↓ 11.3 186,842 12

CTE Scan on openwont1 cte0_6 (cost=0.00..329.48 rows=16,474 width=64) (actual time=0.017..28.488 rows=186,842 loops=12)

62.          

CTE bucketd

63. 256.404 120,588.684 ↑ 709.0 3 12

GroupAggregate (cost=271,385.59..324,819.93 rows=2,127 width=120) (actual time=10,048.989..10,049.057 rows=3 loops=12)

  • Group Key: ($11), (CASE WHEN (t108_opp_split_fact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 19)) THEN CASE WHEN (t108_opp_split_fact_3.c1825_opp_close_date > 20200131) THEN 'pulledIn'::text WHEN (t108_opp_split_fact_3.c1825_opp_close_date < 20191101) THEN 'pushedIn'::text WHEN ((t108_opp_split_fact_3.c1825_opp_close_date >= 20,191,101) AND (t108_opp_split_fact_3.c1825_opp_close_date <= 20,200,131) AND ((alternatives: SubPlan 20 or hashed SubPlan 21) OR (alternatives: SubPlan 22 or hashed SubPlan 23))) THEN 'other'::text ELSE 'ownerChange'::text END ELSE 'other'::text END)
64. 607.848 120,332.280 ↓ 60.0 127,650 12

Sort (cost=271,385.54..271,390.86 rows=2,127 width=60) (actual time=10,018.755..10,027.690 rows=127,650 loops=12)

  • Sort Key: (CASE WHEN (t108_opp_split_fact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 19)) THEN CASE WHEN (t108_opp_split_fact_3.c1825_opp_close_date > 20200131) THEN 'pulledIn'::text WHEN (t108_opp_split_fact_3.c1825_opp_close_date < 20191101) THEN 'pushedIn'::text WHEN ((t108_opp_split_fact_3.c1825_opp_close_date >= 20,191,101) AND (t108_opp_split_fact_3.c1825_opp_close_date <= 20,200,131) AND ((alternatives: SubPlan 20 or hashed SubPlan 21) OR (alternatives: SubPlan 22 or hashed SubPlan 23))) THEN 'other'::text ELSE 'ownerChange'::text END ELSE 'other'::text END)
  • Sort Method: quicksort Memory: 3,188kB
65. 1,339.405 119,724.432 ↓ 60.0 127,650 12

Nested Loop (cost=6.12..271,267.97 rows=2,127 width=60) (actual time=4,028.500..9,977.036 rows=127,650 loops=12)

66. 1,832.412 113,789.592 ↓ 68.4 127,650 12

Nested Loop Left Join (cost=5.65..192,845.40 rows=1,865 width=40) (actual time=4,028.332..9,482.466 rows=127,650 loops=12)

67. 887.172 59,875.776 ↓ 68.4 127,650 12

Nested Loop Left Join (cost=5.08..179,249.79 rows=1,865 width=24) (actual time=4,028.317..4,989.648 rows=127,650 loops=12)

68. 196.008 55,924.992 ↓ 68.4 127,650 12

Nested Loop (cost=5.08..178,621.91 rows=1,865 width=24) (actual time=4,028.299..4,660.416 rows=127,650 loops=12)

69. 0.024 0.036 ↑ 1.0 1 12

HashAggregate (cost=0.02..0.03 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=12)

  • Group Key: cte0_9.c1
70. 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)

71. 8,385.326 55,728.948 ↓ 68.4 127,650 12

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..178,603.22 rows=1,865 width=28) (actual time=4,028.290..4,644.079 rows=127,650 loops=12)

  • Index Cond: ((c1825_opp_close_date >= 20,191,101) AND (c1825_opp_close_date <= 20,200,131) AND (c1816_opp_stagename = cte0_9.c1) AND (start_stamp <= '1595833199999'::bigint) AND (end_stamp > '1595833199999'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 28)) AND ((alternatives: SubPlan 24 or hashed SubPlan 25) OR (alternatives: SubPlan 26 or hashed SubPlan 27)))
  • Rows Removed by Filter: 185,618
72.          

SubPlan (for Index Scan)

73. 47,341.728 47,341.728 ↓ 934.2 186,842 12

CTE Scan on openwont1ids cte0_8 (cost=0.00..4.00 rows=200 width=4) (actual time=3,870.861..3,945.144 rows=186,842 loops=12)

74. 0.000 0.000 ↓ 0.0 0

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

75. 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))
76. 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))
77. 0.756 1.894 ↓ 1.0 3,593 1

Hash Join (cost=166.65..560.98 rows=3,546 width=4) (actual time=0.410..1.894 rows=3,593 loops=1)

  • Hash Cond: (t57_userrolehierarchy_7.c761_user_role_id = t41_userroledim_7.sid)
78. 0.773 0.943 ↑ 1.0 3,598 1

Bitmap Heap Scan on t57_userrolehierarchy t57_userrolehierarchy_7 (cost=133.65..518.32 rows=3,645 width=8) (actual time=0.206..0.943 rows=3,598 loops=1)

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

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

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

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

Seq Scan on t41_userroledim t41_userroledim_7 (cost=0.00..27.19 rows=465 width=4) (actual time=0.005..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
82. 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))
83. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t37_userdim t37_userdim_7 (cost=132.49..2,574.61 rows=3,009 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))
84. 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.74 rows=3,127 width=0) (never executed)

  • Index Cond: ((c532_usertype = 'Standard'::text) AND (end_stamp = '32503680000000'::bigint))
85. 0.000 3,063.612 ↑ 2.0 1 1,531,806

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

86. 0.000 0.000 ↓ 0.0 0 1,531,806

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=1,531,806)

  • 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))
87. 3,063.612 3,063.612 ↑ 1.0 1 1,531,806

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=1,531,806)

  • 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)
88. 52,081.404 52,081.404 ↑ 1.0 1 1,531,806

Index Scan using t108_opp_split_fact_sidendstampunique on t108_opp_split_fact t108_opp_split_fact_3 (cost=0.56..7.28 rows=1 width=16) (actual time=0.012..0.034 rows=1 loops=1,531,806)

  • Index Cond: ((t108_opp_split_fact_2.sid = sid) AND (t108_opp_split_fact_2.sid = sid) AND (end_stamp > $11))
  • Filter: ((NOT deleted) AND (start_stamp <= $11))
  • Rows Removed by Filter: 2
89. 4,595.418 4,595.418 ↑ 1.0 1 1,531,806

Index Scan using idx_72_t78_oppdim_sid_end_stamp_start_stamp_null on t78_oppdim t78_oppdim_1 (cost=0.43..13.42 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1,531,806)

  • 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 c1233_in_forecast_gvp__c)
90.          

SubPlan (for Nested Loop)

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

92. 0.000 0.000 ↓ 0.0 0

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

93. 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))
94. 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))
95. 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)
96. 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))
97. 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))
98. 0.000 0.000 ↓ 0.0 0

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

99. 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))
100. 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))
101. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t37_userdim t37_userdim_5 (cost=132.49..2,574.61 rows=3,009 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))
102. 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.74 rows=3,127 width=0) (never executed)

  • Index Cond: ((c532_usertype = 'Standard'::text) AND (end_stamp = '32503680000000'::bigint))
103. 0.048 120,588.780 ↑ 709.0 3 12

Subquery Scan on "*SELECT* 1" (cost=0.00..69.13 rows=2,127 width=96) (actual time=10,048.995..10,049.065 rows=3 loops=12)

104. 120,588.732 120,588.732 ↑ 709.0 3 12

CTE Scan on bucketd cte0 (cost=0.00..42.54 rows=2,127 width=120) (actual time=10,048.992..10,049.061 rows=3 loops=12)

105. 0.095 863.436 ↑ 164.7 10 12

Hash Left Join (cost=1.11..38.46 rows=1,647 width=96) (actual time=71.939..71.953 rows=10 loops=12)

  • Hash Cond: (cte0_1.c6 = t101_opportunityforecastcategorypicklistdim.sid)
106. 863.328 863.328 ↑ 164.7 10 12

CTE Scan on bucketab cte0_1 (cost=0.00..32.94 rows=1,647 width=68) (actual time=71.933..71.944 rows=10 loops=12)

107. 0.007 0.013 ↑ 1.0 5 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
108. 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 : 15.449 ms
Execution time : 121,463.597 ms