explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HSmq

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 12,738.965 ↑ 56,075.6 44 1

Append (cost=9,178,403.43..9,289,899.74 rows=2,467,327 width=120) (actual time=6,945.569..12,738.965 rows=44 loops=1)

2.          

CTE picklist_0

3. 0.314 0.314 ↑ 1.0 6 1

Seq Scan on t233_opportunitystagenamepicklistdim (cost=0.00..46.54 rows=6 width=4) (actual time=0.011..0.314 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.277 0.277 ↑ 1.0 7 1

Seq Scan on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_1 (cost=0.00..48.08 rows=7 width=4) (actual time=0.004..0.277 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.455 0.455 ↑ 1.0 13 1

Seq Scan on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_2 (cost=0.00..57.31 rows=13 width=4) (actual time=0.010..0.455 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. 15.782 1,110.921 ↑ 3.0 8,167 1

HashAggregate (cost=271,327.18..271,573.23 rows=24,605 width=4) (actual time=1,109.970..1,110.921 rows=8,167 loops=1)

  • Group Key: distinctclosedateowner.ownerid
10.          

CTE owners

11. 202.578 674.733 ↑ 9.9 129,854 1

Hash Join (cost=59,737.62..236,615.96 rows=1,284,554 width=4) (actual time=155.980..674.733 rows=129,854 loops=1)

  • Hash Cond: (t166_userrolehierarchy.c662_user_role_id = t67_userroledim.sid)
12. 324.333 459.418 ↓ 1.0 1,322,770 1

Bitmap Heap Scan on t166_userrolehierarchy (cost=37,339.80..194,851.11 rows=1,284,554 width=8) (actual time=142.517..459.418 rows=1,322,770 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=39468
13. 135.085 135.085 ↓ 1.0 1,326,450 1

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..37,018.66 rows=1,284,554 width=0) (actual time=135.085..135.085 rows=1,326,450 loops=1)

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

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

  • Buckets: 131072 Batches: 2 Memory Usage: 1177kB
15. 6.823 11.313 ↑ 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.907..11.313 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: 5666
  • Heap Blocks: exact=2151
16. 4.490 4.490 ↑ 5.6 14,066 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.490..4.490 rows=14,066 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
17. 80.053 1,095.139 ↓ 1.2 93,387 1

Nested Loop (cost=28,902.90..34,511.13 rows=80,033 width=4) (actual time=745.415..1,095.139 rows=93,387 loops=1)

18. 66.382 773.320 ↓ 604.4 120,883 1

HashAggregate (cost=28,902.47..28,904.47 rows=200 width=4) (actual time=745.363..773.320 rows=120,883 loops=1)

  • Group Key: owners.c1
19. 706.938 706.938 ↑ 9.9 129,854 1

CTE Scan on owners (cost=0.00..25,691.08 rows=1,284,554 width=4) (actual time=155.982..706.938 rows=129,854 loops=1)

20. 241.766 241.766 ↑ 6.0 1 120,883

Index Only Scan using ix_distinctclosedateowner_owner_closedate on distinctclosedateowner (cost=0.43..27.97 rows=6 width=4) (actual time=0.002..0.002 rows=1 loops=120,883)

  • Index Cond: ((ownerid = owners.c1) AND (closedate <= 20190131) AND (closedate >= 20181101))
  • Heap Fetches: 93387
21.          

CTE bucketac

22. 139.489 7,206.217 ↑ 55,520.9 19 1

GroupAggregate (cost=4,032,939.42..4,083,047.08 rows=1,054,898 width=92) (actual time=6,945.532..7,206.217 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 6) THEN CASE WHEN (hashed SubPlan 7) THEN 'won'::text WHEN (hashed SubPlan 8) 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)
23. 330.418 7,066.728 ↑ 2.5 418,251 1

Sort (cost=4,032,385.52..4,035,022.76 rows=1,054,898 width=72) (actual time=6,945.165..7,066.728 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 6) THEN CASE WHEN (hashed SubPlan 7) THEN 'won'::text WHEN (hashed SubPlan 8) 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
24. 256.587 6,736.310 ↑ 2.5 418,251 1

Hash Left Join (cost=3,779,059.55..3,840,308.98 rows=1,054,898 width=72) (actual time=6,186.596..6,736.310 rows=418,251 loops=1)

  • Hash Cond: (t236_opp_line_item_fact.c820_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim_2.sid)
25. 98.333 6,478.413 ↑ 2.5 418,251 1

Merge Right Join (cost=3,778,485.97..3,807,220.62 rows=1,054,898 width=60) (actual time=6,186.564..6,478.413 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))
26. 6.501 63.810 ↑ 1.0 100,545 1

Merge Append (cost=0.31..8,299.20 rows=101,706 width=16) (actual time=0.019..63.810 rows=100,545 loops=1)

  • Sort Key: cur177_t1_r1.rate_date, cur177_t1_r1.sid
27. 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_t1_r1.rate_date, cur177_t1_r1.sid
  • Sort Method: quicksort Memory: 25kB
28. 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)
29. 57.303 57.303 ↑ 1.0 100,545 1

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur177_t1_r1_1 (cost=0.29..7,027.85 rows=101,705 width=16) (actual time=0.013..57.303 rows=100,545 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
30. 62.191 6,316.270 ↑ 2.1 418,251 1

Materialize (cost=3,777,721.36..3,782,183.00 rows=892,328 width=56) (actual time=6,145.379..6,316.270 rows=418,251 loops=1)

31. 312.328 6,254.079 ↑ 2.1 418,251 1

Sort (cost=3,777,721.36..3,779,952.18 rows=892,328 width=56) (actual time=6,145.378..6,254.079 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: 30200kB
32. 89.884 5,941.751 ↑ 2.1 418,251 1

Merge Right Join (cost=3,602,976.28..3,628,522.16 rows=892,328 width=56) (actual time=5,697.262..5,941.751 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))
33. 4.753 47.240 ↑ 1.4 71,874 1

Merge Append (cost=0.31..8,299.20 rows=101,706 width=16) (actual time=0.011..47.240 rows=71,874 loops=1)

  • Sort Key: cur177_t0_r0.rate_date, cur177_t0_r0.sid
34. 0.003 0.005 ↓ 0.0 0 1

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

  • Sort Key: cur177_t0_r0.rate_date, cur177_t0_r0.sid
  • Sort Method: quicksort Memory: 25kB
35. 0.002 0.002 ↓ 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.002..0.002 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
36. 42.482 42.482 ↑ 1.4 71,874 1

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur177_t0_r0_1 (cost=0.29..7,027.85 rows=101,705 width=16) (actual time=0.006..42.482 rows=71,874 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
37. 53.795 5,804.627 ↑ 1.8 418,251 1

Materialize (cost=3,602,211.66..3,605,985.72 rows=754,811 width=56) (actual time=5,648.647..5,804.627 rows=418,251 loops=1)

38. 346.714 5,750.832 ↑ 1.8 418,251 1

Sort (cost=3,602,211.66..3,604,098.69 rows=754,811 width=56) (actual time=5,648.644..5,750.832 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
39. 103.432 5,404.118 ↑ 1.8 418,251 1

Hash Left Join (cost=890.07..3,476,916.38 rows=754,811 width=56) (actual time=1,115.128..5,404.118 rows=418,251 loops=1)

  • Hash Cond: (t236_opp_line_item_fact_1.c820_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim_3.sid)
40. 166.012 5,300.678 ↑ 1.8 418,251 1

Nested Loop Left Join (cost=870.39..3,466,840.62 rows=754,811 width=56) (actual time=1,115.091..5,300.678 rows=418,251 loops=1)

41. 59.777 3,879.913 ↑ 1.8 418,251 1

Nested Loop (cost=869.83..826,406.28 rows=754,811 width=24) (actual time=1,115.078..3,879.913 rows=418,251 loops=1)

42. 4.399 1,116.859 ↓ 40.8 8,167 1

HashAggregate (cost=553.61..555.61 rows=200 width=4) (actual time=1,114.385..1,116.859 rows=8,167 loops=1)

  • Group Key: ownercte_1.c1
43. 1,112.460 1,112.460 ↑ 3.0 8,167 1

CTE Scan on ownercte ownercte_1 (cost=0.00..492.10 rows=24,605 width=4) (actual time=1,109.972..1,112.460 rows=8,167 loops=1)

44. 1,828.948 2,703.277 ↑ 2.5 51 8,167

Bitmap Heap Scan on t236_opp_line_item_fact (cost=316.22..4,127.95 rows=130 width=28) (actual time=0.188..0.331 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 9)))
  • Rows Removed by Filter: 328
  • Heap Blocks: exact=809053
45. 873.869 873.869 ↑ 2.8 382 8,167

Bitmap Index Scan on t236_opp_line_item_fact_564_timestamp (cost=0.00..315.89 rows=1,062 width=0) (actual time=0.107..0.107 rows=382 loops=8,167)

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

SubPlan (forBitmap Heap Scan)

47. 0.460 0.460 ↑ 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.460 rows=13 loops=1)

