explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1Nfy

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 1,244.844 ↑ 54.4 42 1

Append (cost=8,545,089.91..8,547,311.11 rows=2,284 width=120) (actual time=218.685..1,244.844 rows=42 loops=1)

2.          

CTE picklist_0

3. 0.033 0.033 ↑ 1.0 2 1

Seq Scan on t77_opportunitystagenamepicklistdim (cost=0.00..4.44 rows=2 width=4) (actual time=0.011..0.033 rows=2 loops=1)

  • Filter: (correlated_value = ANY ('{"6 - Order Accepted","5 - Field Closed Won"}'::text[]))
  • Rows Removed by Filter: 113
4.          

CTE picklist_1

5. 0.021 0.021 ↑ 1.0 1 1

Seq Scan on t77_opportunitystagenamepicklistdim t77_opportunitystagenamepicklistdim_1 (cost=0.00..4.44 rows=1 width=4) (actual time=0.008..0.021 rows=1 loops=1)

  • Filter: (correlated_value = '7 - Closed (Not Won)'::text)
  • Rows Removed by Filter: 114
6.          

CTE picklist_4

7. 0.031 0.031 ↑ 1.0 3 1

Seq Scan on t77_opportunitystagenamepicklistdim t77_opportunitystagenamepicklistdim_2 (cost=0.00..4.58 rows=3 width=4) (actual time=0.008..0.031 rows=3 loops=1)

  • Filter: (correlated_value = ANY ('{"6 - Order Accepted","5 - Field Closed Won","7 - Closed (Not Won)"}'::text[]))
  • Rows Removed by Filter: 112
8.          

CTE bucketac

9. 3.900 223.384 ↑ 1,248.7 25 1

GroupAggregate (cost=1,667,342.10..2,963,414.25 rows=31,218 width=92) (actual time=218.661..223.384 rows=25 loops=1)

  • Group Key: t60_opp_split_fact.c893_opp_forecastcategory, (CASE WHEN (t60_opp_split_fact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t60_opp_split_fact_1.c855_opp_close_date < 20181001) OR (t60_opp_split_fact_1.c855_opp_close_date > 20181231)) THEN 'slipped'::text WHEN (alternatives: SubPlan 4 or hashed SubPlan 5) THEN CASE WHEN (hashed SubPlan 6) THEN 'won'::text WHEN (hashed SubPlan 7) THEN 'lost'::text ELSE CASE WHEN (t75_opportunityforecastcategorypicklistdim_2.correlated_app_order = t75_opportunityforecastcategorypicklistdim_3.correlated_app_order) THEN 'unchanged'::text WHEN (t75_opportunityforecastcategorypicklistdim_2.correlated_app_order > t75_opportunityforecastcategorypicklistdim_3.correlated_app_order) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
10. 5.752 219.484 ↑ 2.4 12,753 1

Sort (cost=1,667,342.04..1,667,420.08 rows=31,218 width=72) (actual time=218.510..219.484 rows=12,753 loops=1)

  • Sort Key: t60_opp_split_fact.c893_opp_forecastcategory, (CASE WHEN (t60_opp_split_fact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t60_opp_split_fact_1.c855_opp_close_date < 20181001) OR (t60_opp_split_fact_1.c855_opp_close_date > 20181231)) THEN 'slipped'::text WHEN (alternatives: SubPlan 4 or hashed SubPlan 5) THEN CASE WHEN (hashed SubPlan 6) THEN 'won'::text WHEN (hashed SubPlan 7) THEN 'lost'::text ELSE CASE WHEN (t75_opportunityforecastcategorypicklistdim_2.correlated_app_order = t75_opportunityforecastcategorypicklistdim_3.correlated_app_order) THEN 'unchanged'::text WHEN (t75_opportunityforecastcategorypicklistdim_2.correlated_app_order > t75_opportunityforecastcategorypicklistdim_3.correlated_app_order) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
  • Sort Method: quicksort Memory: 1433kB
11. 8.328 213.732 ↑ 2.4 12,753 1

