explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UFOB

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 25,187.305 ↑ 923.8 24 1

Append (cost=9,709,922.50..9,731,428.86 rows=22,171 width=120) (actual time=4,344.864..25,187.305 rows=24 loops=1)

2.          

CTE picklist_0

3. 0.345 0.345 ↑ 1.0 6 1

Seq Scan on t35_opportunitystagenamepicklistdim (cost=0.00..70.54 rows=6 width=4) (actual time=0.010..0.345 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.302 0.302 ↑ 1.0 7 1

Seq Scan on t35_opportunitystagenamepicklistdim t35_opportunitystagenamepicklistdim_1 (cost=0.00..72.08 rows=7 width=4) (actual time=0.006..0.302 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.488 0.488 ↑ 1.0 13 1

Seq Scan on t35_opportunitystagenamepicklistdim t35_opportunitystagenamepicklistdim_2 (cost=0.00..81.31 rows=13 width=4) (actual time=0.014..0.488 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.446 965.304 ↑ 3.0 8,167 1

HashAggregate (cost=430,087.55..430,331.92 rows=24,437 width=4) (actual time=964.229..965.304 rows=8,167 loops=1)

  • Group Key: distinctclosedateowner.ownerid
10. 57.639 948.858 ↑ 1.6 93,387 1

Hash Join (cost=396,390.10..429,713.18 rows=149,749 width=4) (actual time=817.550..948.858 rows=93,387 loops=1)

  • Hash Cond: (distinctclosedateowner.ownerid = t166_userrolehierarchy.sid)
11. 77.242 96.402 ↓ 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.196..96.402 rows=196,760 loops=1)

  • Recheck Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
  • Heap Blocks: exact=17913
12. 19.160 19.160 ↓ 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.160..19.160 rows=196,760 loops=1)

  • Index Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
13. 16.606 794.817 ↑ 1.6 120,703 1

Hash (cost=390,002.50..390,002.50 rows=195,539 width=4) (actual time=794.817..794.817 rows=120,703 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2089kB
14. 15.178 778.211 ↑ 1.6 120,703 1

Unique (cost=383,594.46..390,002.50 rows=195,539 width=4) (actual time=747.679..778.211 rows=120,703 loops=1)

15. 68.195 763.033 ↑ 9.9 129,568 1

Sort (cost=383,594.46..386,798.48 rows=1,281,608 width=4) (actual time=747.677..763.033 rows=129,568 loops=1)

  • Sort Key: t166_userrolehierarchy.sid
  • Sort Method: external merge Disk: 1768kB
16. 197.570 694.838 ↑ 9.9 129,568 1

Hash Join (cost=59,579.42..236,053.88 rows=1,281,608 width=4) (actual time=156.112..694.838 rows=129,568 loops=1)

  • Hash Cond: (t166_userrolehierarchy.c662_user_role_id = t67_userroledim.sid)
17. 349.205 484.158 ↓ 1.0 1,321,359 1

Bitmap Heap Scan on t166_userrolehierarchy (cost=37,181.60..194,331.72 rows=1,281,608 width=8) (actual time=142.249..484.158 rows=1,321,359 loops=1)

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

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..36,861.20 rows=1,281,608 width=0) (actual time=134.953..134.953 rows=1,322,570 loops=1)

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

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

  • Buckets: 131072 Batches: 2 Memory Usage: 1177kB
20. 7.142 11.651 ↑ 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.980..11.651 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: 5520
  • Heap Blocks: exact=2138
21. 4.509 4.509 ↑ 5.7 13,911 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.509..4.509 rows=13,911 loops=1)

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

CTE bucketac

23. 137.523 4,599.074 ↑ 46,674.4 19 1

GroupAggregate (cost=4,224,412.72..4,266,536.38 rows=886,814 width=92) (actual time=4,344.818..4,599.074 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 5) THEN CASE WHEN (hashed SubPlan 6) THEN 'won'::text WHEN (hashed SubPlan 7) 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)
24. 328.194 4,461.551 ↑ 2.1 418,251 1

Sort (cost=4,223,862.59..4,226,079.63 rows=886,814 width=72) (actual time=4,344.409..4,461.551 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 5) THEN CASE WHEN (hashed SubPlan 6) THEN 'won'::text WHEN (hashed SubPlan 7) 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
25. 355.069 4,133.357 ↑ 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,430.918..4,133.357 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))
26. 90.096 3,732.324 ↑ 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,382.770..3,732.324 rows=418,251 loops=1)

  • Hash Cond: (t21_opp_line_item_fact.c320_opp_forecastcategory = t34_opportunityforecastcategorypicklistdim_2.sid)
