explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RhML

Settings
# exclusive inclusive rows x rows loops node
1. 0.067 2,346.014 ↑ 126.7 163 1

Nested Loop (cost=1,556,796.79..20,239,849.44 rows=20,657 width=96) (actual time=393.506..2,346.014 rows=163 loops=1)

2.          

CTE multitimecte

3. 0.019 0.019 ↑ 1.0 13 1

Values Scan on "*VALUES*" (cost=0.00..0.16 rows=13 width=8) (actual time=0.002..0.019 rows=13 loops=1)

4.          

CTE picklist_0

5. 0.032 0.032 ↑ 1.0 2 1

Seq Scan on t77_opportunitystagenamepicklistdim (cost=0.00..4.44 rows=2 width=4) (actual time=0.008..0.032 rows=2 loops=1)

  • Filter: (correlated_value = ANY ('{"6 - Order Accepted","5 - Field Closed Won"}'::text[]))
  • Rows Removed by Filter: 113
6.          

CTE picklist_2

7. 0.021 0.021 ↑ 1.0 1 1

Seq Scan on t77_opportunitystagenamepicklistdim t77_opportunitystagenamepicklistdim_1 (cost=0.00..4.44 rows=1 width=4) (actual time=0.009..0.021 rows=1 loops=1)

  • Filter: (correlated_value = '7 - Closed (Not Won)'::text)
  • Rows Removed by Filter: 114
8.          

CTE picklist_3

9. 0.032 0.032 ↑ 1.0 3 1

Seq Scan on t77_opportunitystagenamepicklistdim t77_opportunitystagenamepicklistdim_2 (cost=0.00..4.58 rows=3 width=4) (actual time=0.008..0.032 rows=3 loops=1)

  • Filter: (correlated_value = ANY ('{"6 - Order Accepted","5 - Field Closed Won","7 - Closed (Not Won)"}'::text[]))
  • Rows Removed by Filter: 112
10. 0.032 0.032 ↑ 1.0 13 1

CTE Scan on multitimecte multitimecte_alias (cost=0.00..0.26 rows=13 width=8) (actual time=0.004..0.032 rows=13 loops=1)

11. 0.052 2,345.915 ↑ 122.2 13 13

Append (cost=1,556,783.17..1,556,878.65 rows=1,589 width=96) (actual time=176.437..180.455 rows=13 loops=13)

12.          

CTE openwont1

13. 84.873 1,652.378 ↑ 2.6 7,098 13

Nested Loop (cost=34,523.28..1,368,066.09 rows=18,230 width=34) (actual time=11.011..127.106 rows=7,098 loops=13)

14. 28.475 880.386 ↓ 1.5 8,188 13

Hash Left Join (cost=34,522.63..117,790.23 rows=5,421 width=60) (actual time=10.962..67.722 rows=8,188 loops=13)

  • Hash Cond: (t65_oppfact_1.c917_opp_currency_code = t59_claricurrencytypedim_1.sid)
15. 42.866 851.903 ↓ 1.5 8,188 13

Nested Loop Left Join (cost=34,521.50..117,762.75 rows=5,421 width=56) (actual time=10.959..65.531 rows=8,188 loops=13)

16. 24.089 383.253 ↓ 1.5 8,188 13

Hash Left Join (cost=34,520.95..72,698.81 rows=5,421 width=28) (actual time=10.951..29.481 rows=8,188 loops=13)

  • Hash Cond: (t65_oppfact.c917_opp_currency_code = t59_claricurrencytypedim.sid)
17. 50.529 359.151 ↓ 1.5 8,188 13

Hash Join (cost=34,519.82..72,671.34 rows=5,421 width=24) (actual time=10.947..27.627 rows=8,188 loops=13)

  • Hash Cond: (t65_oppfact.c1008_opp_ownerid = t61_userrolehierarchy_2.sid)
18. 179.261 250.757 ↓ 1.5 8,188 13

