explain.depesz.com

PostgreSQL's explain analyze made readable

Result: K6qW

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 13,651.776 ↑ 54,258.6 44 1

Append (cost=3,418,389,548.14..3,418,497,648.46 rows=2,387,378 width=144) (actual time=7,930.848..13,651.776 rows=44 loops=1)

2.          

CTE picklist_0

3. 0.365 0.365 ↑ 1.0 6 1

Seq Scan on t233_opportunitystagenamepicklistdim (cost=0.00..70.54 rows=6 width=4) (actual time=0.009..0.365 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.297 0.297 ↑ 1.0 7 1

Seq Scan on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_1 (cost=0.00..72.08 rows=7 width=4) (actual time=0.005..0.297 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.466 0.466 ↑ 1.0 13 1

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

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

10. 72.724 1,543.678 ↓ 1.3 93,388 1

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

  • Sort Key: distinctclosedateowner.ownerid
  • Sort Method: external merge Disk: 1280kB
11. 73.391 1,470.954 ↓ 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=23.474..1,470.954 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. 20.243 20.243 ↓ 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=20.243..20.243 rows=196,760 loops=1)

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

SubPlan (forBitmap Heap Scan)

14. 196.556 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.724 393.724 ↓ 0.0 0 196,862

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,862)

  • 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.671 7,961.524 ↑ 49,170.3 19 1

