explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DsbO

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 409.408 ↑ 6.8 38 1

Append (cost=1,356,150.97..1,356,405.51 rows=260 width=120) (actual time=218.689..409.408 rows=38 loops=1)

2.          

CTE picklist_0

3. 0.047 0.047 ↑ 1.0 2 1

Seq Scan on t77_opportunitystagenamepicklistdim (cost=0.00..4.44 rows=2 width=4) (actual time=0.024..0.047 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.019 0.019 ↑ 1.0 1 1

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

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

CTE picklist_4

7. 0.039 0.039 ↑ 1.0 3 1

Seq Scan on t77_opportunitystagenamepicklistdim t77_opportunitystagenamepicklistdim_2 (cost=0.00..4.58 rows=3 width=4) (actual time=0.016..0.039 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.673 223.236 ↑ 174.2 21 1

GroupAggregate (cost=242,196.93..394,106.98 rows=3,659 width=92) (actual time=218.655..223.236 rows=21 loops=1)

  • Group Key: t65_oppfact.c1033_opp_forecastcategory, (CASE WHEN (t65_oppfact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t65_oppfact_1.c927_opp_close_date < 20181001) OR (t65_oppfact_1.c927_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.686 219.563 ↓ 3.3 11,987 1

Sort (cost=242,196.87..242,206.01 rows=3,659 width=72) (actual time=218.620..219.563 rows=11,987 loops=1)

  • Sort Key: t65_oppfact.c1033_opp_forecastcategory, (CASE WHEN (t65_oppfact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t65_oppfact_1.c927_opp_close_date < 20181001) OR (t65_oppfact_1.c927_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: 1366kB
11. 8.263 213.877 ↓ 3.3 11,987 1

Hash Left Join (cost=32,861.90..241,980.30 rows=3,659 width=72) (actual time=124.428..213.877 rows=11,987 loops=1)

  • Hash Cond: (t65_oppfact_1.c1033_opp_forecastcategory = t75_opportunityforecastcategorypicklistdim_3.sid)
12. 3.084 151.122 ↓ 3.3 11,987 1

Hash Left Join (cost=32,860.72..90,128.53 rows=3,659 width=64) (actual time=68.450..151.122 rows=11,987 loops=1)

  • Hash Cond: (t65_oppfact_1.c917_opp_currency_code = t59_claricurrencytypedim_1.sid)
13. 5.617 148.028 ↓ 3.3 11,987 1

Nested Loop Left Join (cost=32,859.59..90,109.61 rows=3,659 width=60) (actual time=68.432..148.028 rows=11,987 loops=1)

14. 2.451 94.463 ↓ 3.3 11,987 1

Hash Left Join (cost=32,859.03..59,369.66 rows=3,659 width=28) (actual time=68.410..94.463 rows=11,987 loops=1)

  • Hash Cond: (t65_oppfact.c1033_opp_forecastcategory = t75_opportunityforecastcategorypicklistdim_2.sid)
15. 2.814 92.004 ↓ 3.3 11,987 1

Hash Left Join (cost=32,857.92..59,318.23 rows=3,659 width=24) (actual time=68.394..92.004 rows=11,987 loops=1)

  • Hash Cond: (t65_oppfact.c917_opp_currency_code = t59_claricurrencytypedim.sid)
16. 5.411 89.173 ↓ 3.3 11,987 1

Hash Join (cost=32,856.80..59,299.32 rows=3,659 width=20) (actual time=68.361..89.173 rows=11,987 loops=1)

  • Hash Cond: (t65_oppfact.c1008_opp_ownerid = t61_userrolehierarchy_2.sid)
17. 16.611 24.409 ↓ 3.3 11,987 1

Bitmap Heap Scan on t65_oppfact (cost=1,690.61..28,115.09 rows=3,659 width=24) (actual time=8.951..24.409 rows=11,987 loops=1)

  • Recheck Cond: ((c927_opp_close_date >= 20181001) AND (c927_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: 3357
  • Heap Blocks: exact=7156
18. 7.754 7.754 ↓ 2.1 15,344 1

Bitmap Index Scan on ix_t65_oppfact_closedate_stagename_timestamp (cost=0.00..1,689.63 rows=7,317 width=0) (actual time=7.754..7.754 rows=15,344 loops=1)

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

SubPlan (forBitmap Heap Scan)

20. 0.044 0.044 ↑ 1.0 3 1

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

21. 0.850 59.353 ↑ 1.1 6,046 1

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

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

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

  • Group Key: t61_userrolehierarchy_2.sid
23. 12.913 56.256 ↑ 1.0 6,502 1

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

  • Hash Cond: (t61_userrolehierarchy_2.c898_user_role_id = t42_userroledim_2.sid)
24. 32.715 42.747 ↑ 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.808..42.747 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. 10.032 10.032 ↓ 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=10.032..10.032 rows=109,831 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
27. 0.461 0.532 ↑ 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.098..0.532 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.071 0.071 ↑ 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.071..0.071 rows=578 loops=1)

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

Hash (cost=1.11..1.11 rows=1 width=12) (actual time=0.017..0.017 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.004 0.008 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.004 0.004 ↑ 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.004 rows=5 loops=1)

33. 47.948 47.948 ↑ 1.0 1 11,987

Index Scan using idx_t65_oppfact_sid_end_stamp_start_stamp on t65_oppfact t65_oppfact_1 (cost=0.56..8.39 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=11,987)

  • Index Cond: ((t65_oppfact.sid = sid) AND (t65_oppfact.sid = sid) AND (end_stamp > '1546329599999'::bigint) AND (start_stamp <= '1546329599999'::bigint))
  • Filter: (NOT deleted)
34. 0.003 0.010 ↓ 3.0 3 1

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

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

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

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 6
36. 0.004 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.001 0.001 ↑ 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.001 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 = t65_oppfact_1.c1008_opp_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. 12.832 54.416 ↑ 1.0 6,502 1

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

  • Hash Cond: (t61_userrolehierarchy_1.c898_user_role_id = t42_userroledim_1.sid)
43. 30.978 40.986 ↑ 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.705..40.986 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. 10.008 10.008 ↓ 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=10.008..10.008 rows=109,831 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
46. 0.453 0.529 ↑ 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.103..0.529 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.076 0.076 ↑ 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.076..0.076 rows=578 loops=1)

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

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

49. 0.020 0.020 ↑ 1.0 1 1

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

50.          

CTE bucketec

51. 0.914 186.070 ↑ 398.5 17 1

GroupAggregate (cost=680,803.76..962,029.41 rows=6,775 width=116) (actual time=185.078..186.070 rows=17 loops=1)

  • Group Key: (CASE WHEN (t65_oppfact_3.sid IS NULL) THEN 'newBucket'::text WHEN (t65_oppfact_3.c927_opp_close_date > 20181231) THEN 'pulledInBucket'::text WHEN (t65_oppfact_3.c927_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), t65_oppfact_2.c1033_opp_forecastcategory
52. 1.908 185.156 ↑ 1.9 3,478 1

Sort (cost=680,803.69..680,820.63 rows=6,775 width=88) (actual time=184.912..185.156 rows=3,478 loops=1)

  • Sort Key: (CASE WHEN (t65_oppfact_3.sid IS NULL) THEN 'newBucket'::text WHEN (t65_oppfact_3.c927_opp_close_date > 20181231) THEN 'pulledInBucket'::text WHEN (t65_oppfact_3.c927_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), t65_oppfact_2.c1033_opp_forecastcategory
  • Sort Method: quicksort Memory: 368kB
53. 1.846 183.248 ↑ 1.9 3,478 1

Hash Left Join (cost=32,859.28..680,372.60 rows=6,775 width=88) (actual time=122.916..183.248 rows=3,478 loops=1)

  • Hash Cond: (t65_oppfact_2.c917_opp_currency_code = t59_claricurrencytypedim_2.sid)
54. 1.078 181.378 ↑ 1.9 3,478 1

Hash Join (cost=32,858.09..399,299.13 rows=6,775 width=36) (actual time=122.845..181.378 rows=3,478 loops=1)

  • Hash Cond: (t65_oppfact_2.c1008_opp_ownerid = t61_userrolehierarchy_7.sid)
55. 14.418 122.686 ↑ 1.9 3,479 1

Nested Loop Left Join (cost=1,691.90..368,099.52 rows=6,775 width=40) (actual time=65.183..122.686 rows=3,479 loops=1)

  • Filter: ((t65_oppfact_3.sid IS NULL) OR (t65_oppfact_3.c927_opp_close_date < 20181001) OR (t65_oppfact_3.c927_opp_close_date > 20181231) OR (NOT (alternatives: SubPlan 14 or hashed SubPlan 15)))
  • Rows Removed by Filter: 7871
56. 11.523 18.967 ↓ 1.7 11,350 1

Bitmap Heap Scan on t65_oppfact t65_oppfact_2 (cost=1,691.34..26,573.35 rows=6,869 width=28) (actual time=8.430..18.967 rows=11,350 loops=1)

  • Recheck Cond: ((c927_opp_close_date >= 20181001) AND (c927_opp_close_date <= 20181231) AND (start_stamp <= '1546329599999'::bigint) AND (end_stamp > '1546329599999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=6587
57. 7.444 7.444 ↓ 1.7 11,353 1

Bitmap Index Scan on ix_t65_oppfact_closedate_stagename_timestamp (cost=0.00..1,689.63 rows=6,869 width=0) (actual time=7.444..7.444 rows=11,353 loops=1)

  • Index Cond: ((c927_opp_close_date >= 20181001) AND (c927_opp_close_date <= 20181231) AND (start_stamp <= '1546329599999'::bigint) AND (end_stamp > '1546329599999'::bigint))
58. 34.050 34.050 ↑ 1.0 1 11,350

Index Scan using idx_t65_oppfact_sid_end_stamp_start_stamp on t65_oppfact t65_oppfact_3 (cost=0.56..8.23 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=11,350)

  • Index Cond: ((t65_oppfact_2.sid = sid) AND (t65_oppfact_2.sid = sid) AND (end_stamp > '1538377200000'::bigint) AND (start_stamp <= '1538377200000'::bigint))
  • Filter: (NOT deleted)
59.          

SubPlan (forNested Loop Left Join)

60. 0.000 0.000 ↓ 0.0 0

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

61. 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 = t65_oppfact_3.c1008_opp_ownerid) AND (c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
62. 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))
63. 12.923 55.251 ↑ 1.0 6,502 1

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

  • Hash Cond: (t61_userrolehierarchy_6.c898_user_role_id = t42_userroledim_6.sid)
64. 31.701 41.739 ↑ 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.726..41.739 rows=97,968 loops=1)

  • Recheck Cond: ((c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=10585
65. 10.038 10.038 ↓ 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=10.038..10.038 rows=109,831 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
67. 0.468 0.537 ↑ 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.094..0.537 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
68. 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)
69. 0.815 57.614 ↑ 1.1 6,046 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 277kB
70. 2.250 56.799 ↑ 1.1 6,046 1

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

  • Group Key: t61_userrolehierarchy_7.sid
71. 12.679 54.549 ↑ 1.0 6,502 1

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

  • Hash Cond: (t61_userrolehierarchy_7.c898_user_role_id = t42_userroledim_7.sid)
72. 31.197 41.285 ↑ 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.786..41.285 rows=97,968 loops=1)

  • Recheck Cond: ((c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=10585
73. 10.088 10.088 ↓ 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=10.088..10.088 rows=109,831 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
75. 0.448 0.522 ↑ 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.104..0.522 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
76. 0.074 0.074 ↑ 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.074..0.074 rows=578 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
77. 0.005 0.022 ↓ 3.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
78. 0.017 0.017 ↓ 3.0 3 1

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

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

SubPlan (forHash Left Join)

80. 0.000 0.000 ↓ 0.0 0

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

81. 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 = t65_oppfact_3.c1008_opp_ownerid) AND (c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
82. 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))
83. 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)
84. 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))
85. 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))
86. 0.000 0.000 ↓ 0.0 0

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

87. 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))
88. 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)
89. 0.001 0.001 ↑ 1.0 2 1

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

