explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sqh

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 19,560.754 ↑ 4,017.3 64 1

Append (cost=10,952,855.02..10,977,493.76 rows=257,109 width=120) (actual time=7,414.050..19,560.754 rows=64 loops=1)

2.          

CTE picklist_0

3. 0.019 0.019 ↑ 1.0 2 1

Seq Scan on t205_opportunitystagenamepicklistdim t205_opportunitystagenamepicklistdim_3 (cost=0.00..4.80 rows=2 width=4) (actual time=0.013..0.019 rows=2 loops=1)

  • Filter: (correlated_value = ANY ('{"Closed Won","Trial Won"}'::text[]))
  • Rows Removed by Filter: 62
4.          

CTE picklist_1

5. 0.029 0.029 ↑ 1.1 14 1

Seq Scan on t205_opportunitystagenamepicklistdim t205_opportunitystagenamepicklistdim_4 (cost=0.00..5.84 rows=15 width=4) (actual time=0.007..0.029 rows=14 loops=1)

  • Filter: (correlated_value = ANY ('{"16 - Closed / Invalid or Duplicate Oppty","Closed - Dead","Closed - Call later","13 \\u2013 Closed / Lost to No Decision",Churn,"12 - Closed / Lost to Competition","Trial Lost","Closed Lost","15 - Closed / Lost to Churn","14 - Closed / Mid Cycle Qualify Out","13 - Closed / Lost to No Decision","Stage 16 - Closed / Invalid or Duplicate","Closed - Free","11 - Delayed or Deprioritized","Closed - No Decision"}'::text[]))
  • Rows Removed by Filter: 50
6.          

CTE picklist_4

7. 0.044 0.044 ↑ 1.1 16 1

Seq Scan on t205_opportunitystagenamepicklistdim t205_opportunitystagenamepicklistdim_5 (cost=0.00..6.00 rows=17 width=4) (actual time=0.016..0.044 rows=16 loops=1)

  • Filter: (correlated_value = ANY ('{"16 - Closed / Invalid or Duplicate Oppty","Closed - Dead","Closed - Call later","13 \\u2013 Closed / Lost to No Decision",Churn,"12 - Closed / Lost to Competition","Trial Lost","Closed Lost","15 - Closed / Lost to Churn","Closed Won","14 - Closed / Mid Cycle Qualify Out","13 - Closed / Lost to No Decision","Stage 16 - Closed / Invalid or Duplicate","Trial Won","Closed - Free","11 - Delayed or Deprioritized","Closed - No Decision"}'::text[]))
  • Rows Removed by Filter: 48
8.          

CTE ownercte

9. 2.850 17.568 ↑ 2.1 570 1

HashAggregate (cost=3,201.10..3,212.98 rows=1,188 width=4) (actual time=17.501..17.568 rows=570 loops=1)

  • Group Key: distinctclosedateowner.ownerid
10. 3.895 14.718 ↓ 1.2 19,843 1

Hash Join (cost=1,136.51..3,160.69 rows=16,163 width=4) (actual time=4.944..14.718 rows=19,843 loops=1)

  • Hash Cond: (distinctclosedateowner.ownerid = t209_userrolehierarchy.sid)
11. 6.070 7.609 ↓ 1.2 19,843 1

Bitmap Heap Scan on distinctclosedateowner (cost=346.09..2,198.54 rows=16,163 width=4) (actual time=1.713..7.609 rows=19,843 loops=1)

  • Recheck Cond: ((closedate <= 20181231) AND (closedate >= 20181001))
  • Heap Blocks: exact=1353
12. 1.539 1.539 ↓ 1.2 19,843 1

Bitmap Index Scan on ix_distinctclosedateowner_closedate (cost=0.00..342.05 rows=16,163 width=0) (actual time=1.539..1.539 rows=19,843 loops=1)

  • Index Cond: ((closedate <= 20181231) AND (closedate >= 20181001))
13. 0.339 3.214 ↓ 1.4 2,110 1

Hash (cost=771.30..771.30 rows=1,529 width=4) (actual time=3.214..3.214 rows=2,110 loops=1)

  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 107kB
14. 0.693 2.875 ↓ 1.4 2,110 1

HashAggregate (cost=756.01..771.30 rows=1,529 width=4) (actual time=2.663..2.875 rows=2,110 loops=1)

  • Group Key: t209_userrolehierarchy.sid
15. 0.589 2.182 ↓ 1.0 2,559 1

