explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2HB7

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 28,790.120 ↑ 2,196.7 44 1

Append (cost=5,858,315,119.37..5,858,409,006.35 rows=96,656 width=144) (actual time=15,814.521..28,790.120 rows=44 loops=1)

2.          

CTE picklist_0

3. 0.031 0.031 ↑ 1.2 6 1

Index Only Scan using ix_gr_t233_opportunitystagenamepicklistdim on t233_opportunitystagenamepicklistdim (cost=0.28..25.78 rows=7 width=4) (actual time=0.011..0.031 rows=6 loops=1)

  • Index Cond: (correlated_value = ANY ('{"06 - Won, Deploy & Expand","6 - Won, Deploy & Expand","Closed - Won","06 - Won. Deploy & Expand",Won,"06 - Won, Deploy & Expand"}'::text[]))
  • Heap Fetches: 0
4.          

CTE picklist_1

5. 0.016 0.016 ↑ 1.0 7 1

Index Only Scan using ix_gr_t233_opportunitystagenamepicklistdim on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_1 (cost=0.28..26.06 rows=7 width=4) (actual time=0.004..0.016 rows=7 loops=1)

  • Index Cond: (correlated_value = ANY ('{"HPE Not Pursued",Lost,Close:Duplicate,"HP Not Pursued",Duplicate,Error,"Closed - Lost"}'::text[]))
  • Heap Fetches: 0
6.          

CTE picklist_4

7. 0.075 0.075 ↑ 1.1 13 1

Index Only Scan using ix_gr_t233_opportunitystagenamepicklistdim on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_2 (cost=0.28..43.84 rows=14 width=4) (actual time=0.026..0.075 rows=13 loops=1)

  • Index Cond: (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[]))
  • Heap Fetches: 0
8.          

CTE ownercte

9. 7.448 1,803.667 ↑ 2.9 8,739 1

Unique (cost=608,901.33..758,942.12 rows=25,092 width=4) (actual time=1,646.239..1,803.667 rows=8,739 loops=1)

10. 42.138 1,796.219 ↓ 1.1 132,351 1

Merge Join (cost=608,901.33..758,643.52 rows=119,442 width=4) (actual time=1,646.238..1,796.219 rows=132,351 loops=1)

  • Merge Cond: (t728_userrolehierarchy.sid = t629_distinctcdo_opp_closedate_ownerid.c2616_ownerid)
11. 16.491 777.850 ↑ 9.6 137,320 1

Unique (cost=420,315.97..426,880.11 rows=1,312,828 width=4) (actual time=743.261..777.850 rows=137,320 loops=1)

12. 78.082 761.359 ↑ 9.0 145,810 1

Sort (cost=420,315.97..423,598.04 rows=1,312,828 width=4) (actual time=743.261..761.359 rows=145,810 loops=1)

  • Sort Key: t728_userrolehierarchy.sid
  • Sort Method: external merge Disk: 1992kB
13. 8.673 683.277 ↑ 9.0 145,937 1

Append (cost=60,794.50..268,953.27 rows=1,312,828 width=4) (actual time=157.980..683.277 rows=145,937 loops=1)

14. 204.983 661.291 ↑ 10.0 131,217 1

Hash Join (cost=60,794.50..244,439.61 rows=1,309,460 width=4) (actual time=157.979..661.291 rows=131,217 loops=1)

  • Hash Cond: (t728_userrolehierarchy.c2842_user_role_id = t67_userroledim.sid)
15. 307.715 443.459 ↓ 1.0 1,338,682 1

