explain.depesz.com

PostgreSQL's explain analyze made readable

Result: k5xR

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 9,159.230 ↑ 6,492.4 50 1

Append (cost=165,318,809.85..165,330,326.96 rows=324,622 width=120) (actual time=1,036.164..9,159.230 rows=50 loops=1)

2.          

CTE picklist_0

3. 0.041 0.041 ↑ 1.0 6 1

Seq Scan on t376_opportunitystagenamepicklistdim (cost=0.00..4.54 rows=6 width=4) (actual time=0.018..0.041 rows=6 loops=1)

  • Filter: (correlated_value = ANY ('{"Closed - Booked","Closed- Booked","Closed - Booked (sales order no. require",Booked/Won,"Closed Booked","Budget Won/Live"}'::text[]))
  • Rows Removed by Filter: 82
4.          

CTE picklist_1

5. 0.026 0.026 ↑ 1.0 7 1

Seq Scan on t376_opportunitystagenamepicklistdim t376_opportunitystagenamepicklistdim_1 (cost=0.00..4.65 rows=7 width=4) (actual time=0.006..0.026 rows=7 loops=1)

  • Filter: (correlated_value = ANY ('{Lost,"Closed - Clean Up","No Commit","Closed Lost","Closed - CleanUp",Closed/Lost,"Closed - Lost"}'::text[]))
  • Rows Removed by Filter: 81
6.          

CTE picklist_4

7. 0.045 0.045 ↑ 1.0 13 1

Seq Scan on t376_opportunitystagenamepicklistdim t376_opportunitystagenamepicklistdim_2 (cost=0.00..5.31 rows=13 width=4) (actual time=0.014..0.045 rows=13 loops=1)

  • Filter: (correlated_value = ANY ('{Lost,"Closed - Clean Up",Booked/Won,"Closed - CleanUp","Closed Booked","Budget Won/Live","Closed - Booked","Closed- Booked","No Commit","Closed Lost","Closed - Booked (sales order no. require",Closed/Lost,"Closed - Lost"}'::text[]))
  • Rows Removed by Filter: 75
8.          

CTE bucketac

9. 5.484 1,043.000 ↑ 9,579.9 20 1

GroupAggregate (cost=84,152,572.09..106,317,115.84 rows=191,597 width=96) (actual time=1,036.120..1,043.000 rows=20 loops=1)

  • Group Key: t129_oppfact.c1882_forecast_indicator__csid, (CASE WHEN (t129_oppfact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t129_oppfact_1.c1863_opp_close_date < 20190302) OR (t129_oppfact_1.c1863_opp_close_date > 20190531)) THEN 'slipped'::text WHEN ((t129_oppfact_1.c1863_opp_close_date >= 20190302) AND (t129_oppfact_1.c1863_opp_close_date <= 20190531) AND ((alternatives: SubPlan 5 or hashed SubPlan 6) OR (alternatives: SubPlan 7 or hashed SubPlan 8))) THEN CASE WHEN (hashed SubPlan 9) THEN 'won'::text WHEN (hashed SubPlan 10) THEN 'lost'::text ELSE CASE WHEN (CASE WHEN (t373_opportunityforecast_indicator__cpicklistdim_3.correlated_app_order IS NULL) THEN cte0_5.c1 ELSE t373_opportunityforecast_indicator__cpicklistdim_3.correlated_app_order END = CASE WHEN (t373_opportunityforecast_indicator__cpicklistdim_4.correlated_app_order IS NULL) THEN cte0_5.c1 ELSE t373_opportunityforecast_indicator__cpicklistdim_4.correlated_app_order END) THEN 'unchanged'::text WHEN (CASE WHEN (t373_opportunityforecast_indicator__cpicklistdim_3.correlated_app_order IS NULL) THEN cte0_5.c1 ELSE t373_opportunityforecast_indicator__cpicklistdim_3.correlated_app_order END > CASE WHEN (t373_opportunityforecast_indicator__cpicklistdim_4.correlated_app_order IS NULL) THEN cte0_5.c1 ELSE t373_opportunityforecast_indicator__cpicklistdim_4.correlated_app_order END) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
10.          

CTE nullgroupbyvaluecte

11. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on t373_opportunityforecast_indicator__cpicklistdim t373_opportunityforecast_indicator__cpicklistdim_2 (cost=0.00..1.18 rows=1 width=4) (actual time=0.005..0.009 rows=1 loops=1)

  • Filter: (value = 'Value Not Assigned'::text)
  • Rows Removed by Filter: 13
12. 9.205 1,037.516 ↑ 10.8 17,713 1

