explain.depesz.com

PostgreSQL's explain analyze made readable

Result: H4ig

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 1,954.516 ↑ 1,598.6 50 1

Append (cost=64,526,444.51..64,529,301.21 rows=79,929 width=120) (actual time=816.965..1,954.516 rows=50 loops=1)

2.          

CTE picklist_0

3. 0.045 0.045 ↑ 1.0 6 1

Seq Scan on t376_opportunitystagenamepicklistdim (cost=0.00..4.54 rows=6 width=4) (actual time=0.021..0.045 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.029 0.029 ↑ 1.0 7 1

Seq Scan on t376_opportunitystagenamepicklistdim t376_opportunitystagenamepicklistdim_1 (cost=0.00..4.65 rows=7 width=4) (actual time=0.009..0.029 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.059 0.059 ↑ 1.0 13 1

Seq Scan on t376_opportunitystagenamepicklistdim t376_opportunitystagenamepicklistdim_2 (cost=0.00..5.31 rows=13 width=4) (actual time=0.025..0.059 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. 3.328 821.302 ↑ 1,866.5 21 1

GroupAggregate (cost=22,765,573.52..27,299,694.14 rows=39,196 width=96) (actual time=816.931..821.302 rows=21 loops=1)

  • Group Key: oli1880_ft0.c2, (CASE WHEN (oli1880_ft1.c1 IS NULL) THEN 'deleted'::text WHEN ((oli1880_ft1.c2 < 20190302) OR (oli1880_ft1.c2 > 20190531)) THEN 'slipped'::text WHEN ((oli1880_ft1.c2 >= 20190302) AND (oli1880_ft1.c2 <= 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 (oli1880_ft0.c6 IS NULL) THEN cte0_5.c1 ELSE oli1880_ft0.c6 END = CASE WHEN (oli1880_ft1.c5 IS NULL) THEN cte0_5.c1 ELSE oli1880_ft1.c5 END) THEN 'unchanged'::text WHEN (CASE WHEN (oli1880_ft0.c6 IS NULL) THEN cte0_5.c1 ELSE oli1880_ft0.c6 END > CASE WHEN (oli1880_ft1.c5 IS NULL) THEN cte0_5.c1 ELSE oli1880_ft1.c5 END) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
10.          

CTE nullgroupbyvaluecte

11. 0.005 0.005 ↑ 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.004..0.005 rows=1 loops=1)

  • Filter: (value = 'Value Not Assigned'::text)
  • Rows Removed by Filter: 13
12. 6.287 817.974 ↑ 2.7 14,628 1

Sort (cost=22,765,572.05..22,765,670.04 rows=39,196 width=56) (actual time=816.921..817.974 rows=14,628 loops=1)

  • Sort Key: oli1880_ft0.c2, (CASE WHEN (oli1880_ft1.c1 IS NULL) THEN 'deleted'::text WHEN ((oli1880_ft1.c2 < 20190302) OR (oli1880_ft1.c2 > 20190531)) THEN 'slipped'::text WHEN ((oli1880_ft1.c2 >= 20190302) AND (oli1880_ft1.c2 <= 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 (oli1880_ft0.c6 IS NULL) THEN cte0_5.c1 ELSE oli1880_ft0.c6 END = CASE WHEN (oli1880_ft1.c5 IS NULL) THEN cte0_5.c1 ELSE oli1880_ft1.c5 END) THEN 'unchanged'::text WHEN (CASE WHEN (oli1880_ft0.c6 IS NULL) THEN cte0_5.c1 ELSE oli1880_ft0.c6 END > CASE WHEN (oli1880_ft1.c5 IS NULL) THEN cte0_5.c1 ELSE oli1880_ft1.c5 END) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
  • Sort Method: quicksort Memory: 1527kB
13. 29.295 811.687 ↑ 2.7 14,628 1

Nested Loop Left Join (cost=313,970.42..22,762,581.71 rows=39,196 width=56) (actual time=332.267..811.687 rows=14,628 loops=1)

14. 6.366 616.247 ↑ 2.7 14,628 1

Nested Loop Left Join (cost=313,970.13..18,228,656.77 rows=39,196 width=48) (actual time=332.255..616.247 rows=14,628 loops=1)

15. 23.268 375.833 ↑ 2.7 14,628 1

Subquery Scan on oli1880_ft0 (cost=313,822.07..12,404,964.61 rows=39,196 width=20) (actual time=332.144..375.833 rows=14,628 loops=1)

  • Filter: ((NOT (hashed SubPlan 15)) AND ((alternatives: SubPlan 11 or hashed SubPlan 12) OR (alternatives: SubPlan 13 or hashed SubPlan 14)))
  • Rows Removed by Filter: 9980
16. 14.554 186.076 ↑ 4.2 24,608 1

GroupAggregate (cost=313,821.78..317,218.81 rows=104,524 width=32) (actual time=163.757..186.076 rows=24,608 loops=1)

  • Group Key: t337_opp_line_item_fact.c3414_opportunity_sid, t337_opp_line_item_fact.c3477_opportunity_forecast_indicator__csid, t337_opp_line_item_fact.c3468_opp_close_date, t337_opp_line_item_fact.c3419_opp_ownerid, t337_opp_line_item_fact.c3454_opp_stagename, t373_opportunityforecast_indicator__cpicklistdim_3.correlated_app_order
17. 23.547 171.522 ↑ 2.7 39,087 1

Sort (cost=313,821.78..314,083.09 rows=104,524 width=40) (actual time=163.744..171.522 rows=39,087 loops=1)

  • Sort Key: t337_opp_line_item_fact.c3414_opportunity_sid, t337_opp_line_item_fact.c3477_opportunity_forecast_indicator__csid, t337_opp_line_item_fact.c3468_opp_close_date, t337_opp_line_item_fact.c3419_opp_ownerid, t337_opp_line_item_fact.c3454_opp_stagename, t373_opportunityforecast_indicator__cpicklistdim_3.correlated_app_order
  • Sort Method: quicksort Memory: 3999kB
18. 7.518 147.975 ↑ 2.7 39,087 1

Hash Left Join (cost=296,490.71..302,248.39 rows=104,524 width=40) (actual time=107.664..147.975 rows=39,087 loops=1)

  • Hash Cond: (t337_opp_line_item_fact.c3477_opportunity_forecast_indicator__csid = t373_opportunityforecast_indicator__cpicklistdim_3.sid)
19. 8.945 140.441 ↑ 2.7 39,087 1

Merge Right Join (cost=296,489.40..300,830.11 rows=104,524 width=36) (actual time=107.628..140.441 rows=39,087 loops=1)

  • Merge Cond: ((t332_clari_conversion_rate.sid = t337_opp_line_item_fact.c3413_opp_currency_code) AND (t332_clari_conversion_rate.rate_date = t337_opp_line_item_fact.c3468_opp_close_date))
20. 2.125 19.795 ↑ 1.0 31,542 1

Merge Append (cost=0.31..3,084.36 rows=32,769 width=16) (actual time=0.022..19.795 rows=31,542 loops=1)

  • Sort Key: t332_clari_conversion_rate.sid, t332_clari_conversion_rate.rate_date
21. 0.006 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: t332_clari_conversion_rate.sid, t332_clari_conversion_rate.rate_date
  • Sort Method: quicksort Memory: 25kB
22. 0.001 0.001 ↓ 0.0 0 1

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

  • Filter: (to_iso_code = 'USD'::text)
23. 17.663 17.663 ↑ 1.0 31,542 1

Index Scan using t332_clari_conversion_rate_usd_sidratedateunique on t332_clari_conversion_rate_usd (cost=0.29..2,674.72 rows=32,768 width=16) (actual time=0.014..17.663 rows=31,542 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
24. 3.488 111.701 ↑ 2.7 39,087 1

Materialize (cost=296,489.09..297,011.71 rows=104,524 width=32) (actual time=104.988..111.701 rows=39,087 loops=1)

25. 16.034 108.213 ↑ 2.7 39,087 1

Sort (cost=296,489.09..296,750.40 rows=104,524 width=32) (actual time=104.984..108.213 rows=39,087 loops=1)

  • Sort Key: t337_opp_line_item_fact.c3413_opp_currency_code, t337_opp_line_item_fact.c3468_opp_close_date
  • Sort Method: quicksort Memory: 3999kB
26. 92.179 92.179 ↑ 2.7 39,087 1

Index Scan using idx_t337_opp_line_item_fact_closedate_stage_end_start on t337_opp_line_item_fact (cost=0.56..285,272.70 rows=104,524 width=32) (actual time=0.019..92.179 rows=39,087 loops=1)

  • Index Cond: ((c3468_opp_close_date >= 20190302) AND (c3468_opp_close_date <= 20190531) AND (end_stamp > '1551513600000'::bigint) AND (start_stamp <= '1551513600000'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 7978
27. 0.006 0.016 ↑ 1.0 14 1

Hash (cost=1.14..1.14 rows=14 width=8) (actual time=0.016..0.016 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
28. 0.010 0.010 ↑ 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.008..0.010 rows=14 loops=1)

29.          

SubPlan (forSubquery Scan)

30. 0.064 0.064 ↑ 1.0 13 1

CTE Scan on picklist_4 cte0_4 (cost=0.00..0.26 rows=13 width=4) (actual time=0.027..0.064 rows=13 loops=1)

31. 0.000 0.000 ↓ 0.0 0

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

32. 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 = oli1880_ft0.c4) AND (c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
33. 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)))
34. 27.967 157.155 ↓ 4.3 43,566 1

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

  • Hash Cond: (t333_userrolehierarchy_3.c3185_user_role_id = t125_userroledim_3.sid)
35. 51.351 67.426 ↓ 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.081..67.426 rows=194,690 loops=1)

  • Recheck Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=12474
36. 16.075 16.075 ↓ 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.075..16.075 rows=194,852 loops=1)

  • Index Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
37. 0.151 61.762 ↑ 2.1 1,055 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 70kB
38. 61.611 61.611 ↑ 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.068..61.611 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
39. 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 = oli1880_ft0.c4) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c1256_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
40. 8.249 9.270 ↓ 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..9.270 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
41. 1.021 1.021 ↓ 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.021..1.021 rows=8,331 loops=1)

  • Index Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
42. 0.000 234.048 ↑ 1.0 1 14,628

Subquery Scan on oli1880_ft1 (cost=148.06..148.57 rows=1 width=28) (actual time=0.016..0.016 rows=1 loops=14,628)

  • Filter: (oli1880_ft0.c1 = oli1880_ft1.c1)
43. 14.628 234.048 ↑ 12.0 1 14,628

GroupAggregate (cost=148.06..148.42 rows=12 width=28) (actual time=0.016..0.016 rows=1 loops=14,628)

  • Group Key: t337_opp_line_item_fact_1.c3414_opportunity_sid, t337_opp_line_item_fact_1.c3468_opp_close_date, t337_opp_line_item_fact_1.c3419_opp_ownerid, t337_opp_line_item_fact_1.c3454_opp_stagename, t373_opportunityforecast_indicator__cpicklistdim_4.correlated_app_order
44. 29.256 219.420 ↑ 6.0 2 14,628

Sort (cost=148.06..148.09 rows=12 width=36) (actual time=0.015..0.015 rows=2 loops=14,628)

  • Sort Key: t337_opp_line_item_fact_1.c3468_opp_close_date, t337_opp_line_item_fact_1.c3419_opp_ownerid, t337_opp_line_item_fact_1.c3454_opp_stagename, t373_opportunityforecast_indicator__cpicklistdim_4.correlated_app_order
  • Sort Method: quicksort Memory: 25kB
45. 14.612 190.164 ↑ 6.0 2 14,628

Hash Left Join (cost=1.88..147.84 rows=12 width=36) (actual time=0.009..0.013 rows=2 loops=14,628)

  • Hash Cond: (t337_opp_line_item_fact_1.c3477_opportunity_forecast_indicator__csid = t373_opportunityforecast_indicator__cpicklistdim_4.sid)
46. 22.350 175.536 ↑ 6.0 2 14,628

Nested Loop Left Join (cost=0.56..146.36 rows=12 width=36) (actual time=0.009..0.012 rows=2 loops=14,628)

47. 102.396 102.396 ↑ 6.0 2 14,628

Index Scan using t337_opp_line_item_fact_14082_timestamp on t337_opp_line_item_fact t337_opp_line_item_fact_1 (cost=0.56..46.40 rows=12 width=32) (actual time=0.006..0.007 rows=2 loops=14,628)

  • Index Cond: ((oli1880_ft0.c1 = c3414_opportunity_sid) AND (start_stamp <= '1559372399999'::bigint) AND (end_stamp > '1559372399999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 1
48. 0.000 50.790 ↑ 2.0 1 25,395

Append (cost=0.00..8.31 rows=2 width=16) (actual time=0.002..0.002 rows=1 loops=25,395)

49. 0.000 0.000 ↓ 0.0 0 25,395

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=25,395)

  • Filter: ((to_iso_code = 'USD'::text) AND (t337_opp_line_item_fact_1.c3413_opp_currency_code = sid) AND (t337_opp_line_item_fact_1.c3468_opp_close_date = rate_date))
50. 50.790 50.790 ↑ 1.0 1 25,395

Index Scan using t332_clari_conversion_rate_usd_sidratedateunique on t332_clari_conversion_rate_usd t332_clari_conversion_rate_usd_1 (cost=0.29..8.31 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=25,395)

  • Index Cond: ((t337_opp_line_item_fact_1.c3413_opp_currency_code = sid) AND (t337_opp_line_item_fact_1.c3468_opp_close_date = rate_date))
  • Filter: (to_iso_code = 'USD'::text)
51. 0.009 0.016 ↑ 1.0 14 1

Hash (cost=1.14..1.14 rows=14 width=8) (actual time=0.016..0.016 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
52. 0.007 0.007 ↑ 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.005..0.007 rows=14 loops=1)

53. 0.000 0.000 ↑ 1.0 1 14,628

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

54.          

SubPlan (forNested Loop Left Join)

55. 0.000 0.000 ↓ 0.0 0

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

56. 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 = oli1880_ft1.c3) AND (c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
57. 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)))
58. 28.115 156.866 ↓ 4.3 43,566 1

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

  • Hash Cond: (t333_userrolehierarchy_1.c3185_user_role_id = t125_userroledim_1.sid)
