explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mU26

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 8,952.592 ↑ 88,441.9 44 1

Append (cost=5,563,386,398.90..5,563,562,594.84 rows=3,891,444 width=144) (actual time=5,665.584..8,952.592 rows=44 loops=1)

2.          

CTE picklist_0

3. 0.348 0.348 ↑ 1.0 6 1

Seq Scan on t233_opportunitystagenamepicklistdim (cost=0.00..70.54 rows=6 width=4) (actual time=0.010..0.348 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..72.08 rows=7 width=4) (actual time=0.006..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.497 0.497 ↑ 1.0 13 1

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

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

10. 74.931 1,546.308 ↓ 1.3 93,388 1

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

  • Sort Key: distinctclosedateowner.ownerid
  • Sort Method: external merge Disk: 1280kB
11. 73.757 1,471.377 ↓ 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.466..1,471.377 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.300 20.300 ↓ 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.300..20.300 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.834 5,696.280 ↑ 80,063.6 19 1

GroupAggregate (cost=3,273,737,356.72..3,273,805,811.08 rows=1,521,208 width=116) (actual time=5,665.551..5,696.280 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. 113.395 5,676.446 ↑ 16.5 92,464 1

Sort (cost=3,273,736,835.91..3,273,740,638.93 rows=1,521,208 width=60) (actual time=5,665.497..5,676.446 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. 96.711 5,563.051 ↑ 16.5 92,464 1

Nested Loop Left Join (cost=680,291.88..3,273,466,238.29 rows=1,521,208 width=60) (actual time=2,925.850..5,563.051 rows=92,464 loops=1)

21. 128.847 3,153.284 ↑ 11.9 92,464 1

GroupAggregate (cost=676,803.17..707,007.66 rows=1,098,345 width=28) (actual time=2,922.607..3,153.284 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. 269.709 3,024.437 ↑ 2.6 418,251 1

Sort (cost=676,803.17..679,549.03 rows=1,098,345 width=32) (actual time=2,922.591..3,024.437 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. 76.354 2,754.728 ↑ 2.6 418,251 1

Hash Left Join (cost=474,791.64..514,038.27 rows=1,098,345 width=32) (actual time=2,395.149..2,754.728 rows=418,251 loops=1)

  • Hash Cond: (t236_opp_line_item_fact.c820_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim_2.sid)
24. 79.617 2,678.361 ↑ 2.6 418,251 1

Merge Right Join (cost=474,771.96..499,420.86 rows=1,098,345 width=28) (actual time=2,395.121..2,678.361 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 = t236_opp_line_item_fact.c805_opp_close_date))
25. 6.541 68.483 ↑ 1.0 102,851 1

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

  • Sort Key: t539_clari_conversion_rate.sid, t539_clari_conversion_rate.rate_date
26. 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
27. 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)
28. 61.937 61.937 ↑ 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.020..61.937 rows=102,851 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
29. 35.757 2,530.261 ↑ 2.6 418,251 1

Materialize (cost=474,771.65..480,263.37 rows=1,098,345 width=28) (actual time=2,392.951..2,530.261 rows=418,251 loops=1)

30. 266.398 2,494.504 ↑ 2.6 418,251 1

Sort (cost=474,771.65..477,517.51 rows=1,098,345 width=28) (actual time=2,392.948..2,494.504 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. 57.098 2,228.106 ↑ 2.6 418,251 1

Nested Loop (cost=521.37..312,006.75 rows=1,098,345 width=28) (actual time=1,556.086..2,228.106 rows=418,251 loops=1)

32. 4.806 1,558.408 ↓ 40.8 8,168 1

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

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

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

34. 612.099 612.600 ↑ 3.8 51 8,168

Index Scan using ix_t236_opp_line_item_fact_owner_closedate_timestamp on t236_opp_line_item_fact (cost=0.86..1,555.50 rows=192 width=32) (actual time=0.012..0.075 rows=51 loops=8,168)

  • Index Cond: ((c804_opp_ownerid = ownercte_1.c1) AND (c805_opp_close_date >= 20181101) AND (c805_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: 23
35.          

SubPlan (forIndex Scan)

36. 0.501 0.501 ↑ 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.501 rows=13 loops=1)

37. 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
38. 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)

39. 92.464 2,311.600 ↑ 1.0 1 92,464

Subquery Scan on oli174_ft1 (cost=2,967.90..2,979.67 rows=1 width=28) (actual time=0.025..0.025 rows=1 loops=92,464)

  • Filter: (t236_opp_line_item_fact.c801_opportunity_sid = oli174_ft1.c1)
40. 92.464 2,219.136 ↑ 277.0 1 92,464

GroupAggregate (cost=2,967.90..2,976.21 rows=277 width=36) (actual time=0.024..0.024 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
41. 277.392 2,126.672 ↑ 55.4 5 92,464

Sort (cost=2,967.90..2,968.59 rows=277 width=36) (actual time=0.022..0.023 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
42. 184.918 1,849.280 ↑ 55.4 5 92,464

Hash Left Join (cost=20.24..2,956.66 rows=277 width=36) (actual time=0.008..0.020 rows=5 loops=92,464)

  • Hash Cond: (t236_opp_line_item_fact_1.c820_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim_3.sid)
43. 135.256 1,664.352 ↑ 55.4 5 92,464

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

44. 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=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)
45. 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)

46. 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))
47. 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.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)
48. 0.003 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
49. 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)