27. 95.571 3,642.215 ↑ 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,382.739..3,642.215 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))
28. 4.783 44.433 ↑ 1.4 71,814 1

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

  • Sort Key: cur177_t0_r0.rate_date, cur177_t0_r0.sid
29. 0.006 0.007 ↓ 0.0 0 1

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

  • Sort Key: cur177_t0_r0.rate_date, cur177_t0_r0.sid
  • Sort Method: quicksort Memory: 25kB
30. 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)
31. 39.643 39.643 ↑ 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.011..39.643 rows=71,814 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
32. 57.465 3,502.211 ↑ 1.5 418,251 1

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

33. 341.598 3,444.746 ↑ 1.5 418,251 1

Sort (cost=3,822,069.72..3,823,671.46 rows=640,694 width=56) (actual time=3,336.657..3,444.746 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
34. 108.274 3,103.148 ↑ 1.5 418,251 1

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

  • Hash Cond: (t21_opp_line_item_fact_1.c320_opp_forecastcategory = t34_opportunityforecastcategorypicklistdim_3.sid)
35. 163.355 2,994.866 ↑ 1.5 418,251 1

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

36. 50.578 1,576.758 ↑ 1.5 418,251 1

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

37. 3.969 970.824 ↓ 40.8 8,167 1

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

  • Group Key: ownercte_1.c1
38. 966.855 966.855 ↑ 3.0 8,167 1

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

39. 554.865 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.012..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 8)))
  • Rows Removed by Filter: 23
40.          

SubPlan (forIndex Scan)

41. 0.491 0.491 ↑ 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.491 rows=13 loops=1)

42. 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)
43. 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
44. 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.005..0.006 rows=5 loops=1)

45. 0.005 0.013 ↑ 1.0 5 1

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

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

47. 18.970 44.618 ↑ 1.0 100,855 1

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

  • Buckets: 131072 Batches: 2 Memory Usage: 3391kB
48. 6.056 25.648 ↑ 1.0 100,855 1

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

49. 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)
50. 19.591 19.591 ↑ 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.049..19.591 rows=100,855 loops=1)

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

SubPlan (forHash Left Join)

52. 0.693 0.693 ↑ 3.0 8,167 1

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

53. 0.350 0.350 ↑ 1.0 6 1

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

54. 0.303 0.303 ↑ 1.0 7 1

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

55.          

CTE bucketec

56. 15.988 20,588.084 ↓ 5.0 5 1

GroupAggregate (cost=5,012,829.10..5,012,829.15 rows=1 width=116) (actual time=20,569.542..20,588.084 rows=5 loops=1)

  • Group Key: (CASE WHEN (t21_opp_line_item_fact_3.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 10)) 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 11) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END ELSE 'otherBucket'::text END), (CASE WHEN (hashed SubPlan 12) THEN 'won'::text WHEN (hashed SubPlan 13) THEN 'lost'::text ELSE 'newOpen'::text END), t21_opp_line_item_fact_2.c320_opp_forecastcategory
57. 134.207 20,572.096 ↓ 60,323.0 60,323 1

Sort (cost=5,012,278.69..5,012,278.69 rows=1 width=88) (actual time=20,563.870..20,572.096 rows=60,323 loops=1)

  • Sort Key: (CASE WHEN (t21_opp_line_item_fact_3.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 10)) 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 11) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END ELSE 'otherBucket'::text END), (CASE WHEN (hashed SubPlan 12) THEN 'won'::text WHEN (hashed SubPlan 13) THEN 'lost'::text ELSE 'newOpen'::text END), t21_opp_line_item_fact_2.c320_opp_forecastcategory
  • Sort Method: external merge Disk: 3280kB
58. 24.999 20,437.889 ↓ 60,323.0 60,323 1

Nested Loop Left Join (cost=4,882,652.38..5,012,278.68 rows=1 width=88) (actual time=20,270.652..20,437.889 rows=60,323 loops=1)

59. 1,070.210 20,292.241 ↓ 60,323.0 60,323 1

Merge Anti Join (cost=4,882,101.97..5,011,727.91 rows=1 width=44) (actual time=20,270.572..20,292.241 rows=60,323 loops=1)

  • Merge Cond: ((t21_opp_line_item_fact_2.sid = t21_opp_line_item_fact_3.sid) AND (t21_opp_line_item_fact_2.sid = t21_opp_line_item_fact_3.sid))
