explain.depesz.com

PostgreSQL's explain analyze made readable

Result: T4Ea

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 8,324.522 ↑ 57,888.3 44 1

Append (cost=13,745,597.34..13,861,222.33 rows=2,547,085 width=120) (actual time=4,266.829..8,324.522 rows=44 loops=1)

2.          

CTE picklist_0

3. 0.300 0.300 ↑ 1.0 6 1

Seq Scan on t233_opportunitystagenamepicklistdim (cost=0.00..46.54 rows=6 width=4) (actual time=0.012..0.300 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.277 0.277 ↑ 1.0 7 1

Seq Scan on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_1 (cost=0.00..48.08 rows=7 width=4) (actual time=0.006..0.277 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.431 0.431 ↑ 1.0 13 1

Seq Scan on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_2 (cost=0.00..57.31 rows=13 width=4) (actual time=0.011..0.431 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. 16.803 916.341 ↑ 3.0 8,167 1

HashAggregate (cost=430,245.18..430,489.55 rows=24,437 width=4) (actual time=915.386..916.341 rows=8,167 loops=1)

  • Group Key: distinctclosedateowner.ownerid
10. 60.944 899.538 ↑ 1.6 93,387 1

Hash Join (cost=396,547.72..429,870.81 rows=149,749 width=4) (actual time=772.485..899.538 rows=93,387 loops=1)

  • Hash Cond: (distinctclosedateowner.ownerid = t166_userrolehierarchy.sid)
11. 69.672 88.637 ↓ 1.3 196,760 1

Bitmap Heap Scan on distinctclosedateowner (cost=3,179.36..32,515.59 rows=149,749 width=4) (actual time=22.007..88.637 rows=196,760 loops=1)

  • Recheck Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
  • Heap Blocks: exact=17913
12. 18.965 18.965 ↓ 1.3 196,760 1

Bitmap Index Scan on ix_closedate_distinctclosedateowner (cost=0.00..3,141.92 rows=149,749 width=0) (actual time=18.965..18.965 rows=196,760 loops=1)

  • Index Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
13. 16.925 749.957 ↑ 1.6 120,737 1

Hash (cost=390,160.13..390,160.13 rows=195,539 width=4) (actual time=749.957..749.957 rows=120,737 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2089kB
14. 15.166 733.032 ↑ 1.6 120,737 1

Unique (cost=383,749.67..390,160.13 rows=195,539 width=4) (actual time=702.001..733.032 rows=120,737 loops=1)

15. 69.999 717.866 ↑ 9.9 129,640 1

Sort (cost=383,749.67..386,954.90 rows=1,282,092 width=4) (actual time=702.000..717.866 rows=129,640 loops=1)

  • Sort Key: t166_userrolehierarchy.sid
  • Sort Method: external merge Disk: 1768kB
16. 195.650 647.867 ↑ 9.9 129,640 1

Hash Join (cost=59,608.38..236,149.50 rows=1,282,092 width=4) (actual time=149.690..647.867 rows=129,640 loops=1)

  • Hash Cond: (t166_userrolehierarchy.c662_user_role_id = t67_userroledim.sid)
17. 310.396 440.232 ↓ 1.0 1,321,612 1

Bitmap Heap Scan on t166_userrolehierarchy (cost=37,210.56..194,419.94 rows=1,282,092 width=8) (actual time=137.009..440.232 rows=1,321,612 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=39245
18. 129.836 129.836 ↓ 1.0 1,323,172 1

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..36,890.04 rows=1,282,092 width=0) (actual time=129.836..129.836 rows=1,323,172 loops=1)

  • Index Cond: ((c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
19. 1.393 11.985 ↑ 9.3 8,370 1

Hash (cost=21,117.18..21,117.18 rows=78,051 width=4) (actual time=11.985..11.985 rows=8,370 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 1177kB
20. 6.134 10.592 ↑ 9.3 8,370 1

Bitmap Heap Scan on t67_userroledim (cost=4,261.99..21,117.18 rows=78,051 width=4) (actual time=4.902..10.592 rows=8,370 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: 5538
  • Heap Blocks: exact=2140
21. 4.458 4.458 ↑ 5.7 13,929 1

Bitmap Index Scan on ix_t67_userroledim_sid_end_start_timestamp_partial (cost=0.00..4,242.48 rows=78,925 width=0) (actual time=4.458..4.458 rows=13,929 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
22.          

CTE bucketac

23. 140.256 4,525.743 ↑ 46,674.4 19 1

GroupAggregate (cost=4,224,470.81..4,266,594.47 rows=886,814 width=92) (actual time=4,266.791..4,525.743 rows=19 loops=1)

  • Group Key: t21_opp_line_item_fact.c320_opp_forecastcategory, (CASE WHEN (t21_opp_line_item_fact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t21_opp_line_item_fact_1.c270_opp_close_date < 20181101) OR (t21_opp_line_item_fact_1.c270_opp_close_date > 20190131)) THEN 'slipped'::text WHEN (hashed SubPlan 5) THEN CASE WHEN (hashed SubPlan 6) THEN 'won'::text WHEN (hashed SubPlan 7) THEN 'lost'::text ELSE CASE WHEN (t234_opportunityforecastcategorypicklistdim_3.correlated_app_order = t234_opportunityforecastcategorypicklistdim_4.correlated_app_order) THEN 'unchanged'::text WHEN (t234_opportunityforecastcategorypicklistdim_3.correlated_app_order > t234_opportunityforecastcategorypicklistdim_4.correlated_app_order) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
24. 330.167 4,385.487 ↑ 2.1 418,251 1

Sort (cost=4,223,920.68..4,226,137.72 rows=886,814 width=72) (actual time=4,266.391..4,385.487 rows=418,251 loops=1)

  • Sort Key: t21_opp_line_item_fact.c320_opp_forecastcategory, (CASE WHEN (t21_opp_line_item_fact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t21_opp_line_item_fact_1.c270_opp_close_date < 20181101) OR (t21_opp_line_item_fact_1.c270_opp_close_date > 20190131)) THEN 'slipped'::text WHEN (hashed SubPlan 5) THEN CASE WHEN (hashed SubPlan 6) THEN 'won'::text WHEN (hashed SubPlan 7) THEN 'lost'::text ELSE CASE WHEN (t234_opportunityforecastcategorypicklistdim_3.correlated_app_order = t234_opportunityforecastcategorypicklistdim_4.correlated_app_order) THEN 'unchanged'::text WHEN (t234_opportunityforecastcategorypicklistdim_3.correlated_app_order > t234_opportunityforecastcategorypicklistdim_4.correlated_app_order) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
  • Sort Method: external merge Disk: 26576kB
25. 354.742 4,055.320 ↑ 2.1 418,251 1

Hash Left Join (cost=3,828,001.94..4,063,560.12 rows=886,814 width=72) (actual time=3,363.463..4,055.320 rows=418,251 loops=1)

  • Hash Cond: ((t21_opp_line_item_fact_1.c323_opp_currency_code = cur177_t1_r1.sid) AND (t21_opp_line_item_fact_1.c270_opp_close_date = cur177_t1_r1.rate_date))
26. 89.471 3,655.911 ↑ 1.8 418,251 1

Hash Left Join (cost=3,823,242.04..3,855,662.21 rows=753,775 width=60) (actual time=3,316.821..3,655.911 rows=418,251 loops=1)

  • Hash Cond: (t21_opp_line_item_fact.c320_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim_3.sid)
27. 94.627 3,566.429 ↑ 1.8 418,251 1

Merge Right Join (cost=3,823,222.36..3,845,632.40 rows=753,775 width=56) (actual time=3,316.799..3,566.429 rows=418,251 loops=1)

  • Merge Cond: ((cur177_t0_r0.rate_date = t21_opp_line_item_fact.c270_opp_close_date) AND (cur177_t0_r0.sid = t21_opp_line_item_fact.c323_opp_currency_code))
28. 4.575 42.466 ↑ 1.4 71,814 1

Merge Append (cost=0.31..8,226.02 rows=100,865 width=16) (actual time=0.020..42.466 rows=71,814 loops=1)

  • Sort Key: cur177_t0_r0.rate_date, cur177_t0_r0.sid
29. 0.003 0.005 ↓ 0.0 0 1

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

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

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

  • Filter: (to_iso_code = 'USD'::text)
31. 37.886 37.886 ↑ 1.4 71,814 1

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur177_t0_r0_1 (cost=0.29..6,965.19 rows=100,864 width=16) (actual time=0.015..37.886 rows=71,814 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
32. 55.398 3,429.336 ↑ 1.5 418,251 1

Materialize (cost=3,822,109.25..3,825,312.72 rows=640,694 width=56) (actual time=3,272.401..3,429.336 rows=418,251 loops=1)

33. 323.337 3,373.938 ↑ 1.5 418,251 1

Sort (cost=3,822,109.25..3,823,710.99 rows=640,694 width=56) (actual time=3,272.399..3,373.938 rows=418,251 loops=1)

  • Sort Key: t21_opp_line_item_fact.c270_opp_close_date, t21_opp_line_item_fact.c323_opp_currency_code
  • Sort Method: external merge Disk: 30200kB
34. 108.052 3,050.601 ↑ 1.5 418,251 1

Hash Left Join (cost=570.92..3,738,417.14 rows=640,694 width=56) (actual time=920.444..3,050.601 rows=418,251 loops=1)

  • Hash Cond: (t21_opp_line_item_fact_1.c320_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim_4.sid)
35. 161.286 2,942.541 ↑ 1.5 418,251 1

Nested Loop Left Join (cost=551.25..3,729,889.05 rows=640,694 width=56) (actual time=920.422..2,942.541 rows=418,251 loops=1)

36. 49.228 1,526.502 ↑ 1.5 418,251 1

Nested Loop (cost=550.69..218,681.69 rows=640,694 width=24) (actual time=920.392..1,526.502 rows=418,251 loops=1)

37. 4.001 921.918 ↓ 40.8 8,167 1

HashAggregate (cost=549.83..551.83 rows=200 width=4) (actual time=919.881..921.918 rows=8,167 loops=1)

  • Group Key: ownercte_1.c1
38. 917.917 917.917 ↑ 3.0 8,167 1

CTE Scan on ownercte ownercte_1 (cost=0.00..488.74 rows=24,437 width=4) (actual time=915.388..917.917 rows=8,167 loops=1)

39. 554.888 555.356 ↑ 2.6 51 8,167

Index Scan using ix_t21_opp_line_item_fact_owner_closedate_timestamp on t21_opp_line_item_fact (cost=0.85..1,089.31 rows=134 width=28) (actual time=0.013..0.068 rows=51 loops=8,167)

  • Index Cond: ((c268_opp_ownerid = ownercte_1.c1) AND (c270_opp_close_date >= 20181101) AND (c270_opp_close_date <= 20190131) AND (start_stamp <= '1541055600000'::bigint) AND (end_stamp > '1541055600000'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 8)))
  • Rows Removed by Filter: 23
40.          

SubPlan (forIndex Scan)

41. 0.468 0.468 ↑ 1.0 13 1

CTE Scan on picklist_4 cte0_5 (cost=0.00..0.26 rows=13 width=4) (actual time=0.046..0.468 rows=13 loops=1)

42. 1,254.753 1,254.753 ↑ 1.0 1 418,251

Index Scan using ix_t21_opp_line_item_fact_sid_timestamp on t21_opp_line_item_fact t21_opp_line_item_fact_1 (cost=0.56..5.47 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=418,251)

  • Index Cond: ((t21_opp_line_item_fact.sid = sid) AND (t21_opp_line_item_fact.sid = sid) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
  • Filter: (NOT deleted)
43. 0.002 0.008 ↑ 86.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
44. 0.006 0.006 ↑ 86.0 5 1

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

45. 0.007 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
46. 0.004 0.004 ↑ 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.004 rows=5 loops=1)

47. 18.138 43.478 ↑ 1.0 100,855 1

Hash (cost=2,203.80..2,203.80 rows=100,865 width=16) (actual time=43.478..43.478 rows=100,855 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3391kB
48. 5.831 25.340 ↑ 1.0 100,855 1

Append (cost=0.00..2,203.80 rows=100,865 width=16) (actual time=0.050..25.340 rows=100,855 loops=1)

49. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on clari_conversion_rate cur177_t1_r1 (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
50. 19.509 19.509 ↑ 1.0 100,855 1

Seq Scan on usd_clari_conversion_rate cur177_t1_r1_1 (cost=0.00..2,203.80 rows=100,864 width=16) (actual time=0.048..19.509 rows=100,855 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
51.          

SubPlan (forHash Left Join)

52. 0.605 0.605 ↑ 3.0 8,167 1

CTE Scan on ownercte (cost=0.00..488.74 rows=24,437 width=4) (actual time=0.001..0.605 rows=8,167 loops=1)

53. 0.303 0.303 ↑ 1.0 6 1

CTE Scan on picklist_0 cte0_3 (cost=0.00..0.12 rows=6 width=4) (actual time=0.014..0.303 rows=6 loops=1)

54. 0.281 0.281 ↑ 1.0 7 1

CTE Scan on picklist_1 cte0_4 (cost=0.00..0.14 rows=7 width=4) (actual time=0.008..0.281 rows=7 loops=1)

55.          

CTE bucketetoclosed

56. 5.910 1,480.858 ↑ 106,086.4 10 1

GroupAggregate (cost=5,092,114.33..5,137,201.05 rows=1,060,864 width=116) (actual time=1,473.371..1,480.858 rows=10 loops=1)

  • Group Key: (CASE WHEN (t21_opp_line_item_fact_3.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 10)) THEN CASE WHEN (t21_opp_line_item_fact_3.c270_opp_close_date > 20190131) THEN 'pulledInBucket'::text WHEN (t21_opp_line_item_fact_3.c270_opp_close_date < 20181101) THEN 'pushedInBucket'::text WHEN (hashed SubPlan 11) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END ELSE 'otherBucket'::text END), (CASE WHEN (hashed SubPlan 12) THEN 'won'::text WHEN (hashed SubPlan 13) THEN 'lost'::text ELSE 'newOpen'::text END), t21_opp_line_item_fact_2.c320_opp_forecastcategory
57. 9.665 1,474.948 ↑ 47.5 22,349 1

Sort (cost=5,091,563.92..5,094,216.08 rows=1,060,864 width=88) (actual time=1,473.356..1,474.948 rows=22,349 loops=1)

  • Sort Key: (CASE WHEN (t21_opp_line_item_fact_3.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 10)) THEN CASE WHEN (t21_opp_line_item_fact_3.c270_opp_close_date > 20190131) THEN 'pulledInBucket'::text WHEN (t21_opp_line_item_fact_3.c270_opp_close_date < 20181101) THEN 'pushedInBucket'::text WHEN (hashed SubPlan 11) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END ELSE 'otherBucket'::text END), (CASE WHEN (hashed SubPlan 12) THEN 'won'::text WHEN (hashed SubPlan 13) THEN 'lost'::text ELSE 'newOpen'::text END), t21_opp_line_item_fact_2.c320_opp_forecastcategory
  • Sort Method: quicksort Memory: 2548kB
58. 15.789 1,465.283 ↑ 47.5 22,349 1

Merge Right Join (cost=4,839,510.75..4,883,860.36 rows=1,060,864 width=88) (actual time=1,447.808..1,465.283 rows=22,349 loops=1)

  • Merge Cond: ((cur177_t0_r0_2.rate_date = t21_opp_line_item_fact_2.c270_opp_close_date) AND (cur177_t0_r0_2.sid = t21_opp_line_item_fact_2.c323_opp_currency_code))
59. 4.664 44.800 ↑ 1.4 71,805 1

Merge Append (cost=0.31..8,226.02 rows=100,865 width=16) (actual time=0.038..44.800 rows=71,805 loops=1)

  • Sort Key: cur177_t0_r0_2.rate_date, cur177_t0_r0_2.sid
60. 0.004 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: cur177_t0_r0_2.rate_date, cur177_t0_r0_2.sid
  • Sort Method: quicksort Memory: 25kB
61. 0.003 0.003 ↓ 0.0 0 1

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

  • Filter: (to_iso_code = 'USD'::text)
62. 40.129 40.129 ↑ 1.4 71,805 1

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur177_t0_r0_3 (cost=0.29..6,965.19 rows=100,864 width=16) (actual time=0.030..40.129 rows=71,805 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
63. 2.088 1,404.093 ↑ 40.3 22,349 1

Materialize (cost=4,837,847.23..4,842,355.80 rows=901,714 width=44) (actual time=1,400.093..1,404.093 rows=22,349 loops=1)

64. 10.171 1,402.005 ↑ 40.3 22,349 1

Sort (cost=4,837,847.23..4,840,101.51 rows=901,714 width=44) (actual time=1,400.091..1,402.005 rows=22,349 loops=1)

  • Sort Key: t21_opp_line_item_fact_2.c270_opp_close_date, t21_opp_line_item_fact_2.c323_opp_currency_code
  • Sort Method: quicksort Memory: 2515kB
65. 15.007 1,391.834 ↑ 40.3 22,349 1

Nested Loop Left Join (cost=1,101.21..4,693,175.30 rows=901,714 width=44) (actual time=2.426..1,391.834 rows=22,349 loops=1)

  • Filter: ((t21_opp_line_item_fact_3.sid IS NULL) OR (t21_opp_line_item_fact_3.c270_opp_close_date < 20181101) OR (t21_opp_line_item_fact_3.c270_opp_close_date > 20190131) OR (NOT (hashed SubPlan 14)))
  • Rows Removed by Filter: 211098
66. 74.590 675.840 ↑ 4.7 233,447 1

Hash Semi Join (cost=550.82..228,866.75 rows=1,106,436 width=28) (actual time=2.415..675.840 rows=233,447 loops=1)

  • Hash Cond: (t21_opp_line_item_fact_2.c316_opp_stagename = cte0_9.c1)
67. 139.660 601.245 ↑ 1.9 662,713 1

Nested Loop (cost=550.39..213,275.14 rows=1,250,312 width=28) (actual time=2.398..601.245 rows=662,713 loops=1)

68. 3.636 4.233 ↓ 40.8 8,167 1

HashAggregate (cost=549.83..551.83 rows=200 width=4) (actual time=2.379..4.233 rows=8,167 loops=1)

  • Group Key: ownercte_4.c1
69. 0.597 0.597 ↑ 3.0 8,167 1

CTE Scan on ownercte ownercte_4 (cost=0.00..488.74 rows=24,437 width=4) (actual time=0.001..0.597 rows=8,167 loops=1)

70. 457.352 457.352 ↑ 3.2 81 8,167

Index Scan using ix_t21_opp_line_item_fact_owner_closedate_timestamp on t21_opp_line_item_fact t21_opp_line_item_fact_2 (cost=0.56..1,061.01 rows=261 width=32) (actual time=0.008..0.056 rows=81 loops=8,167)

  • Index Cond: ((c268_opp_ownerid = ownercte_4.c1) AND (c270_opp_close_date >= 20181101) AND (c270_opp_close_date <= 20190131) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
71. 0.004 0.005 ↑ 1.0 13 1

Hash (cost=0.26..0.26 rows=13 width=4) (actual time=0.005..0.005 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
72. 0.001 0.001 ↑ 1.0 13 1

CTE Scan on picklist_4 cte0_9 (cost=0.00..0.26 rows=13 width=4) (actual time=0.001..0.001 rows=13 loops=1)

73. 700.341 700.341 ↑ 1.0 1 233,447

Index Scan using ix_t21_opp_line_item_fact_sid_timestamp on t21_opp_line_item_fact t21_opp_line_item_fact_3 (cost=0.56..4.02 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=233,447)

  • Index Cond: ((t21_opp_line_item_fact_2.sid = sid) AND (t21_opp_line_item_fact_2.sid = sid) AND (start_stamp <= '1541055600000'::bigint) AND (end_stamp > '1541055600000'::bigint))
  • Filter: (NOT deleted)
74.          

SubPlan (forNested Loop Left Join)

75. 0.646 0.646 ↑ 3.0 8,167 1

CTE Scan on ownercte ownercte_3 (cost=0.00..488.74 rows=24,437 width=4) (actual time=0.000..0.646 rows=8,167 loops=1)

76.          

SubPlan (forMerge Right Join)

77. 0.003 0.003 ↑ 1.0 13 1

CTE Scan on picklist_4 cte0_6 (cost=0.00..0.26 rows=13 width=4) (actual time=0.002..0.003 rows=13 loops=1)

78. 0.596 0.596 ↑ 3.0 8,167 1

CTE Scan on ownercte ownercte_2 (cost=0.00..488.74 rows=24,437 width=4) (actual time=0.000..0.596 rows=8,167 loops=1)

79. 0.001 0.001 ↑ 1.0 6 1

CTE Scan on picklist_0 cte0_7 (cost=0.00..0.12 rows=6 width=4) (actual time=0.000..0.001 rows=6 loops=1)

80. 0.001 0.001 ↑ 1.0 7 1

CTE Scan on picklist_1 cte0_8 (cost=0.00..0.14 rows=7 width=4) (actual time=0.001..0.001 rows=7 loops=1)

81.          

CTE bucketetoopen

82. 14.939 2,317.735 ↑ 39,960.5 15 1

GroupAggregate (cost=3,888,662.88..3,911,140.65 rows=599,407 width=116) (actual time=2,296.791..2,317.735 rows=15 loops=1)

  • Group Key: (CASE WHEN (t21_opp_line_item_fact_5.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 16)) THEN CASE WHEN (t21_opp_line_item_fact_5.c270_opp_close_date > 20190131) THEN 'pulledInBucket'::text WHEN (t21_opp_line_item_fact_5.c270_opp_close_date < 20181101) THEN 'pushedInBucket'::text WHEN (hashed SubPlan 17) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END ELSE 'otherBucket'::text END), 'newOpen'::text, t21_opp_line_item_fact_4.c320_opp_forecastcategory
83. 100.298 2,302.796 ↑ 10.7 55,915 1

Sort (cost=3,888,112.76..3,889,611.28 rows=599,407 width=88) (actual time=2,296.089..2,302.796 rows=55,915 loops=1)

  • Sort Key: (CASE WHEN (t21_opp_line_item_fact_5.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 16)) THEN CASE WHEN (t21_opp_line_item_fact_5.c270_opp_close_date > 20190131) THEN 'pulledInBucket'::text WHEN (t21_opp_line_item_fact_5.c270_opp_close_date < 20181101) THEN 'pushedInBucket'::text WHEN (hashed SubPlan 17) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END ELSE 'otherBucket'::text END), t21_opp_line_item_fact_4.c320_opp_forecastcategory
  • Sort Method: external merge Disk: 3184kB
84. 21.460 2,202.498 ↑ 10.7 55,915 1

Merge Right Join (cost=3,747,843.47..3,773,218.14 rows=599,407 width=88) (actual time=2,170.523..2,202.498 rows=55,915 loops=1)

  • Merge Cond: ((cur177_t0_r0_4.rate_date = t21_opp_line_item_fact_4.c270_opp_close_date) AND (cur177_t0_r0_4.sid = t21_opp_line_item_fact_4.c323_opp_currency_code))
85. 4.882 45.432 ↑ 1.4 71,810 1

Merge Append (cost=0.31..8,226.02 rows=100,865 width=16) (actual time=0.030..45.432 rows=71,810 loops=1)

  • Sort Key: cur177_t0_r0_4.rate_date, cur177_t0_r0_4.sid
86. 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: cur177_t0_r0_4.rate_date, cur177_t0_r0_4.sid
  • Sort Method: quicksort Memory: 25kB
87. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on clari_conversion_rate cur177_t0_r0_4 (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)
88. 40.543 40.543 ↑ 1.4 71,810 1

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur177_t0_r0_5 (cost=0.29..6,965.19 rows=100,864 width=16) (actual time=0.023..40.543 rows=71,810 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
89. 5.396 2,134.997 ↑ 9.1 55,915 1

Materialize (cost=3,746,180.24..3,748,727.67 rows=509,485 width=40) (actual time=2,122.397..2,134.997 rows=55,915 loops=1)

90. 59.720 2,129.601 ↑ 9.1 55,915 1

Sort (cost=3,746,180.24..3,747,453.95 rows=509,485 width=40) (actual time=2,122.394..2,129.601 rows=55,915 loops=1)

  • Sort Key: t21_opp_line_item_fact_4.c270_opp_close_date, t21_opp_line_item_fact_4.c323_opp_currency_code
  • Sort Method: external merge Disk: 2624kB
91. 134.108 2,069.881 ↑ 9.1 55,915 1

Nested Loop Left Join (cost=1,101.08..3,683,950.93 rows=509,485 width=40) (actual time=4.906..2,069.881 rows=55,915 loops=1)

  • Filter: ((t21_opp_line_item_fact_5.sid IS NULL) OR (t21_opp_line_item_fact_5.c270_opp_close_date < 20181101) OR (t21_opp_line_item_fact_5.c270_opp_close_date > 20190131) OR (NOT (hashed SubPlan 19)))
  • Rows Removed by Filter: 373351
92. 54.813 647.381 ↑ 1.5 429,266 1

Nested Loop (cost=550.69..213,204.14 rows=625,156 width=24) (actual time=2.395..647.381 rows=429,266 loops=1)

93. 3.935 4.544 ↓ 40.8 8,167 1

HashAggregate (cost=549.83..551.83 rows=200 width=4) (actual time=2.336..4.544 rows=8,167 loops=1)

  • Group Key: ownercte_7.c1
94. 0.609 0.609 ↑ 3.0 8,167 1

CTE Scan on ownercte ownercte_7 (cost=0.00..488.74 rows=24,437 width=4) (actual time=0.001..0.609 rows=8,167 loops=1)

95. 588.021 588.024 ↑ 2.5 53 8,167

Index Scan using ix_t21_opp_line_item_fact_owner_closedate_timestamp on t21_opp_line_item_fact t21_opp_line_item_fact_4 (cost=0.85..1,061.95 rows=131 width=28) (actual time=0.015..0.072 rows=53 loops=8,167)

  • Index Cond: ((c268_opp_ownerid = ownercte_7.c1) AND (c270_opp_close_date >= 20181101) AND (c270_opp_close_date <= 20190131) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 18)))
  • Rows Removed by Filter: 29
96.          

SubPlan (forIndex Scan)

97. 0.003 0.003 ↑ 1.0 13 1

CTE Scan on picklist_4 cte0_11 (cost=0.00..0.26 rows=13 width=4) (actual time=0.000..0.003 rows=13 loops=1)

98. 1,287.798 1,287.798 ↑ 1.0 1 429,266

Index Scan using ix_t21_opp_line_item_fact_sid_timestamp on t21_opp_line_item_fact t21_opp_line_item_fact_5 (cost=0.56..5.53 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=429,266)

  • Index Cond: ((t21_opp_line_item_fact_4.sid = sid) AND (t21_opp_line_item_fact_4.sid = sid) AND (start_stamp <= '1541055600000'::bigint) AND (end_stamp > '1541055600000'::bigint))
  • Filter: (NOT deleted)
99.          

SubPlan (forNested Loop Left Join)

100. 0.594 0.594 ↑ 3.0 8,167 1

CTE Scan on ownercte ownercte_6 (cost=0.00..488.74 rows=24,437 width=4) (actual time=0.001..0.594 rows=8,167 loops=1)

101.          

SubPlan (forMerge Right Join)

102. 0.003 0.003 ↑ 1.0 13 1

CTE Scan on picklist_4 cte0_10 (cost=0.00..0.26 rows=13 width=4) (actual time=0.002..0.003 rows=13 loops=1)

103. 0.606 0.606 ↑ 3.0 8,167 1

CTE Scan on ownercte ownercte_5 (cost=0.00..488.74 rows=24,437 width=4) (actual time=0.000..0.606 rows=8,167 loops=1)

104. 0.027 4,525.804 ↑ 46,674.4 19 1

Hash Join (cost=19.68..29,949.65 rows=886,814 width=120) (actual time=4,266.828..4,525.804 rows=19 loops=1)

  • Hash Cond: (cte0.c4 = t234_opportunityforecastcategorypicklistdim.sid)
105. 4,525.760 4,525.760 ↑ 46,674.4 19 1

CTE Scan on bucketac cte0 (cost=0.00..17,736.28 rows=886,814 width=92) (actual time=4,266.795..4,525.760 rows=19 loops=1)

106. 0.003 0.017 ↑ 86.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
107. 0.014 0.014 ↑ 86.0 5 1

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

108. 0.016 2,317.794 ↑ 39,960.5 15 1

Subquery Scan on *SELECT* 2 (cost=19.68..27,742.25 rows=599,407 width=120) (actual time=2,296.830..2,317.794 rows=15 loops=1)

109. 0.019 2,317.778 ↑ 39,960.5 15 1

Hash Join (cost=19.68..20,249.66 rows=599,407 width=144) (actual time=2,296.821..2,317.778 rows=15 loops=1)

  • Hash Cond: (cte0_1.c6 = t234_opportunityforecastcategorypicklistdim_1.sid)
110. 2,317.745 2,317.745 ↑ 39,960.5 15 1

CTE Scan on bucketetoopen cte0_1 (cost=0.00..11,988.14 rows=599,407 width=116) (actual time=2,296.793..2,317.745 rows=15 loops=1)

111. 0.005 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
112. 0.009 0.009 ↑ 86.0 5 1

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

113. 0.015 1,480.915 ↑ 106,086.4 10 1

Subquery Scan on *SELECT* 3 (cost=19.68..49,084.63 rows=1,060,864 width=120) (actual time=1,473.414..1,480.915 rows=10 loops=1)

114. 0.022 1,480.900 ↑ 106,086.4 10 1

Hash Join (cost=19.68..35,823.83 rows=1,060,864 width=144) (actual time=1,473.404..1,480.900 rows=10 loops=1)

  • Hash Cond: (cte0_2.c6 = t234_opportunityforecastcategorypicklistdim_2.sid)
115. 1,480.865 1,480.865 ↑ 106,086.4 10 1

CTE Scan on bucketetoclosed cte0_2 (cost=0.00..21,217.28 rows=1,060,864 width=116) (actual time=1,473.374..1,480.865 rows=10 loops=1)

116. 0.002 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
117. 0.011 0.011 ↑ 86.0 5 1

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