explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TBG9

Settings
# exclusive inclusive rows x rows loops node
1. 0.043 10,465.465 ↑ 1.2 400 1

Nested Loop (cost=5,850,000,507.50..5,850,004,950.22 rows=470 width=19) (actual time=10,462.914..10,465.465 rows=400 loops=1)

2.          

CTE picklist_0

3. 0.067 0.067 ↑ 1.2 6 1

Index Only Scan using ix_gr_t233_opportunitystagenamepicklistdim on t233_opportunitystagenamepicklistdim (cost=0.28..25.78 rows=7 width=4) (actual time=0.021..0.067 rows=6 loops=1)

  • Index Cond: (correlated_value = ANY ('{"06 - Won, Deploy & Expand","6 - Won, Deploy & Expand","Closed - Won","06 - Won. Deploy & Expand",Won,"06 - Won, Deploy & Expand"}'::text[]))
  • Heap Fetches: 0
4.          

CTE picklist_1

5. 0.023 0.023 ↑ 1.0 7 1

Index Only Scan using ix_gr_t233_opportunitystagenamepicklistdim on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_1 (cost=0.28..26.06 rows=7 width=4) (actual time=0.007..0.023 rows=7 loops=1)

  • Index Cond: (correlated_value = ANY ('{"HPE Not Pursued",Lost,Close:Duplicate,"HP Not Pursued",Duplicate,Error,"Closed - Lost"}'::text[]))
  • Heap Fetches: 0
6.          

CTE picklist_4

7. 0.066 0.066 ↑ 1.1 13 1

Index Only Scan using ix_gr_t233_opportunitystagenamepicklistdim on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_2 (cost=0.28..43.84 rows=14 width=4) (actual time=0.020..0.066 rows=13 loops=1)

  • Index Cond: (correlated_value = ANY ('{"06 - Won, Deploy & Expand",Lost,"6 - Won, Deploy & Expand",Close:Duplicate,"HP Not Pursued","06 - Won, Deploy & Expand",Error,"HPE Not Pursued","Closed - Won","06 - Won. Deploy & Expand",Won,Duplicate,"Closed - Lost"}'::text[]))
  • Heap Fetches: 0
8.          

CTE ownercte

9. 7.246 1,645.112 ↑ 2.9 8,743 1

Unique (cost=598,894.01..749,342.58 rows=25,092 width=4) (actual time=1,487.802..1,645.112 rows=8,743 loops=1)

10. 42.340 1,637.866 ↓ 1.1 132,400 1

Merge Join (cost=598,894.01..749,043.53 rows=119,621 width=4) (actual time=1,487.801..1,637.866 rows=132,400 loops=1)

  • Merge Cond: (t728_userrolehierarchy.sid = t629_distinctcdo_opp_closedate_ownerid.c2616_ownerid)
11. 16.197 631.795 ↑ 9.6 137,320 1

Unique (cost=410,025.18..416,598.98 rows=1,314,759 width=4) (actual time=597.329..631.795 rows=137,320 loops=1)

12. 78.597 615.598 ↑ 9.0 145,870 1

Sort (cost=410,025.18..413,312.08 rows=1,314,759 width=4) (actual time=597.327..615.598 rows=145,870 loops=1)

  • Sort Key: t728_userrolehierarchy.sid
  • Sort Method: external merge Disk: 1992kB
13. 8.582 537.001 ↑ 9.0 146,025 1

Append (cost=60,527.36..258,427.81 rows=1,314,759 width=4) (actual time=149.010..537.001 rows=146,025 loops=1)

14. 170.957 515.283 ↑ 10.0 131,299 1

Hash Join (cost=60,527.36..233,894.85 rows=1,311,391 width=4) (actual time=149.009..515.283 rows=131,299 loops=1)

  • Hash Cond: (t728_userrolehierarchy.c2842_user_role_id = t67_userroledim.sid)
15. 203.429 332.315 ↓ 1.0 1,339,306 1