Hash Join (cost=231.84..749.67 rows=2,535 width=4) (actual time=0.794..2.182 rows=2,559 loops=1)

  • Hash Cond: (t209_userrolehierarchy.c808_user_role_id = t37_userroledim.sid)
16. 0.839 1.162 ↓ 1.0 2,559 1

Bitmap Heap Scan on t209_userrolehierarchy (cost=171.12..654.15 rows=2,535 width=8) (actual time=0.357..1.162 rows=2,559 loops=1)

  • Recheck Cond: ((c809_ancestor_role_id = ANY ('{496,30}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=302
17. 0.323 0.323 ↓ 1.0 2,581 1

Bitmap Index Scan on ix_t209_userrolehierarchy_ancestor_timestamp (cost=0.00..170.49 rows=2,535 width=0) (actual time=0.323..0.323 rows=2,581 loops=1)

  • Index Cond: ((c809_ancestor_role_id = ANY ('{496,30}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
18. 0.060 0.431 ↑ 1.2 437 1

Hash (cost=54.43..54.43 rows=503 width=4) (actual time=0.431..0.431 rows=437 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
19. 0.371 0.371 ↑ 1.2 437 1

Seq Scan on t37_userroledim (cost=0.00..54.43 rows=503 width=4) (actual time=0.009..0.371 rows=437 loops=1)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c419_portaltype IS NULL) OR (c419_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 442
20.          

CTE bucketac

21. 185.168 7,760.423 ↑ 7,300.5 41 1

GroupAggregate (cost=3,903,380.15..3,917,597.85 rows=299,320 width=92) (actual time=7,413.990..7,760.423 rows=41 loops=1)

  • Group Key: t203_opp_split_fact.c771_opp_stagename, (CASE WHEN (t203_opp_split_fact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t203_opp_split_fact_1.c782_opp_close_date < 20181001) OR (t203_opp_split_fact_1.c782_opp_close_date > 20181231)) 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 (t205_opportunitystagenamepicklistdim_6.correlated_app_order = t205_opportunitystagenamepicklistdim_7.correlated_app_order) THEN 'unchanged'::text WHEN (t205_opportunitystagenamepicklistdim_6.correlated_app_order > t205_opportunitystagenamepicklistdim_7.correlated_app_order) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
22. 486.188 7,575.255 ↓ 1.9 553,871 1

Sort (cost=3,903,353.03..3,904,101.33 rows=299,320 width=72) (actual time=7,411.855..7,575.255 rows=553,871 loops=1)

  • Sort Key: t203_opp_split_fact.c771_opp_stagename, (CASE WHEN (t203_opp_split_fact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t203_opp_split_fact_1.c782_opp_close_date < 20181001) OR (t203_opp_split_fact_1.c782_opp_close_date > 20181231)) 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 (t205_opportunitystagenamepicklistdim_6.correlated_app_order = t205_opportunitystagenamepicklistdim_7.correlated_app_order) THEN 'unchanged'::text WHEN (t205_opportunitystagenamepicklistdim_6.correlated_app_order > t205_opportunitystagenamepicklistdim_7.correlated_app_order) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
  • Sort Method: external merge Disk: 34872kB
23. 336.768 7,089.067 ↓ 1.9 553,871 1

Hash Left Join (cost=3,848,380.08..3,863,849.89 rows=299,320 width=72) (actual time=6,308.842..7,089.067 rows=553,871 loops=1)

  • Hash Cond: (t203_opp_split_fact_1.c771_opp_stagename = t205_opportunitystagenamepicklistdim_7.sid)
24. 125.966 6,752.174 ↓ 1.9 553,871 1

Hash Left Join (cost=3,848,347.53..3,854,666.23 rows=299,320 width=60) (actual time=6,308.540..6,752.174 rows=553,871 loops=1)

  • Hash Cond: (t203_opp_split_fact.c771_opp_stagename = t205_opportunitystagenamepicklistdim_6.sid)
25. 182.143 6,626.177 ↓ 1.9 553,871 1

Merge Left Join (cost=3,848,342.09..3,850,747.78 rows=299,320 width=56) (actual time=6,308.490..6,626.177 rows=553,871 loops=1)

  • Merge Cond: ((t203_opp_split_fact_1.c778_opp_currency_code = cur290_t1_r1.sid) AND (t203_opp_split_fact_1.c782_opp_close_date = cur290_t1_r1.rate_date))
26. 640.477 6,389.601 ↓ 1.9 553,871 1

Sort (cost=3,847,033.66..3,847,781.96 rows=299,320 width=52) (actual time=6,298.953..6,389.601 rows=553,871 loops=1)

  • Sort Key: t203_opp_split_fact_1.c778_opp_currency_code, t203_opp_split_fact_1.c782_opp_close_date
  • Sort Method: external merge Disk: 37832kB
27. 348.404 5,749.124 ↓ 1.9 553,871 1

Nested Loop Left Join (cost=1,905,492.22..3,809,574.52 rows=299,320 width=52) (actual time=3,371.012..5,749.124 rows=553,871 loops=1)

28. 171.366 3,739.107 ↓ 1.9 553,871 1

Merge Left Join (cost=1,905,491.65..1,907,897.34 rows=299,320 width=24) (actual time=3,370.991..3,739.107 rows=553,871 loops=1)

  • Merge Cond: ((t203_opp_split_fact.c778_opp_currency_code = cur290_t0_r0.sid) AND (t203_opp_split_fact.c782_opp_close_date = cur290_t0_r0.rate_date))
29. 370.671 3,506.711 ↓ 1.9 553,871 1

Sort (cost=1,904,183.22..1,904,931.52 rows=299,320 width=24) (actual time=3,361.433..3,506.711 rows=553,871 loops=1)

  • Sort Key: t203_opp_split_fact.c778_opp_currency_code, t203_opp_split_fact.c782_opp_close_date
  • Sort Method: external merge Disk: 20496kB
30. 110.069 3,136.040 ↓ 1.9 553,871 1

Hash Join (cost=172,370.83..1,870,819.08 rows=299,320 width=24) (actual time=652.420..3,136.040 rows=553,871 loops=1)

  • Hash Cond: (t203_opp_split_fact.c712_opportunity_split_ownerid = ownercte_1.c1)
31. 2,378.726 3,008.024 ↑ 1.1 554,244 1

Bitmap Heap Scan on t203_opp_split_fact (cost=172,339.60..1,867,006.01 rows=598,639 width=28) (actual time=634.464..3,008.024 rows=554,244 loops=1)

  • Recheck Cond: ((c782_opp_close_date >= 20181001) AND (c782_opp_close_date <= 20181231) AND (start_stamp <= '1540796400000'::bigint) AND (end_stamp > '1540796400000'::bigint))
  • Rows Removed by Index Recheck: 6116773
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 8)))
  • Rows Removed by Filter: 695813
  • Heap Blocks: exact=22307 lossy=189440
32. 629.247 629.247 ↓ 1.1 1,409,365 1

Bitmap Index Scan on ix_t203_opp_split_fact_closedate_timestamp (cost=0.00..172,189.56 rows=1,297,955 width=0) (actual time=629.247..629.247 rows=1,409,365 loops=1)

  • Index Cond: ((c782_opp_close_date >= 20181001) AND (c782_opp_close_date <= 20181231) AND (start_stamp <= '1540796400000'::bigint) AND (end_stamp > '1540796400000'::bigint))
33.          

SubPlan (forBitmap Heap Scan)

34. 0.051 0.051 ↑ 1.1 16 1

CTE Scan on picklist_4 cte0_5 (cost=0.00..0.34 rows=17 width=4) (actual time=0.019..0.051 rows=16 loops=1)

35. 0.095 17.947 ↓ 2.9 570 1

Hash (cost=28.73..28.73 rows=200 width=4) (actual time=17.947..17.947 rows=570 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
36. 0.165 17.852 ↓ 2.9 570 1

HashAggregate (cost=26.73..28.73 rows=200 width=4) (actual time=17.785..17.852 rows=570 loops=1)

  • Group Key: ownercte_1.c1
37. 17.687 17.687 ↑ 2.1 570 1

CTE Scan on ownercte ownercte_1 (cost=0.00..23.76 rows=1,188 width=4) (actual time=17.502..17.687 rows=570 loops=1)

38. 57.267 61.030 ↓ 39.4 566,852 1

Sort (cost=1,308.43..1,344.39 rows=14,387 width=16) (actual time=9.325..61.030 rows=566,852 loops=1)

  • Sort Key: cur290_t0_r0.sid, cur290_t0_r0.rate_date
  • Sort Method: quicksort Memory: 1057kB
39. 0.866 3.763 ↑ 1.0 14,351 1

Append (cost=0.00..314.83 rows=14,387 width=16) (actual time=0.020..3.763 rows=14,351 loops=1)

40. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on clari_conversion_rate cur290_t0_r0 (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)
41. 2.894 2.894 ↑ 1.0 14,351 1

Seq Scan on usd_clari_conversion_rate cur290_t0_r0_1 (cost=0.00..314.83 rows=14,386 width=16) (actual time=0.017..2.894 rows=14,351 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
42. 1,661.613 1,661.613 ↑ 1.0 1 553,871

Index Scan using ix_t203_opp_split_fact_sid_timestamp on t203_opp_split_fact t203_opp_split_fact_1 (cost=0.57..6.34 rows=1 width=28) (actual time=0.003..0.003 rows=1 loops=553,871)

  • Index Cond: ((t203_opp_split_fact.sid = sid) AND (t203_opp_split_fact.sid = sid) AND (start_stamp <= '1541404799999'::bigint) AND (end_stamp > '1541404799999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
43. 50.609 54.433 ↓ 39.2 563,668 1

Sort (cost=1,308.43..1,344.39 rows=14,387 width=16) (actual time=9.308..54.433 rows=563,668 loops=1)

  • Sort Key: cur290_t1_r1.sid, cur290_t1_r1.rate_date
  • Sort Method: quicksort Memory: 1057kB
44. 0.834 3.824 ↑ 1.0 14,351 1

Append (cost=0.00..314.83 rows=14,387 width=16) (actual time=0.022..3.824 rows=14,351 loops=1)

45. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on clari_conversion_rate cur290_t1_r1 (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)
46. 2.988 2.988 ↑ 1.0 14,351 1

Seq Scan on usd_clari_conversion_rate cur290_t1_r1_1 (cost=0.00..314.83 rows=14,386 width=16) (actual time=0.018..2.988 rows=14,351 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
47. 0.012 0.031 ↑ 1.0 64 1

Hash (cost=4.64..4.64 rows=64 width=8) (actual time=0.031..0.031 rows=64 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
48. 0.019 0.019 ↑ 1.0 64 1

Seq Scan on t205_opportunitystagenamepicklistdim t205_opportunitystagenamepicklistdim_6 (cost=0.00..4.64 rows=64 width=8) (actual time=0.004..0.019 rows=64 loops=1)

49. 0.009 0.021 ↑ 1.0 64 1

Hash (cost=4.64..4.64 rows=64 width=8) (actual time=0.021..0.021 rows=64 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
50. 0.012 0.012 ↑ 1.0 64 1

Seq Scan on t205_opportunitystagenamepicklistdim t205_opportunitystagenamepicklistdim_7 (cost=0.00..4.64 rows=64 width=8) (actual time=0.002..0.012 rows=64 loops=1)

51.          

SubPlan (forHash Left Join)

52. 0.049 0.049 ↑ 2.1 570 1

CTE Scan on ownercte (cost=0.00..23.76 rows=1,188 width=4) (actual time=0.001..0.049 rows=570 loops=1)

53. 0.021 0.021 ↑ 1.0 2 1

CTE Scan on picklist_0 cte0_3 (cost=0.00..0.04 rows=2 width=4) (actual time=0.015..0.021 rows=2 loops=1)

54. 0.034 0.034 ↑ 1.1 14 1

CTE Scan on picklist_1 cte0_4 (cost=0.00..0.30 rows=15 width=4) (actual time=0.008..0.034 rows=14 loops=1)

55.          

CTE bucketec1

56. 7.016 5,714.585 ↑ 21,147.3 11 1

GroupAggregate (cost=3,198,790.96..3,208,677.31 rows=232,620 width=116) (actual time=5,705.783..5,714.585 rows=11 loops=1)

  • Group Key: (CASE WHEN (t203_opp_split_fact_3.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 10)) THEN CASE WHEN (t203_opp_split_fact_3.c782_opp_close_date > 20181231) THEN 'pulledInBucket'::text WHEN (t203_opp_split_fact_3.c782_opp_close_date < 20181001) 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), t203_opp_split_fact_2.c771_opp_stagename
57. 11.488 5,707.569 ↑ 8.8 26,413 1

Sort (cost=3,198,763.47..3,199,345.02 rows=232,620 width=88) (actual time=5,705.752..5,707.569 rows=26,413 loops=1)

  • Sort Key: (CASE WHEN (t203_opp_split_fact_3.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 10)) THEN CASE WHEN (t203_opp_split_fact_3.c782_opp_close_date > 20181231) THEN 'pulledInBucket'::text WHEN (t203_opp_split_fact_3.c782_opp_close_date < 20181001) 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), t203_opp_split_fact_2.c771_opp_stagename
  • Sort Method: quicksort Memory: 2832kB
58. 234.659 5,696.081 ↑ 8.8 26,413 1

Nested Loop Left Join (cost=1,895,318.25..3,166,894.67 rows=232,620 width=88) (actual time=3,735.073..5,696.081 rows=26,413 loops=1)

  • Filter: ((t203_opp_split_fact_3.sid IS NULL) OR (t203_opp_split_fact_3.c782_opp_close_date < 20181001) OR (t203_opp_split_fact_3.c782_opp_close_date > 20181231) OR (NOT (hashed SubPlan 14)))
  • Rows Removed by Filter: 449167
59. 139.108 4,034.629 ↓ 1.9 475,580 1

Merge Left Join (cost=1,895,263.46..1,897,301.49 rows=251,427 width=24) (actual time=3,728.337..4,034.629 rows=475,580 loops=1)

  • Merge Cond: ((t203_opp_split_fact_2.c778_opp_currency_code = cur290_t0_r0_2.sid) AND (t203_opp_split_fact_2.c782_opp_close_date = cur290_t0_r0_2.rate_date))
60. 304.706 3,847.052 ↓ 1.9 475,580 1

Sort (cost=1,893,955.03..1,894,583.60 rows=251,427 width=24) (actual time=3,719.044..3,847.052 rows=475,580 loops=1)

  • Sort Key: t203_opp_split_fact_2.c778_opp_currency_code, t203_opp_split_fact_2.c782_opp_close_date
  • Sort Method: external merge Disk: 17592kB
61. 129.070 3,542.346 ↓ 1.9 475,580 1

Hash Semi Join (cost=172,514.82..1,866,243.31 rows=251,427 width=24) (actual time=698.399..3,542.346 rows=475,580 loops=1)

  • Hash Cond: (t203_opp_split_fact_2.c771_opp_stagename = cte0_9.c1)
62. 208.434 3,413.269 ↓ 1.8 1,039,043 1

Hash Join (cost=172,514.27..1,861,904.86 rows=586,960 width=24) (actual time=698.377..3,413.269 rows=1,039,043 loops=1)

  • Hash Cond: (t203_opp_split_fact_2.c712_opportunity_split_ownerid = ownercte_4.c1)
63. 2,517.181 3,204.544 ↑ 1.1 1,039,720 1

Bitmap Heap Scan on t203_opp_split_fact t203_opp_split_fact_2 (cost=172,483.04..1,854,457.50 rows=1,173,921 width=28) (actual time=698.072..3,204.544 rows=1,039,720 loops=1)

  • Recheck Cond: ((c782_opp_close_date >= 20181001) AND (c782_opp_close_date <= 20181231) AND (start_stamp <= '1541404799999'::bigint) AND (end_stamp > '1541404799999'::bigint))
  • Rows Removed by Index Recheck: 6064354
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 419440
  • Heap Blocks: exact=46076 lossy=189370
64. 687.363 687.363 ↓ 1.3 1,666,451 1

Bitmap Index Scan on ix_t203_opp_split_fact_closedate_timestamp (cost=0.00..172,189.56 rows=1,272,635 width=0) (actual time=687.363..687.363 rows=1,666,451 loops=1)

  • Index Cond: ((c782_opp_close_date >= 20181001) AND (c782_opp_close_date <= 20181231) AND (start_stamp <= '1541404799999'::bigint) AND (end_stamp > '1541404799999'::bigint))
65. 0.073 0.291 ↓ 2.9 570 1

Hash (cost=28.73..28.73 rows=200 width=4) (actual time=0.291..0.291 rows=570 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
66. 0.172 0.218 ↓ 2.9 570 1

HashAggregate (cost=26.73..28.73 rows=200 width=4) (actual time=0.167..0.218 rows=570 loops=1)

  • Group Key: ownercte_4.c1
67. 0.046 0.046 ↑ 2.1 570 1

CTE Scan on ownercte ownercte_4 (cost=0.00..23.76 rows=1,188 width=4) (actual time=0.001..0.046 rows=570 loops=1)

68. 0.005 0.007 ↑ 1.1 16 1

Hash (cost=0.34..0.34 rows=17 width=4) (actual time=0.007..0.007 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
69. 0.002 0.002 ↑ 1.1 16 1

CTE Scan on picklist_4 cte0_9 (cost=0.00..0.34 rows=17 width=4) (actual time=0.001..0.002 rows=16 loops=1)

70. 44.747 48.469 ↓ 34.0 488,776 1

Sort (cost=1,308.43..1,344.39 rows=14,387 width=16) (actual time=9.063..48.469 rows=488,776 loops=1)

  • Sort Key: cur290_t0_r0_2.sid, cur290_t0_r0_2.rate_date
  • Sort Method: quicksort Memory: 1057kB
71. 0.832 3.722 ↑ 1.0 14,351 1

Append (cost=0.00..314.83 rows=14,387 width=16) (actual time=0.020..3.722 rows=14,351 loops=1)

72. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on clari_conversion_rate cur290_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)
73. 2.887 2.887 ↑ 1.0 14,351 1

Seq Scan on usd_clari_conversion_rate cur290_t0_r0_3 (cost=0.00..314.83 rows=14,386 width=16) (actual time=0.016..2.887 rows=14,351 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
74. 1,426.740 1,426.740 ↑ 1.0 1 475,580

Index Scan using ix_t203_opp_split_fact_sid_timestamp on t203_opp_split_fact t203_opp_split_fact_3 (cost=0.57..5.02 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=475,580)

  • Index Cond: ((t203_opp_split_fact_2.sid = sid) AND (t203_opp_split_fact_2.sid = sid) AND (start_stamp <= '1540796400000'::bigint) AND (end_stamp > '1540796400000'::bigint))
  • Filter: (NOT deleted)
75.          

SubPlan (forNested Loop Left Join)

76. 0.003 0.003 ↑ 1.1 16 1

CTE Scan on picklist_4 cte0_6 (cost=0.00..0.34 rows=17 width=4) (actual time=0.001..0.003 rows=16 loops=1)

77. 0.000 0.000 ↓ 0.0 0

CTE Scan on ownercte ownercte_2 (cost=0.00..23.76 rows=1,188 width=4) (never executed)

78. 0.000 0.000 ↑ 1.0 2 1

CTE Scan on picklist_0 cte0_7 (cost=0.00..0.04 rows=2 width=4) (actual time=0.000..0.000 rows=2 loops=1)

79. 0.000 0.000 ↑ 1.1 14 1

CTE Scan on picklist_1 cte0_8 (cost=0.00..0.30 rows=15 width=4) (actual time=0.000..0.000 rows=14 loops=1)

80. 0.050 0.050 ↑ 2.1 570 1

CTE Scan on ownercte ownercte_3 (cost=0.00..23.76 rows=1,188 width=4) (actual time=0.002..0.050 rows=570 loops=1)

81.          

CTE bucketec2

82. 28.305 6,085.488 ↑ 22,627.3 12 1

GroupAggregate (cost=3,813,162.50..3,823,344.80 rows=271,528 width=116) (actual time=6,085.356..6,085.488 rows=12 loops=1)

  • Group Key: (CASE WHEN (t203_opp_split_fact_5.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 16)) THEN CASE WHEN (t203_opp_split_fact_5.c782_opp_close_date > 20181231) THEN 'pulledInBucket'::text WHEN (t203_opp_split_fact_5.c782_opp_close_date < 20181001) THEN 'pushedInBucket'::text WHEN (hashed SubPlan 17) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END ELSE 'otherBucket'::text END), 'newOpen'::text, t203_opp_split_fact_4.c771_opp_stagename
83. 75.308 6,057.183 ↑ 2.5 107,104 1

Sort (cost=3,813,135.39..3,813,814.21 rows=271,528 width=88) (actual time=6,045.514..6,057.183 rows=107,104 loops=1)

  • Sort Key: (CASE WHEN (t203_opp_split_fact_5.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 16)) THEN CASE WHEN (t203_opp_split_fact_5.c782_opp_close_date > 20181231) THEN 'pulledInBucket'::text WHEN (t203_opp_split_fact_5.c782_opp_close_date < 20181001) THEN 'pushedInBucket'::text WHEN (hashed SubPlan 17) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END ELSE 'otherBucket'::text END), t203_opp_split_fact_4.c771_opp_stagename
  • Sort Method: external sort Disk: 6072kB
84. 274.164 5,981.875 ↑ 2.5 107,104 1

Nested Loop Left Join (cost=1,895,267.10..3,775,633.48 rows=271,528 width=88) (actual time=3,658.448..5,981.875 rows=107,104 loops=1)

  • Filter: ((t203_opp_split_fact_5.sid IS NULL) OR (t203_opp_split_fact_5.c782_opp_close_date < 20181001) OR (t203_opp_split_fact_5.c782_opp_close_date > 20181231) OR (NOT (hashed SubPlan 18)))
  • Rows Removed by Filter: 456359
85. 172.566 4,017.268 ↓ 1.9 563,463 1

Merge Left Join (cost=1,895,212.69..1,897,573.56 rows=293,480 width=24) (actual time=3,657.921..4,017.268 rows=563,463 loops=1)

  • Merge Cond: ((t203_opp_split_fact_4.c778_opp_currency_code = cur290_t0_r0_4.sid) AND (t203_opp_split_fact_4.c782_opp_close_date = cur290_t0_r0_4.rate_date))
86. 366.031 3,787.003 ↓ 1.9 563,463 1

Sort (cost=1,893,904.27..1,894,637.97 rows=293,480 width=24) (actual time=3,648.507..3,787.003 rows=563,463 loops=1)

  • Sort Key: t203_opp_split_fact_4.c778_opp_currency_code, t203_opp_split_fact_4.c782_opp_close_date
  • Sort Method: external merge Disk: 20856kB
87. 112.925 3,420.972 ↓ 1.9 563,463 1

Hash Join (cost=172,367.91..1,861,232.02 rows=293,480 width=24) (actual time=691.902..3,420.972 rows=563,463 loops=1)

  • Hash Cond: (t203_opp_split_fact_4.c712_opportunity_split_ownerid = ownercte_7.c1)
88. 2,627.716 3,307.768 ↑ 1.0 563,826 1

Bitmap Heap Scan on t203_opp_split_fact t203_opp_split_fact_4 (cost=172,336.68..1,857,492.73 rows=586,960 width=28) (actual time=691.612..3,307.768 rows=563,826 loops=1)

  • Recheck Cond: ((c782_opp_close_date >= 20181001) AND (c782_opp_close_date <= 20181231) AND (start_stamp <= '1541404799999'::bigint) AND (end_stamp > '1541404799999'::bigint))
  • Rows Removed by Index Recheck: 6064354
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 19)))
  • Rows Removed by Filter: 895334
  • Heap Blocks: exact=46076 lossy=189370
89. 680.031 680.031 ↓ 1.3 1,666,451 1

Bitmap Index Scan on ix_t203_opp_split_fact_closedate_timestamp (cost=0.00..172,189.56 rows=1,272,635 width=0) (actual time=680.031..680.031 rows=1,666,451 loops=1)

  • Index Cond: ((c782_opp_close_date >= 20181001) AND (c782_opp_close_date <= 20181231) AND (start_stamp <= '1541404799999'::bigint) AND (end_stamp > '1541404799999'::bigint))
90.          

SubPlan (forBitmap Heap Scan)

91. 0.021 0.021 ↑ 1.1 16 1

CTE Scan on picklist_4 cte0_11 (cost=0.00..0.34 rows=17 width=4) (actual time=0.002..0.021 rows=16 loops=1)

92. 0.053 0.279 ↓ 2.9 570 1

Hash (cost=28.73..28.73 rows=200 width=4) (actual time=0.279..0.279 rows=570 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
93. 0.175 0.226 ↓ 2.9 570 1

HashAggregate (cost=26.73..28.73 rows=200 width=4) (actual time=0.161..0.226 rows=570 loops=1)

  • Group Key: ownercte_7.c1
94. 0.051 0.051 ↑ 2.1 570 1

CTE Scan on ownercte ownercte_7 (cost=0.00..23.76 rows=1,188 width=4) (actual time=0.001..0.051 rows=570 loops=1)

95. 53.846 57.699 ↓ 40.1 576,456 1

Sort (cost=1,308.43..1,344.39 rows=14,387 width=16) (actual time=9.183..57.699 rows=576,456 loops=1)

  • Sort Key: cur290_t0_r0_4.sid, cur290_t0_r0_4.rate_date
  • Sort Method: quicksort Memory: 1057kB
96. 0.865 3.853 ↑ 1.0 14,351 1

Append (cost=0.00..314.83 rows=14,387 width=16) (actual time=0.021..3.853 rows=14,351 loops=1)

97. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on clari_conversion_rate cur290_t0_r0_4 (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)
98. 2.985 2.985 ↑ 1.0 14,351 1

Seq Scan on usd_clari_conversion_rate cur290_t0_r0_5 (cost=0.00..314.83 rows=14,386 width=16) (actual time=0.016..2.985 rows=14,351 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
99. 1,690.389 1,690.389 ↑ 1.0 1 563,463

Index Scan using ix_t203_opp_split_fact_sid_timestamp on t203_opp_split_fact t203_opp_split_fact_5 (cost=0.57..6.37 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=563,463)

  • Index Cond: ((t203_opp_split_fact_4.sid = sid) AND (t203_opp_split_fact_4.sid = sid) AND (start_stamp <= '1540796400000'::bigint) AND (end_stamp > '1540796400000'::bigint))
  • Filter: (NOT deleted)
100.          

SubPlan (forNested Loop Left Join)

101. 0.003 0.003 ↑ 1.1 16 1

CTE Scan on picklist_4 cte0_10 (cost=0.00..0.34 rows=17 width=4) (actual time=0.001..0.003 rows=16 loops=1)

102. 0.000 0.000 ↓ 0.0 0

CTE Scan on ownercte ownercte_5 (cost=0.00..23.76 rows=1,188 width=4) (never executed)

103. 0.051 0.051 ↑ 2.1 570 1

CTE Scan on ownercte ownercte_6 (cost=0.00..23.76 rows=1,188 width=4) (actual time=0.002..0.051 rows=570 loops=1)

104. 0.030 7,760.513 ↑ 2,336.1 41 1

Hash Join (cost=5.44..8,072.11 rows=95,782 width=107) (actual time=7,414.048..7,760.513 rows=41 loops=1)

  • Hash Cond: (cte0.c4 = t205_opportunitystagenamepicklistdim.sid)
105. 7,760.445 7,760.445 ↑ 7,300.5 41 1

CTE Scan on bucketac cte0 (cost=0.00..5,986.40 rows=299,320 width=92) (actual time=7,413.994..7,760.445 rows=41 loops=1)

106. 0.015 0.038 ↑ 1.0 64 1

Hash (cost=4.64..4.64 rows=64 width=23) (actual time=0.038..0.038 rows=64 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
107. 0.023 0.023 ↑ 1.0 64 1

Seq Scan on t205_opportunitystagenamepicklistdim (cost=0.00..4.64 rows=64 width=23) (actual time=0.008..0.023 rows=64 loops=1)

108. 0.016 5,714.671 ↑ 6,767.1 11 1

Subquery Scan on *SELECT* 2 (cost=5.44..7,205.02 rows=74,438 width=107) (actual time=5,705.854..5,714.671 rows=11 loops=1)

109. 0.023 5,714.655 ↑ 6,767.1 11 1

Hash Join (cost=5.44..6,274.55 rows=74,438 width=131) (actual time=5,705.844..5,714.655 rows=11 loops=1)

  • Hash Cond: (cte0_1.c6 = t205_opportunitystagenamepicklistdim_1.sid)
110. 5,714.595 5,714.595 ↑ 21,147.3 11 1

CTE Scan on bucketec1 cte0_1 (cost=0.00..4,652.40 rows=232,620 width=116) (actual time=5,705.787..5,714.595 rows=11 loops=1)

111. 0.014 0.037 ↑ 1.0 64 1

Hash (cost=4.64..4.64 rows=64 width=23) (actual time=0.037..0.037 rows=64 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
112. 0.023 0.023 ↑ 1.0 64 1

Seq Scan on t205_opportunitystagenamepicklistdim t205_opportunitystagenamepicklistdim_1 (cost=0.00..4.64 rows=64 width=23) (actual time=0.007..0.023 rows=64 loops=1)

113. 0.009 6,085.561 ↑ 7,240.8 12 1

Subquery Scan on *SELECT* 3 (cost=5.44..8,409.23 rows=86,889 width=107) (actual time=6,085.417..6,085.561 rows=12 loops=1)

114. 0.017 6,085.552 ↑ 7,240.8 12 1

Hash Join (cost=5.44..7,323.12 rows=86,889 width=131) (actual time=6,085.411..6,085.552 rows=12 loops=1)

  • Hash Cond: (cte0_2.c6 = t205_opportunitystagenamepicklistdim_2.sid)
115. 6,085.497 6,085.497 ↑ 22,627.3 12 1

CTE Scan on bucketec2 cte0_2 (cost=0.00..5,430.56 rows=271,528 width=116) (actual time=6,085.359..6,085.497 rows=12 loops=1)

116. 0.015 0.038 ↑ 1.0 64 1

Hash (cost=4.64..4.64 rows=64 width=23) (actual time=0.038..0.038 rows=64 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
117. 0.023 0.023 ↑ 1.0 64 1

Seq Scan on t205_opportunitystagenamepicklistdim t205_opportunitystagenamepicklistdim_2 (cost=0.00..4.64 rows=64 width=23) (actual time=0.006..0.023 rows=64 loops=1)