48. 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.49 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)
49. 0.004 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
50. 0.004 0.004 ↑ 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.004 rows=5 loops=1)

51. 0.004 0.011 ↑ 86.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
52. 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)

53.          

SubPlan (forHash Left Join)

54. 0.701 0.701 ↑ 3.0 8,167 1

CTE Scan on ownercte (cost=0.00..492.10 rows=24,605 width=4) (actual time=0.001..0.701 rows=8,167 loops=1)

55. 0.317 0.317 ↑ 1.0 6 1

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

56. 0.281 0.281 ↑ 1.0 7 1

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

57.          

CTE bucketec

58. 21.784 5,532.603 ↑ 56,497.2 25 1

GroupAggregate (cost=4,763,583.28..4,823,611.51 rows=1,412,429 width=116) (actual time=5,497.090..5,532.603 rows=25 loops=1)

  • Group Key: (CASE WHEN (t236_opp_line_item_fact_3.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 11)) 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 12) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END ELSE 'otherBucket'::text END), (CASE WHEN (hashed SubPlan 13) THEN 'won'::text WHEN (hashed SubPlan 14) THEN 'lost'::text ELSE 'newOpen'::text END), t236_opp_line_item_fact_2.c820_opp_forecastcategory
59. 150.213 5,510.819 ↑ 18.0 78,264 1