59. 50.743 67.363 ↓ 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.772..67.363 rows=194,690 loops=1)

  • Recheck Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=12474
60. 16.620 16.620 ↓ 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.620..16.620 rows=194,852 loops=1)

  • Index Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
61. 0.224 61.388 ↑ 2.1 1,055 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 70kB
62. 61.164 61.164 ↑ 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.087..61.164 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
63. 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 = oli1880_ft1.c3) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c1256_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
64. 8.166 9.200 ↓ 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.504..9.200 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
65. 1.034 1.034 ↓ 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.034..1.034 rows=8,331 loops=1)

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

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

67. 0.031 0.031 ↑ 1.0 7 1

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

68.          

CTE bucketec

69. 4.431 1,133.082 ↑ 1,404.6 29 1

GroupAggregate (cost=32,515,020.30..37,226,734.56 rows=40,733 width=120) (actual time=1,126.947..1,133.082 rows=29 loops=1)

  • Group Key: (CASE WHEN (oli1880_ft1_1.c1 IS NULL) THEN 'newBucket'::text WHEN (oli1880_ft1_1.c2 > 20190531) THEN 'pulledInBucket'::text WHEN (oli1880_ft1_1.c2 < 20190302) THEN 'pushedInBucket'::text WHEN ((oli1880_ft1_1.c2 >= 20190302) AND (oli1880_ft1_1.c2 <= 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), oli1880_ft0_1.c3
70. 11.503 1,128.651 ↑ 1.8 22,831 1

Sort (cost=32,515,020.01..32,515,121.84 rows=40,733 width=80) (actual time=1,126.942..1,128.651 rows=22,831 loops=1)

  • Sort Key: (CASE WHEN (oli1880_ft1_1.c1 IS NULL) THEN 'newBucket'::text WHEN (oli1880_ft1_1.c2 > 20190531) THEN 'pulledInBucket'::text WHEN (oli1880_ft1_1.c2 < 20190302) THEN 'pushedInBucket'::text WHEN ((oli1880_ft1_1.c2 >= 20190302) AND (oli1880_ft1_1.c2 <= 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), oli1880_ft0_1.c3
  • Sort Method: quicksort Memory: 2552kB
71. 49.283 1,117.148 ↑ 1.8 22,831 1

Nested Loop Left Join (cost=257,978.34..32,510,088.10 rows=40,733 width=80) (actual time=432.743..1,117.148 rows=22,831 loops=1)

  • Filter: ((oli1880_ft1_1.c1 IS NULL) OR (oli1880_ft1_1.c2 < 20190302) OR (oli1880_ft1_1.c2 > 20190531) OR ((NOT (alternatives: SubPlan 27 or hashed SubPlan 28)) AND (NOT (alternatives: SubPlan 29 or hashed SubPlan 30))))
  • Rows Removed by Filter: 11763
72. 26.463 495.212 ↑ 1.8 34,594 1

Subquery Scan on oli1880_ft0_1 (cost=257,796.37..9,657,790.20 rows=60,947 width=20) (actual time=432.648..495.212 rows=34,594 loops=1)

  • Filter: ((alternatives: SubPlan 23 or hashed SubPlan 24) OR (alternatives: SubPlan 25 or hashed SubPlan 26))
  • Rows Removed by Filter: 10948
73. 26.514 300.086 ↑ 1.8 45,542 1

GroupAggregate (cost=257,796.37..260,281.22 rows=81,263 width=28) (actual time=263.015..300.086 rows=45,542 loops=1)

  • Group Key: t337_opp_line_item_fact_2.c3414_opportunity_sid, t337_opp_line_item_fact_2.c3454_opp_stagename, t337_opp_line_item_fact_2.c3477_opportunity_forecast_indicator__csid, t337_opp_line_item_fact_2.c3468_opp_close_date, t337_opp_line_item_fact_2.c3419_opp_ownerid
74. 51.192 273.572 ↑ 1.2 70,025 1

Sort (cost=257,796.37..258,005.39 rows=83,611 width=36) (actual time=262.992..273.572 rows=70,025 loops=1)

  • Sort Key: t337_opp_line_item_fact_2.c3414_opportunity_sid, t337_opp_line_item_fact_2.c3454_opp_stagename, t337_opp_line_item_fact_2.c3477_opportunity_forecast_indicator__csid, t337_opp_line_item_fact_2.c3468_opp_close_date, t337_opp_line_item_fact_2.c3419_opp_ownerid
  • Sort Method: external merge Disk: 3424kB
75. 14.591 222.380 ↑ 1.2 70,025 1

Merge Right Join (cost=244,549.52..248,671.58 rows=83,611 width=36) (actual time=176.449..222.380 rows=70,025 loops=1)

  • Merge Cond: ((t332_clari_conversion_rate_2.sid = t337_opp_line_item_fact_2.c3413_opp_currency_code) AND (t332_clari_conversion_rate_2.rate_date = t337_opp_line_item_fact_2.c3468_opp_close_date))
76. 2.006 19.359 ↑ 1.0 31,542 1

Merge Append (cost=0.31..3,084.36 rows=32,769 width=16) (actual time=0.015..19.359 rows=31,542 loops=1)

  • Sort Key: t332_clari_conversion_rate_2.sid, t332_clari_conversion_rate_2.rate_date
77. 0.002 0.003 ↓ 0.0 0 1

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

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

  • Filter: (to_iso_code = 'USD'::text)
79. 17.350 17.350 ↑ 1.0 31,542 1

Index Scan using t332_clari_conversion_rate_usd_sidratedateunique on t332_clari_conversion_rate_usd t332_clari_conversion_rate_usd_2 (cost=0.29..2,674.72 rows=32,768 width=16) (actual time=0.011..17.350 rows=31,542 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
80. 6.460 188.430 ↑ 1.2 70,025 1

Materialize (cost=244,549.22..244,967.27 rows=83,611 width=32) (actual time=173.871..188.430 rows=70,025 loops=1)

81. 60.112 181.970 ↑ 1.2 70,025 1

Sort (cost=244,549.22..244,758.24 rows=83,611 width=32) (actual time=173.869..181.970 rows=70,025 loops=1)

  • Sort Key: t337_opp_line_item_fact_2.c3413_opp_currency_code, t337_opp_line_item_fact_2.c3468_opp_close_date
  • Sort Method: external merge Disk: 3144kB
82. 121.858 121.858 ↑ 1.2 70,025 1

Index Scan using idx_t337_opp_line_item_fact_closedate_stage_end_start on t337_opp_line_item_fact t337_opp_line_item_fact_2 (cost=0.56..235,711.43 rows=83,611 width=32) (actual time=0.019..121.858 rows=70,025 loops=1)

  • Index Cond: ((c3468_opp_close_date >= 20190302) AND (c3468_opp_close_date <= 20190531) AND (end_stamp > '1559372399999'::bigint) AND (start_stamp <= '1559372399999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 13165
83.          

SubPlan (forSubquery Scan)

84. 0.000 0.000 ↓ 0.0 0

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

85. 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 = oli1880_ft0_1.c5) AND (c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
86. 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)))
87. 28.531 159.237 ↓ 4.3 43,566 1

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

  • Hash Cond: (t333_userrolehierarchy_7.c3185_user_role_id = t125_userroledim_7.sid)
88. 53.201 69.447 ↓ 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.294..69.447 rows=194,690 loops=1)

  • Recheck Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=12474
89. 16.246 16.246 ↓ 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.245..16.246 rows=194,852 loops=1)

  • Index Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
90. 0.195 61.259 ↑ 2.1 1,055 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 70kB
91. 61.064 61.064 ↑ 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.117..61.064 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
92. 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 = oli1880_ft0_1.c5) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c1256_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
93. 8.402 9.426 ↓ 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.497..9.426 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
94. 1.024 1.024 ↓ 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.024..1.024 rows=8,331 loops=1)

  • Index Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