Sort (cost=84,152,570.62..84,153,049.61 rows=191,597 width=72) (actual time=1,036.085..1,037.516 rows=17,713 loops=1)

  • Sort Key: t129_oppfact.c1882_forecast_indicator__csid, (CASE WHEN (t129_oppfact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t129_oppfact_1.c1863_opp_close_date < 20190302) OR (t129_oppfact_1.c1863_opp_close_date > 20190531)) THEN 'slipped'::text WHEN ((t129_oppfact_1.c1863_opp_close_date >= 20190302) AND (t129_oppfact_1.c1863_opp_close_date <= 20190531) AND ((alternatives: SubPlan 5 or hashed SubPlan 6) OR (alternatives: SubPlan 7 or hashed SubPlan 8))) THEN CASE WHEN (hashed SubPlan 9) THEN 'won'::text WHEN (hashed SubPlan 10) THEN 'lost'::text ELSE CASE WHEN (CASE WHEN (t373_opportunityforecast_indicator__cpicklistdim_3.correlated_app_order IS NULL) THEN cte0_5.c1 ELSE t373_opportunityforecast_indicator__cpicklistdim_3.correlated_app_order END = CASE WHEN (t373_opportunityforecast_indicator__cpicklistdim_4.correlated_app_order IS NULL) THEN cte0_5.c1 ELSE t373_opportunityforecast_indicator__cpicklistdim_4.correlated_app_order END) THEN 'unchanged'::text WHEN (CASE WHEN (t373_opportunityforecast_indicator__cpicklistdim_3.correlated_app_order IS NULL) THEN cte0_5.c1 ELSE t373_opportunityforecast_indicator__cpicklistdim_3.correlated_app_order END > CASE WHEN (t373_opportunityforecast_indicator__cpicklistdim_4.correlated_app_order IS NULL) THEN cte0_5.c1 ELSE t373_opportunityforecast_indicator__cpicklistdim_4.correlated_app_order END) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
  • Sort Method: quicksort Memory: 2346kB
13. 32.681 1,028.311 ↑ 10.8 17,713 1

Nested Loop Left Join (cost=1.71..84,127,899.17 rows=191,597 width=72) (actual time=348.168..1,028.311 rows=17,713 loops=1)

14. 18.916 827.305 ↑ 10.8 17,713 1

Nested Loop Left Join (cost=1.42..61,965,271.09 rows=191,597 width=64) (actual time=175.206..827.305 rows=17,713 loops=1)

  • Join Filter: (t129_oppfact_1.c1882_forecast_indicator__csid = t373_opportunityforecast_indicator__cpicklistdim_4.sid)
  • Rows Removed by Join Filter: 230276
15. 11.698 790.676 ↑ 10.8 17,713 1

Nested Loop Left Join (cost=1.42..61,925,034.55 rows=191,597 width=64) (actual time=175.200..790.676 rows=17,713 loops=1)

16. 10.345 743.552 ↑ 10.8 17,713 1

Nested Loop Left Join (cost=1.42..61,861,648.17 rows=191,597 width=60) (actual time=175.188..743.552 rows=17,713 loops=1)

17. 20.308 449.799 ↑ 10.8 17,713 1

Nested Loop Left Join (cost=0.85..60,631,816.96 rows=191,597 width=28) (actual time=175.158..449.799 rows=17,713 loops=1)

  • Join Filter: (t129_oppfact.c1882_forecast_indicator__csid = t373_opportunityforecast_indicator__cpicklistdim_3.sid)
  • Rows Removed by Join Filter: 230272
18. 13.487 411.778 ↑ 10.8 17,713 1

Nested Loop Left Join (cost=0.85..60,591,580.42 rows=191,597 width=24) (actual time=175.144..411.778 rows=17,713 loops=1)

19. 188.952 362.865 ↑ 10.8 17,713 1

Index Scan using ix_t129_oppfact_closedate_timestamp on t129_oppfact (cost=0.85..60,526,245.41 rows=191,597 width=24) (actual time=175.109..362.865 rows=17,713 loops=1)

  • Index Cond: ((c1863_opp_close_date >= 20190302) AND (c1863_opp_close_date <= 20190531) AND (start_stamp <= '1551513600000'::bigint) AND (end_stamp > '1551513600000'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 15)) AND ((alternatives: SubPlan 11 or hashed SubPlan 12) OR (alternatives: SubPlan 13 or hashed SubPlan 14)))
  • Rows Removed by Filter: 11159
20.          

SubPlan (forIndex Scan)

21. 0.049 0.049 ↑ 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.049 rows=13 loops=1)

22. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.85..107.19 rows=1 width=0) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t333_userrolehierarchy_sid_c3186_ancestor_role_id_e50459228 on t333_userrolehierarchy t333_userrolehierarchy_2 (cost=0.43..31.60 rows=8 width=4) (never executed)

  • Index Cond: ((sid = t129_oppfact.c1754_opp_ownerid) AND (c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
24. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t125_userroledim_sid_end_stamp_start_stamp on t125_userroledim t125_userroledim_2 (cost=0.42..8.45 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t333_userrolehierarchy_2.c3185_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c1646_portaltype IS NULL) OR (c1646_portaltype = 'None'::text)))
25. 29.914 163.359 ↓ 4.3 43,566 1

