explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8SZT

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 12,958.766 ↑ 58,837.1 44 1

Append (cost=9,383,659.00..9,500,637.39 rows=2,588,833 width=120) (actual time=6,834.860..12,958.766 rows=44 loops=1)

2.          

CTE picklist_0

3. 0.293 0.293 ↑ 1.0 6 1

Seq Scan on t233_opportunitystagenamepicklistdim (cost=0.00..46.54 rows=6 width=4) (actual time=0.009..0.293 rows=6 loops=1)

  • Filter: (correlated_value = ANY ('{"06 - Won, Deploy & Expand","6 - Won, Deploy & Expand","Closed - Won","06 - Won. Deploy & Expand",Won,"06 - Won, Deploy & Expand"}'::text[]))
  • Rows Removed by Filter: 1225
4.          

CTE picklist_1

5. 0.310 0.310 ↑ 1.0 7 1

Seq Scan on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_1 (cost=0.00..48.08 rows=7 width=4) (actual time=0.021..0.310 rows=7 loops=1)

  • Filter: (correlated_value = ANY ('{"HPE Not Pursued",Lost,Close:Duplicate,"HP Not Pursued",Duplicate,Error,"Closed - Lost"}'::text[]))
  • Rows Removed by Filter: 1224
6.          

CTE picklist_4

7. 0.480 0.480 ↑ 1.0 13 1

Seq Scan on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_2 (cost=0.00..57.31 rows=13 width=4) (actual time=0.011..0.480 rows=13 loops=1)

  • Filter: (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[]))
  • Rows Removed by Filter: 1218
8.          

CTE ownercte

9. 17.267 935.877 ↑ 3.0 8,167 1

HashAggregate (cost=430,553.01..430,797.38 rows=24,437 width=4) (actual time=934.841..935.877 rows=8,167 loops=1)

  • Group Key: distinctclosedateowner.ownerid
10. 60.013 918.610 ↑ 1.6 93,387 1

Hash Join (cost=396,855.55..430,178.64 rows=149,749 width=4) (actual time=788.559..918.610 rows=93,387 loops=1)

  • Hash Cond: (distinctclosedateowner.ownerid = t166_userrolehierarchy.sid)
11. 73.599 92.682 ↓ 1.3 196,760 1

Bitmap Heap Scan on distinctclosedateowner (cost=3,179.36..32,515.59 rows=149,749 width=4) (actual time=22.111..92.682 rows=196,760 loops=1)

  • Recheck Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
  • Heap Blocks: exact=17913
12. 19.083 19.083 ↓ 1.3 196,760 1

Bitmap Index Scan on ix_closedate_distinctclosedateowner (cost=0.00..3,141.92 rows=149,749 width=0) (actual time=19.083..19.083 rows=196,760 loops=1)

  • Index Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
13. 16.708 765.915 ↑ 1.6 120,769 1

