explain.depesz.com

PostgreSQL's explain analyze made readable

Result: N7HN

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 103.197 ↑ 1,066.1 40 1

Append (cost=2,215,328.66..2,217,275.94 rows=42,643 width=120) (actual time=66.311..103.197 rows=40 loops=1)

2.          

CTE picklist_0

3. 0.259 0.259 ↑ 1.0 6 1

Seq Scan on t233_opportunitystagenamepicklistdim (cost=0.00..46.54 rows=6 width=4) (actual time=0.004..0.259 rows=6 loops=1)

  • Filter: (correlated_value = ANY ('{"06 - Won, Deploy & Expand","6 - Won, Deploy & Expand","Closed - Won","06 - Won. Deploy & Expand",Won,"06 - Won, Deploy & Expand"}'::text[]))
  • Rows Removed by Filter: 1225
4.          

CTE picklist_1

5. 0.282 0.282 ↑ 1.0 7 1

Seq Scan on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_1 (cost=0.00..48.08 rows=7 width=4) (actual time=0.004..0.282 rows=7 loops=1)

  • Filter: (correlated_value = ANY ('{"HPE Not Pursued",Lost,Close:Duplicate,"HP Not Pursued",Duplicate,Error,"Closed - Lost"}'::text[]))
  • Rows Removed by Filter: 1224
6.          

CTE picklist_4

7. 0.408 0.408 ↑ 1.0 13 1

Seq Scan on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_2 (cost=0.00..57.31 rows=13 width=4) (actual time=0.007..0.408 rows=13 loops=1)

  • Filter: (correlated_value = ANY ('{"06 - Won, Deploy & Expand",Lost,"6 - Won, Deploy & Expand",Close:Duplicate,"HP Not Pursued","06 - Won, Deploy & Expand",Error,"HPE Not Pursued","Closed - Won","06 - Won. Deploy & Expand",Won,Duplicate,"Closed - Lost"}'::text[]))
  • Rows Removed by Filter: 1218
8.          

CTE bucketac

9. 1.280 67.880 ↑ 939.6 19 1

GroupAggregate (cost=493,337.32..798,266.56 rows=17,853 width=92) (actual time=66.294..67.880 rows=19 loops=1)

  • Group Key: t237_oppfact.c844_opp_forecastcategory, (CASE WHEN (t237_oppfact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t237_oppfact_1.c836_opp_close_date < 20181101) OR (t237_oppfact_1.c836_opp_close_date > 20190131)) 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 (t234_opportunityforecastcategorypicklistdim_2.correlated_app_order = t234_opportunityforecastcategorypicklistdim_3.correlated_app_order) THEN 'unchanged'::text WHEN (t234_opportunityforecastcategorypicklistdim_2.correlated_app_order > t234_opportunityforecastcategorypicklistdim_3.correlated_app_order) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
10. 1.952 66.600 ↑ 4.5 4,010 1

Sort (cost=493,337.02..493,381.66 rows=17,853 width=72) (actual time=66.273..66.600 rows=4,010 loops=1)

  • Sort Key: t237_oppfact.c844_opp_forecastcategory, (CASE WHEN (t237_oppfact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t237_oppfact_1.c836_opp_close_date < 20181101) OR (t237_oppfact_1.c836_opp_close_date > 20190131)) 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 (t234_opportunityforecastcategorypicklistdim_2.correlated_app_order = t234_opportunityforecastcategorypicklistdim_3.correlated_app_order) THEN 'unchanged'::text WHEN (t234_opportunityforecastcategorypicklistdim_2.correlated_app_order > t234_opportunityforecastcategorypicklistdim_3.correlated_app_order) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
  • Sort Method: quicksort Memory: 628kB
11. 4.290 64.648 ↑ 4.5 4,010 1

Nested Loop Left Join (cost=17,885.69..492,076.25 rows=17,853 width=72) (actual time=5.586..64.648 rows=4,010 loops=1)

12. 2.615 49.929 ↑ 3.3 4,010 1

Nested Loop Left Join (cost=17,885.40..183,277.40 rows=13,121 width=60) (actual time=2.917..49.929 rows=4,010 loops=1)

13. 1.154 39.294 ↑ 2.4 4,010 1

Hash Left Join (cost=17,885.40..179,474.86 rows=9,643 width=60) (actual time=2.899..39.294 rows=4,010 loops=1)

  • Hash Cond: (t237_oppfact_1.c844_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim_3.sid)
14. 1.131 38.135 ↑ 2.4 4,010 1

Nested Loop Left Join (cost=17,865.72..179,322.59 rows=9,643 width=60) (actual time=2.882..38.135 rows=4,010 loops=1)