GroupAggregate (cost=2,016,828,623.10..2,016,870,663.68 rows=934,235 width=116) (actual time=7,930.819..7,961.524 rows=19 loops=1)

  • Group Key: t236_opp_line_item_fact.c820_opp_forecastcategory, (CASE WHEN (oli174_ft1.c1 IS NULL) THEN 'deleted'::text WHEN ((oli174_ft1.c2 < 20181101) OR (oli174_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 = oli174_ft1.c7) THEN 'unchanged'::text WHEN (t234_opportunityforecastcategorypicklistdim_2.correlated_app_order > oli174_ft1.c7) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
19. 112.745 7,941.853 ↑ 10.1 92,464 1

Sort (cost=2,016,828,102.29..2,016,830,437.88 rows=934,235 width=60) (actual time=7,930.763..7,941.853 rows=92,464 loops=1)

  • Sort Key: t236_opp_line_item_fact.c820_opp_forecastcategory, (CASE WHEN (oli174_ft1.c1 IS NULL) THEN 'deleted'::text WHEN ((oli174_ft1.c2 < 20181101) OR (oli174_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 = oli174_ft1.c7) THEN 'unchanged'::text WHEN (t234_opportunityforecastcategorypicklistdim_2.correlated_app_order > oli174_ft1.c7) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
  • Sort Method: external merge Disk: 4504kB
20. 79.257 7,829.108 ↑ 10.1 92,464 1

Nested Loop Left Join (cost=1,095,357.03..2,016,665,204.89 rows=934,235 width=60) (actual time=5,120.026..7,829.108 rows=92,464 loops=1)

21. 129.197 5,344.314 ↑ 9.3 92,464 1

GroupAggregate (cost=1,092,504.67..1,116,183.44 rows=861,046 width=28) (actual time=5,116.747..5,344.314 rows=92,464 loops=1)

  • Group Key: t236_opp_line_item_fact.c801_opportunity_sid, t236_opp_line_item_fact.c820_opp_forecastcategory, t234_opportunityforecastcategorypicklistdim_2.correlated_app_order
22. 268.989 5,215.117 ↑ 2.1 418,251 1

Sort (cost=1,092,504.67..1,094,657.29 rows=861,046 width=32) (actual time=5,116.732..5,215.117 rows=418,251 loops=1)

  • Sort Key: t236_opp_line_item_fact.c801_opportunity_sid, t236_opp_line_item_fact.c820_opp_forecastcategory, t234_opportunityforecastcategorypicklistdim_2.correlated_app_order
  • Sort Method: external merge Disk: 17136kB
23. 77.824 4,946.128 ↑ 2.1 418,251 1

Hash Left Join (cost=956,308.19..987,019.42 rows=861,046 width=32) (actual time=4,585.490..4,946.128 rows=418,251 loops=1)

  • Hash Cond: (t236_opp_line_item_fact.c820_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim_2.sid)
24. 80.877 4,868.292 ↑ 2.1 418,251 1

Merge Right Join (cost=956,288.51..975,526.59 rows=861,046 width=28) (actual time=4,585.462..4,868.292 rows=418,251 loops=1)

  • Merge Cond: ((t464_clari_conversion_rate.sid = t236_opp_line_item_fact.c821_opp_currency_code) AND (t464_clari_conversion_rate.rate_date = t236_opp_line_item_fact.c805_opp_close_date))
25. 6.991 62.852 ↑ 1.0 102,766 1

Merge Append (cost=0.31..7,183.64 rows=103,189 width=16) (actual time=0.022..62.852 rows=102,766 loops=1)

  • Sort Key: t464_clari_conversion_rate.sid, t464_clari_conversion_rate.rate_date
26. 0.005 0.006 ↓ 0.0 0 1

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

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

Seq Scan on t464_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)
28. 55.855 55.855 ↑ 1.0 102,766 1

Index Scan using t464_clari_conversion_rate_usd_sidratedateunique on t464_clari_conversion_rate_usd (cost=0.29..5,893.75 rows=103,188 width=16) (actual time=0.016..55.855 rows=102,766 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
29. 37.087 4,724.563 ↑ 2.1 418,251 1

Materialize (cost=956,288.20..960,593.43 rows=861,046 width=28) (actual time=4,583.674..4,724.563 rows=418,251 loops=1)

30. 280.333 4,687.476 ↑ 2.1 418,251 1

Sort (cost=956,288.20..958,440.81 rows=861,046 width=28) (actual time=4,583.671..4,687.476 rows=418,251 loops=1)

  • Sort Key: t236_opp_line_item_fact.c821_opp_currency_code, t236_opp_line_item_fact.c805_opp_close_date
  • Sort Method: external merge Disk: 15472kB
31. 65.594 4,407.143 ↑ 2.1 418,251 1

Nested Loop (cost=826.52..850,802.94 rows=861,046 width=28) (actual time=1,553.451..4,407.143 rows=418,251 loops=1)

32. 5.409 1,556.261 ↓ 40.8 8,168 1

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

  • Group Key: ownercte_1.c1
33. 1,550.852 1,550.852 ↑ 2.8 8,168 1

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

34. 1,927.179 2,785.288 ↑ 2.9 51 8,168

Bitmap Heap Scan on t236_opp_line_item_fact (cost=306.00..4,249.93 rows=147 width=32) (actual time=0.194..0.341 rows=51 loops=8,168)

  • 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: 328
  • Heap Blocks: exact=792931
35. 857.640 857.640 ↑ 2.9 380 8,168

Bitmap Index Scan on t236_opp_line_item_fact_564_timestamp (cost=0.00..305.68 rows=1,093 width=0) (actual time=0.105..0.105 rows=380 loops=8,168)

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

SubPlan (forBitmap Heap Scan)

37. 0.469 0.469 ↑ 1.0 13 1

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

38. 0.005 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
39. 0.007 0.007 ↑ 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.007 rows=5 loops=1)

40. 92.464 2,404.064 ↑ 1.0 1 92,464

Subquery Scan on oli174_ft1 (cost=2,331.55..2,340.77 rows=1 width=28) (actual time=0.026..0.026 rows=1 loops=92,464)

  • Filter: (t236_opp_line_item_fact.c801_opportunity_sid = oli174_ft1.c1)
41. 92.464 2,311.600 ↑ 217.0 1 92,464

GroupAggregate (cost=2,331.55..2,338.06 rows=217 width=36) (actual time=0.025..0.025 rows=1 loops=92,464)

  • Group Key: t236_opp_line_item_fact_1.c801_opportunity_sid, t236_opp_line_item_fact_1.c805_opp_close_date, t236_opp_line_item_fact_1.c804_opp_ownerid, t236_opp_line_item_fact_1.c819_opp_stagename, t234_opportunityforecastcategorypicklistdim_3.correlated_app_order
42. 277.392 2,219.136 ↑ 43.4 5 92,464

Sort (cost=2,331.55..2,332.09 rows=217 width=36) (actual time=0.023..0.024 rows=5 loops=92,464)

  • Sort Key: t236_opp_line_item_fact_1.c805_opp_close_date, t236_opp_line_item_fact_1.c804_opp_ownerid, t236_opp_line_item_fact_1.c819_opp_stagename, t234_opportunityforecastcategorypicklistdim_3.correlated_app_order
  • Sort Method: quicksort Memory: 25kB
43. 184.919 1,941.744 ↑ 43.4 5 92,464

Hash Left Join (cost=20.24..2,323.13 rows=217 width=36) (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)
44. 227.720 1,756.816 ↑ 43.4 5 92,464

Nested Loop Left Join (cost=0.56..2,300.56 rows=217 width=36) (actual time=0.008..0.019 rows=5 loops=92,464)

45. 647.248 647.248 ↑ 43.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..799.87 rows=217 width=32) (actual time=0.005..0.007 rows=5 loops=92,464)

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

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

47. 0.000 0.000 ↓ 0.0 0 440,924

Seq Scan on t464_clari_conversion_rate t464_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))
48. 881.848 881.848 ↑ 1.0 1 440,924

