explain.depesz.com

PostgreSQL's explain analyze made readable

Result: E4hM

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 8,241.980 ↑ 1,603.5 50 1

Append (cost=65,839,330.45..65,842,195.98 rows=80,176 width=120) (actual time=4,023.678..8,241.980 rows=50 loops=1)

2.          

CTE picklist_0

3. 0.037 0.037 ↑ 1.0 6 1

Seq Scan on t376_opportunitystagenamepicklistdim (cost=0.00..4.54 rows=6 width=4) (actual time=0.018..0.037 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.043 0.043 ↑ 1.0 7 1

Seq Scan on t376_opportunitystagenamepicklistdim t376_opportunitystagenamepicklistdim_1 (cost=0.00..4.65 rows=7 width=4) (actual time=0.008..0.043 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.041 0.041 ↑ 1.0 13 1

Seq Scan on t376_opportunitystagenamepicklistdim t376_opportunitystagenamepicklistdim_2 (cost=0.00..5.31 rows=13 width=4) (actual time=0.015..0.041 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.234 4,028.003 ↑ 1,872.2 21 1

GroupAggregate (cost=23,367,883.64..27,916,001.31 rows=39,317 width=96) (actual time=4,023.642..4,028.003 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.008 0.008 ↑ 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.006..0.008 rows=1 loops=1)

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

Sort (cost=23,367,882.17..23,367,980.46 rows=39,317 width=56) (actual time=4,023.632..4,024.769 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. 28.253 4,018.335 ↑ 2.7 14,628 1

Nested Loop Left Join (cost=846,556.14..23,364,881.72 rows=39,317 width=56) (actual time=3,535.867..4,018.335 rows=14,628 loops=1)

14. 2.246 3,818.134 ↑ 2.7 14,628 1

Nested Loop Left Join (cost=846,555.85..18,816,960.33 rows=39,317 width=48) (actual time=3,535.850..3,818.134 rows=14,628 loops=1)

15. 24.357 3,581.840 ↑ 2.7 14,628 1

Subquery Scan on oli1880_ft0 (cost=846,407.78..12,974,798.68 rows=39,317 width=20) (actual time=3,535.738..3,581.840 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. 15.394 3,385.259 ↑ 4.3 24,608 1

GroupAggregate (cost=846,407.48..849,814.98 rows=104,846 width=32) (actual time=3,362.890..3,385.259 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.103 3,369.865 ↑ 2.7 39,087 1

Sort (cost=846,407.48..846,669.60 rows=104,846 width=40) (actual time=3,362.871..3,369.865 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.564 3,346.762 ↑ 2.7 39,087 1

Hash Left Join (cost=829,029.02..834,794.42 rows=104,846 width=40) (actual time=3,305.298..3,346.762 rows=39,087 loops=1)

  • Hash Cond: (t337_opp_line_item_fact.c3477_opportunity_forecast_indicator__csid = t373_opportunityforecast_indicator__cpicklistdim_3.sid)
19. 9.233 3,339.185 ↑ 2.7 39,087 1

Merge Right Join (cost=829,027.71..833,371.78 rows=104,846 width=36) (actual time=3,305.254..3,339.185 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.611 20.403 ↑ 1.0 31,542 1

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

  • Sort Key: t332_clari_conversion_rate.sid, t332_clari_conversion_rate.rate_date
21. 0.005 0.006 ↓ 0.0 0 1

Sort (cost=0.01..0.02 rows=1 width=16) (actual time=0.006..0.006 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.786 17.786 ↑ 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.017..17.786 rows=31,542 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
24. 3.599 3,309.549 ↑ 2.7 39,087 1

Materialize (cost=829,027.40..829,551.63 rows=104,846 width=32) (actual time=3,302.453..3,309.549 rows=39,087 loops=1)

25. 17.912 3,305.950 ↑ 2.7 39,087 1

Sort (cost=829,027.40..829,289.52 rows=104,846 width=32) (actual time=3,302.449..3,305.950 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. 3,288.038 3,288.038 ↑ 2.7 39,087 1

Seq Scan on t337_opp_line_item_fact (cost=0.00..817,774.84 rows=104,846 width=32) (actual time=5.290..3,288.038 rows=39,087 loops=1)

  • Filter: ((NOT deleted) AND (start_stamp <= '1551513600000'::bigint) AND (end_stamp > '1551513600000'::bigint) AND (c3468_opp_close_date >= 20190302) AND (c3468_opp_close_date <= 20190531))
  • Rows Removed by Filter: 13132950
27. 0.003 0.013 ↑ 1.0 14 1

Hash (cost=1.14..1.14 rows=14 width=8) (actual time=0.013..0.013 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.005..0.010 rows=14 loops=1)

29.          

SubPlan (forSubquery Scan)

30. 0.045 0.045 ↑ 1.0 13 1

CTE Scan on picklist_4 cte0_4 (cost=0.00..0.26 rows=13 width=4) (actual time=0.016..0.045 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. 28.493 161.587 ↓ 4.3 43,566 1

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

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

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

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

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

  • Buckets: 4096 Batches: 1 Memory Usage: 70kB
38. 68.106 68.106 ↑ 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.378..68.106 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. 9.571 10.592 ↓ 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.511..10.592 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. 14.628 234.048 ↑ 1.0 1 14,628

Subquery Scan on oli1880_ft1 (cost=148.07..148.58 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 219.420 ↑ 12.0 1 14,628

GroupAggregate (cost=148.07..148.43 rows=12 width=28) (actual time=0.015..0.015 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 204.792 ↑ 6.0 2 14,628

Sort (cost=148.07..148.10 rows=12 width=36) (actual time=0.014..0.014 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. 0.000 175.536 ↑ 6.0 2 14,628

Hash Left Join (cost=1.88..147.86 rows=12 width=36) (actual time=0.009..0.012 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. 36.978 175.536 ↑ 6.0 2 14,628

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

47. 87.768 87.768 ↑ 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.42 rows=12 width=32) (actual time=0.006..0.006 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.007 0.013 ↑ 1.0 14 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
52. 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.004..0.006 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.912 161.437 ↓ 4.3 43,566 1

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

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

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

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

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

  • Buckets: 4096 Batches: 1 Memory Usage: 70kB
62. 68.249 68.249 ↑ 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.373..68.249 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. 9.332 10.423 ↓ 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.537..10.423 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.091 1.091 ↓ 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.091..1.091 rows=8,331 loops=1)

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

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

67. 0.044 0.044 ↑ 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.044 rows=7 loops=1)

68.          

CTE bucketec

69. 4.553 4,213.841 ↑ 1,408.9 29 1

GroupAggregate (cost=33,197,024.25..37,923,313.33 rows=40,859 width=120) (actual time=4,207.595..4,213.841 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.956 4,209.288 ↑ 1.8 22,831 1

Sort (cost=33,197,023.96..33,197,126.11 rows=40,859 width=80) (actual time=4,207.589..4,209.288 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. 42.376 4,197.332 ↑ 1.8 22,831 1

Nested Loop Left Join (cost=840,104.36..33,192,078.00 rows=40,859 width=80) (actual time=3,502.117..4,197.332 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. 27.325 3,566.620 ↑ 1.8 34,594 1

Subquery Scan on oli1880_ft0_1 (cost=839,922.39..10,268,950.32 rows=61,136 width=20) (actual time=3,502.009..3,566.620 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. 27.655 3,366.786 ↑ 1.8 45,542 1

GroupAggregate (cost=839,922.39..842,414.91 rows=81,514 width=28) (actual time=3,328.978..3,366.786 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.038 3,339.131 ↑ 1.2 70,025 1

Sort (cost=839,922.39..840,132.06 rows=83,869 width=36) (actual time=3,328.960..3,339.131 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: 3416kB
75. 15.220 3,288.093 ↑ 1.2 70,025 1

Merge Right Join (cost=826,642.89..830,767.65 rows=83,869 width=36) (actual time=3,240.096..3,288.093 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.598 20.162 ↑ 1.0 31,542 1

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

  • Sort Key: t332_clari_conversion_rate_2.sid, t332_clari_conversion_rate_2.rate_date
77. 0.004 0.006 ↓ 0.0 0 1

Sort (cost=0.01..0.02 rows=1 width=16) (actual time=0.006..0.006 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.002 0.002 ↓ 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.002 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
79. 17.558 17.558 ↑ 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.014..17.558 rows=31,542 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
80. 6.515 3,252.711 ↑ 1.2 70,025 1

Materialize (cost=826,642.58..827,061.93 rows=83,869 width=32) (actual time=3,237.393..3,252.711 rows=70,025 loops=1)

81. 71.666 3,246.196 ↑ 1.2 70,025 1

Sort (cost=826,642.58..826,852.26 rows=83,869 width=32) (actual time=3,237.389..3,246.196 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. 3,174.530 3,174.530 ↑ 1.2 70,025 1

Seq Scan on t337_opp_line_item_fact t337_opp_line_item_fact_2 (cost=0.00..817,774.84 rows=83,869 width=32) (actual time=5.302..3,174.530 rows=70,025 loops=1)

  • Filter: ((NOT deleted) AND (start_stamp <= '1559372399999'::bigint) AND (end_stamp > '1559372399999'::bigint) AND (c3468_opp_close_date >= 20190302) AND (c3468_opp_close_date <= 20190531))
  • Rows Removed by Filter: 13102012
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.848 161.807 ↓ 4.3 43,566 1

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

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

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

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

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

  • Buckets: 4096 Batches: 1 Memory Usage: 70kB
91. 68.672 68.672 ↑ 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.363..68.672 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. 9.587 10.702 ↓ 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.628..10.702 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.115 1.115 ↓ 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.115..1.115 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.523 162.412 ↓ 4.3 43,566 1

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

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

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

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

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

  • Buckets: 4096 Batches: 1 Memory Usage: 70kB
111. 68.723 68.723 ↑ 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.381..68.723 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. 9.529 10.618 ↓ 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.585..10.618 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.089 1.089 ↓ 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.089..1.089 rows=8,331 loops=1)

  • Index Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
115. 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)

116. 0.001 0.001 ↑ 1.0 7 1

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

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.588 162.619 ↓ 4.3 43,566 1

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

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

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

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

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

  • Buckets: 4096 Batches: 1 Memory Usage: 70kB
124. 69.033 69.033 ↑ 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.422..69.033 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. 9.488 10.526 ↓ 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.528..10.526 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.038 1.038 ↓ 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.038..1.038 rows=8,331 loops=1)

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

Hash Left Join (cost=1.32..962.61 rows=39,317 width=120) (actual time=4,023.678..4,028.052 rows=21 loops=1)

  • Hash Cond: (cte0.c5 = t373_opportunityforecast_indicator__cpicklistdim.sid)
129. 4,028.010 4,028.010 ↑ 1,872.2 21 1

CTE Scan on bucketac cte0 (cost=0.00..786.34 rows=39,317 width=92) (actual time=4,023.645..4,028.010 rows=21 loops=1)

130. 0.007 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
131. 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)

132. 0.015 4,213.920 ↑ 1,408.9 29 1

Subquery Scan on *SELECT* 2 (cost=1.32..1,511.05 rows=40,859 width=120) (actual time=4,207.641..4,213.920 rows=29 loops=1)

133. 0.026 4,213.905 ↑ 1,408.9 29 1

Hash Left Join (cost=1.32..1,000.32 rows=40,859 width=144) (actual time=4,207.633..4,213.905 rows=29 loops=1)

  • Hash Cond: (cte0_1.c7 = t373_opportunityforecast_indicator__cpicklistdim_1.sid)
134. 4,213.863 4,213.863 ↑ 1,408.9 29 1

CTE Scan on bucketec cte0_1 (cost=0.00..817.18 rows=40,859 width=116) (actual time=4,207.598..4,213.863 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.007..0.010 rows=14 loops=1)

Planning time : 15.771 ms
Execution time : 8,248.371 ms