15. 0.853 16.954 ↑ 2.4 4,010 1

Hash Left Join (cost=17,865.16..102,395.84 rows=9,643 width=28) (actual time=2.861..16.954 rows=4,010 loops=1)

  • Hash Cond: (t237_oppfact.c844_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim_2.sid)
16. 1.061 16.092 ↑ 2.4 4,010 1

Nested Loop (cost=17,845.48..102,243.58 rows=9,643 width=24) (actual time=2.840..16.092 rows=4,010 loops=1)

17. 0.332 2.502 ↑ 2.5 737 1

HashAggregate (cost=17,844.62..17,863.13 rows=1,851 width=4) (actual time=2.382..2.502 rows=737 loops=1)

  • Group Key: t166_userrolehierarchy_2.sid
18. 0.640 2.170 ↑ 2.0 934 1

Nested Loop Semi Join (cost=60.05..17,839.97 rows=1,860 width=4) (actual time=0.159..2.170 rows=934 loops=1)

19. 0.472 0.596 ↑ 2.0 934 1

Bitmap Heap Scan on t166_userrolehierarchy t166_userrolehierarchy_2 (cost=59.63..6,838.32 rows=1,860 width=8) (actual time=0.146..0.596 rows=934 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=130
20. 0.124 0.124 ↑ 2.0 944 1

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..59.16 rows=1,860 width=0) (actual time=0.124..0.124 rows=944 loops=1)

  • Index Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
21. 0.934 0.934 ↑ 1.0 1 934