Hash Left Join (cost=36,983.88..1,665,011.60 rows=31,218 width=72) (actual time=69.229..213.732 rows=12,753 loops=1)

  • Hash Cond: (t60_opp_split_fact_1.c893_opp_forecastcategory = t75_opportunityforecastcategorypicklistdim_3.sid)
12. 3.175 150.176 ↑ 2.4 12,753 1

Hash Left Join (cost=36,982.70..369,449.86 rows=31,218 width=64) (actual time=69.215..150.176 rows=12,753 loops=1)

  • Hash Cond: (t60_opp_split_fact_1.c850_opp_currency_code = t59_claricurrencytypedim_1.sid)
13. 12.461 146.995 ↑ 2.4 12,753 1

Nested Loop Left Join (cost=36,981.58..369,300.16 rows=31,218 width=60) (actual time=69.204..146.995 rows=12,753 loops=1)

14. 2.656 96.275 ↑ 2.4 12,753 1

Hash Left Join (cost=36,981.15..193,031.79 rows=31,218 width=28) (actual time=69.188..96.275 rows=12,753 loops=1)

  • Hash Cond: (t60_opp_split_fact.c893_opp_forecastcategory = t75_opportunityforecastcategorypicklistdim_2.sid)
15. 2.554 93.612 ↑ 2.4 12,753 1

Hash Left Join (cost=36,980.03..192,605.72 rows=31,218 width=24) (actual time=69.175..93.612 rows=12,753 loops=1)

  • Hash Cond: (t60_opp_split_fact.c850_opp_currency_code = t59_claricurrencytypedim.sid)
16. 5.835 91.043 ↑ 2.4 12,753 1

Hash Join (cost=36,978.91..192,456.03 rows=31,218 width=20) (actual time=69.150..91.043 rows=12,753 loops=1)

  • Hash Cond: (t60_opp_split_fact.c879_opportunity_split_ownerid = t61_userrolehierarchy_2.sid)
17. 17.260 27.286 ↑ 2.4 12,753 1

Bitmap Heap Scan on t60_opp_split_fact (cost=5,812.72..161,135.66 rows=31,218 width=24) (actual time=11.184..27.286 rows=12,753 loops=1)

  • Recheck Cond: ((c855_opp_close_date >= 20181001) AND (c855_opp_close_date <= 20181231) AND (start_stamp <= '1538377200000'::bigint) AND (end_stamp > '1538377200000'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 8)))
  • Rows Removed by Filter: 8840
  • Heap Blocks: exact=7304
18. 9.991 9.991 ↑ 3.1 21,593 1

Bitmap Index Scan on ix_t60_opp_split_fact_closedate_stagename_timestamp (cost=0.00..5,804.85 rows=66,749 width=0) (actual time=9.991..9.991 rows=21,593 loops=1)

  • Index Cond: ((c855_opp_close_date >= 20181001) AND (c855_opp_close_date <= 20181231) AND (start_stamp <= '1538377200000'::bigint) AND (end_stamp > '1538377200000'::bigint))
19.          

SubPlan (forBitmap Heap Scan)

20. 0.035 0.035 ↑ 1.0 3 1

CTE Scan on picklist_4 cte0_4 (cost=0.00..0.06 rows=3 width=4) (actual time=0.009..0.035 rows=3 loops=1)

21. 0.857 57.922 ↑ 1.1 6,046 1

