explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WOOn

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 10,449.159 ↑ 116,854.8 44 1

Append (cost=3,982,271,599.89..3,982,502,490.63 rows=5,141,611 width=144) (actual time=7,150.793..10,449.159 rows=44 loops=1)

2.          

CTE picklist_0

3. 0.345 0.345 ↑ 1.0 6 1

Seq Scan on t233_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.286 0.286 ↑ 1.0 7 1

Seq Scan on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_1 (cost=0.00..72.08 rows=7 width=4) (actual time=0.006..0.286 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.471 0.471 ↑ 1.0 13 1

Seq Scan on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_2 (cost=0.00..81.31 rows=13 width=4) (actual time=0.014..0.471 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. 6.021 1,505.936 ↑ 2.8 8,168 1

Unique (cost=7,267,188.08..7,267,559.08 rows=23,134 width=4) (actual time=1,490.706..1,505.936 rows=8,168 loops=1)

10. 72.391 1,499.915 ↓ 1.3 93,388 1

Sort (cost=7,267,188.08..7,267,373.58 rows=74,199 width=4) (actual time=1,490.704..1,499.915 rows=93,388 loops=1)

  • Sort Key: distinctclosedateowner.ownerid
  • Sort Method: external merge Disk: 1280kB
11. 30.977 1,427.524 ↓ 1.3 93,388 1

Bitmap Heap Scan on distinctclosedateowner (cost=3,130.96..7,261,185.71 rows=74,199 width=4) (actual time=22.328..1,427.524 rows=93,388 loops=1)

  • Recheck Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
  • Filter: (SubPlan 4)
  • Rows Removed by Filter: 103372
  • Heap Blocks: exact=17913
12. 19.227 19.227 ↓ 1.3 196,760 1

Bitmap Index Scan on ix_closedate_distinctclosedateowner (cost=0.00..3,112.41 rows=148,398 width=0) (actual time=19.227..19.227 rows=196,760 loops=1)

  • Index Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
13.          

SubPlan (forBitmap Heap Scan)

14. 196.552 1,377.320 ↓ 0.0 0 196,760

Nested Loop Semi Join (cost=0.98..96.42 rows=7 width=4) (actual time=0.007..0.007 rows=0 loops=196,760)

15. 787.040 787.040 ↑ 7.0 1 196,760

Index Scan using ix_t166_userrolehierarchy_sid_ancestor_end_start_timestamp on t166_userrolehierarchy (cost=0.56..37.25 rows=7 width=8) (actual time=0.003..0.004 rows=1 loops=196,760)

  • Index Cond: ((sid = distinctclosedateowner.ownerid) AND (c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
16. 393.728 393.728 ↓ 0.0 0 196,864

Index Scan using ix_t67_userroledim_sid_end_start_timestamp_partial on t67_userroledim (cost=0.42..8.44 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=196,864)

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

CTE bucketac

18. 19.678 7,181.172 ↑ 145,861.8 19 1

GroupAggregate (cost=1,692,566,300.19..1,692,691,012.07 rows=2,771,375 width=116) (actual time=7,150.763..7,181.172 rows=19 loops=1)

  • Group Key: t237_oppfact.c844_opp_forecastcategory, (CASE WHEN (o172_ft1.c1 IS NULL) THEN 'deleted'::text WHEN ((o172_ft1.c2 < 20181101) OR (o172_ft1.c2 > 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 = o172_ft1.c7) THEN 'unchanged'::text WHEN (t234_opportunityforecastcategorypicklistdim_2.correlated_app_order > o172_ft1.c7) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
19. 125.760 7,161.494 ↑ 30.0 92,464 1

Sort (cost=1,692,565,779.38..1,692,572,707.82 rows=2,771,375 width=60) (actual time=7,150.706..7,161.494 rows=92,464 loops=1)

  • Sort Key: t237_oppfact.c844_opp_forecastcategory, (CASE WHEN (o172_ft1.c1 IS NULL) THEN 'deleted'::text WHEN ((o172_ft1.c2 < 20181101) OR (o172_ft1.c2 > 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 = o172_ft1.c7) THEN 'unchanged'::text WHEN (t234_opportunityforecastcategorypicklistdim_2.correlated_app_order > o172_ft1.c7) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
  • Sort Method: external merge Disk: 4504kB
20. 162.042 7,035.734 ↑ 30.0 92,464 1

Nested Loop Left Join (cost=4,674,664.58..1,692,060,815.18 rows=2,771,375 width=60) (actual time=3,299.126..7,035.734 rows=92,464 loops=1)

21. 136.445 3,543.596 ↑ 6.0 92,464 1

GroupAggregate (cost=4,671,122.13..4,686,364.69 rows=554,275 width=28) (actual time=3,295.981..3,543.596 rows=92,464 loops=1)

  • Group Key: t237_oppfact.sid, t237_oppfact.c844_opp_forecastcategory, t234_opportunityforecastcategorypicklistdim_2.correlated_app_order
22. 275.161 3,407.151 ↑ 1.3 418,251 1

Sort (cost=4,671,122.13..4,672,507.82 rows=554,275 width=32) (actual time=3,295.967..3,407.151 rows=418,251 loops=1)

  • Sort Key: t237_oppfact.sid, t237_oppfact.c844_opp_forecastcategory, t234_opportunityforecastcategorypicklistdim_2.correlated_app_order
  • Sort Method: external merge Disk: 17136kB
23. 82.019 3,131.990 ↑ 1.3 418,251 1

Merge Right Join (cost=4,588,384.41..4,604,982.12 rows=554,275 width=32) (actual time=2,850.112..3,131.990 rows=418,251 loops=1)

  • Merge Cond: ((t539_clari_conversion_rate.sid = t236_opp_line_item_fact.c821_opp_currency_code) AND (t539_clari_conversion_rate.rate_date = t237_oppfact.c836_opp_close_date))
24. 6.664 67.976 ↑ 1.0 102,851 1

Merge Append (cost=0.31..9,553.62 rows=103,231 width=16) (actual time=0.024..67.976 rows=102,851 loops=1)

  • Sort Key: t539_clari_conversion_rate.sid, t539_clari_conversion_rate.rate_date
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: t539_clari_conversion_rate.sid, t539_clari_conversion_rate.rate_date
  • Sort Method: quicksort Memory: 25kB
26. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on t539_clari_conversion_rate (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. 61.307 61.307 ↑ 1.0 102,851 1

Index Scan using t539_clari_conversion_rate_usd_sidratedateunique on t539_clari_conversion_rate_usd (cost=0.29..8,263.21 rows=103,230 width=16) (actual time=0.019..61.307 rows=102,851 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
28. 36.042 2,981.995 ↑ 1.3 418,251 1

Materialize (cost=4,588,384.10..4,591,155.48 rows=554,275 width=32) (actual time=2,847.982..2,981.995 rows=418,251 loops=1)

29. 265.885 2,945.953 ↑ 1.3 418,251 1

Sort (cost=4,588,384.10..4,589,769.79 rows=554,275 width=32) (actual time=2,847.979..2,945.953 rows=418,251 loops=1)

  • Sort Key: t236_opp_line_item_fact.c821_opp_currency_code, t237_oppfact.c836_opp_close_date
  • Sort Method: external merge Disk: 17144kB
30. 62.924 2,680.068 ↑ 1.3 418,251 1

Nested Loop (cost=541.61..4,522,244.09 rows=554,275 width=32) (actual time=1,509.486..2,680.068 rows=418,251 loops=1)

31. 23.196 1,842.356 ↓ 2.0 110,684 1

Hash Left Join (cost=541.05..12,870.87 rows=55,388 width=16) (actual time=1,509.471..1,842.356 rows=110,684 loops=1)

  • Hash Cond: (t237_oppfact.c844_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim_2.sid)
32. 12.611 1,819.151 ↓ 2.0 110,684 1

Nested Loop (cost=521.37..12,089.61 rows=55,388 width=12) (actual time=1,509.448..1,819.151 rows=110,684 loops=1)

33. 5.481 1,512.492 ↓ 40.8 8,168 1

HashAggregate (cost=520.51..522.51 rows=200 width=4) (actual time=1,508.929..1,512.492 rows=8,168 loops=1)

  • Group Key: ownercte_1.c1
34. 1,507.011 1,507.011 ↑ 2.8 8,168 1

CTE Scan on ownercte ownercte_1 (cost=0.00..462.68 rows=23,134 width=4) (actual time=1,490.707..1,507.011 rows=8,168 loops=1)

35. 293.572 294.048 ↓ 2.3 14 8,168

Index Scan using ix_t237_oppfact_owner_closedate_timestamp on t237_oppfact (cost=0.86..57.78 rows=6 width=16) (actual time=0.011..0.036 rows=14 loops=8,168)

  • Index Cond: ((c832_opp_ownerid = ownercte_1.c1) 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 9)))
  • Rows Removed by Filter: 3
36.          

SubPlan (forIndex Scan)

37. 0.476 0.476 ↑ 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.476 rows=13 loops=1)

38. 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
39. 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.004..0.004 rows=5 loops=1)

40. 774.788 774.788 ↑ 69.5 4 110,684

Index Scan using t236_opp_line_item_fact_561_timestamp on t236_opp_line_item_fact (cost=0.56..78.63 rows=278 width=20) (actual time=0.005..0.007 rows=4 loops=110,684)

  • Index Cond: ((c801_opportunity_sid = t237_oppfact.sid) AND (start_stamp <= '1541055600000'::bigint) AND (end_stamp > '1541055600000'::bigint))
  • Filter: (NOT deleted)
41. 92.464 3,328.704 ↑ 5.0 1 92,464

Subquery Scan on o172_ft1 (cost=3,021.64..3,044.14 rows=5 width=28) (actual time=0.036..0.036 rows=1 loops=92,464)

  • Filter: (t237_oppfact.sid = o172_ft1.c1)
42. 369.856 3,236.240 ↑ 1,000.0 1 92,464

HashAggregate (cost=3,021.64..3,031.64 rows=1,000 width=36) (actual time=0.034..0.035 rows=1 loops=92,464)

  • Group Key: t237_oppfact_1.sid, t237_oppfact_1.c836_opp_close_date, t237_oppfact_1.c832_opp_ownerid, t237_oppfact_1.c843_opp_stagename, t234_opportunityforecastcategorypicklistdim_3.correlated_app_order
43. 42.792 2,866.384 ↑ 277.0 5 92,464

Nested Loop (cost=20.81..2,997.41 rows=1,385 width=36) (actual time=0.014..0.031 rows=5 loops=92,464)

44. 184.917 1,941.744 ↑ 55.4 5 92,464

Hash Left Join (cost=20.24..2,956.66 rows=277 width=24) (actual time=0.009..0.021 rows=5 loops=92,464)

  • Hash Cond: (t236_opp_line_item_fact_1.c820_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim_3.sid)
45. 227.720 1,756.816 ↑ 55.4 5 92,464

Nested Loop Left Join (cost=0.56..2,933.31 rows=277 width=24) (actual time=0.008..0.019 rows=5 loops=92,464)

46. 647.248 647.248 ↑ 55.4 5 92,464

Index Scan using t236_opp_line_item_fact_561_timestamp on t236_opp_line_item_fact t236_opp_line_item_fact_1 (cost=0.56..1,024.52 rows=277 width=24) (actual time=0.005..0.007 rows=5 loops=92,464)

  • Index Cond: ((c801_opportunity_sid = t237_oppfact.sid) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
  • Filter: (NOT deleted)
47. 0.000 881.848 ↑ 2.0 1 440,924

Append (cost=0.00..6.87 rows=2 width=16) (actual time=0.002..0.002 rows=1 loops=440,924)

48. 0.000 0.000 ↓ 0.0 0 440,924

Seq Scan on t539_clari_conversion_rate t539_clari_conversion_rate_1 (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=440,924)

  • Filter: ((to_iso_code = 'USD'::text) AND (t236_opp_line_item_fact_1.c821_opp_currency_code = sid) AND (t236_opp_line_item_fact_1.c805_opp_close_date = rate_date))
49. 881.848 881.848 ↑ 1.0 1 440,924

Index Scan using t539_clari_conversion_rate_usd_sidratedateunique on t539_clari_conversion_rate_usd t539_clari_conversion_rate_usd_1 (cost=0.29..6.87 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=440,924)

  • Index Cond: ((t236_opp_line_item_fact_1.c821_opp_currency_code = sid) AND (t236_opp_line_item_fact_1.c805_opp_close_date = rate_date))
  • Filter: (to_iso_code = 'USD'::text)
50. 0.004 0.011 ↑ 86.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
51. 0.007 0.007 ↑ 86.0 5 1

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

52. 142.136 881.848 ↑ 5.0 1 440,924

Materialize (cost=0.56..23.44 rows=5 width=16) (actual time=0.001..0.002 rows=1 loops=440,924)

53. 739.712 739.712 ↑ 5.0 1 92,464

Index Scan using t237_oppfact_sidendstampunique on t237_oppfact t237_oppfact_1 (cost=0.56..23.42 rows=5 width=16) (actual time=0.004..0.008 rows=1 loops=92,464)

  • Index Cond: ((sid = t237_oppfact.sid) AND (end_stamp > '1542009599999'::bigint))
  • Filter: ((NOT deleted) AND (start_stamp <= '1542009599999'::bigint))
  • Rows Removed by Filter: 2
54.          

SubPlan (forNested Loop Left Join)

55. 0.758 0.758 ↑ 2.8 8,168 1

CTE Scan on ownercte (cost=0.00..462.68 rows=23,134 width=4) (actual time=0.001..0.758 rows=8,168 loops=1)

56. 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.012..0.348 rows=6 loops=1)

57. 0.286 0.286 ↑ 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.286 rows=7 loops=1)

58.          

CTE bucketec

59. 2.449 3,267.870 ↑ 94,809.4 25 1

GroupAggregate (cost=2,282,212,050.10..2,282,312,785.13 rows=2,370,236 width=140) (actual time=3,264.640..3,267.870 rows=25 loops=1)

  • Group Key: (CASE WHEN (oli174_ft1.c1 IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 11)) THEN CASE WHEN (oli174_ft1.c3 > 20190131) THEN 'pulledInBucket'::text WHEN (oli174_ft1.c3 < 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
60. 7.007 3,265.421 ↑ 203.9 11,625 1

Sort (cost=2,282,211,529.00..2,282,217,454.59 rows=2,370,236 width=84) (actual time=3,264.629..3,265.421 rows=11,625 loops=1)

  • Sort Key: (CASE WHEN (oli174_ft1.c1 IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 11)) THEN CASE WHEN (oli174_ft1.c3 > 20190131) THEN 'pulledInBucket'::text WHEN (oli174_ft1.c3 < 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: 1297kB
61. 32.704 3,258.414 ↑ 203.9 11,625 1

Nested Loop Left Join (cost=1,022,472.07..2,281,733,719.31 rows=2,370,236 width=84) (actual time=1,625.468..3,258.414 rows=11,625 loops=1)

  • Filter: ((oli174_ft1.c1 IS NULL) OR (oli174_ft1.c3 < 20181101) OR (oli174_ft1.c3 > 20190131) OR (NOT (hashed SubPlan 15)))
  • Rows Removed by Filter: 103949
62. 182.776 1,953.024 ↑ 18.9 115,574 1

GroupAggregate (cost=1,020,395.72..1,080,600.92 rows=2,189,280 width=28) (actual time=1,622.981..1,953.024 rows=115,574 loops=1)

  • Group Key: t236_opp_line_item_fact_2.c801_opportunity_sid, t236_opp_line_item_fact_2.c819_opp_stagename, t236_opp_line_item_fact_2.c820_opp_forecastcategory
63. 411.215 1,770.248 ↑ 3.3 662,714 1

Sort (cost=1,020,395.72..1,025,868.92 rows=2,189,280 width=32) (actual time=1,622.964..1,770.248 rows=662,714 loops=1)

  • Sort Key: t236_opp_line_item_fact_2.c801_opportunity_sid, t236_opp_line_item_fact_2.c819_opp_stagename, t236_opp_line_item_fact_2.c820_opp_forecastcategory
  • Sort Method: external merge Disk: 27184kB
64. 120.937 1,359.033 ↑ 3.3 662,714 1

Merge Right Join (cost=645,950.38..685,080.36 rows=2,189,280 width=32) (actual time=932.186..1,359.033 rows=662,714 loops=1)

  • Merge Cond: ((t539_clari_conversion_rate_2.sid = t236_opp_line_item_fact_2.c821_opp_currency_code) AND (t539_clari_conversion_rate_2.rate_date = t236_opp_line_item_fact_2.c805_opp_close_date))
65. 6.595 70.521 ↑ 1.0 103,093 1

Merge Append (cost=0.31..9,553.62 rows=103,231 width=16) (actual time=0.029..70.521 rows=103,093 loops=1)

  • Sort Key: t539_clari_conversion_rate_2.sid, t539_clari_conversion_rate_2.rate_date
66. 0.005 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: t539_clari_conversion_rate_2.sid, t539_clari_conversion_rate_2.rate_date
  • Sort Method: quicksort Memory: 25kB
67. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on t539_clari_conversion_rate t539_clari_conversion_rate_2 (cost=0.00..0.00 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
68. 63.919 63.919 ↑ 1.0 103,093 1

Index Scan using t539_clari_conversion_rate_usd_sidratedateunique on t539_clari_conversion_rate_usd t539_clari_conversion_rate_usd_2 (cost=0.29..8,263.21 rows=103,230 width=16) (actual time=0.021..63.919 rows=103,093 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
69. 89.303 1,167.575 ↑ 3.3 662,714 1

Materialize (cost=645,950.07..656,896.47 rows=2,189,280 width=32) (actual time=930.014..1,167.575 rows=662,714 loops=1)

70. 408.950 1,078.272 ↑ 3.3 662,714 1

Sort (cost=645,950.07..651,423.27 rows=2,189,280 width=32) (actual time=930.010..1,078.272 rows=662,714 loops=1)

  • Sort Key: t236_opp_line_item_fact_2.c821_opp_currency_code, t236_opp_line_item_fact_2.c805_opp_close_date
  • Sort Method: external merge Disk: 27184kB
71. 141.266 669.322 ↑ 3.3 662,714 1

Nested Loop (cost=521.08..310,634.72 rows=2,189,280 width=32) (actual time=2.562..669.322 rows=662,714 loops=1)

72. 4.649 5.304 ↓ 40.8 8,168 1

HashAggregate (cost=520.51..522.51 rows=200 width=4) (actual time=2.534..5.304 rows=8,168 loops=1)

  • Group Key: ownercte_4.c1
73. 0.655 0.655 ↑ 2.8 8,168 1

CTE Scan on ownercte ownercte_4 (cost=0.00..462.68 rows=23,134 width=4) (actual time=0.001..0.655 rows=8,168 loops=1)

74. 522.752 522.752 ↑ 4.7 81 8,168

Index Scan using ix_t236_opp_line_item_fact_owner_closedate_timestamp on t236_opp_line_item_fact t236_opp_line_item_fact_2 (cost=0.56..1,546.72 rows=384 width=36) (actual time=0.009..0.064 rows=81 loops=8,168)

  • Index Cond: ((c804_opp_ownerid = ownercte_4.c1) AND (c805_opp_close_date >= 20181101) AND (c805_opp_close_date <= 20190131) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
75. 115.574 1,271.314 ↑ 1.0 1 115,574

Subquery Scan on oli174_ft1 (cost=1,034.74..1,041.69 rows=1 width=16) (actual time=0.010..0.011 rows=1 loops=115,574)

  • Filter: (t236_opp_line_item_fact_2.c801_opportunity_sid = oli174_ft1.c1)
76. 115.574 1,155.740 ↑ 278.0 1 115,574

Group (cost=1,034.74..1,038.22 rows=278 width=16) (actual time=0.009..0.010 rows=1 loops=115,574)

  • Group Key: t236_opp_line_item_fact_3.c801_opportunity_sid, t236_opp_line_item_fact_3.c819_opp_stagename, t236_opp_line_item_fact_3.c805_opp_close_date, t236_opp_line_item_fact_3.c804_opp_ownerid
77. 231.148 1,040.166 ↑ 55.6 5 115,574

Sort (cost=1,034.74..1,035.44 rows=278 width=16) (actual time=0.009..0.009 rows=5 loops=115,574)

  • Sort Key: t236_opp_line_item_fact_3.c819_opp_stagename, t236_opp_line_item_fact_3.c805_opp_close_date, t236_opp_line_item_fact_3.c804_opp_ownerid
  • Sort Method: quicksort Memory: 25kB
78. 809.018 809.018 ↑ 55.6 5 115,574

Index Scan using t236_opp_line_item_fact_561_timestamp on t236_opp_line_item_fact t236_opp_line_item_fact_3 (cost=0.56..1,023.46 rows=278 width=16) (actual time=0.005..0.007 rows=5 loops=115,574)

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

SubPlan (forNested Loop Left Join)

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

81. 0.623 0.623 ↑ 2.8 8,168 1

CTE Scan on ownercte ownercte_2 (cost=0.00..462.68 rows=23,134 width=4) (actual time=0.001..0.623 rows=8,168 loops=1)

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

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

84. 0.745 0.745 ↑ 2.8 8,168 1

CTE Scan on ownercte ownercte_3 (cost=0.00..462.68 rows=23,134 width=4) (actual time=0.000..0.745 rows=8,168 loops=1)

85. 0.024 7,181.219 ↑ 145,861.8 19 1

Hash Join (cost=19.68..93,553.58 rows=2,771,375 width=144) (actual time=7,150.792..7,181.219 rows=19 loops=1)

  • Hash Cond: (cte0.c4 = t234_opportunityforecastcategorypicklistdim.sid)
86. 7,181.183 7,181.183 ↑ 145,861.8 19 1

CTE Scan on bucketac cte0 (cost=0.00..55,427.50 rows=2,771,375 width=116) (actual time=7,150.765..7,181.183 rows=19 loops=1)

87. 0.003 0.012 ↑ 86.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
88. 0.009 0.009 ↑ 86.0 5 1

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

89. 0.014 3,267.931 ↑ 94,809.4 25 1

Subquery Scan on *SELECT* 2 (cost=19.68..109,643.09 rows=2,370,236 width=144) (actual time=3,264.675..3,267.931 rows=25 loops=1)

90. 0.020 3,267.917 ↑ 94,809.4 25 1

Hash Join (cost=19.68..80,015.14 rows=2,370,236 width=168) (actual time=3,264.669..3,267.917 rows=25 loops=1)

  • Hash Cond: (cte0_1.c6 = t234_opportunityforecastcategorypicklistdim_1.sid)
91. 3,267.884 3,267.884 ↑ 94,809.4 25 1

CTE Scan on bucketec cte0_1 (cost=0.00..47,404.72 rows=2,370,236 width=140) (actual time=3,264.643..3,267.884 rows=25 loops=1)

92. 0.005 0.013 ↑ 86.0 5 1

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

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

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