Index Scan using ix_t67_userroledim_sid_timestamp_partial on t67_userroledim t67_userroledim_2 (cost=0.42..5.90 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=934)

  • Index Cond: ((sid = t166_userrolehierarchy_2.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
22. 12.117 12.529 ↑ 1.0 5 737

Index Scan using ix_t237_oppfact_owner_closedate_timestamp on t237_oppfact (cost=0.86..45.54 rows=5 width=28) (actual time=0.008..0.017 rows=5 loops=737)

  • Index Cond: ((c832_opp_ownerid = t166_userrolehierarchy_2.sid) AND (c836_opp_close_date >= 20181101) AND (c836_opp_close_date <= 20190131) AND (start_stamp <= '1541055600000'::bigint) AND (end_stamp > '1541055600000'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 8)))
  • Rows Removed by Filter: 1
23.          

SubPlan (forIndex Scan)

24. 0.412 0.412 ↑ 1.0 13 1

CTE Scan on picklist_4 cte0_4 (cost=0.00..0.26 rows=13 width=4) (actual time=0.009..0.412 rows=13 loops=1)

25. 0.005 0.009 ↑ 86.0 5 1

Hash (cost=14.30..14.30 rows=430 width=8) (actual time=0.009..0.009 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.004 0.004 ↑ 86.0 5 1

Seq Scan on t234_opportunityforecastcategorypicklistdim t234_opportunityforecastcategorypicklistdim_2 (cost=0.00..14.30 rows=430 width=8) (actual time=0.003..0.004 rows=5 loops=1)

27. 20.050 20.050 ↑ 1.0 1 4,010

Index Scan using ix_t237_oppfact_sid_timestamp on t237_oppfact t237_oppfact_1 (cost=0.56..7.97 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=4,010)

  • Index Cond: ((t237_oppfact.sid = sid) AND (t237_oppfact.sid = sid) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
  • Filter: (NOT deleted)
28. 0.002 0.005 ↑ 86.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.003 0.003 ↑ 86.0 5 1

Seq Scan on t234_opportunityforecastcategorypicklistdim t234_opportunityforecastcategorypicklistdim_3 (cost=0.00..14.30 rows=430 width=8) (actual time=0.002..0.003 rows=5 loops=1)

30. 0.000 8.020 ↑ 2.0 1 4,010

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

31. 0.000 0.000 ↓ 0.0 0 4,010

Seq Scan on clari_conversion_rate cur177_t0_r0 (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=4,010)

  • Filter: ((to_iso_code = 'USD'::text) AND (t237_oppfact.c845_opp_currency_code = sid) AND (t237_oppfact.c836_opp_close_date = rate_date))
32. 8.020 8.020 ↑ 1.0 1 4,010

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur177_t0_r0_1 (cost=0.29..0.37 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=4,010)

  • Index Cond: ((t237_oppfact.c836_opp_close_date = rate_date) AND (t237_oppfact.c845_opp_currency_code = sid))
  • Filter: (to_iso_code = 'USD'::text)
33. 0.000 8.020 ↑ 2.0 1 4,010

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

34. 0.000 0.000 ↓ 0.0 0 4,010

Seq Scan on clari_conversion_rate cur177_t1_r1 (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=4,010)

  • Filter: ((to_iso_code = 'USD'::text) AND (t237_oppfact_1.c845_opp_currency_code = sid) AND (t237_oppfact_1.c836_opp_close_date = rate_date))
35. 8.020 8.020 ↑ 1.0 1 4,010

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur177_t1_r1_1 (cost=0.29..0.32 rows=1 width=16) (actual time=0.001..0.002 rows=1 loops=4,010)

  • Index Cond: ((t237_oppfact_1.c836_opp_close_date = rate_date) AND (t237_oppfact_1.c845_opp_currency_code = sid))
  • Filter: (to_iso_code = 'USD'::text)
36.          

SubPlan (forNested Loop Left Join)

37. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.98..17.04 rows=1 width=0) (never executed)

38. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_t166_userrolehierarchy_sid_ancestor_end_start_timestamp on t166_userrolehierarchy (cost=0.56..8.58 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t237_oppfact_1.c832_opp_ownerid) AND (c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
39. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_t67_userroledim_sid_end_start_timestamp_partial on t67_userroledim (cost=0.42..8.44 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t166_userrolehierarchy.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
40. 0.643 1.860 ↑ 2.0 934 1

Nested Loop Semi Join (cost=60.05..17,839.97 rows=1,860 width=4) (actual time=0.116..1.860 rows=934 loops=1)

41. 0.185 0.283 ↑ 2.0 934 1

Bitmap Heap Scan on t166_userrolehierarchy t166_userrolehierarchy_1 (cost=59.63..6,838.32 rows=1,860 width=8) (actual time=0.110..0.283 rows=934 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=130
42. 0.098 0.098 ↑ 2.0 944 1

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..59.16 rows=1,860 width=0) (actual time=0.098..0.098 rows=944 loops=1)

  • Index Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
43. 0.934 0.934 ↑ 1.0 1 934

Index Scan using ix_t67_userroledim_sid_timestamp_partial on t67_userroledim t67_userroledim_1 (cost=0.42..5.90 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=934)

  • Index Cond: ((sid = t166_userrolehierarchy_1.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
44. 0.264 0.264 ↑ 1.0 6 1

CTE Scan on picklist_0 cte0_2 (cost=0.00..0.12 rows=6 width=4) (actual time=0.008..0.264 rows=6 loops=1)

45. 0.285 0.285 ↑ 1.0 7 1

CTE Scan on picklist_1 cte0_3 (cost=0.00..0.14 rows=7 width=4) (actual time=0.004..0.285 rows=7 loops=1)

46.          

CTE bucketec

47. 0.127 35.243 ↑ 1,180.5 21 1

GroupAggregate (cost=993,601.24..1,416,890.49 rows=24,790 width=116) (actual time=35.092..35.243 rows=21 loops=1)

  • Group Key: (CASE WHEN (t237_oppfact_3.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 10)) THEN CASE WHEN (t237_oppfact_3.c836_opp_close_date > 20190131) THEN 'pulledInBucket'::text WHEN (t237_oppfact_3.c836_opp_close_date < 20181101) THEN 'pushedInBucket'::text WHEN (alternatives: SubPlan 11 or hashed SubPlan 12) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END ELSE 'otherBucket'::text END), (CASE WHEN (hashed SubPlan 13) THEN 'won'::text WHEN (hashed SubPlan 14) THEN 'lost'::text ELSE 'newOpen'::text END), t237_oppfact_2.c844_opp_forecastcategory
48. 0.296 35.116 ↑ 61.7 402 1

Sort (cost=993,600.66..993,662.63 rows=24,790 width=88) (actual time=35.074..35.116 rows=402 loops=1)

  • Sort Key: (CASE WHEN (t237_oppfact_3.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 10)) THEN CASE WHEN (t237_oppfact_3.c836_opp_close_date > 20190131) THEN 'pulledInBucket'::text WHEN (t237_oppfact_3.c836_opp_close_date < 20181101) THEN 'pushedInBucket'::text WHEN (alternatives: SubPlan 11 or hashed SubPlan 12) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END ELSE 'otherBucket'::text END), (CASE WHEN (hashed SubPlan 13) THEN 'won'::text WHEN (hashed SubPlan 14) THEN 'lost'::text ELSE 'newOpen'::text END), t237_oppfact_2.c844_opp_forecastcategory
  • Sort Method: quicksort Memory: 58kB
49. 0.546 34.820 ↑ 61.7 402 1

Nested Loop Left Join (cost=17,846.34..991,791.30 rows=24,790 width=88) (actual time=4.325..34.820 rows=402 loops=1)

50. 4.707 31.234 ↑ 45.3 402 1

Nested Loop Left Join (cost=17,845.75..562,524.30 rows=18,220 width=44) (actual time=4.284..31.234 rows=402 loops=1)

  • Filter: ((t237_oppfact_3.sid IS NULL) OR (t237_oppfact_3.c836_opp_close_date < 20181101) OR (t237_oppfact_3.c836_opp_close_date > 20190131) OR (NOT (alternatives: SubPlan 15 or hashed SubPlan 16)))
  • Rows Removed by Filter: 4431
51. 1.244 10.177 ↑ 3.9 4,833 1

Nested Loop (cost=17,845.19..101,748.44 rows=18,775 width=28) (actual time=2.174..10.177 rows=4,833 loops=1)

52. 0.354 2.300 ↑ 2.5 737 1

HashAggregate (cost=17,844.62..17,863.13 rows=1,851 width=4) (actual time=2.158..2.300 rows=737 loops=1)

  • Group Key: t166_userrolehierarchy_7.sid
53. 0.721 1.946 ↑ 2.0 934 1

Nested Loop Semi Join (cost=60.05..17,839.97 rows=1,860 width=4) (actual time=0.107..1.946 rows=934 loops=1)

54. 0.207 0.291 ↑ 2.0 934 1

Bitmap Heap Scan on t166_userrolehierarchy t166_userrolehierarchy_7 (cost=59.63..6,838.32 rows=1,860 width=8) (actual time=0.098..0.291 rows=934 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=130
55. 0.084 0.084 ↑ 2.0 944 1

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..59.16 rows=1,860 width=0) (actual time=0.084..0.084 rows=944 loops=1)

  • Index Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
56. 0.934 0.934 ↑ 1.0 1 934

Index Scan using ix_t67_userroledim_sid_timestamp_partial on t67_userroledim t67_userroledim_7 (cost=0.42..5.90 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=934)

  • Index Cond: ((sid = t166_userrolehierarchy_7.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
57. 6.633 6.633 ↑ 1.4 7 737

Index Scan using ix_t237_oppfact_owner_closedate_timestamp on t237_oppfact t237_oppfact_2 (cost=0.56..45.22 rows=10 width=32) (actual time=0.004..0.009 rows=7 loops=737)

  • Index Cond: ((c832_opp_ownerid = t166_userrolehierarchy_7.sid) AND (c836_opp_close_date >= 20181101) AND (c836_opp_close_date <= 20190131) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
58. 14.499 14.499 ↑ 1.0 1 4,833

Index Scan using ix_t237_oppfact_sid_timestamp on t237_oppfact t237_oppfact_3 (cost=0.56..7.49 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=4,833)

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

SubPlan (forNested Loop Left Join)

60. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.98..17.04 rows=1 width=0) (never executed)

61. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_t166_userrolehierarchy_sid_ancestor_end_start_timestamp on t166_userrolehierarchy t166_userrolehierarchy_5 (cost=0.56..8.58 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t237_oppfact_3.c832_opp_ownerid) AND (c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
62. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_t67_userroledim_sid_end_start_timestamp_partial on t67_userroledim t67_userroledim_5 (cost=0.42..8.44 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t166_userrolehierarchy_5.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
63. 0.656 1.851 ↑ 2.0 934 1

Nested Loop Semi Join (cost=60.05..17,839.97 rows=1,860 width=4) (actual time=0.099..1.851 rows=934 loops=1)

64. 0.180 0.261 ↑ 2.0 934 1

Bitmap Heap Scan on t166_userrolehierarchy t166_userrolehierarchy_6 (cost=59.63..6,838.32 rows=1,860 width=8) (actual time=0.092..0.261 rows=934 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=130
65. 0.081 0.081 ↑ 2.0 944 1

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..59.16 rows=1,860 width=0) (actual time=0.081..0.081 rows=944 loops=1)

  • Index Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
66. 0.934 0.934 ↑ 1.0 1 934

Index Scan using ix_t67_userroledim_sid_timestamp_partial on t67_userroledim t67_userroledim_6 (cost=0.42..5.90 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=934)

  • Index Cond: ((sid = t166_userrolehierarchy_6.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
67. 0.402 1.206 ↑ 2.0 1 402

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

68. 0.000 0.000 ↓ 0.0 0 402

Seq Scan on clari_conversion_rate cur177_t0_r0_2 (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=402)

  • Filter: ((to_iso_code = 'USD'::text) AND (t237_oppfact_2.c845_opp_currency_code = sid) AND (t237_oppfact_2.c836_opp_close_date = rate_date))
69. 0.804 0.804 ↑ 1.0 1 402

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur177_t0_r0_3 (cost=0.29..0.35 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=402)

  • Index Cond: ((t237_oppfact_2.c836_opp_close_date = rate_date) AND (t237_oppfact_2.c845_opp_currency_code = sid))
  • Filter: (to_iso_code = 'USD'::text)
70.          

SubPlan (forNested Loop Left Join)

71. 0.001 0.001 ↑ 1.0 13 1

CTE Scan on picklist_4 cte0_5 (cost=0.00..0.26 rows=13 width=4) (actual time=0.001..0.001 rows=13 loops=1)

72. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.98..17.04 rows=1 width=0) (never executed)

73. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_t166_userrolehierarchy_sid_ancestor_end_start_timestamp on t166_userrolehierarchy t166_userrolehierarchy_3 (cost=0.56..8.58 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t237_oppfact_3.c832_opp_ownerid) AND (c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
74. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_t67_userroledim_sid_end_start_timestamp_partial on t67_userroledim t67_userroledim_3 (cost=0.42..8.44 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t166_userrolehierarchy_3.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
75. 0.628 1.830 ↑ 2.0 934 1

Nested Loop Semi Join (cost=60.05..17,839.97 rows=1,860 width=4) (actual time=0.098..1.830 rows=934 loops=1)

76. 0.190 0.268 ↑ 2.0 934 1

Bitmap Heap Scan on t166_userrolehierarchy t166_userrolehierarchy_4 (cost=59.63..6,838.32 rows=1,860 width=8) (actual time=0.090..0.268 rows=934 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=130
77. 0.078 0.078 ↑ 2.0 944 1

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..59.16 rows=1,860 width=0) (actual time=0.078..0.078 rows=944 loops=1)

  • Index Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
78. 0.934 0.934 ↑ 1.0 1 934

Index Scan using ix_t67_userroledim_sid_timestamp_partial on t67_userroledim t67_userroledim_4 (cost=0.42..5.90 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=934)

  • Index Cond: ((sid = t166_userrolehierarchy_4.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
79. 0.001 0.001 ↑ 1.0 6 1

CTE Scan on picklist_0 cte0_6 (cost=0.00..0.12 rows=6 width=4) (actual time=0.000..0.001 rows=6 loops=1)

80. 0.002 0.002 ↑ 1.0 7 1

CTE Scan on picklist_1 cte0_7 (cost=0.00..0.14 rows=7 width=4) (actual time=0.000..0.002 rows=7 loops=1)

81. 0.012 67.906 ↑ 939.6 19 1

Hash Join (cost=19.68..622.21 rows=17,853 width=120) (actual time=66.311..67.906 rows=19 loops=1)

  • Hash Cond: (cte0.c4 = t234_opportunityforecastcategorypicklistdim.sid)
82. 67.888 67.888 ↑ 939.6 19 1

CTE Scan on bucketac cte0 (cost=0.00..357.06 rows=17,853 width=92) (actual time=66.297..67.888 rows=19 loops=1)

83. 0.002 0.006 ↑ 86.0 5 1

Hash (cost=14.30..14.30 rows=430 width=36) (actual time=0.006..0.006 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
84. 0.004 0.004 ↑ 86.0 5 1

Seq Scan on t234_opportunityforecastcategorypicklistdim (cost=0.00..14.30 rows=430 width=36) (actual time=0.003..0.004 rows=5 loops=1)

85. 0.011 35.287 ↑ 1,180.5 21 1

Subquery Scan on *SELECT* 2 (cost=19.68..1,166.21 rows=24,790 width=120) (actual time=35.117..35.287 rows=21 loops=1)

86. 0.017 35.276 ↑ 1,180.5 21 1

Hash Join (cost=19.68..856.34 rows=24,790 width=144) (actual time=35.112..35.276 rows=21 loops=1)

  • Hash Cond: (cte0_1.c6 = t234_opportunityforecastcategorypicklistdim_1.sid)
87. 35.252 35.252 ↑ 1,180.5 21 1

CTE Scan on bucketec cte0_1 (cost=0.00..495.80 rows=24,790 width=116) (actual time=35.093..35.252 rows=21 loops=1)

88. 0.002 0.007 ↑ 86.0 5 1

Hash (cost=14.30..14.30 rows=430 width=36) (actual time=0.007..0.007 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
89. 0.005 0.005 ↑ 86.0 5 1

Seq Scan on t234_opportunityforecastcategorypicklistdim t234_opportunityforecastcategorypicklistdim_1 (cost=0.00..14.30 rows=430 width=36) (actual time=0.003..0.005 rows=5 loops=1)