explain.depesz.com

PostgreSQL's explain analyze made readable

Result: opui

Settings
# exclusive inclusive rows x rows loops node
1. 0.118 21,121.002 ↑ 1.3 400 1

Sort (cost=388,299,464.71..388,299,466.00 rows=513 width=27) (actual time=21,120.970..21,121.002 rows=400 loops=1)

  • Sort Key: cte0.c2 DESC NULLS LAST
  • Sort Method: quicksort Memory: 56kB
2.          

CTE picklist_0

3. 0.044 0.044 ↑ 1.0 6 1

Seq Scan on t254_opportunitystagenamepicklistdim (cost=0.00..6.54 rows=6 width=4) (actual time=0.016..0.044 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.030 0.030 ↑ 1.0 7 1

Seq Scan on t254_opportunitystagenamepicklistdim t254_opportunitystagenamepicklistdim_1 (cost=0.00..6.65 rows=7 width=4) (actual time=0.007..0.030 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_2

7. 0.064 0.064 ↑ 1.0 13 1

Seq Scan on t254_opportunitystagenamepicklistdim t254_opportunitystagenamepicklistdim_2 (cost=0.00..7.31 rows=13 width=4) (actual time=0.011..0.064 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 topsids

9. 0.036 21,116.418 ↑ 1.0 400 1

Limit (cost=388,293,887.20..388,293,888.20 rows=400 width=12) (actual time=21,116.342..21,116.418 rows=400 loops=1)

10.          

CTE bucketac

11. 93.271 2,515.449 ↑ 3.7 92,963 1

Nested Loop Left Join (cost=2.89..230,893,676.56 rows=344,767 width=116) (actual time=365.935..2,515.449 rows=92,963 loops=1)

12.          

CTE nullgroupbyvaluecte

13. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on t249_opportunityforecast_indicator__cpicklistdim (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
14. 91.410 2,240.121 ↑ 3.7 92,963 1

Nested Loop Left Join (cost=1.42..190,908,587.40 rows=344,767 width=44) (actual time=182.873..2,240.121 rows=92,963 loops=1)

  • Join Filter: (t197_adobe_product_to_sales_team_assignment__cfact_2.c2728_opportunity__c_forecast_indicator__csid = t249_opportunityforecast_indicator__cpicklistdim_2.sid)
  • Rows Removed by Join Filter: 1256196
15. 274.696 2,055.748 ↑ 3.7 92,963 1

Nested Loop Left Join (cost=1.42..190,836,185.16 rows=344,767 width=44) (actual time=182.860..2,055.748 rows=92,963 loops=1)

  • Join Filter: (t197_adobe_product_to_sales_team_assignment__cfact_2.c2664_currencyisocodesid = t144_claricurrencytypedim.sid)
  • Rows Removed by Join Filter: 3580278
16. 39.670 1,595.126 ↑ 3.7 92,963 1

Nested Loop Left Join (cost=1.42..190,634,456.35 rows=344,767 width=40) (actual time=182.654..1,595.126 rows=92,963 loops=1)

17. 94.704 1,183.604 ↑ 3.7 92,963 1

Nested Loop Left Join (cost=0.86..188,381,136.15 rows=344,767 width=12) (actual time=182.621..1,183.604 rows=92,963 loops=1)

  • Join Filter: (t197_adobe_product_to_sales_team_assignment__cfact_1.c2728_opportunity__c_forecast_indicator__csid = t249_opportunityforecast_indicator__cpicklistdim_1.sid)
  • Rows Removed by Join Filter: 1208529
18. 825.176 995.937 ↑ 3.7 92,963 1

Index Scan using ix_t197_closedate_stagename_timestamp on t197_adobe_product_to_sales_team_assignment__cfact t197_adobe_product_to_sales_team_assignment__cfact_1 (cost=0.86..188,308,733.91 rows=344,767 width=8) (actual time=182.595..995.937 rows=92,963 loops=1)

  • Index Cond: ((c2719_opportunity__c_closedate >= 20190601) AND (c2719_opportunity__c_closedate <= 20190830) AND (end_stamp > '1559372400000'::bigint) AND (start_stamp <= '1559372400000'::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: 555348
19.          

SubPlan (forIndex Scan)

20. 0.070 0.070 ↑ 1.0 13 1

CTE Scan on picklist_2 cte0_3 (cost=0.00..0.26 rows=13 width=4) (actual time=0.014..0.070 rows=13 loops=1)

21. 0.000 0.000 ↓ 0.0 0

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

22. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t133_userrolehierarchy_sid_c2106_ancestor_role_id_e49766525 on t133_userrolehierarchy t133_userrolehierarchy_2 (cost=0.43..31.90 rows=8 width=4) (never executed)

  • Index Cond: ((sid = t197_adobe_product_to_sales_team_assignment__cfact_1.c2751_sales_team_member__c) AND (c2106_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
23. 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 = t133_userrolehierarchy_2.c2104_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)))
24. 29.661 170.691 ↓ 4.4 43,641 1

Hash Join (cost=16,904.06..53,357.81 rows=10,021 width=4) (actual time=90.800..170.691 rows=43,641 loops=1)

  • Hash Cond: (t133_userrolehierarchy_3.c2104_user_role_id = t125_userroledim_3.sid)
25. 52.797 70.847 ↓ 1.0 194,737 1

Bitmap Heap Scan on t133_userrolehierarchy t133_userrolehierarchy_3 (cost=5,842.26..41,535.95 rows=192,179 width=8) (actual time=20.196..70.847 rows=194,737 loops=1)

  • Recheck Cond: ((c2106_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=12318
26. 18.050 18.050 ↓ 1.0 197,286 1

Bitmap Index Scan on idx_t133_userrolehierarchy_c2106_ancestor_role_id_end_s49766525 (cost=0.00..5,794.22 rows=192,179 width=0) (actual time=18.050..18.050 rows=197,286 loops=1)

  • Index Cond: ((c2106_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
27. 0.154 70.183 ↑ 2.1 1,055 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 70kB
28. 70.029 70.029 ↑ 2.1 1,055 1

Seq Scan on t125_userroledim t125_userroledim_3 (cost=0.00..11,034.70 rows=2,167 width=4) (actual time=2.452..70.029 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: 214570
29. 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 = t197_adobe_product_to_sales_team_assignment__cfact_1.c2751_sales_team_member__c) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c1256_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
30. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t105_userdim t105_userdim_3 (cost=245.15..13,035.12 rows=4,358 width=4) (never executed)

  • Recheck Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
31. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_t105_userdim_c1256_userroleid_end_stamp_start_stamp (cost=0.00..244.06 rows=7,964 width=0) (never executed)

  • Index Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
32. 92.953 92.963 ↑ 1.0 14 92,963

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

33. 0.010 0.010 ↑ 1.0 14 1

Seq Scan on t249_opportunityforecast_indicator__cpicklistdim t249_opportunityforecast_indicator__cpicklistdim_1 (cost=0.00..1.14 rows=14 width=8) (actual time=0.007..0.010 rows=14 loops=1)

34. 371.852 371.852 ↓ 0.0 0 92,963

Index Scan using t197_adobe_product_to_sales_team_assignment__cfact_sidendstampu on t197_adobe_product_to_sales_team_assignment__cfact t197_adobe_product_to_sales_team_assignment__cfact_2 (cost=0.56..6.53 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=92,963)

  • Index Cond: ((t197_adobe_product_to_sales_team_assignment__cfact_1.sid = sid) AND (t197_adobe_product_to_sales_team_assignment__cfact_1.sid = sid) AND (end_stamp > '1567234799999'::bigint))
  • Filter: ((NOT deleted) AND (start_stamp <= '1567234799999'::bigint))
  • Rows Removed by Filter: 1
35. 185.662 185.926 ↑ 1.0 39 92,963

Materialize (cost=0.00..40.21 rows=39 width=12) (actual time=0.000..0.002 rows=39 loops=92,963)

36. 0.264 0.264 ↑ 1.0 39 1

Seq Scan on t144_claricurrencytypedim (cost=0.00..40.01 rows=39 width=12) (actual time=0.011..0.264 rows=39 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 1482
37. 92.958 92.963 ↑ 1.0 14 92,963

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

38. 0.005 0.005 ↑ 1.0 14 1

Seq Scan on t249_opportunityforecast_indicator__cpicklistdim t249_opportunityforecast_indicator__cpicklistdim_2 (cost=0.00..1.14 rows=14 width=8) (actual time=0.002..0.005 rows=14 loops=1)

39. 0.000 0.000 ↑ 1.0 1 92,963

CTE Scan on nullgroupbyvaluecte cte0_4 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=92,963)

40.          

SubPlan (forNested Loop Left Join)

41. 0.000 0.000 ↓ 0.0 0

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

42. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t133_userrolehierarchy_sid_c2106_ancestor_role_id_e49766525 on t133_userrolehierarchy (cost=0.43..31.90 rows=8 width=4) (never executed)

  • Index Cond: ((sid = t197_adobe_product_to_sales_team_assignment__cfact_2.c2751_sales_team_member__c) AND (c2106_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
43. 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 = t133_userrolehierarchy.c2104_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)))
44. 29.881 171.087 ↓ 4.4 43,641 1

Hash Join (cost=16,904.06..53,357.81 rows=10,021 width=4) (actual time=90.766..171.087 rows=43,641 loops=1)

  • Hash Cond: (t133_userrolehierarchy_1.c2104_user_role_id = t125_userroledim_1.sid)
45. 52.979 71.212 ↓ 1.0 194,737 1

Bitmap Heap Scan on t133_userrolehierarchy t133_userrolehierarchy_1 (cost=5,842.26..41,535.95 rows=192,179 width=8) (actual time=20.352..71.212 rows=194,737 loops=1)

  • Recheck Cond: ((c2106_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=12318
46. 18.233 18.233 ↓ 1.0 197,286 1

Bitmap Index Scan on idx_t133_userrolehierarchy_c2106_ancestor_role_id_end_s49766525 (cost=0.00..5,794.22 rows=192,179 width=0) (actual time=18.232..18.233 rows=197,286 loops=1)

  • Index Cond: ((c2106_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
47. 0.186 69.994 ↑ 2.1 1,055 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 70kB
48. 69.808 69.808 ↑ 2.1 1,055 1

Seq Scan on t125_userroledim t125_userroledim_1 (cost=0.00..11,034.70 rows=2,167 width=4) (actual time=2.390..69.808 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: 214570
49. 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 = t197_adobe_product_to_sales_team_assignment__cfact_2.c2751_sales_team_member__c) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c1256_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
50. 9.821 10.892 ↓ 1.3 5,561 1

Bitmap Heap Scan on t105_userdim t105_userdim_1 (cost=245.15..13,035.12 rows=4,358 width=4) (actual time=1.538..10.892 rows=5,561 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: 2760
  • Heap Blocks: exact=3394
51. 1.071 1.071 ↓ 1.0 8,327 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.071..1.071 rows=8,327 loops=1)

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

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

53. 0.030 0.030 ↑ 1.0 7 1

CTE Scan on picklist_1 cte0_2 (cost=0.00..0.14 rows=7 width=4) (actual time=0.007..0.030 rows=7 loops=1)

54.          

CTE bucketec

55. 52.059 18,489.501 ↑ 3.0 112,860 1

Hash Left Join (cost=117,942,868.56..157,384,608.64 rows=339,478 width=116) (actual time=17,445.687..18,489.501 rows=112,860 loops=1)

  • Hash Cond: (t197_adobe_product_to_sales_team_assignment__cfact_3.c2664_currencyisocodesid = t144_claricurrencytypedim_1.sid)
56. 291.000 18,437.144 ↑ 3.0 112,860 1

Merge Right Join (cost=117,942,827.77..118,001,343.23 rows=339,478 width=36) (actual time=17,445.339..18,437.144 rows=112,860 loops=1)

  • Merge Cond: ((t197_adobe_product_to_sales_team_assignment__cfact_4.sid = t197_adobe_product_to_sales_team_assignment__cfact_3.sid) AND (t197_adobe_product_to_sales_team_assignment__cfact_4.sid = t197_adobe_product_to_sales_team_assignment__cfact_3.sid))
  • Filter: ((t197_adobe_product_to_sales_team_assignment__cfact_4.sid IS NULL) OR (t197_adobe_product_to_sales_team_assignment__cfact_4.c2719_opportunity__c_closedate < 20190601) OR (t197_adobe_product_to_sales_team_assignment__cfact_4.c2719_opportunity__c_closedate > 20190830) OR ((NOT (alternatives: SubPlan 27 or hashed SubPlan 28)) AND (NOT (alternatives: SubPlan 29 or hashed SubPlan 30))))
  • Rows Removed by Filter: 62803
57. 2,413.512 16,630.202 ↑ 2.2 3,283,658 1

Sort (cost=4,542,736.24..4,560,822.79 rows=7,234,620 width=12) (actual time=15,952.752..16,630.202 rows=3,283,658 loops=1)

  • Sort Key: t197_adobe_product_to_sales_team_assignment__cfact_4.sid
  • Sort Method: external merge Disk: 70424kB
58. 14,216.690 14,216.690 ↑ 2.2 3,283,658 1

Seq Scan on t197_adobe_product_to_sales_team_assignment__cfact t197_adobe_product_to_sales_team_assignment__cfact_4 (cost=0.00..3,471,196.41 rows=7,234,620 width=12) (actual time=6.052..14,216.690 rows=3,283,658 loops=1)

  • Filter: ((NOT deleted) AND (start_stamp <= '1559372400000'::bigint) AND (end_stamp > '1559372400000'::bigint))
  • Rows Removed by Filter: 44645309
59. 16.652 1,345.906 ↑ 2.4 175,663 1

Materialize (cost=113,400,091.53..113,402,161.46 rows=413,986 width=24) (actual time=1,298.493..1,345.906 rows=175,663 loops=1)

60. 121.830 1,329.254 ↑ 2.4 175,663 1

Sort (cost=113,400,091.53..113,401,126.50 rows=413,986 width=24) (actual time=1,298.489..1,329.254 rows=175,663 loops=1)

  • Sort Key: t197_adobe_product_to_sales_team_assignment__cfact_3.sid
  • Sort Method: external merge Disk: 6512kB
61. 1,025.808 1,207.424 ↑ 2.4 175,663 1

Index Scan using ix_t197_closedate_stagename_timestamp on t197_adobe_product_to_sales_team_assignment__cfact t197_adobe_product_to_sales_team_assignment__cfact_3 (cost=0.56..113,352,977.25 rows=413,986 width=24) (actual time=182.771..1,207.424 rows=175,663 loops=1)

  • Index Cond: ((c2719_opportunity__c_closedate >= 20190601) AND (c2719_opportunity__c_closedate <= 20190830) AND (end_stamp > '1567234799999'::bigint) AND (start_stamp <= '1567234799999'::bigint))
  • Filter: ((NOT deleted) AND ((alternatives: SubPlan 23 or hashed SubPlan 24) OR (alternatives: SubPlan 25 or hashed SubPlan 26)))
  • Rows Removed by Filter: 675676
62.          

SubPlan (forIndex Scan)

63. 0.000 0.000 ↓ 0.0 0

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

64. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t133_userrolehierarchy_sid_c2106_ancestor_role_id_e49766525 on t133_userrolehierarchy t133_userrolehierarchy_6 (cost=0.43..31.90 rows=8 width=4) (never executed)

  • Index Cond: ((sid = t197_adobe_product_to_sales_team_assignment__cfact_3.c2751_sales_team_member__c) AND (c2106_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
65. 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 = t133_userrolehierarchy_6.c2104_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)))
66. 29.940 171.023 ↓ 4.4 43,641 1

Hash Join (cost=16,904.06..53,357.81 rows=10,021 width=4) (actual time=91.024..171.023 rows=43,641 loops=1)

  • Hash Cond: (t133_userrolehierarchy_7.c2104_user_role_id = t125_userroledim_7.sid)
67. 52.560 70.830 ↓ 1.0 194,737 1

Bitmap Heap Scan on t133_userrolehierarchy t133_userrolehierarchy_7 (cost=5,842.26..41,535.95 rows=192,179 width=8) (actual time=20.341..70.830 rows=194,737 loops=1)

  • Recheck Cond: ((c2106_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=12318
68. 18.270 18.270 ↓ 1.0 197,286 1

Bitmap Index Scan on idx_t133_userrolehierarchy_c2106_ancestor_role_id_end_s49766525 (cost=0.00..5,794.22 rows=192,179 width=0) (actual time=18.270..18.270 rows=197,286 loops=1)

  • Index Cond: ((c2106_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
69. 0.195 70.253 ↑ 2.1 1,055 1

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

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

Seq Scan on t125_userroledim t125_userroledim_7 (cost=0.00..11,034.70 rows=2,167 width=4) (actual time=2.438..70.058 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: 214570
71. 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 = t197_adobe_product_to_sales_team_assignment__cfact_3.c2751_sales_team_member__c) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c1256_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
72. 9.647 10.593 ↓ 1.3 5,561 1

Bitmap Heap Scan on t105_userdim t105_userdim_7 (cost=245.15..13,035.12 rows=4,358 width=4) (actual time=1.439..10.593 rows=5,561 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: 2760
  • Heap Blocks: exact=3394
73. 0.946 0.946 ↓ 1.0 8,327 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.946..0.946 rows=8,327 loops=1)

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

SubPlan (forMerge Right Join)

75. 0.000 0.000 ↓ 0.0 0

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

76. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t133_userrolehierarchy_sid_c2106_ancestor_role_id_e49766525 on t133_userrolehierarchy t133_userrolehierarchy_8 (cost=0.43..31.90 rows=8 width=4) (never executed)

  • Index Cond: ((sid = t197_adobe_product_to_sales_team_assignment__cfact_4.c2751_sales_team_member__c) AND (c2106_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
77. 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 = t133_userrolehierarchy_8.c2104_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)))
78. 29.680 170.036 ↓ 4.4 43,641 1

Hash Join (cost=16,904.06..53,357.81 rows=10,021 width=4) (actual time=89.702..170.036 rows=43,641 loops=1)

  • Hash Cond: (t133_userrolehierarchy_9.c2104_user_role_id = t125_userroledim_9.sid)
79. 53.061 70.864 ↓ 1.0 194,737 1

Bitmap Heap Scan on t133_userrolehierarchy t133_userrolehierarchy_9 (cost=5,842.26..41,535.95 rows=192,179 width=8) (actual time=19.797..70.864 rows=194,737 loops=1)

  • Recheck Cond: ((c2106_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=12318
80. 17.803 17.803 ↓ 1.0 197,286 1

Bitmap Index Scan on idx_t133_userrolehierarchy_c2106_ancestor_role_id_end_s49766525 (cost=0.00..5,794.22 rows=192,179 width=0) (actual time=17.803..17.803 rows=197,286 loops=1)

  • Index Cond: ((c2106_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
81. 0.195 69.492 ↑ 2.1 1,055 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 70kB
82. 69.297 69.297 ↑ 2.1 1,055 1

Seq Scan on t125_userroledim t125_userroledim_9 (cost=0.00..11,034.70 rows=2,167 width=4) (actual time=2.306..69.297 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: 214570
83. 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 = t197_adobe_product_to_sales_team_assignment__cfact_4.c2751_sales_team_member__c) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c1256_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
84. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t105_userdim t105_userdim_9 (cost=245.15..13,035.12 rows=4,358 width=4) (never executed)

  • Recheck Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
85. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_t105_userdim_c1256_userroleid_end_stamp_start_stamp (cost=0.00..244.06 rows=7,964 width=0) (never executed)

  • Index Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
86. 0.009 0.294 ↑ 1.0 39 1

Hash (cost=40.01..40.01 rows=39 width=12) (actual time=0.294..0.294 rows=39 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
87. 0.285 0.285 ↑ 1.0 39 1

Seq Scan on t144_claricurrencytypedim t144_claricurrencytypedim_1 (cost=0.00..40.01 rows=39 width=12) (actual time=0.018..0.285 rows=39 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 1482
88.          

SubPlan (forHash Left Join)

89. 0.000 0.000 ↓ 0.0 0

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

90. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t133_userrolehierarchy_sid_c2106_ancestor_role_id_e49766525 on t133_userrolehierarchy t133_userrolehierarchy_4 (cost=0.43..31.90 rows=8 width=4) (never executed)

  • Index Cond: ((sid = t197_adobe_product_to_sales_team_assignment__cfact_4.c2751_sales_team_member__c) AND (c2106_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
91. 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 = t133_userrolehierarchy_4.c2104_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)))
92. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=16,904.06..53,357.81 rows=10,021 width=4) (never executed)

  • Hash Cond: (t133_userrolehierarchy_5.c2104_user_role_id = t125_userroledim_5.sid)
93. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t133_userrolehierarchy t133_userrolehierarchy_5 (cost=5,842.26..41,535.95 rows=192,179 width=8) (never executed)

  • Recheck Cond: ((c2106_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
94. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_t133_userrolehierarchy_c2106_ancestor_role_id_end_s49766525 (cost=0.00..5,794.22 rows=192,179 width=0) (never executed)

  • Index Cond: ((c2106_ancestor_role_id = 6405) AND (end_stamp = '32503680000000'::bigint))
95. 0.000 0.000 ↓ 0.0 0

Hash (cost=11,034.70..11,034.70 rows=2,167 width=4) (never executed)

96. 0.000 0.000 ↓ 0.0 0

Seq Scan on t125_userroledim t125_userroledim_5 (cost=0.00..11,034.70 rows=2,167 width=4) (never executed)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c1646_portaltype IS NULL) OR (c1646_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
97. 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 = t197_adobe_product_to_sales_team_assignment__cfact_4.c2751_sales_team_member__c) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c1256_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
98. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t105_userdim t105_userdim_5 (cost=245.15..13,035.12 rows=4,358 width=4) (never executed)

  • Recheck Cond: ((c1256_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c1242_usertype = 'Standard'::text))
99. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_t105_userdim_c1256_userroleid_end_stamp_start_stamp (cost=0.00..244.06 rows=7,964 width=0) (never executed)

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

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

101. 0.002 0.002 ↑ 1.0 7 1

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

102. 2.261 21,116.382 ↑ 5.3 400 1

Sort (cost=15,602.00..15,607.31 rows=2,124 width=12) (actual time=21,116.340..21,116.382 rows=400 loops=1)

  • Sort Key: cte0_7.c4 DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 43kB
103. 0.834 21,114.121 ↓ 3.3 7,109 1

Append (cost=0.00..15,499.58 rows=2,124 width=12) (actual time=365.942..21,114.121 rows=7,109 loops=1)

104. 2,565.847 2,565.847 ↓ 3.9 6,709 1

CTE Scan on bucketac cte0_7 (cost=0.00..7,757.26 rows=1,724 width=12) (actual time=365.942..2,565.847 rows=6,709 loops=1)

  • Filter: (c6 = 'won'::text)
  • Rows Removed by Filter: 86254
105. 0.026 18,547.440 ↑ 1.0 400 1

Limit (cost=7,720.08..7,721.08 rows=400 width=12) (actual time=18,547.391..18,547.440 rows=400 loops=1)

106. 5.272 18,547.414 ↑ 4.2 400 1

Sort (cost=7,720.08..7,724.33 rows=1,697 width=12) (actual time=18,547.390..18,547.414 rows=400 loops=1)

  • Sort Key: cte0_8.c4 DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 43kB
107. 18,542.142 18,542.142 ↓ 21.0 35,559 1

CTE Scan on bucketec cte0_8 (cost=0.00..7,638.26 rows=1,697 width=12) (actual time=17,445.693..18,542.142 rows=35,559 loops=1)

  • Filter: (c6 = 'won'::text)
  • Rows Removed by Filter: 77301
108. 0.081 21,120.884 ↑ 1.3 400 1

Nested Loop (cost=1.13..5,532.92 rows=513 width=27) (actual time=21,116.420..21,120.884 rows=400 loops=1)

109. 0.304 21,118.803 ↑ 1.0 400 1

Nested Loop (cost=0.56..4,610.24 rows=416 width=16) (actual time=21,116.374..21,118.803 rows=400 loops=1)

110. 21,116.499 21,116.499 ↑ 1.0 400 1

CTE Scan on topsids cte0 (cost=0.00..8.00 rows=400 width=12) (actual time=21,116.343..21,116.499 rows=400 loops=1)

111. 2.000 2.000 ↑ 1.0 1 400

Index Scan using idx_t197_adobe_product_to_sales_team_assignment__cfact_50035705 on t197_adobe_product_to_sales_team_assignment__cfact (cost=0.56..11.50 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=400)

  • Index Cond: ((sid = cte0.c1) AND (end_stamp > '1567234799999'::bigint) AND (start_stamp <= '1567234799999'::bigint))
  • Filter: (NOT deleted)
112. 2.000 2.000 ↑ 1.0 1 400

Index Scan using t198_adobe_product_to_sales_team_assignment__cdim_sidendstampun on t198_adobe_product_to_sales_team_assignment__cdim (cost=0.56..2.21 rows=1 width=23) (actual time=0.004..0.005 rows=1 loops=400)

  • Index Cond: ((sid = t197_adobe_product_to_sales_team_assignment__cfact.sid) AND (end_stamp > '1567234799999'::bigint))
  • Filter: ((NOT deleted) AND (start_stamp <= '1567234799999'::bigint))
Planning time : 20.140 ms
Execution time : 21,138.530 ms