explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DeCF

Settings
# exclusive inclusive rows x rows loops node
1. 0.065 1,464.200 ↑ 444.4 172 1

Nested Loop (cost=1,063,370.69..13,827,913.38 rows=76,440 width=96) (actual time=299.652..1,464.200 rows=172 loops=1)

2.          

CTE multitimecte

3. 0.015 0.015 ↑ 1.0 13 1

Values Scan on "*VALUES*" (cost=0.00..0.16 rows=13 width=8) (actual time=0.001..0.015 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.009..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.025 0.025 ↑ 1.0 1 1

Seq Scan on t77_opportunitystagenamepicklistdim t77_opportunitystagenamepicklistdim_1 (cost=0.00..4.44 rows=1 width=4) (actual time=0.011..0.025 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.023 0.023 ↑ 1.0 13 1

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

11. 0.039 1,464.112 ↑ 452.3 13 13

Append (cost=1,063,357.07..1,063,566.98 rows=5,880 width=96) (actual time=107.600..112.624 rows=13 loops=13)

12.          

CTE openwont1

13. 86.782 930.566 ↓ 1.2 8,837 13

Hash Left Join (cost=35,937.30..456,505.12 rows=7,546 width=34) (actual time=12.378..71.582 rows=8,837 loops=13)

  • Hash Cond: (t60_opp_split_fact_1.c850_opp_currency_code = t59_claricurrencytypedim_1.sid)
14. 53.483 794.703 ↓ 1.2 8,837 13

Nested Loop Left Join (cost=35,936.08..143,408.44 rows=7,546 width=56) (actual time=12.372..61.131 rows=8,837 loops=13)

15. 27.755 396.565 ↓ 1.2 8,837 13

Hash Left Join (cost=35,935.65..87,687.55 rows=7,546 width=28) (actual time=12.364..30.505 rows=8,837 loops=13)

  • Hash Cond: (t60_opp_split_fact.c850_opp_currency_code = t59_claricurrencytypedim.sid)
16. 54.378 368.797 ↓ 1.2 8,837 13

Hash Join (cost=35,934.53..87,650.52 rows=7,546 width=24) (actual time=12.361..28.369 rows=8,837 loops=13)

  • Hash Cond: (t60_opp_split_fact.c879_opportunity_split_ownerid = t61_userrolehierarchy_2.sid)
17. 166.634 254.748 ↓ 1.2 8,837 13

Bitmap Heap Scan on t60_opp_split_fact (cost=4,768.34..56,447.06 rows=7,546 width=28) (actual time=7.742..19.596 rows=8,837 loops=13)

  • Recheck Cond: ((c855_opp_close_date >= 20180701) AND (c855_opp_close_date <= 20180930) AND (start_stamp <= $9) AND (end_stamp > $9))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 9)))
  • Rows Removed by Filter: 8331
  • Heap Blocks: exact=81401
18. 88.088 88.088 ↓ 1.1 17,168 13

Bitmap Index Scan on ix_t60_opp_split_fact_closedate_stagename_timestamp (cost=0.00..4,766.43 rows=16,133 width=0) (actual time=6.776..6.776 rows=17,168 loops=13)

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

SubPlan (forBitmap Heap Scan)

20. 0.026 0.026 ↑ 1.0 1 1

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

21. 0.806 59.671 ↑ 1.1 6,046 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 277kB
22. 2.095 58.865 ↑ 1.1 6,046 1

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

  • Group Key: t61_userrolehierarchy_2.sid
23. 12.941 56.770 ↑ 1.0 6,502 1

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

  • Hash Cond: (t61_userrolehierarchy_2.c898_user_role_id = t42_userroledim_2.sid)