Bitmap Heap Scan on t728_userrolehierarchy (cost=39,434.52..203,485.42 rows=1,309,460 width=8) (actual time=143.599..443.459 rows=1,338,682 loops=1)

  • Recheck Cond: ((c2843_ancestor_role_id = 436) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=40597
16. 135.744 135.744 ↓ 1.0 1,339,843 1

Bitmap Index Scan on idx_t728_userrolehierarchy_c2843_ancestor_role_id_end_s36994704 (cost=0.00..39,107.16 rows=1,309,460 width=0) (actual time=135.744..135.744 rows=1,339,843 loops=1)

  • Index Cond: ((c2843_ancestor_role_id = 436) AND (end_stamp = '32503680000000'::bigint))
17. 1.513 12.849 ↑ 9.1 8,424 1

Hash (cost=20,098.76..20,098.76 rows=76,817 width=4) (actual time=12.849..12.849 rows=8,424 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 1178kB
18. 7.021 11.336 ↑ 9.1 8,424 1

Bitmap Heap Scan on t67_userroledim (cost=4,145.56..20,098.76 rows=76,817 width=4) (actual time=5.196..11.336 rows=8,424 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: 7043
  • Heap Blocks: exact=2254
19. 4.315 4.315 ↑ 5.0 15,482 1

Bitmap Index Scan on ix_t67_userroledim_sid_end_start_timestamp_partial (cost=0.00..4,126.35 rows=77,840 width=0) (actual time=4.315..4.315 rows=15,482 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
20. 13.313 13.313 ↓ 4.4 14,720 1

Index Scan using ix_t12_userdim_usertype_nullrole_end_start on t12_userdim (cost=0.29..11,385.38 rows=3,368 width=4) (actual time=0.283..13.313 rows=14,720 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
21. 26.228 976.231 ↓ 1.2 286,250 1

Materialize (cost=188,585.36..189,779.79 rows=238,885 width=4) (actual time=901.708..976.231 rows=286,250 loops=1)

22. 165.125 950.003 ↓ 1.2 286,250 1

Sort (cost=188,585.36..189,182.57 rows=238,885 width=4) (actual time=901.702..950.003 rows=286,250 loops=1)

  • Sort Key: t629_distinctcdo_opp_closedate_ownerid.c2616_ownerid
  • Sort Method: external merge Disk: 3904kB
23. 784.878 784.878 ↓ 1.2 286,250 1

Seq Scan on t629_distinctcdo_opp_closedate_ownerid (cost=0.00..163,976.82 rows=238,885 width=4) (actual time=0.149..784.878 rows=286,250 loops=1)

  • Filter: ((c2620_closedate >= 20181101) AND (c2620_closedate <= 20190131))
  • Rows Removed by Filter: 6192650
24.          

CTE bucketac

25. 20.126 15,845.877 ↑ 94,629.8 19 1

GroupAggregate (cost=3,831,876,531.01..3,831,957,439.48 rows=1,797,966 width=116) (actual time=15,814.495..15,845.877 rows=19 loops=1)

  • Group Key: oli174_ft0.c4, (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 5) THEN CASE WHEN (hashed SubPlan 6) THEN 'won'::text WHEN (hashed SubPlan 7) THEN 'lost'::text ELSE CASE WHEN (oli174_ft0.c5 = oli174_ft1.c7) THEN 'unchanged'::text WHEN (oli174_ft0.c5 > oli174_ft1.c7) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
26. 119.819 15,825.751 ↑ 19.4 92,561 1

Sort (cost=3,831,875,966.12..3,831,880,461.04 rows=1,797,966 width=60) (actual time=15,814.441..15,825.751 rows=92,561 loops=1)

  • Sort Key: oli174_ft0.c4, (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 5) THEN CASE WHEN (hashed SubPlan 6) THEN 'won'::text WHEN (hashed SubPlan 7) THEN 'lost'::text ELSE CASE WHEN (oli174_ft0.c5 = oli174_ft1.c7) THEN 'unchanged'::text WHEN (oli174_ft0.c5 > oli174_ft1.c7) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
  • Sort Method: external merge Disk: 4504kB
27. 97.733 15,705.932 ↑ 19.4 92,561 1

Nested Loop Left Join (cost=3,906,990.50..3,831,553,971.03 rows=1,797,966 width=60) (actual time=11,433.245..15,705.932 rows=92,561 loops=1)

28. 49.512 12,830.294 ↑ 11.7 92,561 1

Hash Join (cost=3,902,916.65..4,139,205.17 rows=1,086,384 width=28) (actual time=11,430.332..12,830.294 rows=92,561 loops=1)

  • Hash Cond: (oli174_ft0.c7 = ownercte_1.c1)
29. 49.781 10,970.932 ↑ 10.5 206,862 1

Subquery Scan on oli174_ft0 (cost=3,902,347.58..4,108,760.54 rows=2,172,768 width=32) (actual time=9,620.472..10,970.932 rows=206,862 loops=1)

  • Filter: (NOT (hashed SubPlan 8))
  • Rows Removed by Filter: 28223
30. 731.250 10,921.073 ↑ 18.5 235,085 1

GroupAggregate (cost=3,902,347.27..4,054,441.03 rows=4,345,536 width=40) (actual time=9,620.370..10,921.073 rows=235,085 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, t236_opp_line_item_fact.c805_opp_close_date, t236_opp_line_item_fact.c804_opp_ownerid, t236_opp_line_item_fact.c819_opp_stagename
31. 2,241.868 10,189.823 ↑ 1.7 2,502,259 1

Sort (cost=3,902,347.27..3,913,211.11 rows=4,345,536 width=44) (actual time=9,620.353..10,189.823 rows=2,502,259 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, t236_opp_line_item_fact.c805_opp_close_date, t236_opp_line_item_fact.c804_opp_ownerid, t236_opp_line_item_fact.c819_opp_stagename
  • Sort Method: external merge Disk: 141920kB
32. 485.863 7,947.955 ↑ 1.7 2,502,259 1

Hash Left Join (cost=3,031,621.41..3,155,869.96 rows=4,345,536 width=44) (actual time=5,981.594..7,947.955 rows=2,502,259 loops=1)

  • Hash Cond: (t236_opp_line_item_fact.c820_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim_2.sid)
33. 448.512 7,462.079 ↑ 1.7 2,502,259 1

Merge Right Join (cost=3,031,620.29..3,098,380.31 rows=4,345,536 width=40) (actual time=5,981.565..7,462.079 rows=2,502,259 loops=1)

  • Merge Cond: ((t716_clari_conversion_rate.sid = t236_opp_line_item_fact.c821_opp_currency_code) AND (t716_clari_conversion_rate.rate_date = t236_opp_line_item_fact.c805_opp_close_date))
34. 6.838 48.694 ↑ 1.0 105,237 1

Merge Append (cost=0.31..7,779.93 rows=105,358 width=16) (actual time=0.028..48.694 rows=105,237 loops=1)

  • Sort Key: t716_clari_conversion_rate.sid, t716_clari_conversion_rate.rate_date
35. 0.005 0.008 ↓ 0.0 0 1

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

  • Sort Key: t716_clari_conversion_rate.sid, t716_clari_conversion_rate.rate_date
  • Sort Method: quicksort Memory: 25kB
36. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on t716_clari_conversion_rate (cost=0.00..0.00 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
37. 41.848 41.848 ↑ 1.0 105,237 1

Index Scan using t716_clari_conversion_rate_usd_sidratedateunique on t716_clari_conversion_rate_usd (cost=0.29..6,462.93 rows=105,357 width=16) (actual time=0.019..41.848 rows=105,237 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
38. 393.610 6,964.873 ↑ 1.7 2,502,259 1

Materialize (cost=3,031,619.98..3,053,347.66 rows=4,345,536 width=36) (actual time=5,980.352..6,964.873 rows=2,502,259 loops=1)

39. 2,055.527 6,571.263 ↑ 1.7 2,502,259 1

Sort (cost=3,031,619.98..3,042,483.82 rows=4,345,536 width=36) (actual time=5,980.349..6,571.263 rows=2,502,259 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: 112552kB
40. 3,624.069 4,515.736 ↑ 1.7 2,502,259 1

Bitmap Heap Scan on t236_opp_line_item_fact (cost=547,368.44..2,314,850.68 rows=4,345,536 width=36) (actual time=901.980..4,515.736 rows=2,502,259 loops=1)

  • Recheck Cond: ((c805_opp_close_date >= 20181101) AND (c805_opp_close_date <= 20190131) AND (start_stamp <= '1541055600000'::bigint) AND (end_stamp > '1541055600000'::bigint))
  • Rows Removed by Index Recheck: 9122136
  • Filter: ((NOT deleted) AND (c804_opp_ownerid IS NOT NULL))
  • Rows Removed by Filter: 85
  • Heap Blocks: exact=39926 lossy=295178
41. 891.667 891.667 ↑ 2.0 2,503,328 1

Bitmap Index Scan on ix_t236_opp_line_item_fact_closedate_timestamp (cost=0.00..546,282.05 rows=5,042,962 width=0) (actual time=891.667..891.667 rows=2,503,328 loops=1)

  • Index Cond: ((c805_opp_close_date >= 20181101) AND (c805_opp_close_date <= 20190131) AND (start_stamp <= '1541055600000'::bigint) AND (end_stamp > '1541055600000'::bigint))
42. 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
43. 0.008 0.008 ↑ 1.0 5 1

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

44.          

SubPlan (forSubquery Scan)

45. 0.078 0.078 ↑ 1.1 13 1

CTE Scan on picklist_4 cte0_4 (cost=0.00..0.28 rows=14 width=4) (actual time=0.029..0.078 rows=13 loops=1)

46. 1.192 1,809.850 ↓ 43.7 8,739 1

Hash (cost=566.57..566.57 rows=200 width=4) (actual time=1,809.850..1,809.850 rows=8,739 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 436kB
47. 3.696 1,808.658 ↓ 43.7 8,739 1

HashAggregate (cost=564.57..566.57 rows=200 width=4) (actual time=1,807.487..1,808.658 rows=8,739 loops=1)

  • Group Key: ownercte_1.c1
48. 1,804.962 1,804.962 ↑ 2.9 8,739 1

CTE Scan on ownercte ownercte_1 (cost=0.00..501.84 rows=25,092 width=4) (actual time=1,646.240..1,804.962 rows=8,739 loops=1)

49. 92.561 2,776.830 ↑ 2.0 1 92,561

Subquery Scan on oli174_ft1 (cost=3,508.96..3,523.03 rows=2 width=28) (actual time=0.030..0.030 rows=1 loops=92,561)

  • Filter: (oli174_ft0.c1 = oli174_ft1.c1)
50. 92.561 2,684.269 ↑ 331.0 1 92,561

GroupAggregate (cost=3,508.96..3,518.89 rows=331 width=36) (actual time=0.029..0.029 rows=1 loops=92,561)

  • 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
51. 370.244 2,591.708 ↑ 66.2 5 92,561

Sort (cost=3,508.96..3,509.79 rows=331 width=36) (actual time=0.027..0.028 rows=5 loops=92,561)

  • 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
52. 370.244 2,221.464 ↑ 66.2 5 92,561

Nested Loop Left Join (cost=0.56..3,495.11 rows=331 width=36) (actual time=0.009..0.024 rows=5 loops=92,561)

  • Join Filter: (t236_opp_line_item_fact_1.c820_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim_3.sid)
  • Rows Removed by Join Filter: 19
53. 317.909 1,851.220 ↑ 66.2 5 92,561

Nested Loop Left Join (cost=0.56..3,469.22 rows=331 width=36) (actual time=0.008..0.020 rows=5 loops=92,561)

54. 647.927 647.927 ↑ 66.2 5 92,561

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,242.33 rows=331 width=32) (actual time=0.005..0.007 rows=5 loops=92,561)

  • Index Cond: ((oli174_ft0.c1 = c801_opportunity_sid) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
  • Filter: (NOT deleted)
55. 0.000 885.384 ↑ 2.0 1 442,692

Append (cost=0.00..6.71 rows=2 width=16) (actual time=0.002..0.002 rows=1 loops=442,692)

56. 0.000 0.000 ↓ 0.0 0 442,692

Seq Scan on t716_clari_conversion_rate t716_clari_conversion_rate_1 (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=442,692)

  • 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))
57. 885.384 885.384 ↑ 1.0 1 442,692

Index Scan using t716_clari_conversion_rate_usd_sidratedateunique on t716_clari_conversion_rate_usd t716_clari_conversion_rate_usd_1 (cost=0.29..6.71 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=442,692)

  • 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)
58. 0.000 0.000 ↑ 1.0 5 442,692

Materialize (cost=0.00..1.07 rows=5 width=8) (actual time=0.000..0.000 rows=5 loops=442,692)

59. 0.007 0.007 ↑ 1.0 5 1

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

60.          

SubPlan (forNested Loop Left Join)

61. 1.019 1.019 ↑ 2.9 8,739 1

CTE Scan on ownercte (cost=0.00..501.84 rows=25,092 width=4) (actual time=0.001..1.019 rows=8,739 loops=1)

62. 0.034 0.034 ↑ 1.2 6 1

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

63. 0.022 0.022 ↑ 1.0 7 1

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

64.          

CTE bucketec

65. 2.429 12,944.126 ↑ 82,731.4 25 1

GroupAggregate (cost=2,025,510,738.81..2,025,598,640.97 rows=2,068,286 width=140) (actual time=12,940.863..12,944.126 rows=25 loops=1)

  • Group Key: (CASE WHEN (oli174_ft1_1.c1 IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 10)) THEN CASE WHEN (oli174_ft1_1.c3 > 20190131) THEN 'pulledInBucket'::text WHEN (oli174_ft1_1.c3 < 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
66. 7.019 12,941.697 ↑ 177.7 11,638 1

Sort (cost=2,025,510,173.61..2,025,515,344.33 rows=2,068,286 width=84) (actual time=12,940.849..12,941.697 rows=11,638 loops=1)

  • Sort Key: (CASE WHEN (oli174_ft1_1.c1 IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 10)) THEN CASE WHEN (oli174_ft1_1.c3 > 20190131) THEN 'pulledInBucket'::text WHEN (oli174_ft1_1.c3 < 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: 1298kB
67. 144.262 12,934.678 ↑ 177.7 11,638 1

Nested Loop Left Join (cost=3,879,285.32..2,025,095,264.37 rows=2,068,286 width=84) (actual time=10,025.792..12,934.678 rows=11,638 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 14)) OR (oli174_ft1_1.c4 IS NULL))
  • Rows Removed by Filter: 104077
68. 52.574 11,514.450 ↑ 14.4 115,715 1

Hash Join (cost=3,876,947.93..4,090,551.55 rows=1,662,487 width=28) (actual time=10,020.403..11,514.450 rows=115,715 loops=1)

  • Hash Cond: (t236_opp_line_item_fact_2.c804_opp_ownerid = ownercte_4.c1)
69. 810.675 11,456.756 ↑ 13.3 250,210 1

GroupAggregate (cost=3,876,378.86..4,011,014.34 rows=3,324,974 width=36) (actual time=10,015.270..11,456.756 rows=250,210 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, t236_opp_line_item_fact_2.c805_opp_close_date, t236_opp_line_item_fact_2.c804_opp_ownerid
70. 2,446.228 10,646.081 ↑ 1.6 2,767,952 1

Sort (cost=3,876,378.86..3,887,643.94 rows=4,506,033 width=40) (actual time=10,015.255..10,646.081 rows=2,767,952 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, t236_opp_line_item_fact_2.c805_opp_close_date, t236_opp_line_item_fact_2.c804_opp_ownerid
  • Sort Method: external merge Disk: 135320kB
71. 505.213 8,199.853 ↑ 1.6 2,767,952 1

Merge Right Join (cost=3,063,038.08..3,131,957.02 rows=4,506,033 width=40) (actual time=6,530.402..8,199.853 rows=2,767,952 loops=1)

  • Merge Cond: ((t716_clari_conversion_rate_2.sid = t236_opp_line_item_fact_2.c821_opp_currency_code) AND (t716_clari_conversion_rate_2.rate_date = t236_opp_line_item_fact_2.c805_opp_close_date))
72. 7.350 52.913 ↑ 1.0 105,237 1

Merge Append (cost=0.31..7,779.93 rows=105,358 width=16) (actual time=0.028..52.913 rows=105,237 loops=1)

  • Sort Key: t716_clari_conversion_rate_2.sid, t716_clari_conversion_rate_2.rate_date
73. 0.006 0.008 ↓ 0.0 0 1

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

  • Sort Key: t716_clari_conversion_rate_2.sid, t716_clari_conversion_rate_2.rate_date
  • Sort Method: quicksort Memory: 25kB
74. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on t716_clari_conversion_rate t716_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)
75. 45.555 45.555 ↑ 1.0 105,237 1

Index Scan using t716_clari_conversion_rate_usd_sidratedateunique on t716_clari_conversion_rate_usd t716_clari_conversion_rate_usd_2 (cost=0.29..6,462.93 rows=105,357 width=16) (actual time=0.018..45.555 rows=105,237 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
76. 442.864 7,641.727 ↑ 1.6 2,767,952 1

Materialize (cost=3,063,037.77..3,085,567.93 rows=4,506,033 width=36) (actual time=6,529.219..7,641.727 rows=2,767,952 loops=1)

77. 2,341.305 7,198.863 ↑ 1.6 2,767,952 1

Sort (cost=3,063,037.77..3,074,302.85 rows=4,506,033 width=36) (actual time=6,529.213..7,198.863 rows=2,767,952 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: 124560kB
78. 3,966.171 4,857.558 ↑ 1.6 2,767,952 1

Bitmap Heap Scan on t236_opp_line_item_fact t236_opp_line_item_fact_2 (cost=547,408.56..2,318,615.92 rows=4,506,033 width=36) (actual time=900.946..4,857.558 rows=2,767,952 loops=1)

  • Recheck Cond: ((c805_opp_close_date >= 20181101) AND (c805_opp_close_date <= 20190131) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
  • Rows Removed by Index Recheck: 9873135
  • Filter: ((NOT deleted) AND (c804_opp_ownerid IS NOT NULL))
  • Rows Removed by Filter: 85
  • Heap Blocks: exact=37833 lossy=321563
79. 891.387 891.387 ↑ 1.9 2,769,664 1

Bitmap Index Scan on ix_t236_opp_line_item_fact_closedate_timestamp (cost=0.00..546,282.05 rows=5,229,218 width=0) (actual time=891.387..891.387 rows=2,769,664 loops=1)

  • Index Cond: ((c805_opp_close_date >= 20181101) AND (c805_opp_close_date <= 20190131) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
80. 1.088 5.120 ↓ 43.7 8,739 1

Hash (cost=566.57..566.57 rows=200 width=4) (actual time=5.120..5.120 rows=8,739 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 436kB
81. 3.230 4.032 ↓ 43.7 8,739 1

HashAggregate (cost=564.57..566.57 rows=200 width=4) (actual time=2.828..4.032 rows=8,739 loops=1)

  • Group Key: ownercte_4.c1
82. 0.802 0.802 ↑ 2.9 8,739 1

CTE Scan on ownercte ownercte_4 (cost=0.00..501.84 rows=25,092 width=4) (actual time=0.001..0.802 rows=8,739 loops=1)

83. 0.000 1,272.865 ↑ 2.0 1 115,715

Subquery Scan on oli174_ft1_1 (cost=1,207.62..1,215.60 rows=2 width=16) (actual time=0.011..0.011 rows=1 loops=115,715)

  • Filter: (t236_opp_line_item_fact_2.c801_opportunity_sid = oli174_ft1_1.c1)
84. 115.715 1,272.865 ↑ 319.0 1 115,715

Group (cost=1,207.62..1,211.61 rows=319 width=16) (actual time=0.010..0.011 rows=1 loops=115,715)

  • 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
85. 231.430 1,157.150 ↑ 63.8 5 115,715

Sort (cost=1,207.62..1,208.42 rows=319 width=16) (actual time=0.010..0.010 rows=5 loops=115,715)

  • 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
86. 925.720 925.720 ↑ 63.8 5 115,715

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,194.36 rows=319 width=16) (actual time=0.005..0.008 rows=5 loops=115,715)

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

SubPlan (forNested Loop Left Join)

88. 0.004 0.004 ↑ 1.1 13 1

CTE Scan on picklist_4 cte0_5 (cost=0.00..0.28 rows=14 width=4) (actual time=0.001..0.004 rows=13 loops=1)

89. 1.377 1.377 ↑ 2.9 8,739 1

CTE Scan on ownercte ownercte_2 (cost=0.00..501.84 rows=25,092 width=4) (actual time=0.001..1.377 rows=8,739 loops=1)

90. 0.001 0.001 ↑ 1.2 6 1

CTE Scan on picklist_0 cte0_6 (cost=0.00..0.14 rows=7 width=4) (actual time=0.001..0.001 rows=6 loops=1)

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

92. 1.718 1.718 ↑ 2.9 8,739 1

CTE Scan on ownercte ownercte_3 (cost=0.00..501.84 rows=25,092 width=4) (actual time=0.002..1.718 rows=8,739 loops=1)

93. 0.023 15,845.922 ↑ 2,365.7 19 1

Hash Join (cost=1.11..43,152.29 rows=44,949 width=120) (actual time=15,814.520..15,845.922 rows=19 loops=1)

  • Hash Cond: (cte0.c4 = t234_opportunityforecastcategorypicklistdim.sid)
94. 15,845.889 15,845.889 ↑ 94,629.8 19 1

CTE Scan on bucketac cte0 (cost=0.00..35,959.32 rows=1,797,966 width=116) (actual time=15,814.498..15,845.889 rows=19 loops=1)

95. 0.004 0.010 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
96. 0.006 0.006 ↑ 1.0 5 1

Seq Scan on t234_opportunityforecastcategorypicklistdim (cost=0.00..1.05 rows=5 width=12) (actual time=0.005..0.006 rows=5 loops=1)

97. 0.014 12,944.192 ↑ 2,068.3 25 1

Subquery Scan on *SELECT* 2 (cost=1.11..50,286.31 rows=51,707 width=120) (actual time=12,940.902..12,944.192 rows=25 loops=1)

98. 0.023 12,944.178 ↑ 2,068.3 25 1

Hash Join (cost=1.11..49,639.97 rows=51,707 width=144) (actual time=12,940.895..12,944.178 rows=25 loops=1)

  • Hash Cond: (cte0_1.c6 = t234_opportunityforecastcategorypicklistdim_1.sid)
99. 12,944.144 12,944.144 ↑ 82,731.4 25 1

CTE Scan on bucketec cte0_1 (cost=0.00..41,365.72 rows=2,068,286 width=140) (actual time=12,940.867..12,944.144 rows=25 loops=1)

100. 0.004 0.011 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
101. 0.007 0.007 ↑ 1.0 5 1

Seq Scan on t234_opportunityforecastcategorypicklistdim t234_opportunityforecastcategorypicklistdim_1 (cost=0.00..1.05 rows=5 width=12) (actual time=0.006..0.007 rows=5 loops=1)