50.          

SubPlan (forNested Loop Left Join)

51. 0.815 0.815 ↑ 2.8 8,168 1

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

52. 0.352 0.352 ↑ 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.352 rows=6 loops=1)

53. 0.289 0.289 ↑ 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.289 rows=7 loops=1)

54.          

CTE bucketec

55. 2.420 3,256.187 ↑ 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,252.928..3,256.187 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
56. 7.154 3,253.767 ↑ 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,252.915..3,253.767 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
57. 26.764 3,246.613 ↑ 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,619.857..3,246.613 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
58. 181.211 1,947.187 ↑ 18.9 115,574 1

GroupAggregate (cost=1,020,395.72..1,080,600.92 rows=2,189,280 width=28) (actual time=1,617.404..1,947.187 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
59. 414.169 1,765.976 ↑ 3.3 662,714 1

Sort (cost=1,020,395.72..1,025,868.92 rows=2,189,280 width=32) (actual time=1,617.385..1,765.976 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
60. 120.579 1,351.807 ↑ 3.3 662,714 1

Merge Right Join (cost=645,950.38..685,080.36 rows=2,189,280 width=32) (actual time=922.485..1,351.807 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))
61. 6.593 69.819 ↑ 1.0 103,093 1

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

  • Sort Key: t539_clari_conversion_rate_2.sid, t539_clari_conversion_rate_2.rate_date
62. 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: t539_clari_conversion_rate_2.sid, t539_clari_conversion_rate_2.rate_date
  • Sort Method: quicksort Memory: 25kB
63. 0.001 0.001 ↓ 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.001..0.001 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
64. 63.220 63.220 ↑ 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.017..63.220 rows=103,093 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
65. 89.495 1,161.409 ↑ 3.3 662,714 1

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

66. 410.135 1,071.914 ↑ 3.3 662,714 1

Sort (cost=645,950.07..651,423.27 rows=2,189,280 width=32) (actual time=920.299..1,071.914 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
67. 142.004 661.779 ↑ 3.3 662,714 1

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

68. 4.523 5.191 ↓ 40.8 8,168 1

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

  • Group Key: ownercte_4.c1
69. 0.668 0.668 ↑ 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.668 rows=8,168 loops=1)

70. 514.584 514.584 ↑ 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.063 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
71. 115.574 1,271.314 ↑ 1.0 1 115,574

Subquery Scan on oli174_ft1_1 (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_1.c1)
72. 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
73. 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
74. 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)
75.          

SubPlan (forNested Loop Left Join)

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

77. 0.598 0.598 ↑ 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.598 rows=8,168 loops=1)

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

79. 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.001..0.002 rows=7 loops=1)

80. 0.746 0.746 ↑ 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.746 rows=8,168 loops=1)

81. 0.021 5,696.330 ↑ 80,063.6 19 1

Hash Join (cost=19.68..51,360.45 rows=1,521,208 width=144) (actual time=5,665.584..5,696.330 rows=19 loops=1)

  • Hash Cond: (cte0.c4 = t234_opportunityforecastcategorypicklistdim.sid)
82. 5,696.295 5,696.295 ↑ 80,063.6 19 1

CTE Scan on bucketac cte0 (cost=0.00..30,424.16 rows=1,521,208 width=116) (actual time=5,665.555..5,696.295 rows=19 loops=1)

83. 0.007 0.014 ↑ 86.0 5 1

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

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

85. 0.017 3,256.254 ↑ 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,252.966..3,256.254 rows=25 loops=1)

86. 0.024 3,256.237 ↑ 94,809.4 25 1

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

  • Hash Cond: (cte0_1.c6 = t234_opportunityforecastcategorypicklistdim_1.sid)
87. 3,256.202 3,256.202 ↑ 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,252.931..3,256.202 rows=25 loops=1)

88. 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
89. 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)