95. 0.000 242.158 ↓ 0.0 0 34,594

Subquery Scan on oli1880_ft1_1 (cost=181.68..182.01 rows=1 width=12) (actual time=0.007..0.007 rows=0 loops=34,594)

  • Filter: (oli1880_ft0_1.c1 = oli1880_ft1_1.c1)
96. 34.594 242.158 ↓ 0.0 0 34,594

Group (cost=181.68..181.83 rows=15 width=12) (actual time=0.007..0.007 rows=0 loops=34,594)

  • Group Key: t337_opp_line_item_fact_3.c3414_opportunity_sid, t337_opp_line_item_fact_3.c3468_opp_close_date, t337_opp_line_item_fact_3.c3419_opp_ownerid
97. 34.594 207.564 ↑ 15.0 1 34,594

Sort (cost=181.68..181.71 rows=15 width=12) (actual time=0.006..0.006 rows=1 loops=34,594)

  • Sort Key: t337_opp_line_item_fact_3.c3468_opp_close_date, t337_opp_line_item_fact_3.c3419_opp_ownerid
  • Sort Method: quicksort Memory: 25kB
98. 24.930 172.970 ↑ 15.0 1 34,594

Nested Loop Left Join (cost=0.56..181.38 rows=15 width=12) (actual time=0.004..0.005 rows=1 loops=34,594)