Hash (cost=390,467.94..390,467.94 rows=195,540 width=4) (actual time=765.915..765.915 rows=120,769 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2089kB
14. 15.539 749.207 ↑ 1.6 120,769 1

Unique (cost=384,052.60..390,467.94 rows=195,540 width=4) (actual time=718.119..749.207 rows=120,769 loops=1)

15. 69.206 733.668 ↑ 9.9 129,697 1

Sort (cost=384,052.60..387,260.27 rows=1,283,067 width=4) (actual time=718.117..733.668 rows=129,697 loops=1)

  • Sort Key: t166_userrolehierarchy.sid
  • Sort Method: external merge Disk: 1768kB
16. 204.844 664.462 ↑ 9.9 129,697 1

Hash Join (cost=59,662.38..236,335.98 rows=1,283,067 width=4) (actual time=150.129..664.462 rows=129,697 loops=1)

  • Hash Cond: (t166_userrolehierarchy.c662_user_role_id = t67_userroledim.sid)
17. 317.441 447.737 ↓ 1.0 1,321,874 1

Bitmap Heap Scan on t166_userrolehierarchy (cost=37,264.57..194,593.57 rows=1,283,067 width=8) (actual time=137.540..447.737 rows=1,321,874 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=39327
18. 130.296 130.296 ↓ 1.0 1,324,435 1

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..36,943.80 rows=1,283,067 width=0) (actual time=130.296..130.296 rows=1,324,435 loops=1)

  • Index Cond: ((c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
19. 1.412 11.881 ↑ 9.3 8,370 1

Hash (cost=21,117.18..21,117.18 rows=78,051 width=4) (actual time=11.881..11.881 rows=8,370 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 1177kB
20. 6.083 10.469 ↑ 9.3 8,370 1

Bitmap Heap Scan on t67_userroledim (cost=4,261.99..21,117.18 rows=78,051 width=4) (actual time=4.823..10.469 rows=8,370 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: 5587
  • Heap Blocks: exact=2140
21. 4.386 4.386 ↑ 5.6 13,978 1

Bitmap Index Scan on ix_t67_userroledim_sid_end_start_timestamp_partial (cost=0.00..4,242.48 rows=78,925 width=0) (actual time=4.386..4.386 rows=13,978 loops=1)

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

CTE bucketac

23. 135.119 7,092.557 ↑ 58,417.1 19 1

GroupAggregate (cost=4,065,453.55..4,118,174.94 rows=1,109,924 width=92) (actual time=6,834.828..7,092.557 rows=19 loops=1)

  • Group Key: t236_opp_line_item_fact.c820_opp_forecastcategory, (CASE WHEN (t236_opp_line_item_fact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t236_opp_line_item_fact_1.c805_opp_close_date < 20181101) OR (t236_opp_line_item_fact_1.c805_opp_close_date > 20190131)) THEN 'slipped'::text WHEN (hashed SubPlan 5) THEN CASE WHEN (hashed SubPlan 6) THEN 'won'::text WHEN (hashed SubPlan 7) THEN 'lost'::text ELSE CASE WHEN (t234_opportunityforecastcategorypicklistdim_2.correlated_app_order = t234_opportunityforecastcategorypicklistdim_3.correlated_app_order) THEN 'unchanged'::text WHEN (t234_opportunityforecastcategorypicklistdim_2.correlated_app_order > t234_opportunityforecastcategorypicklistdim_3.correlated_app_order) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
24. 331.242 6,957.438 ↑ 2.7 418,251 1

Sort (cost=4,064,903.43..4,067,678.24 rows=1,109,924 width=72) (actual time=6,834.445..6,957.438 rows=418,251 loops=1)

  • Sort Key: t236_opp_line_item_fact.c820_opp_forecastcategory, (CASE WHEN (t236_opp_line_item_fact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t236_opp_line_item_fact_1.c805_opp_close_date < 20181101) OR (t236_opp_line_item_fact_1.c805_opp_close_date > 20190131)) THEN 'slipped'::text WHEN (hashed SubPlan 5) THEN CASE WHEN (hashed SubPlan 6) THEN 'won'::text WHEN (hashed SubPlan 7) THEN 'lost'::text ELSE CASE WHEN (t234_opportunityforecastcategorypicklistdim_2.correlated_app_order = t234_opportunityforecastcategorypicklistdim_3.correlated_app_order) THEN 'unchanged'::text WHEN (t234_opportunityforecastcategorypicklistdim_2.correlated_app_order > t234_opportunityforecastcategorypicklistdim_3.correlated_app_order) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
  • Sort Method: external merge Disk: 26568kB
25. 246.831 6,626.196 ↑ 2.7 418,251 1

Hash Left Join (cost=3,798,271.46..3,862,406.80 rows=1,109,924 width=72) (actual time=6,093.738..6,626.196 rows=418,251 loops=1)

  • Hash Cond: (t236_opp_line_item_fact.c820_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim_2.sid)
26. 96.531 6,378.039 ↑ 2.7 418,251 1

Merge Right Join (cost=3,797,701.66..3,827,644.31 rows=1,109,924 width=60) (actual time=6,093.707..6,378.039 rows=418,251 loops=1)

  • Merge Cond: ((cur177_t1_r1.rate_date = t236_opp_line_item_fact_1.c805_opp_close_date) AND (cur177_t1_r1.sid = t236_opp_line_item_fact_1.c821_opp_currency_code))
27. 6.541 62.867 ↑ 1.0 100,413 1

Merge Append (cost=0.31..8,292.46 rows=101,614 width=16) (actual time=0.019..62.867 rows=100,413 loops=1)

  • Sort Key: cur177_t1_r1.rate_date, cur177_t1_r1.sid
28. 0.003 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: cur177_t1_r1.rate_date, cur177_t1_r1.sid
  • Sort Method: quicksort Memory: 25kB
29. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on clari_conversion_rate cur177_t1_r1 (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)
30. 56.322 56.322 ↑ 1.0 100,413 1

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur177_t1_r1_1 (cost=0.29..7,022.26 rows=101,613 width=16) (actual time=0.014..56.322 rows=100,413 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
31. 56.583 6,218.641 ↑ 2.2 418,251 1

Materialize (cost=3,797,089.32..3,801,750.90 rows=932,317 width=56) (actual time=6,054.149..6,218.641 rows=418,251 loops=1)

32. 310.317 6,162.058 ↑ 2.2 418,251 1

Sort (cost=3,797,089.32..3,799,420.11 rows=932,317 width=56) (actual time=6,054.148..6,162.058 rows=418,251 loops=1)

  • Sort Key: t236_opp_line_item_fact_1.c805_opp_close_date, t236_opp_line_item_fact_1.c821_opp_currency_code
  • Sort Method: external merge Disk: 30192kB
33. 90.610 5,851.741 ↑ 2.2 418,251 1

Merge Right Join (cost=3,614,451.38..3,640,912.94 rows=932,317 width=56) (actual time=5,605.173..5,851.741 rows=418,251 loops=1)

  • Merge Cond: ((cur177_t0_r0.rate_date = t236_opp_line_item_fact.c805_opp_close_date) AND (cur177_t0_r0.sid = t236_opp_line_item_fact.c821_opp_currency_code))
34. 4.664 43.309 ↑ 1.4 71,860 1

Merge Append (cost=0.31..8,292.46 rows=101,614 width=16) (actual time=0.009..43.309 rows=71,860 loops=1)

  • Sort Key: cur177_t0_r0.rate_date, cur177_t0_r0.sid
35. 0.004 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: cur177_t0_r0.rate_date, cur177_t0_r0.sid
  • Sort Method: quicksort Memory: 25kB
36. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on clari_conversion_rate cur177_t0_r0 (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
37. 38.641 38.641 ↑ 1.4 71,860 1

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur177_t0_r0_1 (cost=0.29..7,022.26 rows=101,613 width=16) (actual time=0.005..38.641 rows=71,860 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
38. 54.677 5,717.822 ↑ 1.9 418,251 1

Materialize (cost=3,613,839.03..3,617,754.68 rows=783,130 width=56) (actual time=5,560.127..5,717.822 rows=418,251 loops=1)

39. 346.150 5,663.145 ↑ 1.9 418,251 1

Sort (cost=3,613,839.03..3,615,796.86 rows=783,130 width=56) (actual time=5,560.122..5,663.145 rows=418,251 loops=1)

  • Sort Key: t236_opp_line_item_fact.c805_opp_close_date, t236_opp_line_item_fact.c821_opp_currency_code
  • Sort Method: external merge Disk: 30200kB
40. 107.553 5,316.995 ↑ 1.9 418,251 1

Hash Left Join (cost=888.75..3,483,638.94 rows=783,130 width=56) (actual time=940.096..5,316.995 rows=418,251 loops=1)

  • Hash Cond: (t236_opp_line_item_fact_1.c820_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim_3.sid)
41. 147.526 5,209.434 ↑ 1.9 418,251 1

Nested Loop Left Join (cost=869.07..3,473,198.68 rows=783,130 width=56) (actual time=940.075..5,209.434 rows=418,251 loops=1)

42. 63.992 3,807.155 ↑ 1.9 418,251 1

Nested Loop (cost=868.51..860,397.16 rows=783,130 width=24) (actual time=940.061..3,807.155 rows=418,251 loops=1)

43. 4.514 941.882 ↓ 40.8 8,167 1

HashAggregate (cost=549.83..551.83 rows=200 width=4) (actual time=939.311..941.882 rows=8,167 loops=1)

  • Group Key: ownercte_1.c1
44. 937.368 937.368 ↑ 3.0 8,167 1

CTE Scan on ownercte ownercte_1 (cost=0.00..488.74 rows=24,437 width=4) (actual time=934.844..937.368 rows=8,167 loops=1)

45. 1,869.759 2,801.281 ↑ 2.7 51 8,167

Bitmap Heap Scan on t236_opp_line_item_fact (cost=318.68..4,297.87 rows=136 width=28) (actual time=0.196..0.343 rows=51 loops=8,167)

  • Recheck Cond: ((c804_opp_ownerid = ownercte_1.c1) AND (start_stamp <= '1541055600000'::bigint) AND (end_stamp > '1541055600000'::bigint))
  • Filter: ((NOT deleted) AND (c805_opp_close_date >= 20181101) AND (c805_opp_close_date <= 20190131) AND (NOT (hashed SubPlan 8)))
  • Rows Removed by Filter: 328
  • Heap Blocks: exact=811472
46. 931.038 931.038 ↑ 2.9 383 8,167

Bitmap Index Scan on t236_opp_line_item_fact_564_timestamp (cost=0.00..318.35 rows=1,114 width=0) (actual time=0.114..0.114 rows=383 loops=8,167)

  • Index Cond: ((c804_opp_ownerid = ownercte_1.c1) AND (start_stamp <= '1541055600000'::bigint) AND (end_stamp > '1541055600000'::bigint))
47.          

SubPlan (forBitmap Heap Scan)

48. 0.484 0.484 ↑ 1.0 13 1

CTE Scan on picklist_4 cte0_4 (cost=0.00..0.26 rows=13 width=4) (actual time=0.012..0.484 rows=13 loops=1)

49. 1,254.753 1,254.753 ↑ 1.0 1 418,251

Index Scan using ix_t236_opp_line_item_fact_sid_timestamp on t236_opp_line_item_fact t236_opp_line_item_fact_1 (cost=0.56..3.33 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=418,251)

  • Index Cond: ((t236_opp_line_item_fact.sid = sid) AND (t236_opp_line_item_fact.sid = sid) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
  • Filter: (NOT deleted)
50. 0.003 0.008 ↑ 86.0 5 1

Hash (cost=14.30..14.30 rows=430 width=8) (actual time=0.008..0.008 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
51. 0.005 0.005 ↑ 86.0 5 1

Seq Scan on t234_opportunityforecastcategorypicklistdim t234_opportunityforecastcategorypicklistdim_3 (cost=0.00..14.30 rows=430 width=8) (actual time=0.004..0.005 rows=5 loops=1)

52. 0.003 0.010 ↑ 86.0 5 1

Hash (cost=14.30..14.30 rows=430 width=8) (actual time=0.010..0.010 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
53. 0.007 0.007 ↑ 86.0 5 1

Seq Scan on t234_opportunityforecastcategorypicklistdim t234_opportunityforecastcategorypicklistdim_2 (cost=0.00..14.30 rows=430 width=8) (actual time=0.006..0.007 rows=5 loops=1)

54.          

SubPlan (forHash Left Join)

55. 0.710 0.710 ↑ 3.0 8,167 1

CTE Scan on ownercte (cost=0.00..488.74 rows=24,437 width=4) (actual time=0.001..0.710 rows=8,167 loops=1)

56. 0.295 0.295 ↑ 1.0 6 1

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

57. 0.311 0.311 ↑ 1.0 7 1

CTE Scan on picklist_1 cte0_3 (cost=0.00..0.14 rows=7 width=4) (actual time=0.022..0.311 rows=7 loops=1)

58.          

CTE bucketec

59. 21.449 5,866.054 ↑ 59,156.4 25 1

GroupAggregate (cost=4,771,661.43..4,834,515.06 rows=1,478,909 width=116) (actual time=5,830.314..5,866.054 rows=25 loops=1)

  • Group Key: (CASE WHEN (t236_opp_line_item_fact_3.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 10)) THEN CASE WHEN (t236_opp_line_item_fact_3.c805_opp_close_date > 20190131) THEN 'pulledInBucket'::text WHEN (t236_opp_line_item_fact_3.c805_opp_close_date < 20181101) THEN 'pushedInBucket'::text WHEN (hashed SubPlan 11) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END ELSE 'otherBucket'::text END), (CASE WHEN (hashed SubPlan 12) THEN 'won'::text WHEN (hashed SubPlan 13) THEN 'lost'::text ELSE 'newOpen'::text END), t236_opp_line_item_fact_2.c820_opp_forecastcategory
60. 149.518 5,844.605 ↑ 18.9 78,264 1

Sort (cost=4,771,111.01..4,774,808.28 rows=1,478,909 width=88) (actual time=5,830.294..5,844.605 rows=78,264 loops=1)

  • Sort Key: (CASE WHEN (t236_opp_line_item_fact_3.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 10)) THEN CASE WHEN (t236_opp_line_item_fact_3.c805_opp_close_date > 20190131) THEN 'pulledInBucket'::text WHEN (t236_opp_line_item_fact_3.c805_opp_close_date < 20181101) THEN 'pushedInBucket'::text WHEN (hashed SubPlan 11) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END ELSE 'otherBucket'::text END), (CASE WHEN (hashed SubPlan 12) THEN 'won'::text WHEN (hashed SubPlan 13) THEN 'lost'::text ELSE 'newOpen'::text END), t236_opp_line_item_fact_2.c820_opp_forecastcategory
  • Sort Method: external merge Disk: 4296kB
61. 40.114 5,695.087 ↑ 18.9 78,264 1

Merge Right Join (cost=4,418,653.29..4,478,011.66 rows=1,478,909 width=88) (actual time=5,638.017..5,695.087 rows=78,264 loops=1)

  • Merge Cond: ((cur177_t0_r0_2.rate_date = t236_opp_line_item_fact_2.c805_opp_close_date) AND (cur177_t0_r0_2.sid = t236_opp_line_item_fact_2.c821_opp_currency_code))
62. 4.602 46.465 ↑ 1.4 71,855 1

Merge Append (cost=0.31..8,292.46 rows=101,614 width=16) (actual time=0.024..46.465 rows=71,855 loops=1)

  • Sort Key: cur177_t0_r0_2.rate_date, cur177_t0_r0_2.sid
63. 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: cur177_t0_r0_2.rate_date, cur177_t0_r0_2.sid
  • Sort Method: quicksort Memory: 25kB
64. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on clari_conversion_rate cur177_t0_r0_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)
65. 41.857 41.857 ↑ 1.4 71,855 1

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur177_t0_r0_3 (cost=0.29..7,022.26 rows=101,613 width=16) (actual time=0.017..41.857 rows=71,855 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
66. 7.159 5,607.777 ↑ 15.9 78,264 1

Materialize (cost=4,417,490.53..4,423,701.82 rows=1,242,258 width=44) (actual time=5,589.020..5,607.777 rows=78,264 loops=1)

67. 88.727 5,600.618 ↑ 15.9 78,264 1

Sort (cost=4,417,490.53..4,420,596.17 rows=1,242,258 width=44) (actual time=5,589.018..5,600.618 rows=78,264 loops=1)

  • Sort Key: t236_opp_line_item_fact_2.c805_opp_close_date, t236_opp_line_item_fact_2.c821_opp_currency_code
  • Sort Method: external merge Disk: 3960kB
68. 240.328 5,511.891 ↑ 15.9 78,264 1

Nested Loop Left Join (cost=1,444.88..4,215,312.86 rows=1,242,258 width=44) (actual time=5.199..5,511.891 rows=78,264 loops=1)

  • Filter: ((t236_opp_line_item_fact_3.sid IS NULL) OR (t236_opp_line_item_fact_3.c805_opp_close_date < 20181101) OR (t236_opp_line_item_fact_3.c805_opp_close_date > 20190131) OR (NOT (hashed SubPlan 14)))
  • Rows Removed by Filter: 584449
69. 133.707 3,282.838 ↑ 2.3 662,713 1

Nested Loop (cost=894.48..851,295.42 rows=1,536,778 width=28) (actual time=2.656..3,282.838 rows=662,713 loops=1)

70. 4.217 4.836 ↓ 40.8 8,167 1

HashAggregate (cost=549.83..551.83 rows=200 width=4) (actual time=2.390..4.836 rows=8,167 loops=1)

  • Group Key: ownercte_4.c1
71. 0.619 0.619 ↑ 3.0 8,167 1

CTE Scan on ownercte ownercte_4 (cost=0.00..488.74 rows=24,437 width=4) (actual time=0.001..0.619 rows=8,167 loops=1)

72. 2,058.084 3,144.295 ↑ 3.3 81 8,167

Bitmap Heap Scan on t236_opp_line_item_fact t236_opp_line_item_fact_2 (cost=344.65..4,251.05 rows=267 width=32) (actual time=0.213..0.385 rows=81 loops=8,167)

  • Recheck Cond: ((c804_opp_ownerid = ownercte_4.c1) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
  • Filter: ((NOT deleted) AND (c805_opp_close_date >= 20181101) AND (c805_opp_close_date <= 20190131))
  • Rows Removed by Filter: 333
  • Heap Blocks: exact=873963
73. 1,086.211 1,086.211 ↑ 2.6 419 8,167

Bitmap Index Scan on t236_opp_line_item_fact_564_timestamp (cost=0.00..344.59 rows=1,093 width=0) (actual time=0.133..0.133 rows=419 loops=8,167)

  • Index Cond: ((c804_opp_ownerid = ownercte_4.c1) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
74. 1,988.139 1,988.139 ↑ 1.0 1 662,713

Index Scan using ix_t236_opp_line_item_fact_sid_timestamp on t236_opp_line_item_fact t236_opp_line_item_fact_3 (cost=0.56..2.17 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=662,713)

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

SubPlan (forNested Loop Left Join)

76. 0.586 0.586 ↑ 3.0 8,167 1

CTE Scan on ownercte ownercte_3 (cost=0.00..488.74 rows=24,437 width=4) (actual time=0.001..0.586 rows=8,167 loops=1)

77.          

SubPlan (forMerge Right Join)

78. 0.002 0.002 ↑ 1.0 13 1

CTE Scan on picklist_4 cte0_5 (cost=0.00..0.26 rows=13 width=4) (actual time=0.001..0.002 rows=13 loops=1)

79. 0.724 0.724 ↑ 3.0 8,167 1

CTE Scan on ownercte ownercte_2 (cost=0.00..488.74 rows=24,437 width=4) (actual time=0.001..0.724 rows=8,167 loops=1)

80. 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.000..0.002 rows=6 loops=1)

81. 0.003 0.003 ↑ 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.003 rows=7 loops=1)

82. 0.028 7,092.618 ↑ 58,417.1 19 1

Hash Join (cost=19.68..37,479.61 rows=1,109,924 width=120) (actual time=6,834.860..7,092.618 rows=19 loops=1)

  • Hash Cond: (cte0.c4 = t234_opportunityforecastcategorypicklistdim.sid)
83. 7,092.575 7,092.575 ↑ 58,417.1 19 1

CTE Scan on bucketac cte0 (cost=0.00..22,198.48 rows=1,109,924 width=92) (actual time=6,834.832..7,092.575 rows=19 loops=1)

84. 0.006 0.015 ↑ 86.0 5 1

Hash (cost=14.30..14.30 rows=430 width=36) (actual time=0.015..0.015 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
85. 0.009 0.009 ↑ 86.0 5 1

Seq Scan on t234_opportunityforecastcategorypicklistdim (cost=0.00..14.30 rows=430 width=36) (actual time=0.007..0.009 rows=5 loops=1)

86. 0.016 5,866.130 ↑ 59,156.4 25 1

Subquery Scan on *SELECT* 2 (cost=19.68..68,419.22 rows=1,478,909 width=120) (actual time=5,830.353..5,866.130 rows=25 loops=1)

87. 0.030 5,866.114 ↑ 59,156.4 25 1

Hash Join (cost=19.68..49,932.85 rows=1,478,909 width=144) (actual time=5,830.344..5,866.114 rows=25 loops=1)

  • Hash Cond: (cte0_1.c6 = t234_opportunityforecastcategorypicklistdim_1.sid)
88. 5,866.069 5,866.069 ↑ 59,156.4 25 1

CTE Scan on bucketec cte0_1 (cost=0.00..29,578.18 rows=1,478,909 width=116) (actual time=5,830.316..5,866.069 rows=25 loops=1)

89. 0.007 0.015 ↑ 86.0 5 1

Hash (cost=14.30..14.30 rows=430 width=36) (actual time=0.015..0.015 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
90. 0.008 0.008 ↑ 86.0 5 1

Seq Scan on t234_opportunityforecastcategorypicklistdim t234_opportunityforecastcategorypicklistdim_1 (cost=0.00..14.30 rows=430 width=36) (actual time=0.007..0.008 rows=5 loops=1)