60. 409.915 1,005.988 ↑ 1.9 662,713 1

Sort (cost=399,729.34..402,855.12 rows=1,250,312 width=28) (actual time=852.436..1,005.988 rows=662,713 loops=1)

  • Sort Key: t21_opp_line_item_fact_2.sid
  • Sort Method: external merge Disk: 27192kB
61. 142.922 596.073 ↑ 1.9 662,713 1

Nested Loop (cost=550.39..213,275.14 rows=1,250,312 width=28) (actual time=2.410..596.073 rows=662,713 loops=1)

62. 3.360 3.966 ↓ 40.8 8,167 1

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

  • Group Key: ownercte_3.c1
63. 0.606 0.606 ↑ 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.606 rows=8,167 loops=1)

64. 449.185 449.185 ↑ 3.2 81 8,167

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..1,061.01 rows=261 width=32) (actual time=0.009..0.055 rows=81 loops=8,167)

  • Index Cond: ((c268_opp_ownerid = ownercte_3.c1) AND (c270_opp_close_date >= 20181101) AND (c270_opp_close_date <= 20190131) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
65. 854.089 18,216.043 ↑ 1.2 9,724,362 1

Materialize (cost=4,482,372.62..4,542,496.87 rows=12,024,850 width=16) (actual time=15,339.494..18,216.043 rows=9,724,362 loops=1)

66. 8,265.398 17,361.954 ↑ 1.2 9,724,362 1

Sort (cost=4,482,372.62..4,512,434.75 rows=12,024,850 width=16) (actual time=15,339.491..17,361.954 rows=9,724,362 loops=1)

  • Sort Key: t21_opp_line_item_fact_3.sid
  • Sort Method: external merge Disk: 247176kB
67. 9,096.556 9,096.556 ↑ 1.2 9,724,362 1

Seq Scan on t21_opp_line_item_fact t21_opp_line_item_fact_3 (cost=0.00..2,657,267.39 rows=12,024,850 width=16) (actual time=0.284..9,096.556 rows=9,724,362 loops=1)

  • Filter: ((NOT deleted) AND (start_stamp <= '1541055600000'::bigint) AND (end_stamp > '1541055600000'::bigint))
  • Rows Removed by Filter: 22932812
68. 60.323 120.646 ↑ 2.0 1 60,323

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

69. 0.000 0.000 ↓ 0.0 0 60,323

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=60,323)

  • 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))
70. 60.323 60.323 ↑ 1.0 1 60,323

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur177_t0_r0_3 (cost=0.29..0.32 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=60,323)

  • 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)
71.          

SubPlan (forNested Loop Left Join)

72. 0.000 0.000 ↓ 0.0 0

CTE Scan on picklist_4 cte0_5 (cost=0.00..0.26 rows=13 width=4) (never executed)

73. 0.000 0.000 ↓ 0.0 0

CTE Scan on ownercte ownercte_2 (cost=0.00..488.74 rows=24,437 width=4) (never executed)

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

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

76. 0.039 4,599.149 ↑ 1,166.8 19 1

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

  • Hash Cond: (cte0.c4 = t34_opportunityforecastcategorypicklistdim.sid)
77. 4,599.092 4,599.092 ↑ 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.822..4,599.092 rows=19 loops=1)

78. 0.004 0.018 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
79. 0.014 0.014 ↑ 1.0 5 1

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

80. 0.026 20,588.150 ↓ 5.0 5 1

Subquery Scan on *SELECT* 2 (cost=0.03..1.12 rows=1 width=96) (actual time=20,588.143..20,588.150 rows=5 loops=1)

81. 0.021 20,588.124 ↓ 5.0 5 1

Hash Join (cost=0.03..1.11 rows=1 width=120) (actual time=20,588.118..20,588.124 rows=5 loops=1)

  • Hash Cond: (t34_opportunityforecastcategorypicklistdim_1.sid = cte0_1.c6)
82. 0.005 0.005 ↑ 1.0 5 1

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

83. 0.007 20,588.098 ↓ 5.0 5 1

Hash (cost=0.02..0.02 rows=1 width=116) (actual time=20,588.098..20,588.098 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
84. 20,588.091 20,588.091 ↓ 5.0 5 1

CTE Scan on bucketec cte0_1 (cost=0.00..0.02 rows=1 width=116) (actual time=20,569.545..20,588.091 rows=5 loops=1)