99. 103.782 103.782 ↑ 15.0 1 34,594

Index Scan using t337_opp_line_item_fact_14082_timestamp on t337_opp_line_item_fact t337_opp_line_item_fact_3 (cost=0.56..56.43 rows=15 width=16) (actual time=0.003..0.003 rows=1 loops=34,594)

  • Index Cond: ((oli1880_ft0_1.c1 = c3414_opportunity_sid) AND (start_stamp <= '1551513600000'::bigint) AND (end_stamp > '1551513600000'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
100. 0.000 44.258 ↑ 2.0 1 22,129

Append (cost=0.00..8.31 rows=2 width=8) (actual time=0.002..0.002 rows=1 loops=22,129)

101. 0.000 0.000 ↓ 0.0 0 22,129

Seq Scan on t332_clari_conversion_rate t332_clari_conversion_rate_3 (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=22,129)

  • Filter: ((to_iso_code = 'USD'::text) AND (t337_opp_line_item_fact_3.c3413_opp_currency_code = sid) AND (t337_opp_line_item_fact_3.c3468_opp_close_date = rate_date))
102. 44.258 44.258 ↑ 1.0 1 22,129

Index Scan using t332_clari_conversion_rate_usd_sidratedateunique on t332_clari_conversion_rate_usd t332_clari_conversion_rate_usd_3 (cost=0.29..8.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=22,129)

  • Index Cond: ((t337_opp_line_item_fact_3.c3413_opp_currency_code = sid) AND (t337_opp_line_item_fact_3.c3468_opp_close_date = rate_date))
  • Filter: (to_iso_code = 'USD'::text)
103.          

SubPlan (forNested Loop Left Join)

104. 0.000 0.000 ↓ 0.0 0

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

105. 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 = oli1880_ft1_1.c3) AND (c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
106. 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)))
107. 28.296 155.567 ↓ 4.3 43,566 1

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

  • Hash Cond: (t333_userrolehierarchy_5.c3185_user_role_id = t125_userroledim_5.sid)
