explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1GhK

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 8,211.803 ↑ 519.1 44 1

Append (cost=7,766,580.99..7,788,738.98 rows=22,840 width=120) (actual time=4,344.970..8,211.803 rows=44 loops=1)

2.          

CTE picklist_0

3. 0.344 0.344 ↑ 1.0 6 1

Seq Scan on t35_opportunitystagenamepicklistdim (cost=0.00..70.54 rows=6 width=4) (actual time=0.009..0.344 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.275 0.275 ↑ 1.0 7 1

Seq Scan on t35_opportunitystagenamepicklistdim t35_opportunitystagenamepicklistdim_1 (cost=0.00..72.08 rows=7 width=4) (actual time=0.004..0.275 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.437 0.437 ↑ 1.0 13 1

Seq Scan on t35_opportunitystagenamepicklistdim t35_opportunitystagenamepicklistdim_2 (cost=0.00..81.31 rows=13 width=4) (actual time=0.013..0.437 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 ownercte

9. 16.409 920.897 ↑ 3.0 8,167 1

HashAggregate (cost=430,107.40..430,351.77 rows=24,437 width=4) (actual time=919.797..920.897 rows=8,167 loops=1)

  • Group Key: distinctclosedateowner.ownerid
10. 59.100 904.488 ↑ 1.6 93,387 1

Hash Join (cost=396,409.94..429,733.02 rows=149,749 width=4) (actual time=783.260..904.488 rows=93,387 loops=1)

  • Hash Cond: (distinctclosedateowner.ownerid = t166_userrolehierarchy.sid)
11. 65.791 85.216 ↓ 1.3 196,760 1

Bitmap Heap Scan on distinctclosedateowner (cost=3,179.36..32,515.59 rows=149,749 width=4) (actual time=22.585..85.216 rows=196,760 loops=1)

  • Recheck Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
  • Heap Blocks: exact=17913
12. 19.425 19.425 ↓ 1.3 196,760 1

Bitmap Index Scan on ix_closedate_distinctclosedateowner (cost=0.00..3,141.92 rows=149,749 width=0) (actual time=19.425..19.425 rows=196,760 loops=1)

  • Index Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
13. 16.592 760.172 ↑ 1.6 120,705 1

Hash (cost=390,022.34..390,022.34 rows=195,539 width=4) (actual time=760.172..760.172 rows=120,705 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2089kB
14. 15.195 743.580 ↑ 1.6 120,705 1

Unique (cost=383,614.07..390,022.34 rows=195,539 width=4) (actual time=713.199..743.580 rows=120,705 loops=1)

15. 70.088 728.385 ↑ 9.9 129,585 1

Sort (cost=383,614.07..386,818.21 rows=1,281,655 width=4) (actual time=713.197..728.385 rows=129,585 loops=1)

  • Sort Key: t166_userrolehierarchy.sid
  • Sort Method: external merge Disk: 1768kB
16. 198.512 658.297 ↑ 9.9 129,585 1

Hash Join (cost=59,587.89..236,068.38 rows=1,281,655 width=4) (actual time=151.300..658.297 rows=129,585 loops=1)

  • Hash Cond: (t166_userrolehierarchy.c662_user_role_id = t67_userroledim.sid)
17. 316.520 447.656 ↓ 1.0 1,321,404 1

Bitmap Heap Scan on t166_userrolehierarchy (cost=37,190.07..194,345.90 rows=1,281,655 width=8) (actual time=138.479..447.656 rows=1,321,404 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=39203
18. 131.136 131.136 ↓ 1.0 1,322,634 1

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..36,869.66 rows=1,281,655 width=0) (actual time=131.136..131.136 rows=1,322,634 loops=1)

  • Index Cond: ((c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
19. 1.410 12.129 ↑ 9.3 8,370 1

Hash (cost=21,117.18..21,117.18 rows=78,051 width=4) (actual time=12.129..12.129 rows=8,370 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 1177kB
20. 6.246 10.719 ↑ 9.3 8,370 1

Bitmap Heap Scan on t67_userroledim (cost=4,261.99..21,117.18 rows=78,051 width=4) (actual time=4.924..10.719 rows=8,370 loops=1)

  • Recheck Cond: ((end_stamp = '32503680000000'::bigint) AND ((c377_portaltype IS NULL) OR (c377_portaltype = 'None'::text)))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
  • Rows Removed by Filter: 5524
  • Heap Blocks: exact=2139
21. 4.473 4.473 ↑ 5.7 13,915 1

Bitmap Index Scan on ix_t67_userroledim_sid_end_start_timestamp_partial (cost=0.00..4,242.48 rows=78,925 width=0) (actual time=4.473..4.473 rows=13,915 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
22.          

CTE closedatecte

23. 0.026 0.026 ↑ 1.0 12 1

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

24.          

CTE bucketac

25. 136.902 4,600.595 ↑ 46,674.4 19 1

GroupAggregate (cost=4,224,412.72..4,266,536.38 rows=886,814 width=92) (actual time=4,344.941..4,600.595 rows=19 loops=1)

  • Group Key: t21_opp_line_item_fact.c320_opp_forecastcategory, (CASE WHEN (t21_opp_line_item_fact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t21_opp_line_item_fact_1.c270_opp_close_date < 20181101) OR (t21_opp_line_item_fact_1.c270_opp_close_date > 20190131)) THEN 'slipped'::text WHEN (hashed SubPlan 6) THEN CASE WHEN (hashed SubPlan 7) THEN 'won'::text WHEN (hashed SubPlan 8) THEN 'lost'::text ELSE CASE WHEN (t34_opportunityforecastcategorypicklistdim_2.correlated_app_order = t34_opportunityforecastcategorypicklistdim_3.correlated_app_order) THEN 'unchanged'::text WHEN (t34_opportunityforecastcategorypicklistdim_2.correlated_app_order > t34_opportunityforecastcategorypicklistdim_3.correlated_app_order) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
26. 323.914 4,463.693 ↑ 2.1 418,251 1

Sort (cost=4,223,862.59..4,226,079.63 rows=886,814 width=72) (actual time=4,344.567..4,463.693 rows=418,251 loops=1)

  • Sort Key: t21_opp_line_item_fact.c320_opp_forecastcategory, (CASE WHEN (t21_opp_line_item_fact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t21_opp_line_item_fact_1.c270_opp_close_date < 20181101) OR (t21_opp_line_item_fact_1.c270_opp_close_date > 20190131)) THEN 'slipped'::text WHEN (hashed SubPlan 6) THEN CASE WHEN (hashed SubPlan 7) THEN 'won'::text WHEN (hashed SubPlan 8) THEN 'lost'::text ELSE CASE WHEN (t34_opportunityforecastcategorypicklistdim_2.correlated_app_order = t34_opportunityforecastcategorypicklistdim_3.correlated_app_order) THEN 'unchanged'::text WHEN (t34_opportunityforecastcategorypicklistdim_2.correlated_app_order > t34_opportunityforecastcategorypicklistdim_3.correlated_app_order) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
  • Sort Method: external merge Disk: 26576kB
27. 361.139 4,139.779 ↑ 2.1 418,251 1

Hash Left Join (cost=3,827,943.84..4,063,502.03 rows=886,814 width=72) (actual time=3,429.674..4,139.779 rows=418,251 loops=1)

  • Hash Cond: ((t21_opp_line_item_fact_1.c323_opp_currency_code = cur177_t1_r1.sid) AND (t21_opp_line_item_fact_1.c270_opp_close_date = cur177_t1_r1.rate_date))
28. 91.044 3,732.167 ↑ 1.8 418,251 1

Hash Left Join (cost=3,823,183.94..3,855,604.12 rows=753,775 width=60) (actual time=3,380.934..3,732.167 rows=418,251 loops=1)

  • Hash Cond: (t21_opp_line_item_fact.c320_opp_forecastcategory = t34_opportunityforecastcategorypicklistdim_2.sid)
29. 95.196 3,641.109 ↑ 1.8 418,251 1

Merge Right Join (cost=3,823,182.83..3,845,592.87 rows=753,775 width=56) (actual time=3,380.902..3,641.109 rows=418,251 loops=1)

  • Merge Cond: ((cur177_t0_r0.rate_date = t21_opp_line_item_fact.c270_opp_close_date) AND (cur177_t0_r0.sid = t21_opp_line_item_fact.c323_opp_currency_code))
30. 4.849 46.422 ↑ 1.4 71,814 1

Merge Append (cost=0.31..8,226.02 rows=100,865 width=16) (actual time=0.019..46.422 rows=71,814 loops=1)

  • Sort Key: cur177_t0_r0.rate_date, cur177_t0_r0.sid
31. 0.004 0.005 ↓ 0.0 0 1

Sort (cost=0.01..0.02 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=1)

  • Sort Key: cur177_t0_r0.rate_date, cur177_t0_r0.sid
  • Sort Method: quicksort Memory: 25kB
32. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on clari_conversion_rate cur177_t0_r0 (cost=0.00..0.00 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
33. 41.568 41.568 ↑ 1.4 71,814 1

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur177_t0_r0_1 (cost=0.29..6,965.19 rows=100,864 width=16) (actual time=0.013..41.568 rows=71,814 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
34. 57.889 3,499.491 ↑ 1.5 418,251 1

Materialize (cost=3,822,069.72..3,825,273.19 rows=640,694 width=56) (actual time=3,332.886..3,499.491 rows=418,251 loops=1)

35. 339.866 3,441.602 ↑ 1.5 418,251 1

Sort (cost=3,822,069.72..3,823,671.46 rows=640,694 width=56) (actual time=3,332.883..3,441.602 rows=418,251 loops=1)

  • Sort Key: t21_opp_line_item_fact.c270_opp_close_date, t21_opp_line_item_fact.c323_opp_currency_code
  • Sort Method: external merge Disk: 30200kB
36. 114.639 3,101.736 ↑ 1.5 418,251 1

Hash Left Join (cost=552.36..3,738,377.61 rows=640,694 width=56) (actual time=924.843..3,101.736 rows=418,251 loops=1)

  • Hash Cond: (t21_opp_line_item_fact_1.c320_opp_forecastcategory = t34_opportunityforecastcategorypicklistdim_3.sid)
37. 201.060 2,987.089 ↑ 1.5 418,251 1

Nested Loop Left Join (cost=551.25..3,729,868.08 rows=640,694 width=56) (actual time=924.824..2,987.089 rows=418,251 loops=1)

38. 49.329 1,531.276 ↑ 1.5 418,251 1

Nested Loop (cost=550.69..218,681.69 rows=640,694 width=24) (actual time=924.809..1,531.276 rows=418,251 loops=1)

39. 4.150 926.591 ↓ 40.8 8,167 1

HashAggregate (cost=549.83..551.83 rows=200 width=4) (actual time=924.316..926.591 rows=8,167 loops=1)

  • Group Key: ownercte_1.c1
40. 922.441 922.441 ↑ 3.0 8,167 1

CTE Scan on ownercte ownercte_1 (cost=0.00..488.74 rows=24,437 width=4) (actual time=919.799..922.441 rows=8,167 loops=1)

41. 554.912 555.356 ↑ 2.6 51 8,167

Index Scan using ix_t21_opp_line_item_fact_owner_closedate_timestamp on t21_opp_line_item_fact (cost=0.85..1,089.31 rows=134 width=28) (actual time=0.013..0.068 rows=51 loops=8,167)

  • Index Cond: ((c268_opp_ownerid = ownercte_1.c1) AND (c270_opp_close_date >= 20181101) AND (c270_opp_close_date <= 20190131) AND (start_stamp <= '1541055600000'::bigint) AND (end_stamp > '1541055600000'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 9)))
  • Rows Removed by Filter: 23
42.          

SubPlan (forIndex Scan)

43. 0.444 0.444 ↑ 1.0 13 1

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

44. 1,254.753 1,254.753 ↑ 1.0 1 418,251

Index Scan using ix_t21_opp_line_item_fact_sid_timestamp on t21_opp_line_item_fact t21_opp_line_item_fact_1 (cost=0.56..5.47 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=418,251)

  • Index Cond: ((t21_opp_line_item_fact.sid = sid) AND (t21_opp_line_item_fact.sid = sid) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
  • Filter: (NOT deleted)
45. 0.002 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
46. 0.006 0.006 ↑ 1.0 5 1

Seq Scan on t34_opportunityforecastcategorypicklistdim t34_opportunityforecastcategorypicklistdim_3 (cost=0.00..1.05 rows=5 width=8) (actual time=0.004..0.006 rows=5 loops=1)

47. 0.006 0.014 ↑ 1.0 5 1

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

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

Seq Scan on t34_opportunityforecastcategorypicklistdim t34_opportunityforecastcategorypicklistdim_2 (cost=0.00..1.05 rows=5 width=8) (actual time=0.006..0.008 rows=5 loops=1)

49. 18.711 45.235 ↑ 1.0 100,855 1

Hash (cost=2,203.80..2,203.80 rows=100,865 width=16) (actual time=45.235..45.235 rows=100,855 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3391kB
50. 6.119 26.524 ↑ 1.0 100,855 1

Append (cost=0.00..2,203.80 rows=100,865 width=16) (actual time=0.053..26.524 rows=100,855 loops=1)

51. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on clari_conversion_rate cur177_t1_r1 (cost=0.00..0.00 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
52. 20.404 20.404 ↑ 1.0 100,855 1

Seq Scan on usd_clari_conversion_rate cur177_t1_r1_1 (cost=0.00..2,203.80 rows=100,864 width=16) (actual time=0.050..20.404 rows=100,855 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
53.          

SubPlan (forHash Left Join)

54. 0.614 0.614 ↑ 3.0 8,167 1

CTE Scan on ownercte (cost=0.00..488.74 rows=24,437 width=4) (actual time=0.002..0.614 rows=8,167 loops=1)

55. 0.348 0.348 ↑ 1.0 6 1

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

56. 0.276 0.276 ↑ 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.276 rows=7 loops=1)

57.          

CTE bucketec

58. 21.007 3,611.080 ↑ 1,072.1 25 1

GroupAggregate (cost=3,063,168.94..3,069,467.64 rows=26,803 width=116) (actual time=3,575.867..3,611.080 rows=25 loops=1)

  • Group Key: (CASE WHEN (t21_opp_line_item_fact_3.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 11)) THEN CASE WHEN (t21_opp_line_item_fact_3.c270_opp_close_date > 20190131) THEN 'pulledInBucket'::text WHEN (t21_opp_line_item_fact_3.c270_opp_close_date < 20181101) THEN 'pushedInBucket'::text WHEN (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), t21_opp_line_item_fact_2.c320_opp_forecastcategory
59. 164.069 3,590.073 ↑ 4.1 78,264 1

Sort (cost=3,062,618.52..3,063,422.61 rows=321,636 width=88) (actual time=3,575.850..3,590.073 rows=78,264 loops=1)

  • Sort Key: (CASE WHEN (t21_opp_line_item_fact_3.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 11)) THEN CASE WHEN (t21_opp_line_item_fact_3.c270_opp_close_date > 20190131) THEN 'pulledInBucket'::text WHEN (t21_opp_line_item_fact_3.c270_opp_close_date < 20181101) THEN 'pushedInBucket'::text WHEN (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), t21_opp_line_item_fact_2.c320_opp_forecastcategory
  • Sort Method: external merge Disk: 4304kB
60. 41.577 3,426.004 ↑ 4.1 78,264 1

Nested Loop (cost=1,651.21..3,017,803.76 rows=321,636 width=88) (actual time=2.444..3,426.004 rows=78,264 loops=1)

61. 0.052 0.052 ↑ 1.0 12 1

CTE Scan on closedatecte (cost=0.00..0.24 rows=12 width=8) (actual time=0.005..0.052 rows=12 loops=1)

62. 38.040 3,383.784 ↑ 4.1 6,522 12

Nested Loop Left Join (cost=1,100.79..250,499.65 rows=26,803 width=44) (actual time=0.503..281.982 rows=6,522 loops=12)

63. 183.389 3,189.216 ↑ 3.5 6,522 12

Nested Loop Left Join (cost=1,100.79..240,846.79 rows=22,782 width=44) (actual time=0.496..265.768 rows=6,522 loops=12)

  • Filter: ((t21_opp_line_item_fact_3.sid IS NULL) OR (t21_opp_line_item_fact_3.c270_opp_close_date < 20181101) OR (t21_opp_line_item_fact_3.c270_opp_close_date > 20190131) OR (NOT (hashed SubPlan 15)))
  • Rows Removed by Filter: 48704
64. 210.636 1,017.012 ↓ 2.0 55,226 12

Nested Loop (cost=550.39..6,334.13 rows=27,954 width=28) (actual time=0.209..84.751 rows=55,226 loops=12)

65. 21.714 22.344 ↓ 40.8 8,167 12

HashAggregate (cost=549.83..551.83 rows=200 width=4) (actual time=0.197..1.862 rows=8,167 loops=12)

  • Group Key: ownercte_4.c1
66. 0.630 0.630 ↑ 3.0 8,167 1

CTE Scan on ownercte ownercte_4 (cost=0.00..488.74 rows=24,437 width=4) (actual time=0.001..0.630 rows=8,167 loops=1)

67. 784.032 784.032 ↓ 1.2 7 98,004

Index Scan using ix_t21_opp_line_item_fact_owner_closedate_timestamp on t21_opp_line_item_fact t21_opp_line_item_fact_2 (cost=0.56..28.85 rows=6 width=32) (actual time=0.005..0.008 rows=7 loops=98,004)

  • Index Cond: ((c268_opp_ownerid = ownercte_4.c1) AND (c270_opp_close_date >= closedatecte.c1) AND (c270_opp_close_date <= closedatecte.c2) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
68. 1,988.139 1,988.139 ↑ 1.0 1 662,713

Index Scan using ix_t21_opp_line_item_fact_sid_timestamp on t21_opp_line_item_fact t21_opp_line_item_fact_3 (cost=0.56..8.35 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=662,713)

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

SubPlan (forNested Loop Left Join)

70. 0.676 0.676 ↑ 3.0 8,167 1

CTE Scan on ownercte ownercte_3 (cost=0.00..488.74 rows=24,437 width=4) (actual time=0.000..0.676 rows=8,167 loops=1)

71. 0.000 156.528 ↑ 2.0 1 78,264

Append (cost=0.00..0.40 rows=2 width=16) (actual time=0.002..0.002 rows=1 loops=78,264)

72. 0.000 0.000 ↓ 0.0 0 78,264

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=78,264)

  • Filter: ((to_iso_code = 'USD'::text) AND (t21_opp_line_item_fact_2.c323_opp_currency_code = sid) AND (t21_opp_line_item_fact_2.c270_opp_close_date = rate_date))
73. 156.528 156.528 ↑ 1.0 1 78,264

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur177_t0_r0_3 (cost=0.29..0.40 rows=1 width=16) (actual time=0.001..0.002 rows=1 loops=78,264)

  • Index Cond: ((t21_opp_line_item_fact_2.c270_opp_close_date = rate_date) AND (t21_opp_line_item_fact_2.c323_opp_currency_code = sid))
  • Filter: (to_iso_code = 'USD'::text)
74.          

SubPlan (forNested Loop)

75. 0.002 0.002 ↑ 1.0 13 1

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

76. 0.585 0.585 ↑ 3.0 8,167 1

CTE Scan on ownercte ownercte_2 (cost=0.00..488.74 rows=24,437 width=4) (actual time=0.000..0.585 rows=8,167 loops=1)

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

78. 0.003 0.003 ↑ 1.0 7 1

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

79. 0.023 4,600.649 ↑ 1,166.8 19 1

Hash Join (cost=1.11..21,284.64 rows=22,170 width=96) (actual time=4,344.968..4,600.649 rows=19 loops=1)

  • Hash Cond: (cte0.c4 = t34_opportunityforecastcategorypicklistdim.sid)
80. 4,600.613 4,600.613 ↑ 46,674.4 19 1

CTE Scan on bucketac cte0 (cost=0.00..17,736.28 rows=886,814 width=92) (actual time=4,344.943..4,600.613 rows=19 loops=1)

81. 0.004 0.013 ↑ 1.0 5 1

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

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

Seq Scan on t34_opportunityforecastcategorypicklistdim (cost=0.00..1.05 rows=5 width=12) (actual time=0.008..0.009 rows=5 loops=1)

83. 0.019 3,611.142 ↑ 26.8 25 1

Subquery Scan on *SELECT* 2 (cost=1.11..652.76 rows=670 width=96) (actual time=3,575.897..3,611.142 rows=25 loops=1)

84. 0.022 3,611.123 ↑ 26.8 25 1

Hash Join (cost=1.11..644.38 rows=670 width=120) (actual time=3,575.891..3,611.123 rows=25 loops=1)

  • Hash Cond: (cte0_1.c6 = t34_opportunityforecastcategorypicklistdim_1.sid)
85. 3,611.091 3,611.091 ↑ 1,072.1 25 1

CTE Scan on bucketec cte0_1 (cost=0.00..536.06 rows=26,803 width=116) (actual time=3,575.869..3,611.091 rows=25 loops=1)

86. 0.004 0.010 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
87. 0.006 0.006 ↑ 1.0 5 1

Seq Scan on t34_opportunityforecastcategorypicklistdim t34_opportunityforecastcategorypicklistdim_1 (cost=0.00..1.05 rows=5 width=12) (actual time=0.006..0.006 rows=5 loops=1)