Bitmap Heap Scan on t65_oppfact (cost=3,353.64..41,478.41 rows=5,421 width=28) (actual time=6.487..19.289 rows=8,188 loops=13)

  • Recheck Cond: ((c927_opp_close_date >= 20180701) AND (c927_opp_close_date <= 20180930) AND (start_stamp <= $9) AND (end_stamp > $9))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 9)))
  • Rows Removed by Filter: 4331
  • Heap Blocks: exact=85295
19. 71.474 71.474 ↓ 1.2 12,520 13

Bitmap Index Scan on ix_t65_oppfact_closedate_stagename_timestamp (cost=0.00..3,352.26 rows=10,842 width=0) (actual time=5.498..5.498 rows=12,520 loops=13)

  • Index Cond: ((c927_opp_close_date >= 20180701) AND (c927_opp_close_date <= 20180930) AND (start_stamp <= $9) AND (end_stamp > $9))
20.          

SubPlan (forBitmap Heap Scan)

21. 0.022 0.022 ↑ 1.0 1 1

CTE Scan on picklist_2 cte0_4 (cost=0.00..0.02 rows=1 width=4) (actual time=0.009..0.022 rows=1 loops=1)

22. 0.825 57.865 ↑ 1.1 6,046 1

Hash (cost=31,083.91..31,083.91 rows=6,582 width=4) (actual time=57.865..57.865 rows=6,046 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 277kB
23. 2.094 57.040 ↑ 1.1 6,046 1

HashAggregate (cost=31,018.09..31,083.91 rows=6,582 width=4) (actual time=56.297..57.040 rows=6,046 loops=1)

  • Group Key: t61_userrolehierarchy_2.sid
24. 12.983 54.946 ↑ 1.0 6,502 1

Hash Semi Join (cost=4,315.67..31,001.64 rows=6,582 width=4) (actual time=12.328..54.946 rows=6,502 loops=1)

  • Hash Cond: (t61_userrolehierarchy_2.c898_user_role_id = t42_userroledim_2.sid)
25. 31.345 41.386 ↑ 1.0 97,968 1

Bitmap Heap Scan on t61_userrolehierarchy t61_userrolehierarchy_2 (cost=3,138.89..29,419.97 rows=101,272 width=8) (actual time=11.742..41.386 rows=97,968 loops=1)

  • Recheck Cond: ((c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=10585
26. 10.041 10.041 ↓ 1.1 109,831 1

Bitmap Index Scan on idx_t61_userrolehierarchy_c899_ancestor_role_id_end_sta34629852 (cost=0.00..3,113.58 rows=101,272 width=0) (actual time=10.041..10.041 rows=109,831 loops=1)

  • Index Cond: ((c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
27. 0.057 0.577 ↑ 1.3 451 1

Hash (cost=1,169.49..1,169.49 rows=583 width=4) (actual time=0.577..0.577 rows=451 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
28. 0.451 0.520 ↑ 1.3 451 1

Bitmap Heap Scan on t42_userroledim t42_userroledim_2 (cost=41.12..1,169.49 rows=583 width=4) (actual time=0.089..0.520 rows=451 loops=1)

  • Recheck Cond: ((end_stamp = '32503680000000'::bigint) AND ((c572_portaltype IS NULL) OR (c572_portaltype = 'None'::text)))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
  • Rows Removed by Filter: 127
  • Heap Blocks: exact=172
29. 0.069 0.069 ↑ 1.0 578 1

Bitmap Index Scan on ix_t42_userroledim_sid_timestamp_partial (cost=0.00..40.97 rows=585 width=0) (actual time=0.069..0.069 rows=578 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
30. 0.002 0.013 ↓ 3.0 3 1

Hash (cost=1.11..1.11 rows=1 width=12) (actual time=0.013..0.013 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
31. 0.011 0.011 ↓ 3.0 3 1

Seq Scan on t59_claricurrencytypedim (cost=0.00..1.11 rows=1 width=12) (actual time=0.009..0.011 rows=3 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 6
32. 425.784 425.784 ↑ 1.0 1 106,446

Index Scan using idx_t65_oppfact_sid_end_stamp_start_stamp on t65_oppfact t65_oppfact_1 (cost=0.56..8.30 rows=1 width=28) (actual time=0.004..0.004 rows=1 loops=106,446)

  • Index Cond: ((t65_oppfact.sid = sid) AND (t65_oppfact.sid = sid) AND (end_stamp > '1546243199999'::bigint) AND (start_stamp <= '1546243199999'::bigint))
  • Filter: (NOT deleted)
33. 0.004 0.008 ↓ 3.0 3 1

Hash (cost=1.11..1.11 rows=1 width=12) (actual time=0.008..0.008 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
34. 0.004 0.004 ↓ 3.0 3 1

Seq Scan on t59_claricurrencytypedim t59_claricurrencytypedim_1 (cost=0.00..1.11 rows=1 width=12) (actual time=0.004..0.004 rows=3 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 6
35. 638.676 638.676 ↑ 17.0 1 106,446

Index Scan using idx_t45_oppdim_sid_end_stamp_start_stamp on t45_oppdim (cost=0.56..90.95 rows=17 width=4) (actual time=0.005..0.006 rows=1 loops=106,446)

  • Index Cond: ((sid = t65_oppfact.sid) AND (end_stamp > $9) AND (start_stamp <= $9))
  • Filter: ((NOT deleted) AND (c725_is_my_alignment__c = 'Y'::text))
  • Rows Removed by Filter: 0
36.          

SubPlan (forNested Loop)

37. 0.000 0.000 ↑ 1.0 2 1

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

38. 0.000 0.000 ↑ 1.0 2 1

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

39. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.71..41.47 rows=1 width=0) (never executed)

40. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t61_userrolehierarchy_sid_c899_ancestor_role_id_end34629852 on t61_userrolehierarchy (cost=0.43..16.85 rows=2 width=4) (never executed)

  • Index Cond: ((sid = t65_oppfact_1.c1008_opp_ownerid) AND (c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
41. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_t42_userroledim_sid_timestamp_partial on t42_userroledim (cost=0.28..8.30 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t61_userrolehierarchy.c898_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
42. 12.356 48.443 ↑ 1.0 6,502 1

Hash Semi Join (cost=4,315.67..31,001.64 rows=6,582 width=4) (actual time=11.780..48.443 rows=6,502 loops=1)

  • Hash Cond: (t61_userrolehierarchy_1.c898_user_role_id = t42_userroledim_1.sid)
43. 26.020 35.602 ↑ 1.0 97,968 1

Bitmap Heap Scan on t61_userrolehierarchy t61_userrolehierarchy_1 (cost=3,138.89..29,419.97 rows=101,272 width=8) (actual time=11.285..35.602 rows=97,968 loops=1)

  • Recheck Cond: ((c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=10585
44. 9.582 9.582 ↓ 1.1 109,831 1

Bitmap Index Scan on idx_t61_userrolehierarchy_c899_ancestor_role_id_end_sta34629852 (cost=0.00..3,113.58 rows=101,272 width=0) (actual time=9.582..9.582 rows=109,831 loops=1)

  • Index Cond: ((c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
45. 0.060 0.485 ↑ 1.3 451 1

Hash (cost=1,169.49..1,169.49 rows=583 width=4) (actual time=0.485..0.485 rows=451 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
46. 0.357 0.425 ↑ 1.3 451 1

Bitmap Heap Scan on t42_userroledim t42_userroledim_1 (cost=41.12..1,169.49 rows=583 width=4) (actual time=0.093..0.425 rows=451 loops=1)

  • Recheck Cond: ((end_stamp = '32503680000000'::bigint) AND ((c572_portaltype IS NULL) OR (c572_portaltype = 'None'::text)))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
  • Rows Removed by Filter: 127
  • Heap Blocks: exact=172
47. 0.068 0.068 ↑ 1.0 578 1

Bitmap Index Scan on ix_t42_userroledim_sid_timestamp_partial (cost=0.00..40.97 rows=585 width=0) (actual time=0.068..0.068 rows=578 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
48.          

CTE openwont1ids

49. 45.123 1,734.096 ↓ 35.5 7,098 13

HashAggregate (cost=410.18..412.18 rows=200 width=4) (actual time=132.438..133.392 rows=7,098 loops=13)

  • Group Key: cte0_5.c1
50. 1,688.973 1,688.973 ↑ 2.6 7,098 13

CTE Scan on openwont1 cte0_5 (cost=0.00..364.60 rows=18,230 width=4) (actual time=11.014..129.921 rows=7,098 loops=13)

51.          

CTE bucketab

52. 30.966 47.996 ↑ 202.6 9 13

HashAggregate (cost=638.05..656.28 rows=1,823 width=68) (actual time=3.689..3.692 rows=9 loops=13)

  • Group Key: cte0_6.c5, CASE WHEN cte0_6.c6 THEN CASE WHEN cte0_6.c4 THEN 'convertedWon'::text ELSE 'convertedOpen'::text END ELSE CASE WHEN cte0_6.c4 THEN 'unconvertedWon'::text ELSE 'unconvertedOpen'::text END END, cte0_6.c3
53. 17.030 17.030 ↑ 2.6 7,098 13

CTE Scan on openwont1 cte0_6 (cost=0.00..364.60 rows=18,230 width=64) (actual time=0.002..1.310 rows=7,098 loops=13)

54.          

CTE bucketd

55. 3.185 2,297.503 ↑ 385.8 4 13

GroupAggregate (cost=123,607.46..187,648.62 rows=1,543 width=120) (actual time=176.430..176.731 rows=4 loops=13)

  • Group Key: ($9), (CASE WHEN (t65_oppfact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 13)) THEN CASE WHEN (t65_oppfact_3.c927_opp_close_date > 20180930) THEN 'pulledIn'::text WHEN (t65_oppfact_3.c927_opp_close_date < 20180701) THEN 'pushedIn'::text WHEN (alternatives: SubPlan 14 or hashed SubPlan 15) THEN 'other'::text ELSE 'ownerChange'::text END ELSE 'other'::text END)
56. 5.213 2,294.318 ↑ 1.5 1,042 13

Sort (cost=123,607.39..123,611.25 rows=1,543 width=60) (actual time=176.419..176.486 rows=1,042 loops=13)

  • Sort Key: (CASE WHEN (t65_oppfact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 13)) THEN CASE WHEN (t65_oppfact_3.c927_opp_close_date > 20180930) THEN 'pulledIn'::text WHEN (t65_oppfact_3.c927_opp_close_date < 20180701) THEN 'pushedIn'::text WHEN (alternatives: SubPlan 14 or hashed SubPlan 15) THEN 'other'::text ELSE 'ownerChange'::text END ELSE 'other'::text END)
  • Sort Method: quicksort Memory: 25kB
57. 5.457 2,289.105 ↑ 1.5 1,042 13

Nested Loop (cost=7.00..123,525.68 rows=1,543 width=60) (actual time=142.839..176.085 rows=1,042 loops=13)

58. 12.330 2,117.856 ↓ 3.6 1,422 13

Nested Loop Left Join (cost=6.37..18,602.30 rows=390 width=36) (actual time=136.717..162.912 rows=1,422 loops=13)

59. 12.779 2,050.074 ↓ 3.6 1,422 13

Nested Loop Left Join (cost=5.81..15,386.54 rows=390 width=20) (actual time=136.710..157.698 rows=1,422 loops=13)

  • Join Filter: (t65_oppfact_2.c917_opp_currency_code = t59_claricurrencytypedim_2.sid)
  • Rows Removed by Join Filter: 2844
60. 7.290 2,037.295 ↓ 3.6 1,422 13

Nested Loop Semi Join (cost=5.81..15,379.58 rows=390 width=16) (actual time=136.706..156.715 rows=1,422 loops=13)

61. 5.980 1,937.585 ↓ 3.6 1,422 13

Nested Loop (cost=5.11..10,285.19 rows=390 width=20) (actual time=136.680..149.045 rows=1,422 loops=13)

62. 0.026 0.117 ↑ 1.0 2 13

Unique (cost=0.05..0.06 rows=2 width=4) (actual time=0.006..0.009 rows=2 loops=13)

63. 0.039 0.091 ↑ 1.0 2 13

Sort (cost=0.05..0.06 rows=2 width=4) (actual time=0.006..0.007 rows=2 loops=13)

  • Sort Key: cte0_9.c1
  • Sort Method: quicksort Memory: 25kB
64. 0.052 0.052 ↑ 1.0 2 13

CTE Scan on picklist_0 cte0_9 (cost=0.00..0.04 rows=2 width=4) (actual time=0.001..0.004 rows=2 loops=13)

65. 176.215 1,931.488 ↓ 3.6 711 26

Index Scan using ix_t65_oppfact_closedate_stagename_timestamp on t65_oppfact t65_oppfact_2 (cost=5.06..5,140.62 rows=195 width=24) (actual time=70.081..74.288 rows=711 loops=26)

  • Index Cond: ((c927_opp_close_date >= 20180701) AND (c927_opp_close_date <= 20180930) AND (c933_opp_stagename = cte0_9.c1) AND (start_stamp <= '1546243199999'::bigint) AND (end_stamp > '1546243199999'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 16)))
  • Rows Removed by Filter: 1070
66.          

SubPlan (forIndex Scan)

67. 1,755.273 1,755.273 ↓ 35.5 7,098 13

CTE Scan on openwont1ids cte0_8 (cost=0.00..4.00 rows=200 width=4) (actual time=132.444..135.021 rows=7,098 loops=13)

68. 18.484 92.420 ↑ 1.0 1 18,484

Nested Loop Semi Join (cost=0.71..13.05 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=18,484)

69. 36.968 36.968 ↑ 2.0 1 18,484

Index Scan using idx_t61_userrolehierarchy_sid_c899_ancestor_role_id_end34629852 on t61_userrolehierarchy t61_userrolehierarchy_5 (cost=0.43..12.25 rows=2 width=8) (actual time=0.002..0.002 rows=1 loops=18,484)

  • Index Cond: ((sid = t65_oppfact_2.c1008_opp_ownerid) AND (c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
70. 36.968 36.968 ↑ 1.0 1 18,484

Index Scan using ix_t42_userroledim_sid_timestamp_partial on t42_userroledim t42_userroledim_5 (cost=0.28..0.36 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=18,484)

  • Index Cond: ((sid = t61_userrolehierarchy_5.c898_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
71. 0.000 0.000 ↓ 3.0 3 18,484

Materialize (cost=0.00..1.12 rows=1 width=12) (actual time=0.000..0.000 rows=3 loops=18,484)

72. 0.010 0.010 ↓ 3.0 3 1

Seq Scan on t59_claricurrencytypedim t59_claricurrencytypedim_2 (cost=0.00..1.11 rows=1 width=12) (actual time=0.008..0.010 rows=3 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 6
73. 55.452 55.452 ↓ 0.0 0 18,484

Index Scan using idx_t65_oppfact_sid_end_stamp_start_stamp on t65_oppfact t65_oppfact_3 (cost=0.56..8.24 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=18,484)

  • Index Cond: ((t65_oppfact_2.sid = sid) AND (t65_oppfact_2.sid = sid) AND (end_stamp > $9) AND (start_stamp <= $9))
  • Filter: (NOT deleted)
74. 110.904 110.904 ↑ 19.0 1 18,484

Index Scan using idx_t45_oppdim_sid_end_stamp_start_stamp on t45_oppdim t45_oppdim_1 (cost=0.56..104.73 rows=19 width=4) (actual time=0.005..0.006 rows=1 loops=18,484)

  • Index Cond: ((sid = t65_oppfact_2.sid) AND (end_stamp > '1546243199999'::bigint) AND (start_stamp <= '1546243199999'::bigint))
  • Filter: ((NOT deleted) AND (c725_is_my_alignment__c = 'Y'::text))
  • Rows Removed by Filter: 0
75.          

SubPlan (forNested Loop)

76. 0.036 0.036 ↑ 1.0 3 1

CTE Scan on picklist_3 cte0_7 (cost=0.00..0.06 rows=3 width=4) (actual time=0.010..0.036 rows=3 loops=1)

77. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.71..41.47 rows=1 width=0) (never executed)

78. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_t61_userrolehierarchy_sid_c899_ancestor_role_id_end34629852 on t61_userrolehierarchy t61_userrolehierarchy_3 (cost=0.43..16.85 rows=2 width=4) (never executed)

  • Index Cond: ((sid = t65_oppfact_3.c1008_opp_ownerid) AND (c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
79. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_t42_userroledim_sid_timestamp_partial on t42_userroledim t42_userroledim_3 (cost=0.28..8.30 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t61_userrolehierarchy_3.c898_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
80. 12.835 54.852 ↑ 1.0 6,502 1

Hash Semi Join (cost=4,315.67..31,001.64 rows=6,582 width=4) (actual time=12.276..54.852 rows=6,502 loops=1)

  • Hash Cond: (t61_userrolehierarchy_4.c898_user_role_id = t42_userroledim_4.sid)
81. 31.437 41.452 ↑ 1.0 97,968 1

Bitmap Heap Scan on t61_userrolehierarchy t61_userrolehierarchy_4 (cost=3,138.89..29,419.97 rows=101,272 width=8) (actual time=11.700..41.452 rows=97,968 loops=1)

  • Recheck Cond: ((c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=10585
82. 10.015 10.015 ↓ 1.1 109,831 1

Bitmap Index Scan on idx_t61_userrolehierarchy_c899_ancestor_role_id_end_sta34629852 (cost=0.00..3,113.58 rows=101,272 width=0) (actual time=10.015..10.015 rows=109,831 loops=1)

  • Index Cond: ((c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
83. 0.056 0.565 ↑ 1.3 451 1

Hash (cost=1,169.49..1,169.49 rows=583 width=4) (actual time=0.565..0.565 rows=451 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
84. 0.449 0.509 ↑ 1.3 451 1

Bitmap Heap Scan on t42_userroledim t42_userroledim_4 (cost=41.12..1,169.49 rows=583 width=4) (actual time=0.084..0.509 rows=451 loops=1)

  • Recheck Cond: ((end_stamp = '32503680000000'::bigint) AND ((c572_portaltype IS NULL) OR (c572_portaltype = 'None'::text)))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
  • Rows Removed by Filter: 127
  • Heap Blocks: exact=172
85. 0.060 0.060 ↑ 1.0 578 1

Bitmap Index Scan on ix_t42_userroledim_sid_timestamp_partial (cost=0.00..40.97 rows=585 width=0) (actual time=0.060..0.060 rows=578 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
86. 0.065 2,297.620 ↑ 385.8 4 13

Subquery Scan on *SELECT* 1 (cost=0.00..50.15 rows=1,543 width=96) (actual time=176.436..176.740 rows=4 loops=13)

87. 2,297.555 2,297.555 ↑ 385.8 4 13

CTE Scan on bucketd cte0 (cost=0.00..30.86 rows=1,543 width=120) (actual time=176.432..176.735 rows=4 loops=13)

88. 0.079 48.243 ↑ 5.1 9 13

Hash Join (cost=1.11..44.87 rows=46 width=96) (actual time=3.703..3.711 rows=9 loops=13)

  • Hash Cond: (cte0_1.c6 = t75_opportunityforecastcategorypicklistdim.sid)
89. 48.152 48.152 ↑ 202.6 9 13

CTE Scan on bucketab cte0_1 (cost=0.00..36.46 rows=1,823 width=68) (actual time=3.699..3.704 rows=9 loops=13)

90. 0.006 0.012 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=36) (actual time=0.012..0.012 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
91. 0.006 0.006 ↑ 1.0 5 1

Seq Scan on t75_opportunityforecastcategorypicklistdim (cost=0.00..1.05 rows=5 width=36) (actual time=0.006..0.006 rows=5 loops=1)