explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lcxi

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 385.573 ↑ 61,603.5 40 1

Append (cost=8,890,475.53..9,001,829.25 rows=2,464,141 width=120) (actual time=228.349..385.573 rows=40 loops=1)

2.          

CTE picklist_0

3. 0.305 0.305 ↑ 1.0 6 1

Seq Scan on t233_opportunitystagenamepicklistdim (cost=0.00..46.54 rows=6 width=4) (actual time=0.007..0.305 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.290 0.290 ↑ 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.290 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.428 0.428 ↑ 1.0 13 1

Seq Scan on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_2 (cost=0.00..57.31 rows=13 width=4) (actual time=0.008..0.428 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. 0.654 9.348 ↑ 68.3 360 1

HashAggregate (cost=23,698.64..23,944.69 rows=24,605 width=4) (actual time=9.290..9.348 rows=360 loops=1)

  • Group Key: distinctclosedateowner.ownerid
10.          

CTE owners

11. 0.077 2.391 ↑ 2.0 935 1

Nested Loop Semi Join (cost=60.06..17,848.02 rows=1,861 width=4) (actual time=0.117..2.391 rows=935 loops=1)

12. 0.356 0.444 ↑ 2.0 935 1

Bitmap Heap Scan on t166_userrolehierarchy (cost=59.64..6,841.91 rows=1,861 width=8) (actual time=0.105..0.444 rows=935 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=131
13. 0.088 0.088 ↑ 2.0 945 1

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..59.17 rows=1,861 width=0) (actual time=0.088..0.088 rows=945 loops=1)

  • Index Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
14. 1.870 1.870 ↑ 1.0 1 935

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

  • Index Cond: ((sid = t166_userrolehierarchy.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
15. 0.555 8.694 ↑ 19.6 4,093 1

Nested Loop (cost=42.30..5,650.54 rows=80,033 width=4) (actual time=2.946..8.694 rows=4,093 loops=1)

16. 0.355 2.973 ↓ 3.7 738 1

HashAggregate (cost=41.87..43.87 rows=200 width=4) (actual time=2.891..2.973 rows=738 loops=1)

  • Group Key: owners.c1
17. 2.618 2.618 ↑ 2.0 935 1

CTE Scan on owners (cost=0.00..37.22 rows=1,861 width=4) (actual time=0.118..2.618 rows=935 loops=1)

18. 5.166 5.166 ↑ 1.0 6 738

Index Only Scan using ix_distinctclosedateowner_owner_closedate on distinctclosedateowner (cost=0.43..27.97 rows=6 width=4) (actual time=0.003..0.007 rows=6 loops=738)

  • Index Cond: ((ownerid = owners.c1) AND (closedate <= 20190131) AND (closedate >= 20181101))
  • Heap Fetches: 4093
19.          

CTE bucketac

20. 3.048 232.063 ↑ 55,420.5 19 1

GroupAggregate (cost=4,012,306.91..4,062,323.93 rows=1,052,990 width=92) (actual time=228.331..232.063 rows=19 loops=1)

  • Group Key: t236_opp_line_item_fact.c820_opp_forecastcategory, (CASE WHEN (t236_opp_line_item_fact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t236_opp_line_item_fact_1.c805_opp_close_date < 20181101) OR (t236_opp_line_item_fact_1.c805_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 (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)
21. 3.572 229.015 ↑ 102.5 10,270 1

Sort (cost=4,011,753.00..4,014,385.48 rows=1,052,990 width=72) (actual time=228.312..229.015 rows=10,270 loops=1)

  • Sort Key: t236_opp_line_item_fact.c820_opp_forecastcategory, (CASE WHEN (t236_opp_line_item_fact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t236_opp_line_item_fact_1.c805_opp_close_date < 20181101) OR (t236_opp_line_item_fact_1.c805_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 (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: 1718kB
22. 6.065 225.443 ↑ 102.5 10,270 1

Hash Left Join (cost=3,758,893.47..3,820,042.10 rows=1,052,990 width=72) (actual time=206.564..225.443 rows=10,270 loops=1)

  • Hash Cond: (t236_opp_line_item_fact.c820_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim_2.sid)
23. 7.585 218.735 ↑ 102.5 10,270 1

Merge Right Join (cost=3,758,319.89..3,787,012.56 rows=1,052,990 width=60) (actual time=206.537..218.735 rows=10,270 loops=1)

  • Merge Cond: ((cur177_t1_r1.rate_date = t236_opp_line_item_fact_1.c805_opp_close_date) AND (cur177_t1_r1.sid = t236_opp_line_item_fact_1.c821_opp_currency_code))
24. 5.187 43.404 ↑ 1.3 79,564 1

Merge Append (cost=0.31..8,292.46 rows=101,614 width=16) (actual time=0.016..43.404 rows=79,564 loops=1)

  • Sort Key: cur177_t1_r1.rate_date, cur177_t1_r1.sid
25. 0.003 0.004 ↓ 0.0 0 1

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

  • Sort Key: cur177_t1_r1.rate_date, cur177_t1_r1.sid
  • Sort Method: quicksort Memory: 25kB
26. 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)
27. 38.213 38.213 ↑ 1.3 79,564 1

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur177_t1_r1_1 (cost=0.29..7,022.26 rows=101,613 width=16) (actual time=0.012..38.213 rows=79,564 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
28. 1.063 167.746 ↑ 86.8 10,270 1

Materialize (cost=3,757,555.90..3,762,013.50 rows=891,520 width=56) (actual time=165.804..167.746 rows=10,270 loops=1)

29. 4.017 166.683 ↑ 86.8 10,270 1

Sort (cost=3,757,555.90..3,759,784.70 rows=891,520 width=56) (actual time=165.802..166.683 rows=10,270 loops=1)

  • Sort Key: t236_opp_line_item_fact_1.c805_opp_close_date, t236_opp_line_item_fact_1.c821_opp_currency_code
  • Sort Method: quicksort Memory: 1829kB
30. 6.818 162.666 ↑ 86.8 10,270 1

Merge Right Join (cost=3,582,973.22..3,608,498.39 rows=891,520 width=56) (actual time=156.784..162.666 rows=10,270 loops=1)

  • Merge Cond: ((cur177_t0_r0.rate_date = t236_opp_line_item_fact.c805_opp_close_date) AND (cur177_t0_r0.sid = t236_opp_line_item_fact.c821_opp_currency_code))
31. 4.732 42.571 ↑ 1.4 71,835 1

Merge Append (cost=0.31..8,292.46 rows=101,614 width=16) (actual time=0.010..42.571 rows=71,835 loops=1)

  • Sort Key: cur177_t0_r0.rate_date, cur177_t0_r0.sid
32. 0.003 0.003 ↓ 0.0 0 1

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

  • Sort Key: cur177_t0_r0.rate_date, cur177_t0_r0.sid
  • Sort Method: quicksort Memory: 25kB
33. 0.000 0.000 ↓ 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.000..0.000 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
34. 37.836 37.836 ↑ 1.4 71,835 1

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur177_t0_r0_1 (cost=0.29..7,022.26 rows=101,613 width=16) (actual time=0.006..37.836 rows=71,835 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
35. 0.943 113.277 ↑ 73.5 10,270 1

Materialize (cost=3,582,209.23..3,585,983.29 rows=754,811 width=56) (actual time=111.432..113.277 rows=10,270 loops=1)

36. 5.144 112.334 ↑ 73.5 10,270 1

Sort (cost=3,582,209.23..3,584,096.26 rows=754,811 width=56) (actual time=111.429..112.334 rows=10,270 loops=1)

  • Sort Key: t236_opp_line_item_fact.c805_opp_close_date, t236_opp_line_item_fact.c821_opp_currency_code
  • Sort Method: quicksort Memory: 1829kB
37. 2.576 107.190 ↑ 73.5 10,270 1

Hash Left Join (cost=886.11..3,456,913.96 rows=754,811 width=56) (actual time=10.091..107.190 rows=10,270 loops=1)

  • Hash Cond: (t236_opp_line_item_fact_1.c820_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim_3.sid)
38. 6.028 104.607 ↑ 73.5 10,270 1

Nested Loop Left Join (cost=866.43..3,446,838.19 rows=754,811 width=56) (actual time=10.076..104.607 rows=10,270 loops=1)

39. 1.678 67.769 ↑ 73.5 10,270 1

Nested Loop (cost=865.87..825,614.28 rows=754,811 width=24) (actual time=10.066..67.769 rows=10,270 loops=1)

40. 0.158 9.571 ↓ 1.8 360 1

HashAggregate (cost=553.61..555.61 rows=200 width=4) (actual time=9.487..9.571 rows=360 loops=1)

  • Group Key: ownercte_1.c1
41. 9.413 9.413 ↑ 68.3 360 1

CTE Scan on ownercte ownercte_1 (cost=0.00..492.10 rows=24,605 width=4) (actual time=9.290..9.413 rows=360 loops=1)

42. 37.729 56.520 ↑ 4.5 29 360

Bitmap Heap Scan on t236_opp_line_item_fact (cost=312.26..4,123.99 rows=130 width=28) (actual time=0.085..0.157 rows=29 loops=360)

  • Recheck Cond: ((c804_opp_ownerid = ownercte_1.c1) AND (start_stamp <= '1541055600000'::bigint) AND (end_stamp > '1541055600000'::bigint))
  • Filter: ((NOT deleted) AND (c805_opp_close_date >= 20181101) AND (c805_opp_close_date <= 20190131) AND (NOT (hashed SubPlan 9)))
  • Rows Removed by Filter: 104
  • Heap Blocks: exact=20442
43. 18.360 18.360 ↑ 8.0 133 360

Bitmap Index Scan on t236_opp_line_item_fact_564_timestamp (cost=0.00..311.93 rows=1,062 width=0) (actual time=0.051..0.051 rows=133 loops=360)

  • Index Cond: ((c804_opp_ownerid = ownercte_1.c1) AND (start_stamp <= '1541055600000'::bigint) AND (end_stamp > '1541055600000'::bigint))
44.          

SubPlan (forBitmap Heap Scan)

45. 0.431 0.431 ↑ 1.0 13 1

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

46. 30.810 30.810 ↑ 1.0 1 10,270

Index Scan using ix_t236_opp_line_item_fact_sid_timestamp on t236_opp_line_item_fact t236_opp_line_item_fact_1 (cost=0.56..3.46 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=10,270)

  • Index Cond: ((t236_opp_line_item_fact.sid = sid) AND (t236_opp_line_item_fact.sid = sid) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
  • Filter: (NOT deleted)
47. 0.004 0.007 ↑ 86.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
48. 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)

49. 0.004 0.010 ↑ 86.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
50. 0.006 0.006 ↑ 86.0 5 1

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

51.          

SubPlan (forHash Left Join)

52. 0.029 0.029 ↑ 68.3 360 1

CTE Scan on ownercte (cost=0.00..492.10 rows=24,605 width=4) (actual time=0.001..0.029 rows=360 loops=1)

53. 0.307 0.307 ↑ 1.0 6 1

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

54. 0.297 0.297 ↑ 1.0 7 1

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

55.          

CTE bucketec

56. 0.513 153.417 ↑ 67,197.7 21 1

GroupAggregate (cost=4,744,061.38..4,804,035.29 rows=1,411,151 width=116) (actual time=152.837..153.417 rows=21 loops=1)

  • Group Key: (CASE WHEN (t236_opp_line_item_fact_3.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 11)) THEN CASE WHEN (t236_opp_line_item_fact_3.c805_opp_close_date > 20190131) THEN 'pulledInBucket'::text WHEN (t236_opp_line_item_fact_3.c805_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), t236_opp_line_item_fact_2.c820_opp_forecastcategory
57. 1.011 152.904 ↑ 762.0 1,852 1

Sort (cost=4,743,507.18..4,747,035.06 rows=1,411,151 width=88) (actual time=152.784..152.904 rows=1,852 loops=1)

  • Sort Key: (CASE WHEN (t236_opp_line_item_fact_3.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 11)) THEN CASE WHEN (t236_opp_line_item_fact_3.c805_opp_close_date > 20190131) THEN 'pulledInBucket'::text WHEN (t236_opp_line_item_fact_3.c805_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), t236_opp_line_item_fact_2.c820_opp_forecastcategory
  • Sort Method: quicksort Memory: 224kB
58. 5.720 151.893 ↑ 762.0 1,852 1

Merge Right Join (cost=4,407,425.26..4,464,311.10 rows=1,411,151 width=88) (actual time=148.612..151.893 rows=1,852 loops=1)

  • Merge Cond: ((cur177_t0_r0_2.rate_date = t236_opp_line_item_fact_2.c805_opp_close_date) AND (cur177_t0_r0_2.sid = t236_opp_line_item_fact_2.c821_opp_currency_code))
59. 4.698 41.385 ↑ 1.4 71,828 1

Merge Append (cost=0.31..8,292.46 rows=101,614 width=16) (actual time=0.014..41.385 rows=71,828 loops=1)

  • Sort Key: cur177_t0_r0_2.rate_date, cur177_t0_r0_2.sid
60. 0.003 0.004 ↓ 0.0 0 1

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

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

Seq Scan on clari_conversion_rate cur177_t0_r0_2 (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)
62. 36.683 36.683 ↑ 1.4 71,828 1

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur177_t0_r0_3 (cost=0.29..7,022.26 rows=101,613 width=16) (actual time=0.010..36.683 rows=71,828 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
63. 0.212 104.754 ↑ 645.1 1,852 1

Materialize (cost=4,406,107.07..4,412,080.86 rows=1,194,759 width=44) (actual time=104.409..104.754 rows=1,852 loops=1)

64. 0.846 104.542 ↑ 645.1 1,852 1

Sort (cost=4,406,107.07..4,409,093.97 rows=1,194,759 width=44) (actual time=104.407..104.542 rows=1,852 loops=1)

  • Sort Key: t236_opp_line_item_fact_2.c805_opp_close_date, t236_opp_line_item_fact_2.c821_opp_currency_code
  • Sort Method: quicksort Memory: 193kB
65. 6.451 103.696 ↑ 645.1 1,852 1

Nested Loop Left Join (cost=1,445.77..4,211,999.37 rows=1,194,759 width=44) (actual time=0.660..103.696 rows=1,852 loops=1)

  • Filter: ((t236_opp_line_item_fact_3.sid IS NULL) OR (t236_opp_line_item_fact_3.c805_opp_close_date < 20181101) OR (t236_opp_line_item_fact_3.c805_opp_close_date > 20190131) OR (NOT (hashed SubPlan 15)))
  • Rows Removed by Filter: 11037
66. 2.926 58.554 ↑ 114.9 12,889 1

Nested Loop (cost=891.59..817,243.40 rows=1,480,392 width=28) (actual time=0.233..58.554 rows=12,889 loops=1)

67. 0.161 0.188 ↓ 1.8 360 1

HashAggregate (cost=553.61..555.61 rows=200 width=4) (actual time=0.093..0.188 rows=360 loops=1)

  • Group Key: ownercte_4.c1
68. 0.027 0.027 ↑ 68.3 360 1

CTE Scan on ownercte ownercte_4 (cost=0.00..492.10 rows=24,605 width=4) (actual time=0.001..0.027 rows=360 loops=1)

69. 36.720 55.440 ↑ 7.1 36 360

Bitmap Heap Scan on t236_opp_line_item_fact t236_opp_line_item_fact_2 (cost=337.98..4,080.89 rows=255 width=32) (actual time=0.084..0.154 rows=36 loops=360)

  • Recheck Cond: ((c804_opp_ownerid = ownercte_4.c1) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
  • Filter: ((NOT deleted) AND (c805_opp_close_date >= 20181101) AND (c805_opp_close_date <= 20190131))
  • Rows Removed by Filter: 103
  • Heap Blocks: exact=21211
70. 18.720 18.720 ↑ 7.4 140 360

Bitmap Index Scan on t236_opp_line_item_fact_564_timestamp (cost=0.00..337.92 rows=1,042 width=0) (actual time=0.052..0.052 rows=140 loops=360)

  • Index Cond: ((c804_opp_ownerid = ownercte_4.c1) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
71. 38.667 38.667 ↑ 1.0 1 12,889

Index Scan using ix_t236_opp_line_item_fact_sid_timestamp on t236_opp_line_item_fact t236_opp_line_item_fact_3 (cost=0.56..2.28 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=12,889)

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

SubPlan (forNested Loop Left Join)

73. 0.024 0.024 ↑ 68.3 360 1

CTE Scan on ownercte ownercte_3 (cost=0.00..492.10 rows=24,605 width=4) (actual time=0.000..0.024 rows=360 loops=1)

74.          

SubPlan (forMerge Right Join)

75. 0.000 0.000 ↑ 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.000 rows=13 loops=1)

76. 0.030 0.030 ↑ 68.3 360 1

CTE Scan on ownercte ownercte_2 (cost=0.00..492.10 rows=24,605 width=4) (actual time=0.000..0.030 rows=360 loops=1)

77. 0.003 0.003 ↑ 1.0 6 1

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

78. 0.001 0.001 ↑ 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.001 rows=7 loops=1)

79. 0.011 232.093 ↑ 55,420.5 19 1

Hash Join (cost=19.68..35,558.09 rows=1,052,990 width=120) (actual time=228.348..232.093 rows=19 loops=1)

  • Hash Cond: (cte0.c4 = t234_opportunityforecastcategorypicklistdim.sid)
80. 232.076 232.076 ↑ 55,420.5 19 1

CTE Scan on bucketac cte0 (cost=0.00..21,059.80 rows=1,052,990 width=92) (actual time=228.334..232.076 rows=19 loops=1)

81. 0.003 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
82. 0.003 0.003 ↑ 86.0 5 1

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

83. 0.012 153.472 ↑ 67,197.7 21 1

Subquery Scan on *SELECT* 2 (cost=19.68..65,285.41 rows=1,411,151 width=120) (actual time=152.873..153.472 rows=21 loops=1)

84. 0.019 153.460 ↑ 67,197.7 21 1

Hash Join (cost=19.68..47,646.02 rows=1,411,151 width=144) (actual time=152.866..153.460 rows=21 loops=1)

  • Hash Cond: (cte0_1.c6 = t234_opportunityforecastcategorypicklistdim_1.sid)
85. 153.431 153.431 ↑ 67,197.7 21 1

CTE Scan on bucketec cte0_1 (cost=0.00..28,223.02 rows=1,411,151 width=116) (actual time=152.840..153.431 rows=21 loops=1)

86. 0.005 0.010 ↑ 86.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
87. 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.005..0.005 rows=5 loops=1)