Index Scan using t464_clari_conversion_rate_usd_sidratedateunique on t464_clari_conversion_rate_usd t464_clari_conversion_rate_usd_1 (cost=0.29..6.90 rows=1 width=16) (actual time=0.001..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)
49. 0.004 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
50. 0.005 0.005 ↑ 86.0 5 1

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

51.          

SubPlan (forNested Loop Left Join)

52. 0.803 0.803 ↑ 2.8 8,168 1

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

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

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

55.          

CTE bucketec

56. 2.461 5,690.140 ↑ 58,125.7 25 1

GroupAggregate (cost=1,394,189,323.20..1,394,251,081.78 rows=1,453,143 width=140) (actual time=5,686.895..5,690.140 rows=25 loops=1)

  • Group Key: (CASE WHEN (oli174_ft1_1.c1 IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 11)) THEN CASE WHEN (oli174_ft1_1.c3 > 20190131) THEN 'pulledInBucket'::text WHEN (oli174_ft1_1.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
57. 7.040 5,687.679 ↑ 125.0 11,625 1

Sort (cost=1,394,188,802.10..1,394,192,434.96 rows=1,453,143 width=84) (actual time=5,686.880..5,687.679 rows=11,625 loops=1)

  • Sort Key: (CASE WHEN (oli174_ft1_1.c1 IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 11)) THEN CASE WHEN (oli174_ft1_1.c3 > 20190131) THEN 'pulledInBucket'::text WHEN (oli174_ft1_1.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
58. 48.129 5,680.639 ↑ 125.0 11,625 1

Nested Loop Left Join (cost=1,420,569.13..1,393,900,991.50 rows=1,453,143 width=84) (actual time=4,028.846..5,680.639 rows=11,625 loops=1)

  • Filter: ((oli174_ft1_1.c1 IS NULL) OR (oli174_ft1_1.c3 < 20181101) OR (oli174_ft1_1.c3 > 20190131) OR (NOT (hashed SubPlan 15)))
  • Rows Removed by Filter: 103949
59. 186.442 4,359.774 ↑ 15.0 115,574 1

GroupAggregate (cost=1,418,730.61..1,466,456.83 rows=1,735,499 width=28) (actual time=4,026.217..4,359.774 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
60. 416.769 4,173.332 ↑ 2.6 662,714 1

Sort (cost=1,418,730.61..1,423,069.36 rows=1,735,499 width=32) (actual time=4,026.203..4,173.332 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: 27176kB
61. 122.821 3,756.563 ↑ 2.6 662,714 1

Merge Right Join (cost=1,124,868.32..1,155,824.87 rows=1,735,499 width=32) (actual time=3,327.978..3,756.563 rows=662,714 loops=1)

  • Merge Cond: ((t464_clari_conversion_rate_2.sid = t236_opp_line_item_fact_2.c821_opp_currency_code) AND (t464_clari_conversion_rate_2.rate_date = t236_opp_line_item_fact_2.c805_opp_close_date))
62. 6.713 63.147 ↑ 1.0 103,008 1

Merge Append (cost=0.31..7,183.64 rows=103,189 width=16) (actual time=0.022..63.147 rows=103,008 loops=1)

  • Sort Key: t464_clari_conversion_rate_2.sid, t464_clari_conversion_rate_2.rate_date
63. 0.005 0.006 ↓ 0.0 0 1

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

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

Seq Scan on t464_clari_conversion_rate t464_clari_conversion_rate_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)
65. 56.428 56.428 ↑ 1.0 103,008 1

Index Scan using t464_clari_conversion_rate_usd_sidratedateunique on t464_clari_conversion_rate_usd t464_clari_conversion_rate_usd_2 (cost=0.29..5,893.75 rows=103,188 width=16) (actual time=0.014..56.428 rows=103,008 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
66. 91.806 3,570.595 ↑ 2.6 662,714 1

Materialize (cost=1,124,868.01..1,133,545.50 rows=1,735,499 width=32) (actual time=3,326.267..3,570.595 rows=662,714 loops=1)

67. 429.471 3,478.789 ↑ 2.6 662,714 1

Sort (cost=1,124,868.01..1,129,206.76 rows=1,735,499 width=32) (actual time=3,326.263..3,478.789 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
68. 135.773 3,049.318 ↑ 2.6 662,714 1

Nested Loop (cost=856.29..861,962.27 rows=1,735,499 width=32) (actual time=2.755..3,049.318 rows=662,714 loops=1)

69. 5.117 5.737 ↓ 40.8 8,168 1

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

  • Group Key: ownercte_4.c1
70. 0.620 0.620 ↑ 2.8 8,168 1

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

71. 1,984.824 2,907.808 ↑ 3.7 81 8,168

Bitmap Heap Scan on t236_opp_line_item_fact t236_opp_line_item_fact_2 (cost=335.77..4,304.23 rows=297 width=36) (actual time=0.188..0.356 rows=81 loops=8,168)

  • 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: 333
  • Heap Blocks: exact=853426
72. 922.984 922.984 ↑ 2.7 415 8,168

Bitmap Index Scan on t236_opp_line_item_fact_564_timestamp (cost=0.00..335.70 rows=1,101 width=0) (actual time=0.113..0.113 rows=415 loops=8,168)

  • Index Cond: ((c804_opp_ownerid = ownercte_4.c1) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
73. 115.574 1,271.314 ↑ 1.0 1 115,574

Subquery Scan on oli174_ft1_1 (cost=796.91..802.28 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_1.c1)
74. 0.000 1,155.740 ↑ 215.0 1 115,574

Group (cost=796.91..799.60 rows=215 width=16) (actual time=0.010..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
75. 346.722 1,155.740 ↑ 43.0 5 115,574

Sort (cost=796.91..797.45 rows=215 width=16) (actual time=0.009..0.010 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
76. 809.018 809.018 ↑ 43.0 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..788.58 rows=215 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)
77.          

SubPlan (forNested Loop Left Join)

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

79. 0.640 0.640 ↑ 2.8 8,168 1

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

80. 0.002 0.002 ↑ 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.002 rows=6 loops=1)

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

82. 0.778 0.778 ↑ 2.8 8,168 1

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

83. 0.020 7,961.572 ↑ 49,170.3 19 1

Hash Join (cost=19.68..31,550.11 rows=934,235 width=144) (actual time=7,930.847..7,961.572 rows=19 loops=1)

  • Hash Cond: (cte0.c4 = t234_opportunityforecastcategorypicklistdim.sid)
84. 7,961.540 7,961.540 ↑ 49,170.3 19 1

CTE Scan on bucketac cte0 (cost=0.00..18,684.70 rows=934,235 width=116) (actual time=7,930.823..7,961.540 rows=19 loops=1)

85. 0.005 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
86. 0.007 0.007 ↑ 86.0 5 1

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

87. 0.010 5,690.193 ↑ 58,125.7 25 1

Subquery Scan on *SELECT* 2 (cost=19.68..67,227.54 rows=1,453,143 width=144) (actual time=5,686.925..5,690.193 rows=25 loops=1)

88. 0.019 5,690.183 ↑ 58,125.7 25 1

Hash Join (cost=19.68..49,063.25 rows=1,453,143 width=168) (actual time=5,686.919..5,690.183 rows=25 loops=1)

  • Hash Cond: (cte0_1.c6 = t234_opportunityforecastcategorypicklistdim_1.sid)
89. 5,690.154 5,690.154 ↑ 58,125.7 25 1

CTE Scan on bucketec cte0_1 (cost=0.00..29,062.86 rows=1,453,143 width=140) (actual time=5,686.898..5,690.154 rows=25 loops=1)

90. 0.002 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
91. 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.006..0.008 rows=5 loops=1)