Sort (cost=4,763,029.08..4,766,560.15 rows=1,412,429 width=88) (actual time=5,497.070..5,510.819 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 11)) 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 12) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END ELSE 'otherBucket'::text END), (CASE WHEN (hashed SubPlan 13) THEN 'won'::text WHEN (hashed SubPlan 14) THEN 'lost'::text ELSE 'newOpen'::text END), t236_opp_line_item_fact_2.c820_opp_forecastcategory
  • Sort Method: external merge Disk: 4296kB
60. 41.017 5,360.606 ↑ 18.0 78,264 1

Merge Right Join (cost=4,426,640.31..4,483,574.25 rows=1,412,429 width=88) (actual time=5,302.636..5,360.606 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))
61. 4.672 45.354 ↑ 1.4 71,869 1

Merge Append (cost=0.31..8,299.20 rows=101,706 width=16) (actual time=0.043..45.354 rows=71,869 loops=1)

  • Sort Key: cur177_t0_r0_2.rate_date, cur177_t0_r0_2.sid
62. 0.005 0.007 ↓ 0.0 0 1

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

  • Sort Key: cur177_t0_r0_2.rate_date, cur177_t0_r0_2.sid
  • Sort Method: quicksort Memory: 25kB
63. 0.002 0.002 ↓ 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.002..0.002 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
64. 40.675 40.675 ↑ 1.4 71,869 1

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur177_t0_r0_3 (cost=0.29..7,027.85 rows=101,705 width=16) (actual time=0.035..40.675 rows=71,869 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
65. 7.404 5,273.600 ↑ 15.3 78,264 1

Materialize (cost=4,425,321.49..4,431,295.29 rows=1,194,759 width=44) (actual time=5,254.746..5,273.600 rows=78,264 loops=1)

66. 93.593 5,266.196 ↑ 15.3 78,264 1

Sort (cost=4,425,321.49..4,428,308.39 rows=1,194,759 width=44) (actual time=5,254.744..5,266.196 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
67. 174.774 5,172.603 ↑ 15.3 78,264 1

Nested Loop Left Join (cost=1,445.79..4,231,213.80 rows=1,194,759 width=44) (actual time=5.173..5,172.603 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 15)))
  • Rows Removed by Filter: 584449