24. 32.887 43.220 ↑ 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=12.186..43.220 rows=97,968 loops=1)

  • Recheck Cond: ((c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=10585
25. 10.333 10.333 ↓ 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.333..10.333 rows=109,831 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
27. 0.471 0.540 ↑ 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.092..0.540 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
28. 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)
29. 0.004 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
30. 0.009 0.009 ↓ 3.0 3 1

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

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 6
31. 344.655 344.655 ↑ 1.0 1 114,885

Index Scan using idx_t60_opp_split_fact_sid_end_stamp_start_stamp on t60_opp_split_fact t60_opp_split_fact_1 (cost=0.43..7.37 rows=1 width=28) (actual time=0.003..0.003 rows=1 loops=114,885)

  • Index Cond: ((t60_opp_split_fact.sid = sid) AND (t60_opp_split_fact.sid = sid) AND (end_stamp > '1546243199999'::bigint) AND (start_stamp <= '1546243199999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
32. 0.002 0.009 ↓ 3.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
33. 0.007 0.007 ↓ 3.0 3 1

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

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 6
34.          

SubPlan (forHash Left Join)

35. 0.002 0.002 ↑ 1.0 2 1

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

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

37. 0.000 0.000 ↓ 0.0 0

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

38. 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 = t60_opp_split_fact_1.c879_opportunity_split_ownerid) AND (c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
39. 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))
40. 12.516 49.070 ↑ 1.0 6,502 1

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

  • Hash Cond: (t61_userrolehierarchy_1.c898_user_role_id = t42_userroledim_1.sid)
41. 25.807 35.997 ↑ 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.918..35.997 rows=97,968 loops=1)

  • Recheck Cond: ((c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=10585
42. 10.190 10.190 ↓ 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.190..10.190 rows=109,831 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
44. 0.428 0.496 ↑ 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.092..0.496 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
45. 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)
46.          

CTE openwont1ids

47. 56.043 1,031.420 ↓ 44.2 8,837 13

HashAggregate (cost=169.79..171.79 rows=200 width=4) (actual time=78.226..79.340 rows=8,837 loops=13)

  • Group Key: cte0_5.c1
48. 975.377 975.377 ↓ 1.2 8,837 13

CTE Scan on openwont1 cte0_5 (cost=0.00..150.92 rows=7,546 width=4) (actual time=12.380..75.029 rows=8,837 loops=13)

49.          

CTE bucketab

50. 38.857 59.969 ↑ 83.9 9 13

HashAggregate (cost=264.11..271.66 rows=755 width=68) (actual time=4.609..4.613 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
51. 21.112 21.112 ↓ 1.2 8,837 13

CTE Scan on openwont1 cte0_6 (cost=0.00..150.92 rows=7,546 width=64) (actual time=0.002..1.624 rows=8,837 loops=13)

52.          

CTE bucketd

53. 4.030 1,403.818 ↑ 1,465.2 4 13

GroupAggregate (cost=363,151.68..606,408.51 rows=5,861 width=120) (actual time=107.593..107.986 rows=4 loops=13)

  • Group Key: ($9), (CASE WHEN (t60_opp_split_fact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 13)) THEN CASE WHEN (t60_opp_split_fact_3.c855_opp_close_date > 20180930) THEN 'pulledIn'::text WHEN (t60_opp_split_fact_3.c855_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)
54. 5.343 1,399.788 ↑ 4.4 1,319 13

Sort (cost=363,151.61..363,166.26 rows=5,861 width=60) (actual time=107.585..107.676 rows=1,319 loops=13)

  • Sort Key: (CASE WHEN (t60_opp_split_fact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 13)) THEN CASE WHEN (t60_opp_split_fact_3.c855_opp_close_date > 20180930) THEN 'pulledIn'::text WHEN (t60_opp_split_fact_3.c855_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: 27kB
55. 0.256 1,394.445 ↑ 4.4 1,319 13

Nested Loop Left Join (cost=36,160.52..362,784.80 rows=5,861 width=60) (actual time=93.333..107.265 rows=1,319 loops=13)

56. 3.480 1,342.705 ↑ 4.4 1,319 13

Hash Left Join (cost=36,160.02..85,029.07 rows=5,861 width=20) (actual time=93.325..103.285 rows=1,319 loops=13)

  • Hash Cond: (t60_opp_split_fact_2.c850_opp_currency_code = t59_claricurrencytypedim_2.sid)
57. 4.535 1,339.208 ↑ 4.4 1,319 13

Hash Join (cost=36,158.90..85,000.05 rows=5,861 width=16) (actual time=93.322..103.016 rows=1,319 loops=13)

  • Hash Cond: (t60_opp_split_fact_2.c879_opportunity_split_ownerid = t61_userrolehierarchy_5.sid)
58. 4.628 1,276.782 ↑ 4.4 1,319 13

Nested Loop (cost=4,992.71..53,804.92 rows=5,861 width=20) (actual time=88.865..98.214 rows=1,319 loops=13)

59. 0.013 0.104 ↑ 1.0 2 13

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

60. 0.052 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
61. 0.039 0.039 ↑ 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.003 rows=2 loops=13)

62. 83.044 1,272.050 ↑ 4.4 660 26

Bitmap Heap Scan on t60_opp_split_fact t60_opp_split_fact_2 (cost=4,992.66..26,873.13 rows=2,930 width=24) (actual time=46.969..48.925 rows=660 loops=26)

  • Recheck Cond: ((c855_opp_close_date >= 20180701) AND (c855_opp_close_date <= 20180930) AND (c856_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: 2231
  • Heap Blocks: exact=38766
63. 133.822 133.822 ↑ 2.2 2,890 26

Bitmap Index Scan on ix_t60_opp_split_fact_closedate_stagename_timestamp (cost=0.00..4,987.43 rows=6,266 width=0) (actual time=5.147..5.147 rows=2,890 loops=26)

  • Index Cond: ((c855_opp_close_date >= 20180701) AND (c855_opp_close_date <= 20180930) AND (c856_opp_stagename = cte0_9.c1) AND (start_stamp <= '1546243199999'::bigint) AND (end_stamp > '1546243199999'::bigint))
64.          

SubPlan (forBitmap Heap Scan)

65. 1,055.184 1,055.184 ↓ 44.2 8,837 13

CTE Scan on openwont1ids cte0_8 (cost=0.00..4.00 rows=200 width=4) (actual time=78.240..81.168 rows=8,837 loops=13)

66. 0.829 57.891 ↑ 1.1 6,046 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 277kB
67. 2.136 57.062 ↑ 1.1 6,046 1

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

  • Group Key: t61_userrolehierarchy_5.sid
68. 13.110 54.926 ↑ 1.0 6,502 1

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

  • Hash Cond: (t61_userrolehierarchy_5.c898_user_role_id = t42_userroledim_5.sid)
69. 31.268 41.233 ↑ 1.0 97,968 1

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

  • Recheck Cond: ((c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=10585
70. 9.965 9.965 ↓ 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.965..9.965 rows=109,831 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
72. 0.452 0.526 ↑ 1.3 451 1

Bitmap Heap Scan on t42_userroledim t42_userroledim_5 (cost=41.12..1,169.49 rows=583 width=4) (actual time=0.099..0.526 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
73. 0.074 0.074 ↑ 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.074..0.074 rows=578 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
74. 0.003 0.017 ↓ 3.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
75. 0.014 0.014 ↓ 3.0 3 1

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

  • Filter: (to_iso_code = 'USD'::text)
  • Rows Removed by Filter: 6
76. 51.450 51.450 ↓ 0.0 0 17,150

Index Scan using idx_t60_opp_split_fact_sid_end_stamp_start_stamp on t60_opp_split_fact t60_opp_split_fact_3 (cost=0.43..5.90 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=17,150)

  • Index Cond: ((t60_opp_split_fact_2.sid = sid) AND (t60_opp_split_fact_2.sid = sid) AND (end_stamp > $9) AND (start_stamp <= $9))
  • Filter: (NOT deleted)
77.          

SubPlan (forNested Loop Left Join)

78. 0.034 0.034 ↑ 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.034 rows=3 loops=1)

79. 0.000 0.000 ↓ 0.0 0

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

80. 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 = t60_opp_split_fact_3.c879_opportunity_split_ownerid) AND (c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
81. 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))
82. 0.000 0.000 ↓ 0.0 0

Hash Semi Join (cost=4,315.67..31,001.64 rows=6,582 width=4) (never executed)

  • Hash Cond: (t61_userrolehierarchy_4.c898_user_role_id = t42_userroledim_4.sid)
83. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t61_userrolehierarchy t61_userrolehierarchy_4 (cost=3,138.89..29,419.97 rows=101,272 width=8) (never executed)

  • Recheck Cond: ((c899_ancestor_role_id = ANY ('{1172,22279}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
84. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_t61_userrolehierarchy_c899_ancestor_role_id_end_sta34629852 (cost=0.00..3,113.58 rows=101,272 width=0) (never executed)

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

Hash (cost=1,169.49..1,169.49 rows=583 width=4) (never executed)

86. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t42_userroledim t42_userroledim_4 (cost=41.12..1,169.49 rows=583 width=4) (never executed)

  • Recheck Cond: ((end_stamp = '32503680000000'::bigint) AND ((c572_portaltype IS NULL) OR (c572_portaltype = 'None'::text)))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
87. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ix_t42_userroledim_sid_timestamp_partial (cost=0.00..40.97 rows=585 width=0) (never executed)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
88. 0.078 1,403.935 ↑ 1,465.2 4 13

Subquery Scan on *SELECT* 1 (cost=0.00..190.48 rows=5,861 width=96) (actual time=107.599..107.995 rows=4 loops=13)

89. 1,403.857 1,403.857 ↑ 1,465.2 4 13

CTE Scan on bucketd cte0 (cost=0.00..117.22 rows=5,861 width=120) (actual time=107.595..107.989 rows=4 loops=13)

90. 0.058 60.138 ↑ 2.1 9 13

Hash Join (cost=1.11..19.23 rows=19 width=96) (actual time=4.618..4.626 rows=9 loops=13)

  • Hash Cond: (cte0_1.c6 = t75_opportunityforecastcategorypicklistdim.sid)
91. 60.073 60.073 ↑ 83.9 9 13

CTE Scan on bucketab cte0_1 (cost=0.00..15.10 rows=755 width=68) (actual time=4.615..4.621 rows=9 loops=13)

92. 0.003 0.007 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
93. 0.004 0.004 ↑ 1.0 5 1

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