Hash Join (cost=16,900.58..53,507.96 rows=10,116 width=4) (actual time=87.758..163.359 rows=43,566 loops=1)

  • Hash Cond: (t333_userrolehierarchy_3.c3185_user_role_id = t125_userroledim_3.sid)
26. 48.131 64.744 ↓ 1.0 194,690 1

Bitmap Heap Scan on t333_userrolehierarchy t333_userrolehierarchy_3 (cost=5,837.24..41,678.84 rows=193,640 width=8) (actual time=18.653..64.744 rows=194,690 loops=1)

  • Recheck Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=12474
27. 16.613 16.613 ↓ 1.0 194,852 1

Bitmap Index Scan on idx_t333_userrolehierarchy_c3186_ancestor_role_id_end_s50459228 (cost=0.00..5,788.83 rows=193,640 width=0) (actual time=16.613..16.613 rows=194,852 loops=1)

  • Index Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
28. 0.181 68.701 ↑ 2.1 1,055 1

Hash (cost=11,036.25..11,036.25 rows=2,167 width=4) (actual time=68.701..68.701 rows=1,055 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 70kB
29. 68.520 68.520 ↑ 2.1 1,055 1

Seq Scan on t125_userroledim t125_userroledim_3 (cost=0.00..11,036.25 rows=2,167 width=4) (actual time=2.330..68.520 rows=1,055 loops=1)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c1646_portaltype IS NULL) OR (c1646_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 214618
30. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t105_userdim_sid_end_stamp_start_stamp on t105_userdim t105_userdim_2 (cost=0.42..8.45 rows=1 width=0) (never executed)

  • Index Cond: ((sid = t129_oppfact.c1754_opp_ownerid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c1256_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
31. 9.521 10.505 ↓ 1.3 5,557 1

Bitmap Heap Scan on t105_userdim t105_userdim_3 (cost=245.15..13,035.12 rows=4,358 width=4) (actual time=1.493..10.505 rows=5,557 loops=1)

  • Recheck Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
  • Rows Removed by Filter: 2762
  • Heap Blocks: exact=3398
32. 0.984 0.984 ↓ 1.0 8,331 1

Bitmap Index Scan on idx_t105_userdim_c1256_userroleid_end_stamp_start_stamp (cost=0.00..244.06 rows=7,964 width=0) (actual time=0.984..0.984 rows=8,331 loops=1)

  • Index Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
33. 0.000 35.426 ↑ 2.0 1 17,713

Append (cost=0.00..0.32 rows=2 width=16) (actual time=0.002..0.002 rows=1 loops=17,713)

34. 0.000 0.000 ↓ 0.0 0 17,713

Seq Scan on t332_clari_conversion_rate (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=17,713)

  • Filter: ((to_iso_code = 'USD'::text) AND (t129_oppfact.c1746_opp_currency_code = sid) AND (t129_oppfact.c1863_opp_close_date = rate_date))
35. 35.426 35.426 ↑ 1.0 1 17,713

Index Scan using t332_clari_conversion_rate_usd_sidratedateunique on t332_clari_conversion_rate_usd (cost=0.29..0.32 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=17,713)

  • Index Cond: ((t129_oppfact.c1746_opp_currency_code = sid) AND (t129_oppfact.c1863_opp_close_date = rate_date))
  • Filter: (to_iso_code = 'USD'::text)
36. 17.705 17.713 ↑ 1.0 14 17,713

Materialize (cost=0.00..1.21 rows=14 width=8) (actual time=0.000..0.001 rows=14 loops=17,713)

37. 0.008 0.008 ↑ 1.0 14 1

Seq Scan on t373_opportunityforecast_indicator__cpicklistdim t373_opportunityforecast_indicator__cpicklistdim_3 (cost=0.00..1.14 rows=14 width=8) (actual time=0.005..0.008 rows=14 loops=1)

38. 283.408 283.408 ↑ 1.0 1 17,713

Index Scan using t129_oppfact_sidendstampunique on t129_oppfact t129_oppfact_1 (cost=0.56..6.41 rows=1 width=32) (actual time=0.005..0.016 rows=1 loops=17,713)

  • Index Cond: ((t129_oppfact.sid = sid) AND (t129_oppfact.sid = sid) AND (end_stamp > '1559372399999'::bigint))
  • Filter: ((NOT deleted) AND (start_stamp <= '1559372399999'::bigint))
  • Rows Removed by Filter: 8
39. 0.000 35.426 ↑ 2.0 1 17,713

Append (cost=0.00..0.31 rows=2 width=16) (actual time=0.002..0.002 rows=1 loops=17,713)

40. 0.000 0.000 ↓ 0.0 0 17,713

Seq Scan on t332_clari_conversion_rate t332_clari_conversion_rate_1 (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=17,713)

  • Filter: ((to_iso_code = 'USD'::text) AND (t129_oppfact_1.c1746_opp_currency_code = sid) AND (t129_oppfact_1.c1863_opp_close_date = rate_date))
41. 35.426 35.426 ↑ 1.0 1 17,713

Index Scan using t332_clari_conversion_rate_usd_sidratedateunique on t332_clari_conversion_rate_usd t332_clari_conversion_rate_usd_1 (cost=0.29..0.31 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=17,713)

  • Index Cond: ((t129_oppfact_1.c1746_opp_currency_code = sid) AND (t129_oppfact_1.c1863_opp_close_date = rate_date))
  • Filter: (to_iso_code = 'USD'::text)
42. 17.707 17.713 ↑ 1.0 14 17,713

Materialize (cost=0.00..1.21 rows=14 width=8) (actual time=0.000..0.001 rows=14 loops=17,713)

43. 0.006 0.006 ↑ 1.0 14 1

Seq Scan on t373_opportunityforecast_indicator__cpicklistdim t373_opportunityforecast_indicator__cpicklistdim_4 (cost=0.00..1.14 rows=14 width=8) (actual time=0.002..0.006 rows=14 loops=1)

44. 0.000 0.000 ↑ 1.0 1 17,713

CTE Scan on nullgroupbyvaluecte cte0_5 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=17,713)

45.          

SubPlan (forNested Loop Left Join)

46. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.85..107.19 rows=1 width=0) (never executed)

47. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t333_userrolehierarchy_sid_c3186_ancestor_role_id_e50459228 on t333_userrolehierarchy (cost=0.43..31.60 rows=8 width=4) (never executed)

  • Index Cond: ((sid = t129_oppfact_1.c1754_opp_ownerid) AND (c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
48. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t125_userroledim_sid_end_stamp_start_stamp on t125_userroledim (cost=0.42..8.45 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t333_userrolehierarchy.c3185_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c1646_portaltype IS NULL) OR (c1646_portaltype = 'None'::text)))
49. 28.414 161.306 ↓ 4.3 43,566 1

Hash Join (cost=16,900.58..53,507.96 rows=10,116 width=4) (actual time=87.666..161.306 rows=43,566 loops=1)

  • Hash Cond: (t333_userrolehierarchy_1.c3185_user_role_id = t125_userroledim_1.sid)
50. 47.743 64.404 ↓ 1.0 194,690 1

Bitmap Heap Scan on t333_userrolehierarchy t333_userrolehierarchy_1 (cost=5,837.24..41,678.84 rows=193,640 width=8) (actual time=18.793..64.404 rows=194,690 loops=1)

  • Recheck Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=12474
51. 16.661 16.661 ↓ 1.0 194,852 1

Bitmap Index Scan on idx_t333_userrolehierarchy_c3186_ancestor_role_id_end_s50459228 (cost=0.00..5,788.83 rows=193,640 width=0) (actual time=16.661..16.661 rows=194,852 loops=1)

  • Index Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
52. 0.160 68.488 ↑ 2.1 1,055 1

Hash (cost=11,036.25..11,036.25 rows=2,167 width=4) (actual time=68.488..68.488 rows=1,055 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 70kB
53. 68.328 68.328 ↑ 2.1 1,055 1

Seq Scan on t125_userroledim t125_userroledim_1 (cost=0.00..11,036.25 rows=2,167 width=4) (actual time=2.389..68.328 rows=1,055 loops=1)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c1646_portaltype IS NULL) OR (c1646_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 214618
54. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t105_userdim_sid_end_stamp_start_stamp on t105_userdim (cost=0.42..8.45 rows=1 width=0) (never executed)

  • Index Cond: ((sid = t129_oppfact_1.c1754_opp_ownerid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c1256_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
55. 5.948 6.948 ↓ 1.3 5,557 1

Bitmap Heap Scan on t105_userdim t105_userdim_1 (cost=245.15..13,035.12 rows=4,358 width=4) (actual time=1.483..6.948 rows=5,557 loops=1)

  • Recheck Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
  • Rows Removed by Filter: 2762
  • Heap Blocks: exact=3398
56. 1.000 1.000 ↓ 1.0 8,331 1

Bitmap Index Scan on idx_t105_userdim_c1256_userroleid_end_stamp_start_stamp (cost=0.00..244.06 rows=7,964 width=0) (actual time=1.000..1.000 rows=8,331 loops=1)

  • Index Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
57. 0.042 0.042 ↑ 1.0 6 1

CTE Scan on picklist_0 cte0_2 (cost=0.00..0.12 rows=6 width=4) (actual time=0.020..0.042 rows=6 loops=1)

58. 0.029 0.029 ↑ 1.0 7 1

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

59.          

CTE bucketec

60. 6.222 8,116.082 ↑ 4,434.2 30 1

GroupAggregate (cost=43,613,925.37..59,001,678.20 rows=133,025 width=120) (actual time=8,108.044..8,116.082 rows=30 loops=1)

  • Group Key: (CASE WHEN (t129_oppfact_3.sid IS NULL) THEN 'newBucket'::text WHEN (t129_oppfact_3.c1863_opp_close_date > 20190531) THEN 'pulledInBucket'::text WHEN (t129_oppfact_3.c1863_opp_close_date < 20190302) THEN 'pushedInBucket'::text WHEN ((t129_oppfact_3.c1863_opp_close_date >= 20190302) AND (t129_oppfact_3.c1863_opp_close_date <= 20190531) AND ((alternatives: SubPlan 17 or hashed SubPlan 18) OR (alternatives: SubPlan 19 or hashed SubPlan 20))) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END), (CASE WHEN (hashed SubPlan 21) THEN 'won'::text WHEN (hashed SubPlan 22) THEN 'lost'::text ELSE 'newOpen'::text END), t129_oppfact_2.c1882_forecast_indicator__csid
61. 12.394 8,109.860 ↑ 5.5 24,405 1

Sort (cost=43,613,925.08..43,614,257.64 rows=133,025 width=88) (actual time=8,108.029..8,109.860 rows=24,405 loops=1)

  • Sort Key: (CASE WHEN (t129_oppfact_3.sid IS NULL) THEN 'newBucket'::text WHEN (t129_oppfact_3.c1863_opp_close_date > 20190531) THEN 'pulledInBucket'::text WHEN (t129_oppfact_3.c1863_opp_close_date < 20190302) THEN 'pushedInBucket'::text WHEN ((t129_oppfact_3.c1863_opp_close_date >= 20190302) AND (t129_oppfact_3.c1863_opp_close_date <= 20190531) AND ((alternatives: SubPlan 17 or hashed SubPlan 18) OR (alternatives: SubPlan 19 or hashed SubPlan 20))) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END), (CASE WHEN (hashed SubPlan 21) THEN 'won'::text WHEN (hashed SubPlan 22) THEN 'lost'::text ELSE 'newOpen'::text END), t129_oppfact_2.c1882_forecast_indicator__csid
  • Sort Method: quicksort Memory: 2676kB
62. 27.667 8,097.466 ↑ 5.5 24,405 1

Merge Left Join (cost=28,210,839.37..43,596,237.26 rows=133,025 width=88) (actual time=7,891.148..8,097.466 rows=24,405 loops=1)

  • Merge Cond: ((t129_oppfact_2.c1746_opp_currency_code = t332_clari_conversion_rate_2.sid) AND (t129_oppfact_2.c1863_opp_close_date = t332_clari_conversion_rate_2.rate_date))
63. 13.739 7,868.669 ↑ 5.5 24,405 1

Sort (cost=28,207,566.79..28,207,899.36 rows=133,025 width=40) (actual time=7,865.694..7,868.669 rows=24,405 loops=1)

  • Sort Key: t129_oppfact_2.c1746_opp_currency_code, t129_oppfact_2.c1863_opp_close_date
  • Sort Method: quicksort Memory: 2675kB
64. 112.060 7,854.930 ↑ 5.5 24,405 1

Merge Right Join (cost=28,143,895.78..28,192,605.48 rows=133,025 width=40) (actual time=7,345.575..7,854.930 rows=24,405 loops=1)

  • Merge Cond: ((t129_oppfact_3.sid = t129_oppfact_2.sid) AND (t129_oppfact_3.sid = t129_oppfact_2.sid))
  • Filter: ((t129_oppfact_3.sid IS NULL) OR (t129_oppfact_3.c1863_opp_close_date < 20190302) OR (t129_oppfact_3.c1863_opp_close_date > 20190531) OR ((NOT (alternatives: SubPlan 27 or hashed SubPlan 28)) AND (NOT (alternatives: SubPlan 29 or hashed SubPlan 30))))
  • Rows Removed by Filter: 15441
65. 649.803 7,150.133 ↑ 5.4 1,122,779 1

Sort (cost=2,523,757.01..2,538,800.28 rows=6,017,307 width=12) (actual time=6,933.242..7,150.133 rows=1,122,779 loops=1)

  • Sort Key: t129_oppfact_3.sid
  • Sort Method: external merge Disk: 24104kB
66. 6,500.330 6,500.330 ↑ 5.4 1,122,779 1

Seq Scan on t129_oppfact t129_oppfact_3 (cost=0.00..1,640,513.58 rows=6,017,307 width=12) (actual time=303.530..6,500.330 rows=1,122,779 loops=1)

  • Filter: ((NOT deleted) AND (start_stamp <= '1551513600000'::bigint) AND (end_stamp > '1551513600000'::bigint))
  • Rows Removed by Filter: 25902323
67. 3.865 422.087 ↑ 4.1 39,846 1

Materialize (cost=25,620,138.77..25,620,945.71 rows=161,388 width=28) (actual time=412.282..422.087 rows=39,846 loops=1)

68. 20.027 418.222 ↑ 4.1 39,846 1

Sort (cost=25,620,138.77..25,620,542.24 rows=161,388 width=28) (actual time=412.277..418.222 rows=39,846 loops=1)

  • Sort Key: t129_oppfact_2.sid
  • Sort Method: quicksort Memory: 4059kB
69. 228.236 398.195 ↑ 4.1 39,846 1

Index Scan using ix_t129_oppfact_closedate_timestamp on t129_oppfact t129_oppfact_2 (cost=0.56..25,602,314.56 rows=161,388 width=28) (actual time=182.841..398.195 rows=39,846 loops=1)

  • Index Cond: ((c1863_opp_close_date >= 20190302) AND (c1863_opp_close_date <= 20190531) AND (start_stamp <= '1559372399999'::bigint) AND (end_stamp > '1559372399999'::bigint))
  • Filter: ((NOT deleted) AND ((alternatives: SubPlan 23 or hashed SubPlan 24) OR (alternatives: SubPlan 25 or hashed SubPlan 26)))
  • Rows Removed by Filter: 12575
70.          

SubPlan (forIndex Scan)

71. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.85..107.19 rows=1 width=0) (never executed)

72. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t333_userrolehierarchy_sid_c3186_ancestor_role_id_e50459228 on t333_userrolehierarchy t333_userrolehierarchy_6 (cost=0.43..31.60 rows=8 width=4) (never executed)

  • Index Cond: ((sid = t129_oppfact_2.c1754_opp_ownerid) AND (c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
73. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t125_userroledim_sid_end_stamp_start_stamp on t125_userroledim t125_userroledim_6 (cost=0.42..8.45 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t333_userrolehierarchy_6.c3185_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c1646_portaltype IS NULL) OR (c1646_portaltype = 'None'::text)))
74. 28.105 159.445 ↓ 4.3 43,566 1

Hash Join (cost=16,900.58..53,507.96 rows=10,116 width=4) (actual time=86.946..159.445 rows=43,566 loops=1)

  • Hash Cond: (t333_userrolehierarchy_7.c3185_user_role_id = t125_userroledim_7.sid)
75. 46.826 63.420 ↓ 1.0 194,690 1

Bitmap Heap Scan on t333_userrolehierarchy t333_userrolehierarchy_7 (cost=5,837.24..41,678.84 rows=193,640 width=8) (actual time=18.641..63.420 rows=194,690 loops=1)

  • Recheck Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=12474
76. 16.594 16.594 ↓ 1.0 194,852 1

Bitmap Index Scan on idx_t333_userrolehierarchy_c3186_ancestor_role_id_end_s50459228 (cost=0.00..5,788.83 rows=193,640 width=0) (actual time=16.594..16.594 rows=194,852 loops=1)

  • Index Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
77. 0.182 67.920 ↑ 2.1 1,055 1

Hash (cost=11,036.25..11,036.25 rows=2,167 width=4) (actual time=67.920..67.920 rows=1,055 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 70kB
78. 67.738 67.738 ↑ 2.1 1,055 1

Seq Scan on t125_userroledim t125_userroledim_7 (cost=0.00..11,036.25 rows=2,167 width=4) (actual time=2.395..67.738 rows=1,055 loops=1)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c1646_portaltype IS NULL) OR (c1646_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 214618
79. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t105_userdim_sid_end_stamp_start_stamp on t105_userdim t105_userdim_6 (cost=0.42..8.45 rows=1 width=0) (never executed)

  • Index Cond: ((sid = t129_oppfact_2.c1754_opp_ownerid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c1256_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
80. 9.451 10.514 ↓ 1.3 5,557 1

Bitmap Heap Scan on t105_userdim t105_userdim_7 (cost=245.15..13,035.12 rows=4,358 width=4) (actual time=1.617..10.514 rows=5,557 loops=1)

  • Recheck Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
  • Rows Removed by Filter: 2762
  • Heap Blocks: exact=3398
81. 1.063 1.063 ↓ 1.0 8,331 1

Bitmap Index Scan on idx_t105_userdim_c1256_userroleid_end_stamp_start_stamp (cost=0.00..244.06 rows=7,964 width=0) (actual time=1.063..1.063 rows=8,331 loops=1)

  • Index Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
82.          

SubPlan (forMerge Right Join)

83. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.85..107.19 rows=1 width=0) (never executed)

84. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t333_userrolehierarchy_sid_c3186_ancestor_role_id_e50459228 on t333_userrolehierarchy t333_userrolehierarchy_8 (cost=0.43..31.60 rows=8 width=4) (never executed)

  • Index Cond: ((sid = t129_oppfact_3.c1754_opp_ownerid) AND (c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
85. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t125_userroledim_sid_end_stamp_start_stamp on t125_userroledim t125_userroledim_8 (cost=0.42..8.45 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t333_userrolehierarchy_8.c3185_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c1646_portaltype IS NULL) OR (c1646_portaltype = 'None'::text)))
86. 28.815 160.080 ↓ 4.3 43,566 1

Hash Join (cost=16,900.58..53,507.96 rows=10,116 width=4) (actual time=86.538..160.080 rows=43,566 loops=1)

  • Hash Cond: (t333_userrolehierarchy_9.c3185_user_role_id = t125_userroledim_9.sid)
87. 47.142 63.350 ↓ 1.0 194,690 1

Bitmap Heap Scan on t333_userrolehierarchy t333_userrolehierarchy_9 (cost=5,837.24..41,678.84 rows=193,640 width=8) (actual time=18.239..63.350 rows=194,690 loops=1)

  • Recheck Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=12474
88. 16.208 16.208 ↓ 1.0 194,852 1

Bitmap Index Scan on idx_t333_userrolehierarchy_c3186_ancestor_role_id_end_s50459228 (cost=0.00..5,788.83 rows=193,640 width=0) (actual time=16.208..16.208 rows=194,852 loops=1)

  • Index Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
89. 0.156 67.915 ↑ 2.1 1,055 1

Hash (cost=11,036.25..11,036.25 rows=2,167 width=4) (actual time=67.915..67.915 rows=1,055 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 70kB
90. 67.759 67.759 ↑ 2.1 1,055 1

Seq Scan on t125_userroledim t125_userroledim_9 (cost=0.00..11,036.25 rows=2,167 width=4) (actual time=2.222..67.759 rows=1,055 loops=1)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c1646_portaltype IS NULL) OR (c1646_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 214618
91. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t105_userdim_sid_end_stamp_start_stamp on t105_userdim t105_userdim_8 (cost=0.42..8.45 rows=1 width=0) (never executed)

  • Index Cond: ((sid = t129_oppfact_3.c1754_opp_ownerid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c1256_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
92. 9.539 10.570 ↓ 1.3 5,557 1

Bitmap Heap Scan on t105_userdim t105_userdim_9 (cost=245.15..13,035.12 rows=4,358 width=4) (actual time=1.543..10.570 rows=5,557 loops=1)

  • Recheck Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
  • Rows Removed by Filter: 2762
  • Heap Blocks: exact=3398
93. 1.031 1.031 ↓ 1.0 8,331 1

Bitmap Index Scan on idx_t105_userdim_c1256_userroleid_end_stamp_start_stamp (cost=0.00..244.06 rows=7,964 width=0) (actual time=1.031..1.031 rows=8,331 loops=1)

  • Index Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
94. 19.834 29.466 ↓ 1.7 55,349 1

Sort (cost=3,272.28..3,354.20 rows=32,769 width=16) (actual time=24.824..29.466 rows=55,349 loops=1)

  • Sort Key: t332_clari_conversion_rate_2.sid, t332_clari_conversion_rate_2.rate_date
  • Sort Method: quicksort Memory: 2304kB
95. 2.027 9.632 ↑ 1.0 32,747 1

Append (cost=0.00..814.60 rows=32,769 width=16) (actual time=0.150..9.632 rows=32,747 loops=1)

96. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on t332_clari_conversion_rate t332_clari_conversion_rate_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)
97. 7.602 7.602 ↑ 1.0 32,747 1

Seq Scan on t332_clari_conversion_rate_usd t332_clari_conversion_rate_usd_2 (cost=0.00..814.60 rows=32,768 width=16) (actual time=0.146..7.602 rows=32,747 loops=1)

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

SubPlan (forMerge Left Join)

99. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.85..107.19 rows=1 width=0) (never executed)

100. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t333_userrolehierarchy_sid_c3186_ancestor_role_id_e50459228 on t333_userrolehierarchy t333_userrolehierarchy_4 (cost=0.43..31.60 rows=8 width=4) (never executed)

  • Index Cond: ((sid = t129_oppfact_3.c1754_opp_ownerid) AND (c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
101. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t125_userroledim_sid_end_stamp_start_stamp on t125_userroledim t125_userroledim_4 (cost=0.42..8.45 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t333_userrolehierarchy_4.c3185_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c1646_portaltype IS NULL) OR (c1646_portaltype = 'None'::text)))
102. 28.922 161.329 ↓ 4.3 43,566 1

Hash Join (cost=16,900.58..53,507.96 rows=10,116 width=4) (actual time=87.072..161.329 rows=43,566 loops=1)

  • Hash Cond: (t333_userrolehierarchy_5.c3185_user_role_id = t125_userroledim_5.sid)
103. 47.734 64.300 ↓ 1.0 194,690 1

Bitmap Heap Scan on t333_userrolehierarchy t333_userrolehierarchy_5 (cost=5,837.24..41,678.84 rows=193,640 width=8) (actual time=18.604..64.300 rows=194,690 loops=1)

  • Recheck Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=12474
104. 16.566 16.566 ↓ 1.0 194,852 1

Bitmap Index Scan on idx_t333_userrolehierarchy_c3186_ancestor_role_id_end_s50459228 (cost=0.00..5,788.83 rows=193,640 width=0) (actual time=16.566..16.566 rows=194,852 loops=1)

  • Index Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
105. 0.181 68.107 ↑ 2.1 1,055 1

Hash (cost=11,036.25..11,036.25 rows=2,167 width=4) (actual time=68.107..68.107 rows=1,055 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 70kB
106. 67.926 67.926 ↑ 2.1 1,055 1

Seq Scan on t125_userroledim t125_userroledim_5 (cost=0.00..11,036.25 rows=2,167 width=4) (actual time=2.345..67.926 rows=1,055 loops=1)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c1646_portaltype IS NULL) OR (c1646_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 214618
107. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t105_userdim_sid_end_stamp_start_stamp on t105_userdim t105_userdim_4 (cost=0.42..8.45 rows=1 width=0) (never executed)

  • Index Cond: ((sid = t129_oppfact_3.c1754_opp_ownerid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c1256_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
108. 9.251 10.331 ↓ 1.3 5,557 1

Bitmap Heap Scan on t105_userdim t105_userdim_5 (cost=245.15..13,035.12 rows=4,358 width=4) (actual time=1.557..10.331 rows=5,557 loops=1)

  • Recheck Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
  • Rows Removed by Filter: 2762
  • Heap Blocks: exact=3398
109. 1.080 1.080 ↓ 1.0 8,331 1

Bitmap Index Scan on idx_t105_userdim_c1256_userroleid_end_stamp_start_stamp (cost=0.00..244.06 rows=7,964 width=0) (actual time=1.080..1.080 rows=8,331 loops=1)

  • Index Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
110. 0.002 0.002 ↑ 1.0 6 1

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

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

112. 0.032 1,043.058 ↑ 9,579.9 20 1

Hash Left Join (cost=1.32..4,685.86 rows=191,597 width=120) (actual time=1,036.162..1,043.058 rows=20 loops=1)

  • Hash Cond: (cte0.c5 = t373_opportunityforecast_indicator__cpicklistdim.sid)
113. 1,043.012 1,043.012 ↑ 9,579.9 20 1

CTE Scan on bucketac cte0 (cost=0.00..3,831.94 rows=191,597 width=92) (actual time=1,036.124..1,043.012 rows=20 loops=1)

114. 0.005 0.014 ↑ 1.0 14 1

Hash (cost=1.14..1.14 rows=14 width=36) (actual time=0.014..0.014 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
115. 0.009 0.009 ↑ 1.0 14 1

Seq Scan on t373_opportunityforecast_indicator__cpicklistdim (cost=0.00..1.14 rows=14 width=36) (actual time=0.006..0.009 rows=14 loops=1)

116. 0.023 8,116.165 ↑ 4,434.2 30 1

Subquery Scan on *SELECT* 2 (cost=1.32..4,916.59 rows=133,025 width=120) (actual time=8,108.095..8,116.165 rows=30 loops=1)

117. 0.025 8,116.142 ↑ 4,434.2 30 1

Hash Left Join (cost=1.32..3,253.78 rows=133,025 width=144) (actual time=8,108.084..8,116.142 rows=30 loops=1)

  • Hash Cond: (cte0_1.c7 = t373_opportunityforecast_indicator__cpicklistdim_1.sid)
118. 8,116.102 8,116.102 ↑ 4,434.2 30 1

CTE Scan on bucketec cte0_1 (cost=0.00..2,660.50 rows=133,025 width=116) (actual time=8,108.049..8,116.102 rows=30 loops=1)

119. 0.004 0.015 ↑ 1.0 14 1

Hash (cost=1.14..1.14 rows=14 width=36) (actual time=0.015..0.015 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
120. 0.011 0.011 ↑ 1.0 14 1

Seq Scan on t373_opportunityforecast_indicator__cpicklistdim t373_opportunityforecast_indicator__cpicklistdim_1 (cost=0.00..1.14 rows=14 width=36) (actual time=0.007..0.011 rows=14 loops=1)

Planning time : 15.795 ms
Execution time : 9,168.363 ms