explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GJ0G

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 488.873 ↑ 65,020.9 40 1

Append (cost=9,041,949.90..9,159,446.29 rows=2,600,836 width=120) (actual time=327.775..488.873 rows=40 loops=1)

2.          

CTE picklist_0

3. 0.297 0.297 ↑ 1.0 6 1

Seq Scan on t233_opportunitystagenamepicklistdim (cost=0.00..46.54 rows=6 width=4) (actual time=0.007..0.297 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.287 0.287 ↑ 1.0 7 1

Seq Scan on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_1 (cost=0.00..48.08 rows=7 width=4) (actual time=0.003..0.287 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.680 0.680 ↑ 1.0 13 1

Seq Scan on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_2 (cost=0.00..57.31 rows=13 width=4) (actual time=0.015..0.680 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.955 108.446 ↑ 31.6 360 1

HashAggregate (cost=51,005.99..51,119.74 rows=11,375 width=4) (actual time=108.354..108.446 rows=360 loops=1)

  • Group Key: distinctclosedateowner.ownerid
10. 19.306 107.491 ↑ 2.8 4,093 1

Hash Join (cost=21,065.63..50,977.55 rows=11,375 width=4) (actual time=25.280..107.491 rows=4,093 loops=1)

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

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

  • Index Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
13. 0.089 2.456 ↑ 2.5 737 1

Hash (cost=17,863.14..17,863.14 rows=1,851 width=4) (actual time=2.456..2.456 rows=737 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 42kB
14. 0.272 2.367 ↑ 2.5 737 1

HashAggregate (cost=17,844.63..17,863.14 rows=1,851 width=4) (actual time=2.289..2.367 rows=737 loops=1)

  • Group Key: t166_userrolehierarchy.sid
15. 0.711 2.095 ↑ 2.0 934 1

Nested Loop Semi Join (cost=60.05..17,839.98 rows=1,860 width=4) (actual time=0.118..2.095 rows=934 loops=1)

16. 0.360 0.450 ↑ 2.0 934 1

Bitmap Heap Scan on t166_userrolehierarchy (cost=59.63..6,838.33 rows=1,860 width=8) (actual time=0.103..0.450 rows=934 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=130
17. 0.090 0.090 ↑ 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.090..0.090 rows=944 loops=1)

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

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

  • Index Cond: ((sid = t166_userrolehierarchy.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
19.          

CTE bucketac

20. 3.141 331.658 ↑ 59,199.0 19 1

GroupAggregate (cost=4,099,059.11..4,152,486.21 rows=1,124,781 width=92) (actual time=327.753..331.658 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 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)
21. 3.769 328.517 ↑ 109.5 10,270 1

Sort (cost=4,098,802.88..4,101,614.84 rows=1,124,781 width=72) (actual time=327.738..328.517 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 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: 1718kB
22. 6.066 324.748 ↑ 109.5 10,270 1

Hash Left Join (cost=3,829,388.76..3,893,481.57 rows=1,124,781 width=72) (actual time=306.292..324.748 rows=10,270 loops=1)

  • Hash Cond: (t236_opp_line_item_fact.c820_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim_2.sid)
23. 7.462 318.054 ↑ 109.5 10,270 1

Merge Right Join (cost=3,829,112.85..3,858,575.16 rows=1,124,781 width=60) (actual time=306.266..318.054 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.068 41.769 ↑ 1.3 79,564 1

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

  • Sort Key: cur177_t1_r1.rate_date, cur177_t1_r1.sid
25. 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_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. 36.696 36.696 ↑ 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.015..36.696 rows=79,564 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
28. 1.038 268.823 ↑ 91.8 10,270 1

Materialize (cost=3,827,734.49..3,832,447.43 rows=942,589 width=56) (actual time=266.906..268.823 rows=10,270 loops=1)

29. 4.229 267.785 ↑ 91.8 10,270 1

Sort (cost=3,827,734.49..3,830,090.96 rows=942,589 width=56) (actual time=266.904..267.785 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.908 263.556 ↑ 91.8 10,270 1

Merge Right Join (cost=3,643,872.86..3,669,765.11 rows=942,589 width=56) (actual time=257.860..263.556 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.536 41.549 ↑ 1.4 71,835 1

Merge Append (cost=0.31..8,292.46 rows=101,614 width=16) (actual time=0.009..41.549 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.010 37.010 ↑ 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.010 rows=71,835 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
35. 0.929 215.099 ↑ 76.9 10,270 1

Materialize (cost=3,642,494.50..3,646,444.05 rows=789,909 width=56) (actual time=213.250..215.099 rows=10,270 loops=1)

36. 5.123 214.170 ↑ 76.9 10,270 1

Sort (cost=3,642,494.50..3,644,469.27 rows=789,909 width=56) (actual time=213.245..214.170 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.766 209.047 ↑ 76.9 10,270 1

Hash Left Join (cost=594.54..3,511,119.67 rows=789,909 width=56) (actual time=109.653..209.047 rows=10,270 loops=1)

  • Hash Cond: (t236_opp_line_item_fact_1.c820_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim_3.sid)
38. 8.775 206.268 ↑ 76.9 10,270 1

Nested Loop Left Join (cost=574.87..3,500,603.16 rows=789,909 width=56) (actual time=109.625..206.268 rows=10,270 loops=1)

39. 1.756 166.683 ↑ 76.9 10,270 1

Nested Loop (cost=574.30..848,363.71 rows=789,909 width=24) (actual time=109.604..166.683 rows=10,270 loops=1)

40. 0.188 108.767 ↓ 1.8 360 1

HashAggregate (cost=255.94..257.94 rows=200 width=4) (actual time=108.688..108.767 rows=360 loops=1)

  • Group Key: ownercte_1.c1
41. 108.579 108.579 ↑ 31.6 360 1

CTE Scan on ownercte ownercte_1 (cost=0.00..227.50 rows=11,375 width=4) (actual time=108.364..108.579 rows=360 loops=1)

42. 37.476 56.160 ↑ 4.7 29 360

Bitmap Heap Scan on t236_opp_line_item_fact (cost=318.36..4,239.16 rows=137 width=28) (actual time=0.087..0.156 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 8)))
  • Rows Removed by Filter: 104
  • Heap Blocks: exact=20479
43. 18.000 18.000 ↑ 8.2 134 360

Bitmap Index Scan on t236_opp_line_item_fact_564_timestamp (cost=0.00..318.04 rows=1,096 width=0) (actual time=0.050..0.050 rows=134 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.684 0.684 ↑ 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.684 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.35 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.005 0.013 ↑ 86.0 5 1

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

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

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

49. 0.004 0.012 ↑ 86.0 5 1

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

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

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

51.          

SubPlan (forHash Left Join)

52. 0.027 0.027 ↑ 31.6 360 1

CTE Scan on ownercte (cost=0.00..227.50 rows=11,375 width=4) (actual time=0.001..0.027 rows=360 loops=1)

53. 0.299 0.299 ↑ 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.299 rows=6 loops=1)

54. 0.290 0.290 ↑ 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.290 rows=7 loops=1)

55.          

CTE bucketec

56. 0.477 157.118 ↑ 70,288.3 21 1

GroupAggregate (cost=4,775,440.01..4,838,172.34 rows=1,476,055 width=116) (actual time=156.546..157.118 rows=21 loops=1)

  • Group Key: (CASE WHEN (t236_opp_line_item_fact_3.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 10)) 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 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), t236_opp_line_item_fact_2.c820_opp_forecastcategory
57. 1.009 156.641 ↑ 797.0 1,852 1

Sort (cost=4,775,183.48..4,778,873.62 rows=1,476,055 width=88) (actual time=156.491..156.641 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 10)) 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 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), t236_opp_line_item_fact_2.c820_opp_forecastcategory
  • Sort Method: quicksort Memory: 224kB
58. 5.749 155.632 ↑ 797.0 1,852 1

Merge Right Join (cost=4,424,183.82..4,482,670.18 rows=1,476,055 width=88) (actual time=152.357..155.632 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.446 42.886 ↑ 1.4 71,828 1

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

  • Sort Key: cur177_t0_r0_2.rate_date, cur177_t0_r0_2.sid
60. 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_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. 38.435 38.435 ↑ 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.012..38.435 rows=71,828 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
63. 0.184 106.955 ↑ 667.9 1,852 1

Materialize (cost=4,422,548.93..4,428,733.75 rows=1,236,964 width=44) (actual time=106.624..106.955 rows=1,852 loops=1)

64. 0.810 106.771 ↑ 667.9 1,852 1

Sort (cost=4,422,548.93..4,425,641.34 rows=1,236,964 width=44) (actual time=106.621..106.771 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. 9.081 105.961 ↑ 667.9 1,852 1

Nested Loop Left Join (cost=856.82..4,221,274.24 rows=1,236,964 width=44) (actual time=0.670..105.961 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 14)))
  • Rows Removed by Filter: 11037
66. 2.897 58.187 ↑ 119.2 12,889 1

Nested Loop (cost=600.32..833,820.63 rows=1,535,966 width=28) (actual time=0.300..58.187 rows=12,889 loops=1)

67. 0.186 0.210 ↓ 1.8 360 1

HashAggregate (cost=255.94..257.94 rows=200 width=4) (actual time=0.121..0.210 rows=360 loops=1)

  • Group Key: ownercte_4.c1
68. 0.024 0.024 ↑ 31.6 360 1

CTE Scan on ownercte ownercte_4 (cost=0.00..227.50 rows=11,375 width=4) (actual time=0.000..0.024 rows=360 loops=1)

69. 36.720 55.080 ↑ 7.4 36 360

Bitmap Heap Scan on t236_opp_line_item_fact t236_opp_line_item_fact_2 (cost=344.38..4,165.15 rows=266 width=32) (actual time=0.084..0.153 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=21258
70. 18.360 18.360 ↑ 7.6 141 360

Bitmap Index Scan on t236_opp_line_item_fact_564_timestamp (cost=0.00..344.32 rows=1,066 width=0) (actual time=0.051..0.051 rows=141 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.19 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.026 0.026 ↑ 31.6 360 1

CTE Scan on ownercte ownercte_3 (cost=0.00..227.50 rows=11,375 width=4) (actual time=0.000..0.026 rows=360 loops=1)

74.          

SubPlan (forMerge Right Join)

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

76. 0.037 0.037 ↑ 31.6 360 1

CTE Scan on ownercte ownercte_2 (cost=0.00..227.50 rows=11,375 width=4) (actual time=0.001..0.037 rows=360 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.001..0.001 rows=6 loops=1)

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

79. 0.016 331.696 ↑ 59,199.0 19 1

Hash Join (cost=19.68..37,981.03 rows=1,124,781 width=120) (actual time=327.774..331.696 rows=19 loops=1)

  • Hash Cond: (cte0.c4 = t234_opportunityforecastcategorypicklistdim.sid)
80. 331.672 331.672 ↑ 59,199.0 19 1

CTE Scan on bucketac cte0 (cost=0.00..22,495.62 rows=1,124,781 width=92) (actual time=327.756..331.672 rows=19 loops=1)

81. 0.005 0.008 ↑ 86.0 5 1

Hash (cost=14.30..14.30 rows=430 width=36) (actual time=0.008..0.008 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 157.172 ↑ 70,288.3 21 1

Subquery Scan on *SELECT* 2 (cost=19.68..68,287.22 rows=1,476,055 width=120) (actual time=156.582..157.172 rows=21 loops=1)

84. 0.021 157.160 ↑ 70,288.3 21 1

Hash Join (cost=19.68..49,836.53 rows=1,476,055 width=144) (actual time=156.574..157.160 rows=21 loops=1)

  • Hash Cond: (cte0_1.c6 = t234_opportunityforecastcategorypicklistdim_1.sid)
85. 157.128 157.128 ↑ 70,288.3 21 1

CTE Scan on bucketec cte0_1 (cost=0.00..29,521.10 rows=1,476,055 width=116) (actual time=156.548..157.128 rows=21 loops=1)

86. 0.004 0.011 ↑ 86.0 5 1

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

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