108. 50.477 66.536 ↓ 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.141..66.536 rows=194,690 loops=1)

  • Recheck Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=12474
109. 16.059 16.059 ↓ 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.059..16.059 rows=194,852 loops=1)

  • Index Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
110. 0.198 60.735 ↑ 2.1 1,055 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 70kB
111. 60.537 60.537 ↑ 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.049..60.537 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
112. 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 = oli1880_ft1_1.c3) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c1256_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
113. 8.320 9.340 ↓ 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.500..9.340 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
114. 1.020 1.020 ↓ 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.020..1.020 rows=8,331 loops=1)

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

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

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

117. 0.000 0.000 ↓ 0.0 0

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

118. 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 = oli1880_ft1_1.c3) AND (c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
119. 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)))
120. 28.164 156.393 ↓ 4.3 43,566 1

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

  • Hash Cond: (t333_userrolehierarchy_9.c3185_user_role_id = t125_userroledim_9.sid)
121. 50.903 67.318 ↓ 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.455..67.318 rows=194,690 loops=1)

  • Recheck Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=12474
122. 16.415 16.415 ↓ 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.415..16.415 rows=194,852 loops=1)

  • Index Cond: ((c3186_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
123. 0.192 60.911 ↑ 2.1 1,055 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 70kB
124. 60.719 60.719 ↑ 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.097..60.719 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
125. 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 = oli1880_ft1_1.c3) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c1256_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
126. 8.180 9.192 ↓ 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.494..9.192 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
127. 1.012 1.012 ↓ 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.012..1.012 rows=8,331 loops=1)

  • Index Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