68. 137.367 3,009.097 ↑ 2.2 662,713 1

Nested Loop (cost=891.61..817,247.40 rows=1,480,392 width=28) (actual time=2.755..3,009.097 rows=662,713 loops=1)

69. 4.538 5.113 ↓ 40.8 8,167 1

HashAggregate (cost=553.61..555.61 rows=200 width=4) (actual time=2.502..5.113 rows=8,167 loops=1)

  • Group Key: ownercte_4.c1
70. 0.575 0.575 ↑ 3.0 8,167 1

CTE Scan on ownercte ownercte_4 (cost=0.00..492.10 rows=24,605 width=4) (actual time=0.001..0.575 rows=8,167 loops=1)

71. 1,902.911 2,866.617 ↑ 3.1 81 8,167

Bitmap Heap Scan on t236_opp_line_item_fact t236_opp_line_item_fact_2 (cost=338.00..4,080.91 rows=255 width=32) (actual time=0.189..0.351 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=871553
72. 963.706 963.706 ↑ 2.5 418 8,167

Bitmap Index Scan on t236_opp_line_item_fact_564_timestamp (cost=0.00..337.94 rows=1,042 width=0) (actual time=0.118..0.118 rows=418 loops=8,167)

  • Index Cond: ((c804_opp_ownerid = ownercte_4.c1) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
73. 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.29 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)
74.          

SubPlan (forNested Loop Left Join)

75. 0.593 0.593 ↑ 3.0 8,167 1

CTE Scan on ownercte ownercte_3 (cost=0.00..492.10 rows=24,605 width=4) (actual time=0.001..0.593 rows=8,167 loops=1)

76.          

SubPlan (forMerge Right Join)

77. 0.001 0.001 ↑ 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.001 rows=13 loops=1)

78. 0.633 0.633 ↑ 3.0 8,167 1

CTE Scan on ownercte ownercte_2 (cost=0.00..492.10 rows=24,605 width=4) (actual time=0.000..0.633 rows=8,167 loops=1)

79. 0.001 0.001 ↑ 1.0 6 1

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

80. 0.000 0.000 ↑ 1.0 7 1

CTE Scan on picklist_1 cte0_7 (cost=0.00..0.14 rows=7 width=4) (actual time=0.000..0.000 rows=7 loops=1)

81. 0.028 7,206.281 ↑ 55,520.9 19 1

Hash Join (cost=19.68..35,622.48 rows=1,054,898 width=120) (actual time=6,945.568..7,206.281 rows=19 loops=1)

  • Hash Cond: (cte0.c4 = t234_opportunityforecastcategorypicklistdim.sid)
82. 7,206.235 7,206.235 ↑ 55,520.9 19 1

CTE Scan on bucketac cte0 (cost=0.00..21,097.96 rows=1,054,898 width=92) (actual time=6,945.535..7,206.235 rows=19 loops=1)

83. 0.010 0.018 ↑ 86.0 5 1

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

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

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

85. 0.022 5,532.678 ↑ 56,497.2 25 1

Subquery Scan on *SELECT* 2 (cost=19.68..65,344.52 rows=1,412,429 width=120) (actual time=5,497.129..5,532.678 rows=25 loops=1)

86. 0.025 5,532.656 ↑ 56,497.2 25 1

Hash Join (cost=19.68..47,689.15 rows=1,412,429 width=144) (actual time=5,497.121..5,532.656 rows=25 loops=1)

  • Hash Cond: (cte0_1.c6 = t234_opportunityforecastcategorypicklistdim_1.sid)
87. 5,532.617 5,532.617 ↑ 56,497.2 25 1

CTE Scan on bucketec cte0_1 (cost=0.00..28,248.58 rows=1,412,429 width=116) (actual time=5,497.092..5,532.617 rows=25 loops=1)

88. 0.005 0.014 ↑ 86.0 5 1

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

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

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