90. 0.001 0.001 ↑ 1.0 1 1

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

91. 0.027 223.287 ↑ 4.3 21 1

Hash Join (cost=1.11..88.92 rows=91 width=120) (actual time=218.689..223.287 rows=21 loops=1)

  • Hash Cond: (cte0.c4 = t75_opportunityforecastcategorypicklistdim.sid)
92. 223.249 223.249 ↑ 174.2 21 1

CTE Scan on bucketac cte0 (cost=0.00..73.18 rows=3,659 width=92) (actual time=218.659..223.249 rows=21 loops=1)

93. 0.002 0.011 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
94. 0.009 0.009 ↑ 1.0 5 1

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

95. 0.012 186.117 ↑ 9.9 17 1

Subquery Scan on *SELECT* 2 (cost=1.11..165.82 rows=169 width=120) (actual time=185.110..186.117 rows=17 loops=1)

96. 0.014 186.105 ↑ 9.9 17 1

Hash Join (cost=1.11..163.71 rows=169 width=144) (actual time=185.105..186.105 rows=17 loops=1)

  • Hash Cond: (cte0_1.c6 = t75_opportunityforecastcategorypicklistdim_1.sid)
97. 186.079 186.079 ↑ 398.5 17 1

CTE Scan on bucketec cte0_1 (cost=0.00..135.50 rows=6,775 width=116) (actual time=185.080..186.079 rows=17 loops=1)

98. 0.005 0.012 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
99. 0.007 0.007 ↑ 1.0 5 1

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