128. 0.026 821.352 ↑ 1,866.5 21 1

Hash Left Join (cost=1.32..959.66 rows=39,196 width=120) (actual time=816.964..821.352 rows=21 loops=1)

  • Hash Cond: (cte0.c5 = t373_opportunityforecast_indicator__cpicklistdim.sid)
129. 821.311 821.311 ↑ 1,866.5 21 1

CTE Scan on bucketac cte0 (cost=0.00..783.92 rows=39,196 width=92) (actual time=816.933..821.311 rows=21 loops=1)

130. 0.003 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
131. 0.012 0.012 ↑ 1.0 14 1

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

132. 0.015 1,133.159 ↑ 1,404.6 29 1

Subquery Scan on *SELECT* 2 (cost=1.32..1,506.40 rows=40,733 width=120) (actual time=1,126.990..1,133.159 rows=29 loops=1)

133. 0.026 1,133.144 ↑ 1,404.6 29 1

Hash Left Join (cost=1.32..997.23 rows=40,733 width=144) (actual time=1,126.983..1,133.144 rows=29 loops=1)

  • Hash Cond: (cte0_1.c7 = t373_opportunityforecast_indicator__cpicklistdim_1.sid)
134. 1,133.102 1,133.102 ↑ 1,404.6 29 1

CTE Scan on bucketec cte0_1 (cost=0.00..814.66 rows=40,733 width=116) (actual time=1,126.951..1,133.102 rows=29 loops=1)

135. 0.006 0.016 ↑ 1.0 14 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
136. 0.010 0.010 ↑ 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.008..0.010 rows=14 loops=1)

Planning time : 15.651 ms
Execution time : 1,961.154 ms