Bitmap Heap Scan on t728_userrolehierarchy (cost=39,522.32..203,815.18 rows=1,311,391 width=8) (actual time=136.525..332.315 rows=1,339,306 loops=1)

  • Recheck Cond: ((c2843_ancestor_role_id = 436) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=40728
16. 128.886 128.886 ↓ 1.0 1,342,124 1

Bitmap Index Scan on idx_t728_userrolehierarchy_c2843_ancestor_role_id_end_s36994704 (cost=0.00..39,194.47 rows=1,311,391 width=0) (actual time=128.886..128.886 rows=1,342,124 loops=1)

  • Index Cond: ((c2843_ancestor_role_id = 436) AND (end_stamp = '32503680000000'::bigint))
17. 1.212 12.011 ↑ 9.0 8,424 1

Hash (cost=20,053.11..20,053.11 rows=76,155 width=4) (actual time=12.011..12.011 rows=8,424 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 1321kB
18. 6.670 10.799 ↑ 9.0 8,424 1

Bitmap Heap Scan on t67_userroledim (cost=4,111.65..20,053.11 rows=76,155 width=4) (actual time=4.980..10.799 rows=8,424 loops=1)

  • Recheck Cond: ((end_stamp = '32503680000000'::bigint) AND ((c377_portaltype IS NULL) OR (c377_portaltype = 'None'::text)))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
  • Rows Removed by Filter: 7084
  • Heap Blocks: exact=2279
19. 4.129 4.129 ↑ 4.9 15,743 1

Bitmap Index Scan on ix_t67_userroledim_sid_end_start_timestamp_partial (cost=0.00..4,092.61 rows=77,169 width=0) (actual time=4.129..4.129 rows=15,743 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
20. 13.136 13.136 ↓ 4.4 14,726 1

Index Scan using ix_t12_userdim_usertype_nullrole_end_start on t12_userdim (cost=0.29..11,385.38 rows=3,368 width=4) (actual time=0.261..13.136 rows=14,726 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
21. 26.204 963.731 ↓ 1.2 286,335 1

Materialize (cost=188,868.83..190,065.04 rows=239,242 width=4) (actual time=889.177..963.731 rows=286,335 loops=1)

22. 165.999 937.527 ↓ 1.2 286,335 1

Sort (cost=188,868.83..189,466.93 rows=239,242 width=4) (actual time=889.172..937.527 rows=286,335 loops=1)

  • Sort Key: t629_distinctcdo_opp_closedate_ownerid.c2616_ownerid
  • Sort Method: external merge Disk: 3904kB
23. 771.528 771.528 ↓ 1.2 286,335 1

Seq Scan on t629_distinctcdo_opp_closedate_ownerid (cost=0.00..164,222.32 rows=239,242 width=4) (actual time=0.140..771.528 rows=286,335 loops=1)

  • Filter: ((c2620_closedate >= 20181101) AND (c2620_closedate <= 20190131))
  • Rows Removed by Filter: 6202196
24.          

CTE bucketac

25. 56.104 6,403.642 ↑ 17.1 92,561 1

Nested Loop Left Join (cost=615,353.99..3,417,524,128.10 rows=1,581,125 width=164) (actual time=3,050.923..6,403.642 rows=92,561 loops=1)

26. 149.133 3,291.914 ↑ 11.1 92,561 1

GroupAggregate (cost=611,487.30..639,813.65 rows=1,030,049 width=44) (actual time=3,048.024..3,291.914 rows=92,561 loops=1)

  • Group Key: t236_opp_line_item_fact.c801_opportunity_sid, t236_opp_line_item_fact.c820_opp_forecastcategory, t234_opportunityforecastcategorypicklistdim.correlated_app_order
27. 260.755 3,142.781 ↑ 2.5 419,442 1

Sort (cost=611,487.30..614,062.43 rows=1,030,049 width=28) (actual time=3,048.000..3,142.781 rows=419,442 loops=1)

  • Sort Key: t236_opp_line_item_fact.c801_opportunity_sid, t236_opp_line_item_fact.c820_opp_forecastcategory, t234_opportunityforecastcategorypicklistdim.correlated_app_order
  • Sort Method: external merge Disk: 17184kB
28. 78.006 2,882.026 ↑ 2.5 419,442 1

Hash Left Join (cost=448,230.45..483,967.86 rows=1,030,049 width=28) (actual time=2,538.379..2,882.026 rows=419,442 loops=1)

  • Hash Cond: (t236_opp_line_item_fact.c820_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim.sid)
29. 78.516 2,804.006 ↑ 2.5 419,442 1

Merge Right Join (cost=448,229.34..470,333.02 rows=1,030,049 width=24) (actual time=2,538.346..2,804.006 rows=419,442 loops=1)

  • Merge Cond: ((t716_clari_conversion_rate.sid = t236_opp_line_item_fact.c821_opp_currency_code) AND (t716_clari_conversion_rate.rate_date = t236_opp_line_item_fact.c805_opp_close_date))
30. 6.830 50.032 ↑ 1.0 105,100 1

Merge Append (cost=0.31..7,793.41 rows=105,520 width=16) (actual time=0.021..50.032 rows=105,100 loops=1)

  • Sort Key: t716_clari_conversion_rate.sid, t716_clari_conversion_rate.rate_date
31. 0.002 0.004 ↓ 0.0 0 1

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

  • Sort Key: t716_clari_conversion_rate.sid, t716_clari_conversion_rate.rate_date
  • Sort Method: quicksort Memory: 25kB
32. 0.002 0.002 ↓ 0.0 0 1

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

  • Filter: (to_iso_code = 'USD'::text)
33. 43.198 43.198 ↑ 1.0 105,100 1

Index Scan using t716_clari_conversion_rate_usd_sidratedateunique on t716_clari_conversion_rate_usd (cost=0.29..6,474.39 rows=105,519 width=16) (actual time=0.015..43.198 rows=105,100 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
34. 36.565 2,675.458 ↑ 2.5 419,442 1

Materialize (cost=448,229.03..453,379.27 rows=1,030,049 width=24) (actual time=2,537.120..2,675.458 rows=419,442 loops=1)

35. 258.172 2,638.893 ↑ 2.5 419,442 1

Sort (cost=448,229.03..450,804.15 rows=1,030,049 width=24) (actual time=2,537.114..2,638.893 rows=419,442 loops=1)

  • Sort Key: t236_opp_line_item_fact.c821_opp_currency_code, t236_opp_line_item_fact.c805_opp_close_date
  • Sort Method: external merge Disk: 15512kB
36. 55.446 2,380.721 ↑ 2.5 419,442 1

Nested Loop (cost=565.45..324,230.59 rows=1,030,049 width=24) (actual time=1,649.112..2,380.721 rows=419,442 loops=1)

37. 5.598 1,652.064 ↓ 43.7 8,743 1

HashAggregate (cost=564.57..566.57 rows=200 width=4) (actual time=1,648.982..1,652.064 rows=8,743 loops=1)

  • Group Key: ownercte_1.c1
38. 1,646.466 1,646.466 ↑ 2.9 8,743 1

CTE Scan on ownercte ownercte_1 (cost=0.00..501.84 rows=25,092 width=4) (actual time=1,487.804..1,646.466 rows=8,743 loops=1)

39. 673.140 673.211 ↑ 3.8 48 8,743

Index Scan using ix_t236_opp_line_item_fact_owner_closedate_timestamp on t236_opp_line_item_fact (cost=0.88..1,616.51 rows=181 width=28) (actual time=0.013..0.077 rows=48 loops=8,743)

  • Index Cond: ((c804_opp_ownerid = ownercte_1.c1) AND (c805_opp_close_date >= 20181101) AND (c805_opp_close_date <= 20190131) AND (start_stamp <= '1541055600000'::bigint) AND (end_stamp > '1541055600000'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 8)))
  • Rows Removed by Filter: 22
40.          

SubPlan (forIndex Scan)

41. 0.071 0.071 ↑ 1.1 13 1

CTE Scan on picklist_4 cte0_2 (cost=0.00..0.28 rows=14 width=4) (actual time=0.022..0.071 rows=13 loops=1)

42. 0.006 0.014 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=8) (actual time=0.014..0.014 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
43. 0.008 0.008 ↑ 1.0 5 1

Seq Scan on t234_opportunityforecastcategorypicklistdim (cost=0.00..1.05 rows=5 width=8) (actual time=0.007..0.008 rows=5 loops=1)

44. 92.561 3,054.513 ↑ 2.0 1 92,561

Subquery Scan on oli174_ft1 (cost=3,301.80..3,317.15 rows=2 width=84) (actual time=0.033..0.033 rows=1 loops=92,561)

  • Filter: (t236_opp_line_item_fact.c801_opportunity_sid = oli174_ft1.c1)
45. 277.683 2,961.952 ↑ 307.0 1 92,561

GroupAggregate (cost=3,301.80..3,313.31 rows=307 width=84) (actual time=0.032..0.032 rows=1 loops=92,561)

  • Group Key: t236_opp_line_item_fact_1.c801_opportunity_sid, t236_opp_line_item_fact_1.c805_opp_close_date, t236_opp_line_item_fact_1.c804_opp_ownerid, t236_opp_line_item_fact_1.c819_opp_stagename, t234_opportunityforecastcategorypicklistdim_1.correlated_app_order
46. 277.683 2,684.269 ↑ 61.4 5 92,561

Sort (cost=3,301.80..3,302.57 rows=307 width=40) (actual time=0.029..0.029 rows=5 loops=92,561)

  • Sort Key: t236_opp_line_item_fact_1.c805_opp_close_date, t236_opp_line_item_fact_1.c804_opp_ownerid, t236_opp_line_item_fact_1.c819_opp_stagename, t234_opportunityforecastcategorypicklistdim_1.correlated_app_order
  • Sort Method: quicksort Memory: 25kB
47. 462.805 2,406.586 ↑ 61.4 5 92,561

Nested Loop Left Join (cost=0.56..3,289.12 rows=307 width=40) (actual time=0.010..0.026 rows=5 loops=92,561)

  • Join Filter: (t236_opp_line_item_fact_1.c820_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim_1.sid)
  • Rows Removed by Join Filter: 21
48. 235.307 1,943.781 ↑ 61.4 5 92,561

Nested Loop Left Join (cost=0.56..3,265.03 rows=307 width=40) (actual time=0.009..0.021 rows=5 loops=92,561)

49. 740.488 740.488 ↑ 61.4 5 92,561

Index Scan using t236_opp_line_item_fact_561_timestamp on t236_opp_line_item_fact t236_opp_line_item_fact_1 (cost=0.56..1,170.18 rows=307 width=36) (actual time=0.006..0.008 rows=5 loops=92,561)

  • Index Cond: ((t236_opp_line_item_fact.c801_opportunity_sid = c801_opportunity_sid) AND (start_stamp <= '1549007999999'::bigint) AND (end_stamp > '1549007999999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
50. 0.000 967.986 ↑ 2.0 1 483,993

Append (cost=0.00..6.80 rows=2 width=16) (actual time=0.002..0.002 rows=1 loops=483,993)

51. 0.000 0.000 ↓ 0.0 0 483,993

Seq Scan on t716_clari_conversion_rate t716_clari_conversion_rate_1 (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=483,993)

  • Filter: ((to_iso_code = 'USD'::text) AND (t236_opp_line_item_fact_1.c821_opp_currency_code = sid) AND (t236_opp_line_item_fact_1.c805_opp_close_date = rate_date))
52. 967.986 967.986 ↑ 1.0 1 483,993

Index Scan using t716_clari_conversion_rate_usd_sidratedateunique on t716_clari_conversion_rate_usd t716_clari_conversion_rate_usd_1 (cost=0.29..6.80 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=483,993)

  • Index Cond: ((t236_opp_line_item_fact_1.c821_opp_currency_code = sid) AND (t236_opp_line_item_fact_1.c805_opp_close_date = rate_date))
  • Filter: (to_iso_code = 'USD'::text)
53. 0.000 0.000 ↑ 1.0 5 483,993

Materialize (cost=0.00..1.07 rows=5 width=8) (actual time=0.000..0.000 rows=5 loops=483,993)

54. 0.008 0.008 ↑ 1.0 5 1

Seq Scan on t234_opportunityforecastcategorypicklistdim t234_opportunityforecastcategorypicklistdim_1 (cost=0.00..1.05 rows=5 width=8) (actual time=0.005..0.008 rows=5 loops=1)

55.          

SubPlan (forNested Loop Left Join)

56. 1.017 1.017 ↑ 2.9 8,743 1

CTE Scan on ownercte (cost=0.00..501.84 rows=25,092 width=4) (actual time=0.001..1.017 rows=8,743 loops=1)

57. 0.071 0.071 ↑ 1.2 6 1

CTE Scan on picklist_0 cte0 (cost=0.00..0.14 rows=7 width=4) (actual time=0.023..0.071 rows=6 loops=1)

58. 0.023 0.023 ↑ 1.0 7 1

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

59.          

CTE bucketec

60. 92.457 3,778.941 ↑ 50.2 48,989 1

Nested Loop Left Join (cost=1,013,680.52..2,431,632,747.62 rows=2,459,601 width=164) (actual time=2,032.030..3,778.941 rows=48,989 loops=1)

  • Filter: ((oli174_ft1_1.c1 IS NULL) OR (oli174_ft1_1.c3 < 20181101) OR (oli174_ft1_1.c3 > 20190131) OR (NOT (hashed SubPlan 14)))
  • Rows Removed by Filter: 64559
61. 331.352 2,549.095 ↑ 18.5 113,548 1

GroupAggregate (cost=1,011,403.78..1,079,823.49 rows=2,105,222 width=76) (actual time=2,029.080..2,549.095 rows=113,548 loops=1)

  • Group Key: t236_opp_line_item_fact_2.c801_opportunity_sid, t236_opp_line_item_fact_2.c819_opp_stagename, t236_opp_line_item_fact_2.c820_opp_forecastcategory
62. 549.511 2,217.743 ↑ 2.7 785,203 1

Sort (cost=1,011,403.78..1,016,666.83 rows=2,105,222 width=32) (actual time=2,029.052..2,217.743 rows=785,203 loops=1)

  • Sort Key: t236_opp_line_item_fact_2.c801_opportunity_sid, t236_opp_line_item_fact_2.c819_opp_stagename, t236_opp_line_item_fact_2.c820_opp_forecastcategory
  • Sort Method: external merge Disk: 31904kB
63. 155.700 1,668.232 ↑ 2.7 785,203 1

Merge Right Join (cost=653,062.67..689,553.14 rows=2,105,222 width=32) (actual time=1,116.701..1,668.232 rows=785,203 loops=1)

  • Merge Cond: ((t716_clari_conversion_rate_2.sid = t236_opp_line_item_fact_2.c821_opp_currency_code) AND (t716_clari_conversion_rate_2.rate_date = t236_opp_line_item_fact_2.c805_opp_close_date))
64. 7.035 54.243 ↑ 1.0 105,342 1

Merge Append (cost=0.31..7,793.41 rows=105,520 width=16) (actual time=0.039..54.243 rows=105,342 loops=1)

  • Sort Key: t716_clari_conversion_rate_2.sid, t716_clari_conversion_rate_2.rate_date
65. 0.021 0.022 ↓ 0.0 0 1

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

  • Sort Key: t716_clari_conversion_rate_2.sid, t716_clari_conversion_rate_2.rate_date
  • Sort Method: quicksort Memory: 25kB
66. 0.001 0.001 ↓ 0.0 0 1

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

  • Filter: (to_iso_code = 'USD'::text)
67. 47.186 47.186 ↑ 1.0 105,342 1

Index Scan using t716_clari_conversion_rate_usd_sidratedateunique on t716_clari_conversion_rate_usd t716_clari_conversion_rate_usd_2 (cost=0.29..6,474.39 rows=105,519 width=16) (actual time=0.017..47.186 rows=105,342 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
68. 116.219 1,458.289 ↑ 2.7 785,203 1

Materialize (cost=653,062.36..663,588.47 rows=2,105,222 width=32) (actual time=1,115.514..1,458.289 rows=785,203 loops=1)

69. 575.008 1,342.070 ↑ 2.7 785,203 1

Sort (cost=653,062.36..658,325.42 rows=2,105,222 width=32) (actual time=1,115.512..1,342.070 rows=785,203 loops=1)

  • Sort Key: t236_opp_line_item_fact_2.c821_opp_currency_code, t236_opp_line_item_fact_2.c805_opp_close_date
  • Sort Method: external merge Disk: 31912kB
70. 158.287 767.062 ↑ 2.7 785,203 1

Nested Loop (cost=565.14..331,211.73 rows=2,105,222 width=32) (actual time=2.714..767.062 rows=785,203 loops=1)

71. 4.711 5.508 ↓ 43.7 8,743 1

HashAggregate (cost=564.57..566.57 rows=200 width=4) (actual time=2.688..5.508 rows=8,743 loops=1)

  • Group Key: ownercte_4.c1
72. 0.797 0.797 ↑ 2.9 8,743 1

CTE Scan on ownercte ownercte_4 (cost=0.00..501.84 rows=25,092 width=4) (actual time=0.001..0.797 rows=8,743 loops=1)

73. 603.267 603.267 ↑ 4.1 90 8,743

Index Scan using ix_t236_opp_line_item_fact_owner_closedate_timestamp on t236_opp_line_item_fact t236_opp_line_item_fact_2 (cost=0.56..1,649.52 rows=371 width=36) (actual time=0.009..0.069 rows=90 loops=8,743)

  • Index Cond: ((c804_opp_ownerid = ownercte_4.c1) AND (c805_opp_close_date >= 20181101) AND (c805_opp_close_date <= 20190131) AND (start_stamp <= '1549007999999'::bigint) AND (end_stamp > '1549007999999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 10
74. 0.000 1,135.480 ↑ 2.0 1 113,548

Subquery Scan on oli174_ft1_1 (cost=1,146.97..1,154.47 rows=2 width=16) (actual time=0.009..0.010 rows=1 loops=113,548)

  • Filter: (t236_opp_line_item_fact_2.c801_opportunity_sid = oli174_ft1_1.c1)
75. 113.548 1,135.480 ↑ 300.0 1 113,548

Group (cost=1,146.97..1,150.72 rows=300 width=16) (actual time=0.009..0.010 rows=1 loops=113,548)

  • Group Key: t236_opp_line_item_fact_3.c801_opportunity_sid, t236_opp_line_item_fact_3.c819_opp_stagename, t236_opp_line_item_fact_3.c805_opp_close_date, t236_opp_line_item_fact_3.c804_opp_ownerid
76. 227.096 1,021.932 ↑ 75.0 4 113,548

Sort (cost=1,146.97..1,147.72 rows=300 width=16) (actual time=0.009..0.009 rows=4 loops=113,548)

  • Sort Key: t236_opp_line_item_fact_3.c819_opp_stagename, t236_opp_line_item_fact_3.c805_opp_close_date, t236_opp_line_item_fact_3.c804_opp_ownerid
  • Sort Method: quicksort Memory: 25kB
77. 794.836 794.836 ↑ 75.0 4 113,548

Index Scan using t236_opp_line_item_fact_561_timestamp on t236_opp_line_item_fact t236_opp_line_item_fact_3 (cost=0.56..1,134.63 rows=300 width=16) (actual time=0.004..0.007 rows=4 loops=113,548)

  • Index Cond: ((t236_opp_line_item_fact_2.c801_opportunity_sid = c801_opportunity_sid) AND (start_stamp <= '1541055600000'::bigint) AND (end_stamp > '1541055600000'::bigint))
  • Filter: (NOT deleted)
78.          

SubPlan (forNested Loop Left Join)

79. 0.001 0.001 ↑ 1.1 13 1

CTE Scan on picklist_4 cte0_3 (cost=0.00..0.28 rows=14 width=4) (actual time=0.000..0.001 rows=13 loops=1)

80. 0.766 0.766 ↑ 2.9 8,743 1

CTE Scan on ownercte ownercte_2 (cost=0.00..501.84 rows=25,092 width=4) (actual time=0.001..0.766 rows=8,743 loops=1)

81. 0.001 0.001 ↑ 1.2 6 1

CTE Scan on picklist_0 cte0_4 (cost=0.00..0.14 rows=7 width=4) (actual time=0.001..0.001 rows=6 loops=1)

82. 0.003 0.003 ↑ 1.0 7 1

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

83. 1.138 1.138 ↑ 2.9 8,743 1

CTE Scan on ownercte ownercte_3 (cost=0.00..501.84 rows=25,092 width=4) (actual time=0.001..1.138 rows=8,743 loops=1)

84.          

CTE topsids

85. 0.019 10,462.959 ↑ 1.0 400 1

Limit (cost=94,191.95..94,192.95 rows=400 width=12) (actual time=10,462.891..10,462.959 rows=400 loops=1)

86. 38.089 10,462.940 ↑ 126.2 400 1

Sort (cost=94,191.95..94,318.20 rows=50,500 width=12) (actual time=10,462.891..10,462.940 rows=400 loops=1)

  • Sort Key: (unnest(cte0_6.c3)) DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 43kB
87. 20.390 10,424.851 ↓ 5.6 283,811 1

Append (cost=1.11..91,756.88 rows=50,500 width=12) (actual time=3,050.958..10,424.851 rows=283,811 loops=1)

88. 40.138 6,526.297 ↓ 7.3 145,065 1

Hash Join (cost=1.11..35,707.05 rows=19,800 width=12) (actual time=3,050.957..6,526.297 rows=145,065 loops=1)

  • Hash Cond: (cte0_6.c4 = t234_opportunityforecastcategorypicklistdim_2.sid)
89. 6,486.151 6,486.151 ↓ 2.1 16,863 1

CTE Scan on bucketac cte0_6 (cost=0.00..35,575.31 rows=7,906 width=68) (actual time=3,050.928..6,486.151 rows=16,863 loops=1)

  • Filter: (c5 = 'won'::text)
  • Rows Removed by Filter: 75698
90. 0.004 0.008 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=4) (actual time=0.008..0.008 rows=5 loops=1)

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

Seq Scan on t234_opportunityforecastcategorypicklistdim t234_opportunityforecastcategorypicklistdim_2 (cost=0.00..1.05 rows=5 width=4) (actual time=0.002..0.004 rows=5 loops=1)

92. 53.210 3,878.164 ↓ 4.5 138,746 1

Hash Join (cost=1.11..55,544.82 rows=30,700 width=12) (actual time=2,032.064..3,878.164 rows=138,746 loops=1)

  • Hash Cond: (cte0_7.c6 = t234_opportunityforecastcategorypicklistdim_3.sid)
93. 3,824.943 3,824.943 ↓ 2.6 31,413 1

CTE Scan on bucketec cte0_7 (cost=0.00..55,341.02 rows=12,298 width=68) (actual time=2,032.036..3,824.943 rows=31,413 loops=1)

  • Filter: (c5 = 'won'::text)
  • Rows Removed by Filter: 17576
94. 0.003 0.011 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=4) (actual time=0.011..0.011 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
95. 0.008 0.008 ↑ 1.0 5 1

Seq Scan on t234_opportunityforecastcategorypicklistdim t234_opportunityforecastcategorypicklistdim_3 (cost=0.00..1.05 rows=5 width=4) (actual time=0.006..0.008 rows=5 loops=1)

96. 10,463.022 10,463.022 ↑ 1.0 400 1

CTE Scan on topsids (cost=0.00..8.00 rows=400 width=4) (actual time=10,462.893..10,463.022 rows=400 loops=1)

97. 2.400 2.400 ↑ 1.0 1 400

Index Scan using t200_opportunitylineitemdim_sidendstampunique on t200_opportunitylineitemdim (cost=0.56..11.08 rows=1 width=23) (actual time=0.005..0.006 rows=1 loops=400)

  • Index Cond: ((sid = topsids.c1) AND (end_stamp > '1549007999999'::bigint))
  • Filter: ((NOT deleted) AND (start_stamp <= '1549007999999'::bigint))
  • Rows Removed by Filter: 0