Hash (cost=31,083.91..31,083.91 rows=6,582 width=4) (actual time=57.922..57.922 rows=6,046 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 277kB
22. 2.297 57.065 ↑ 1.1 6,046 1

HashAggregate (cost=31,018.09..31,083.91 rows=6,582 width=4) (actual time=56.247..57.065 rows=6,046 loops=1)

  • Group Key: t61_userrolehierarchy_2.sid
23. 12.857 54.768 ↑ 1.0 6,502 1

Hash Semi Join (cost=4,315.67..31,001.64 rows=6,582 width=4) (actual time=12.266..54.768 rows=6,502 loops=1)

  • Hash Cond: (t61_userrolehierarchy_2.c898_user_role_id = t42_userroledim_2.sid)
24. 31.343 41.339 ↑ 1.0 97,968 1

Bitmap Heap Scan on t61_userrolehierarchy t61_userrolehierarchy_2 (cost=3,138.89..29,419.97 rows=101,272 width=8) (actual time=11.685..41.339 rows=97,968 loops=1)

  • Recheck Cond: ((c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=10585
25. 9.996 9.996 ↓ 1.1 109,831 1

Bitmap Index Scan on idx_t61_userrolehierarchy_c899_ancestor_role_id_end_sta34629852 (cost=0.00..3,113.58 rows=101,272 width=0) (actual time=9.996..9.996 rows=109,831 loops=1)

  • Index Cond: ((c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
26. 0.064 0.572 ↑ 1.3 451 1

Hash (cost=1,169.49..1,169.49 rows=583 width=4) (actual time=0.572..0.572 rows=451 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
27. 0.440 0.508 ↑ 1.3 451 1

Bitmap Heap Scan on t42_userroledim t42_userroledim_2 (cost=41.12..1,169.49 rows=583 width=4) (actual time=0.092..0.508 rows=451 loops=1)

  • Recheck Cond: ((end_stamp = '32503680000000'::bigint) AND ((c572_portaltype IS NULL) OR (c572_portaltype = 'None'::text)))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
  • Rows Removed by Filter: 127
  • Heap Blocks: exact=172
28. 0.068 0.068 ↑ 1.0 578 1

Bitmap Index Scan on ix_t42_userroledim_sid_timestamp_partial (cost=0.00..40.97 rows=585 width=0) (actual time=0.068..0.068 rows=578 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
29. 0.002 0.015 ↓ 3.0 3 1

Hash (cost=1.11..1.11 rows=1 width=12) (actual time=0.015..0.015 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.013 0.013 ↓ 3.0 3 1

Seq Scan on t59_claricurrencytypedim (cost=0.00..1.11 rows=1 width=12) (actual time=0.011..0.013 rows=3 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 6
31. 0.002 0.007 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.005 0.005 ↑ 1.0 5 1

Seq Scan on t75_opportunityforecastcategorypicklistdim t75_opportunityforecastcategorypicklistdim_2 (cost=0.00..1.05 rows=5 width=8) (actual time=0.004..0.005 rows=5 loops=1)

33. 38.259 38.259 ↑ 1.0 1 12,753

Index Scan using idx_t60_opp_split_fact_sid_end_stamp_start_stamp on t60_opp_split_fact t60_opp_split_fact_1 (cost=0.43..5.64 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=12,753)

  • Index Cond: ((t60_opp_split_fact.sid = sid) AND (t60_opp_split_fact.sid = sid) AND (end_stamp > '1546329599999'::bigint) AND (start_stamp <= '1546329599999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
34. 0.002 0.006 ↓ 3.0 3 1

Hash (cost=1.11..1.11 rows=1 width=12) (actual time=0.006..0.006 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
35. 0.004 0.004 ↓ 3.0 3 1

Seq Scan on t59_claricurrencytypedim t59_claricurrencytypedim_1 (cost=0.00..1.11 rows=1 width=12) (actual time=0.003..0.004 rows=3 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 6
36. 0.003 0.005 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.002 0.002 ↑ 1.0 5 1

Seq Scan on t75_opportunityforecastcategorypicklistdim t75_opportunityforecastcategorypicklistdim_3 (cost=0.00..1.05 rows=5 width=8) (actual time=0.001..0.002 rows=5 loops=1)

38.          

SubPlan (forHash Left Join)

39. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.71..41.47 rows=1 width=0) (never executed)

40. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t61_userrolehierarchy_sid_c899_ancestor_role_id_end34629852 on t61_userrolehierarchy (cost=0.43..16.85 rows=2 width=4) (never executed)

  • Index Cond: ((sid = t60_opp_split_fact_1.c879_opportunity_split_ownerid) AND (c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
41. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_t42_userroledim_sid_timestamp_partial on t42_userroledim (cost=0.28..8.30 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t61_userrolehierarchy.c898_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
42. 13.125 55.164 ↑ 1.0 6,502 1

Hash Semi Join (cost=4,315.67..31,001.64 rows=6,582 width=4) (actual time=12.200..55.164 rows=6,502 loops=1)

  • Hash Cond: (t61_userrolehierarchy_1.c898_user_role_id = t42_userroledim_1.sid)
43. 31.555 41.439 ↑ 1.0 97,968 1

Bitmap Heap Scan on t61_userrolehierarchy t61_userrolehierarchy_1 (cost=3,138.89..29,419.97 rows=101,272 width=8) (actual time=11.592..41.439 rows=97,968 loops=1)

  • Recheck Cond: ((c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=10585
44. 9.884 9.884 ↓ 1.1 109,831 1

Bitmap Index Scan on idx_t61_userrolehierarchy_c899_ancestor_role_id_end_sta34629852 (cost=0.00..3,113.58 rows=101,272 width=0) (actual time=9.884..9.884 rows=109,831 loops=1)

  • Index Cond: ((c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
45. 0.054 0.600 ↑ 1.3 451 1

Hash (cost=1,169.49..1,169.49 rows=583 width=4) (actual time=0.600..0.600 rows=451 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
46. 0.481 0.546 ↑ 1.3 451 1

Bitmap Heap Scan on t42_userroledim t42_userroledim_1 (cost=41.12..1,169.49 rows=583 width=4) (actual time=0.089..0.546 rows=451 loops=1)

  • Recheck Cond: ((end_stamp = '32503680000000'::bigint) AND ((c572_portaltype IS NULL) OR (c572_portaltype = 'None'::text)))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
  • Rows Removed by Filter: 127
  • Heap Blocks: exact=172
47. 0.065 0.065 ↑ 1.0 578 1

Bitmap Index Scan on ix_t42_userroledim_sid_timestamp_partial (cost=0.00..40.97 rows=585 width=0) (actual time=0.065..0.065 rows=578 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
48. 0.037 0.037 ↑ 1.0 2 1

CTE Scan on picklist_0 cte0_2 (cost=0.00..0.04 rows=2 width=4) (actual time=0.014..0.037 rows=2 loops=1)

49. 0.022 0.022 ↑ 1.0 1 1

CTE Scan on picklist_1 cte0_3 (cost=0.00..0.02 rows=1 width=4) (actual time=0.009..0.022 rows=1 loops=1)

50.          

CTE bucketec

51. 1.067 1,021.365 ↑ 3,539.9 17 1

GroupAggregate (cost=3,083,713.12..5,581,661.09 rows=60,178 width=116) (actual time=1,020.244..1,021.365 rows=17 loops=1)

  • Group Key: (CASE WHEN (t60_opp_split_fact_3.sid IS NULL) THEN 'newBucket'::text WHEN (t60_opp_split_fact_3.c855_opp_close_date > 20181231) THEN 'pulledInBucket'::text WHEN (t60_opp_split_fact_3.c855_opp_close_date < 20181001) THEN 'pushedInBucket'::text WHEN (alternatives: SubPlan 10 or hashed SubPlan 11) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END), (CASE WHEN (hashed SubPlan 12) THEN 'won'::text WHEN (hashed SubPlan 13) THEN 'lost'::text ELSE 'newOpen'::text END), t60_opp_split_fact_2.c893_opp_forecastcategory
52. 1.739 1,020.298 ↑ 15.0 4,013 1

Sort (cost=3,083,713.05..3,083,863.50 rows=60,178 width=88) (actual time=1,020.036..1,020.298 rows=4,013 loops=1)

  • Sort Key: (CASE WHEN (t60_opp_split_fact_3.sid IS NULL) THEN 'newBucket'::text WHEN (t60_opp_split_fact_3.c855_opp_close_date > 20181231) THEN 'pulledInBucket'::text WHEN (t60_opp_split_fact_3.c855_opp_close_date < 20181001) THEN 'pushedInBucket'::text WHEN (alternatives: SubPlan 10 or hashed SubPlan 11) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END), (CASE WHEN (hashed SubPlan 12) THEN 'won'::text WHEN (hashed SubPlan 13) THEN 'lost'::text ELSE 'newOpen'::text END), t60_opp_split_fact_2.c893_opp_forecastcategory
  • Sort Method: quicksort Memory: 410kB
53. 1.776 1,018.559 ↑ 15.0 4,013 1

Hash Left Join (cost=360,540.66..3,076,055.34 rows=60,178 width=88) (actual time=955.512..1,018.559 rows=4,013 loops=1)

  • Hash Cond: (t60_opp_split_fact_2.c850_opp_currency_code = t59_claricurrencytypedim_2.sid)
54. 46.393 1,016.766 ↑ 15.0 4,013 1

Hash Left Join (cost=360,539.46..579,474.68 rows=60,178 width=36) (actual time=955.449..1,016.766 rows=4,013 loops=1)

  • Hash Cond: ((t60_opp_split_fact_2.sid = t60_opp_split_fact_3.sid) AND (t60_opp_split_fact_2.sid = t60_opp_split_fact_3.sid))
  • Filter: ((t60_opp_split_fact_3.sid IS NULL) OR (t60_opp_split_fact_3.c855_opp_close_date < 20181001) OR (t60_opp_split_fact_3.c855_opp_close_date > 20181231) OR (NOT (alternatives: SubPlan 14 or hashed SubPlan 15)))
  • Rows Removed by Filter: 7984
55. 5.788 86.807 ↑ 5.4 11,997 1

Hash Join (cost=36,987.32..196,774.36 rows=65,143 width=24) (actual time=68.891..86.807 rows=11,997 loops=1)

  • Hash Cond: (t60_opp_split_fact_2.c879_opportunity_split_ownerid = t61_userrolehierarchy_7.sid)
56. 13.293 22.903 ↑ 5.4 11,998 1

Bitmap Heap Scan on t60_opp_split_fact t60_opp_split_fact_2 (cost=5,821.14..165,286.43 rows=65,143 width=28) (actual time=10.732..22.903 rows=11,998 loops=1)

  • Recheck Cond: ((c855_opp_close_date >= 20181001) AND (c855_opp_close_date <= 20181231) AND (start_stamp <= '1546329599999'::bigint) AND (end_stamp > '1546329599999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 4726
  • Heap Blocks: exact=7209
57. 9.610 9.610 ↑ 4.2 16,724 1

Bitmap Index Scan on ix_t60_opp_split_fact_closedate_stagename_timestamp (cost=0.00..5,804.85 rows=69,642 width=0) (actual time=9.610..9.610 rows=16,724 loops=1)

  • Index Cond: ((c855_opp_close_date >= 20181001) AND (c855_opp_close_date <= 20181231) AND (start_stamp <= '1546329599999'::bigint) AND (end_stamp > '1546329599999'::bigint))
58. 0.806 58.116 ↑ 1.1 6,046 1

Hash (cost=31,083.91..31,083.91 rows=6,582 width=4) (actual time=58.116..58.116 rows=6,046 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 277kB
59. 2.393 57.310 ↑ 1.1 6,046 1

HashAggregate (cost=31,018.09..31,083.91 rows=6,582 width=4) (actual time=56.372..57.310 rows=6,046 loops=1)

  • Group Key: t61_userrolehierarchy_7.sid
60. 12.702 54.917 ↑ 1.0 6,502 1

Hash Semi Join (cost=4,315.67..31,001.64 rows=6,582 width=4) (actual time=12.107..54.917 rows=6,502 loops=1)

  • Hash Cond: (t61_userrolehierarchy_7.c898_user_role_id = t42_userroledim_7.sid)
61. 31.825 41.641 ↑ 1.0 97,968 1

Bitmap Heap Scan on t61_userrolehierarchy t61_userrolehierarchy_7 (cost=3,138.89..29,419.97 rows=101,272 width=8) (actual time=11.517..41.641 rows=97,968 loops=1)

  • Recheck Cond: ((c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=10585
62. 9.816 9.816 ↓ 1.1 109,831 1

Bitmap Index Scan on idx_t61_userrolehierarchy_c899_ancestor_role_id_end_sta34629852 (cost=0.00..3,113.58 rows=101,272 width=0) (actual time=9.816..9.816 rows=109,831 loops=1)

  • Index Cond: ((c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
63. 0.063 0.574 ↑ 1.3 451 1

Hash (cost=1,169.49..1,169.49 rows=583 width=4) (actual time=0.574..0.574 rows=451 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
64. 0.441 0.511 ↑ 1.3 451 1

Bitmap Heap Scan on t42_userroledim t42_userroledim_7 (cost=41.12..1,169.49 rows=583 width=4) (actual time=0.093..0.511 rows=451 loops=1)

  • Recheck Cond: ((end_stamp = '32503680000000'::bigint) AND ((c572_portaltype IS NULL) OR (c572_portaltype = 'None'::text)))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
  • Rows Removed by Filter: 127
  • Heap Blocks: exact=172
65. 0.070 0.070 ↑ 1.0 578 1

Bitmap Index Scan on ix_t42_userroledim_sid_timestamp_partial (cost=0.00..40.97 rows=585 width=0) (actual time=0.070..0.070 rows=578 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
66. 98.187 829.162 ↑ 1.7 527,759 1

Hash (cost=306,150.09..306,150.09 rows=875,203 width=12) (actual time=829.162..829.162 rows=527,759 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 2433kB
67. 730.975 730.975 ↑ 1.7 527,759 1

Seq Scan on t60_opp_split_fact t60_opp_split_fact_3 (cost=0.00..306,150.09 rows=875,203 width=12) (actual time=0.139..730.975 rows=527,759 loops=1)

  • Filter: ((NOT deleted) AND (start_stamp <= '1538377200000'::bigint) AND (end_stamp > '1538377200000'::bigint))
  • Rows Removed by Filter: 1617423
68.          

SubPlan (forHash Left Join)

69. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.71..41.47 rows=1 width=0) (never executed)

70. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t61_userrolehierarchy_sid_c899_ancestor_role_id_end34629852 on t61_userrolehierarchy t61_userrolehierarchy_5 (cost=0.43..16.85 rows=2 width=4) (never executed)

  • Index Cond: ((sid = t60_opp_split_fact_3.c879_opportunity_split_ownerid) AND (c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
71. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_t42_userroledim_sid_timestamp_partial on t42_userroledim t42_userroledim_5 (cost=0.28..8.30 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t61_userrolehierarchy_5.c898_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
72. 12.832 54.404 ↑ 1.0 6,502 1

Hash Semi Join (cost=4,315.67..31,001.64 rows=6,582 width=4) (actual time=12.229..54.404 rows=6,502 loops=1)

  • Hash Cond: (t61_userrolehierarchy_6.c898_user_role_id = t42_userroledim_6.sid)
73. 31.057 40.981 ↑ 1.0 97,968 1

Bitmap Heap Scan on t61_userrolehierarchy t61_userrolehierarchy_6 (cost=3,138.89..29,419.97 rows=101,272 width=8) (actual time=11.624..40.981 rows=97,968 loops=1)

  • Recheck Cond: ((c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=10585
74. 9.924 9.924 ↓ 1.1 109,831 1

Bitmap Index Scan on idx_t61_userrolehierarchy_c899_ancestor_role_id_end_sta34629852 (cost=0.00..3,113.58 rows=101,272 width=0) (actual time=9.924..9.924 rows=109,831 loops=1)

  • Index Cond: ((c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
75. 0.050 0.591 ↑ 1.3 451 1

Hash (cost=1,169.49..1,169.49 rows=583 width=4) (actual time=0.591..0.591 rows=451 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
76. 0.472 0.541 ↑ 1.3 451 1

Bitmap Heap Scan on t42_userroledim t42_userroledim_6 (cost=41.12..1,169.49 rows=583 width=4) (actual time=0.109..0.541 rows=451 loops=1)

  • Recheck Cond: ((end_stamp = '32503680000000'::bigint) AND ((c572_portaltype IS NULL) OR (c572_portaltype = 'None'::text)))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
  • Rows Removed by Filter: 127
  • Heap Blocks: exact=172
77. 0.069 0.069 ↑ 1.0 578 1

Bitmap Index Scan on ix_t42_userroledim_sid_timestamp_partial (cost=0.00..40.97 rows=585 width=0) (actual time=0.069..0.069 rows=578 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
78. 0.003 0.015 ↓ 3.0 3 1

Hash (cost=1.11..1.11 rows=1 width=12) (actual time=0.015..0.015 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
79. 0.012 0.012 ↓ 3.0 3 1

Seq Scan on t59_claricurrencytypedim t59_claricurrencytypedim_2 (cost=0.00..1.11 rows=1 width=12) (actual time=0.010..0.012 rows=3 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 6
80.          

SubPlan (forHash Left Join)

81. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.71..41.47 rows=1 width=0) (never executed)

82. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t61_userrolehierarchy_sid_c899_ancestor_role_id_end34629852 on t61_userrolehierarchy t61_userrolehierarchy_3 (cost=0.43..16.85 rows=2 width=4) (never executed)

  • Index Cond: ((sid = t60_opp_split_fact_3.c879_opportunity_split_ownerid) AND (c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
83. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_t42_userroledim_sid_timestamp_partial on t42_userroledim t42_userroledim_3 (cost=0.28..8.30 rows=1 width=4) (never executed)

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

Hash Semi Join (cost=4,315.67..31,001.64 rows=6,582 width=4) (never executed)

  • Hash Cond: (t61_userrolehierarchy_4.c898_user_role_id = t42_userroledim_4.sid)
85. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t61_userrolehierarchy t61_userrolehierarchy_4 (cost=3,138.89..29,419.97 rows=101,272 width=8) (never executed)

  • Recheck Cond: ((c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
86. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_t61_userrolehierarchy_c899_ancestor_role_id_end_sta34629852 (cost=0.00..3,113.58 rows=101,272 width=0) (never executed)

  • Index Cond: ((c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
87. 0.000 0.000 ↓ 0.0 0

Hash (cost=1,169.49..1,169.49 rows=583 width=4) (never executed)

88. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t42_userroledim t42_userroledim_4 (cost=41.12..1,169.49 rows=583 width=4) (never executed)

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

Bitmap Index Scan on ix_t42_userroledim_sid_timestamp_partial (cost=0.00..40.97 rows=585 width=0) (never executed)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
90. 0.002 0.002 ↑ 1.0 2 1

CTE Scan on picklist_0 cte0_5 (cost=0.00..0.04 rows=2 width=4) (actual time=0.002..0.002 rows=2 loops=1)

91. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on picklist_1 cte0_6 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

92. 0.022 223.428 ↑ 31.2 25 1

Hash Join (cost=1.11..750.34 rows=780 width=120) (actual time=218.685..223.428 rows=25 loops=1)

  • Hash Cond: (cte0.c4 = t75_opportunityforecastcategorypicklistdim.sid)
93. 223.397 223.397 ↑ 1,248.7 25 1

CTE Scan on bucketac cte0 (cost=0.00..624.36 rows=31,218 width=92) (actual time=218.664..223.397 rows=25 loops=1)

94. 0.002 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: 1024 Batches: 1 Memory Usage: 9kB
95. 0.007 0.007 ↑ 1.0 5 1

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

96. 0.013 1,021.410 ↑ 88.5 17 1

Subquery Scan on *SELECT* 2 (cost=1.11..1,464.18 rows=1,504 width=120) (actual time=1,020.273..1,021.410 rows=17 loops=1)

97. 0.014 1,021.397 ↑ 88.5 17 1

Hash Join (cost=1.11..1,445.38 rows=1,504 width=144) (actual time=1,020.267..1,021.397 rows=17 loops=1)

  • Hash Cond: (cte0_1.c6 = t75_opportunityforecastcategorypicklistdim_1.sid)
98. 1,021.373 1,021.373 ↑ 3,539.9 17 1

CTE Scan on bucketec cte0_1 (cost=0.00..1,203.56 rows=60,178 width=116) (actual time=1,020.247..1,021.373 rows=17 loops=1)

99. 0.002 0.010 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
100. 0.008 0.008 ↑ 1.0 5 1

Seq Scan on t75_opportunityforecastcategorypicklistdim t75_opportunityforecastcategorypicklistdim_1 (cost=0.00..1.05 rows=5 width=36) (actual time=0.007..0.008 rows=5 loops=1)