explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oGHe

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 26,663.953 ↑ 20,912.8 40 1

Append (cost=36,599,074.63..36,636,878.62 rows=836,513 width=120) (actual time=4,399.471..26,663.953 rows=40 loops=1)

2.          

CTE picklist_0

3. 0.297 0.297 ↑ 1.0 6 1

Seq Scan on t233_opportunitystagenamepicklistdim (cost=0.00..46.54 rows=6 width=4) (actual time=0.007..0.297 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.290 0.290 ↑ 1.0 7 1

Seq Scan on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_1 (cost=0.00..48.08 rows=7 width=4) (actual time=0.005..0.290 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.424 0.424 ↑ 1.0 13 1

Seq Scan on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_2 (cost=0.00..57.31 rows=13 width=4) (actual time=0.013..0.424 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 bucketac

9. 3.163 4,403.393 ↑ 19,040.3 19 1

GroupAggregate (cost=8,699,557.72..14,878,521.00 rows=361,766 width=92) (actual time=4,399.449..4,403.393 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 (alternatives: SubPlan 4 or 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)
10. 3.920 4,400.230 ↑ 35.2 10,270 1

Sort (cost=8,699,557.43..8,700,461.84 rows=361,766 width=72) (actual time=4,399.432..4,400.230 rows=10,270 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 (alternatives: SubPlan 4 or 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: quicksort Memory: 1718kB
11. 6.429 4,396.310 ↑ 35.2 10,270 1

Hash Left Join (cost=2,463,889.24..8,651,317.93 rows=361,766 width=72) (actual time=4,375.674..4,396.310 rows=10,270 loops=1)

  • Hash Cond: (t236_opp_line_item_fact.c820_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim_2.sid)
12. 7.492 4,387.336 ↑ 35.2 10,270 1

Merge Right Join (cost=2,463,869.28..2,478,380.27 rows=361,766 width=60) (actual time=4,375.636..4,387.336 rows=10,270 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))
13. 5.109 41.350 ↑ 1.3 79,564 1

Merge Append (cost=0.31..8,292.46 rows=101,614 width=16) (actual time=0.020..41.350 rows=79,564 loops=1)

  • Sort Key: cur177_t1_r1.rate_date, cur177_t1_r1.sid
14. 0.004 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_t1_r1.rate_date, cur177_t1_r1.sid
  • Sort Method: quicksort Memory: 25kB
15. 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)
16. 36.236 36.236 ↑ 1.3 79,564 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..36.236 rows=79,564 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
17. 1.003 4,338.494 ↑ 29.5 10,270 1

Materialize (cost=2,462,490.91..2,464,006.75 rows=303,167 width=56) (actual time=4,336.618..4,338.494 rows=10,270 loops=1)

18. 4.369 4,337.491 ↑ 29.5 10,270 1

Sort (cost=2,462,490.91..2,463,248.83 rows=303,167 width=56) (actual time=4,336.616..4,337.491 rows=10,270 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: quicksort Memory: 1829kB
19. 6.624 4,333.122 ↑ 29.5 10,270 1

Merge Right Join (cost=2,411,161.69..2,424,524.43 rows=303,167 width=56) (actual time=4,327.414..4,333.122 rows=10,270 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))
20. 4.875 42.257 ↑ 1.4 71,835 1

Merge Append (cost=0.31..8,292.46 rows=101,614 width=16) (actual time=0.010..42.257 rows=71,835 loops=1)

  • Sort Key: cur177_t0_r0.rate_date, cur177_t0_r0.sid
21. 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_t0_r0.rate_date, cur177_t0_r0.sid
  • Sort Method: quicksort Memory: 25kB
22. 0.001 0.001 ↓ 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.001..0.001 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
23. 37.378 37.378 ↑ 1.4 71,835 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.006..37.378 rows=71,835 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
24. 0.922 4,284.241 ↑ 24.7 10,270 1

Materialize (cost=2,409,783.33..2,411,053.63 rows=254,060 width=56) (actual time=4,282.440..4,284.241 rows=10,270 loops=1)

25. 6.314 4,283.319 ↑ 24.7 10,270 1

Sort (cost=2,409,783.33..2,410,418.48 rows=254,060 width=56) (actual time=4,282.439..4,283.319 rows=10,270 loops=1)

  • Sort Key: t236_opp_line_item_fact.c805_opp_close_date, t236_opp_line_item_fact.c821_opp_currency_code
  • Sort Method: quicksort Memory: 1829kB
26. 3.224 4,277.005 ↑ 24.7 10,270 1

Hash Left Join (cost=357,752.50..2,378,288.33 rows=254,060 width=56) (actual time=702.045..4,277.005 rows=10,270 loops=1)

  • Hash Cond: (t236_opp_line_item_fact_1.c820_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim_3.sid)
27. 5.902 4,273.771 ↑ 24.7 10,270 1

Nested Loop Left Join (cost=357,732.83..2,374,892.54 rows=254,060 width=56) (actual time=702.022..4,273.771 rows=10,270 loops=1)

28. 167.074 4,226.789 ↑ 24.7 10,270 1

Hash Join (cost=357,732.26..1,521,847.50 rows=254,060 width=24) (actual time=702.005..4,226.789 rows=10,270 loops=1)

  • Hash Cond: (t236_opp_line_item_fact.c804_opp_ownerid = t166_userrolehierarchy_2.sid)
29. 3,382.689 4,057.282 ↓ 1.3 2,156,650 1

Bitmap Heap Scan on t236_opp_line_item_fact (cost=339,845.99..1,497,603.51 rows=1,656,225 width=28) (actual time=684.647..4,057.282 rows=2,156,650 loops=1)

  • Recheck Cond: ((c805_opp_close_date >= 20181101) AND (c805_opp_close_date <= 20190131) AND (start_stamp <= '1541055600000'::bigint) AND (end_stamp > '1541055600000'::bigint))
  • Rows Removed by Index Recheck: 8318769
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 8)))
  • Rows Removed by Filter: 345694
  • Heap Blocks: exact=46589 lossy=293639
30. 674.144 674.144 ↑ 1.3 2,548,080 1

Bitmap Index Scan on ix_t236_opp_line_item_fact_closedate_timestamp (cost=0.00..339,431.64 rows=3,313,223 width=0) (actual time=674.144..674.144 rows=2,548,080 loops=1)

  • Index Cond: ((c805_opp_close_date >= 20181101) AND (c805_opp_close_date <= 20190131) AND (start_stamp <= '1541055600000'::bigint) AND (end_stamp > '1541055600000'::bigint))
31.          

SubPlan (forBitmap Heap Scan)

32. 0.449 0.449 ↑ 1.0 13 1

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

33. 0.102 2.433 ↑ 2.5 737 1

Hash (cost=17,863.14..17,863.14 rows=1,851 width=4) (actual time=2.433..2.433 rows=737 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 42kB
34. 0.255 2.331 ↑ 2.5 737 1

HashAggregate (cost=17,844.63..17,863.14 rows=1,851 width=4) (actual time=2.260..2.331 rows=737 loops=1)

  • Group Key: t166_userrolehierarchy_2.sid
35. 0.735 2.076 ↑ 2.0 934 1

Nested Loop Semi Join (cost=60.05..17,839.98 rows=1,860 width=4) (actual time=0.123..2.076 rows=934 loops=1)

36. 0.314 0.407 ↑ 2.0 934 1

Bitmap Heap Scan on t166_userrolehierarchy t166_userrolehierarchy_2 (cost=59.63..6,838.33 rows=1,860 width=8) (actual time=0.107..0.407 rows=934 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=130
37. 0.093 0.093 ↑ 2.0 944 1

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..59.16 rows=1,860 width=0) (actual time=0.093..0.093 rows=944 loops=1)

  • Index Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
38. 0.934 0.934 ↑ 1.0 1 934

Index Scan using ix_t67_userroledim_sid_timestamp_partial on t67_userroledim t67_userroledim_2 (cost=0.42..5.90 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=934)

  • Index Cond: ((sid = t166_userrolehierarchy_2.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
39. 41.080 41.080 ↑ 1.0 1 10,270

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.35 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=10,270)

  • 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)
40. 0.005 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
41. 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.003..0.005 rows=5 loops=1)

42. 0.003 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
43. 0.008 0.008 ↑ 86.0 5 1

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

44.          

SubPlan (forHash Left Join)

45. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.98..17.04 rows=1 width=0) (never executed)

46. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_t166_userrolehierarchy_sid_ancestor_end_start_timestamp on t166_userrolehierarchy (cost=0.56..8.58 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t236_opp_line_item_fact_1.c804_opp_ownerid) AND (c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
47. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_t67_userroledim_sid_end_start_timestamp_partial on t67_userroledim (cost=0.42..8.44 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t166_userrolehierarchy.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
48. 0.591 1.941 ↑ 2.0 934 1

Nested Loop Semi Join (cost=60.05..17,839.98 rows=1,860 width=4) (actual time=0.122..1.941 rows=934 loops=1)

49. 0.326 0.416 ↑ 2.0 934 1

Bitmap Heap Scan on t166_userrolehierarchy t166_userrolehierarchy_1 (cost=59.63..6,838.33 rows=1,860 width=8) (actual time=0.106..0.416 rows=934 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=130
50. 0.090 0.090 ↑ 2.0 944 1

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..59.16 rows=1,860 width=0) (actual time=0.090..0.090 rows=944 loops=1)

  • Index Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
51. 0.934 0.934 ↑ 1.0 1 934

Index Scan using ix_t67_userroledim_sid_timestamp_partial on t67_userroledim t67_userroledim_1 (cost=0.42..5.90 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=934)

  • Index Cond: ((sid = t166_userrolehierarchy_1.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
52. 0.300 0.300 ↑ 1.0 6 1

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

53. 0.293 0.293 ↑ 1.0 7 1

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

54.          

CTE bucketec

55. 0.482 22,260.453 ↑ 22,607.0 21 1

GroupAggregate (cost=13,614,077.00..21,720,382.02 rows=474,747 width=116) (actual time=22,259.901..22,260.453 rows=21 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 (alternatives: SubPlan 11 or 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
56. 1.022 22,259.971 ↑ 256.3 1,852 1

Sort (cost=13,614,076.41..13,615,263.28 rows=474,747 width=88) (actual time=22,259.844..22,259.971 rows=1,852 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 (alternatives: SubPlan 11 or 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: quicksort Memory: 224kB
57. 7.161 22,258.949 ↑ 256.3 1,852 1

Hash Left Join (cost=5,249,902.24..13,546,596.87 rows=474,747 width=88) (actual time=19,750.575..22,258.949 rows=1,852 loops=1)

  • Hash Cond: ((t236_opp_line_item_fact_2.c821_opp_currency_code = cur177_t0_r0_2.sid) AND (t236_opp_line_item_fact_2.c805_opp_close_date = cur177_t0_r0_2.rate_date))
58. 675.507 22,203.532 ↑ 214.8 1,852 1

Merge Right Join (cost=5,245,660.29..5,350,335.24 rows=397,847 width=44) (actual time=19,570.354..22,203.532 rows=1,852 loops=1)

  • Merge Cond: ((t236_opp_line_item_fact_3.sid = t236_opp_line_item_fact_2.sid) AND (t236_opp_line_item_fact_3.sid = t236_opp_line_item_fact_2.sid))
  • 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 (alternatives: SubPlan 15 or hashed SubPlan 16)))
  • Rows Removed by Filter: 11037
59. 8,388.289 17,407.445 ↑ 1.4 9,724,363 1

Sort (cost=3,668,997.04..3,702,224.92 rows=13,291,153 width=16) (actual time=15,445.473..17,407.445 rows=9,724,363 loops=1)

  • Sort Key: t236_opp_line_item_fact_3.sid
  • Sort Method: external merge Disk: 247176kB
60. 9,019.156 9,019.156 ↑ 1.4 9,724,457 1

Seq Scan on t236_opp_line_item_fact t236_opp_line_item_fact_3 (cost=0.00..1,642,097.28 rows=13,291,153 width=16) (actual time=0.194..9,019.156 rows=9,724,457 loops=1)

  • Filter: ((NOT deleted) AND (start_stamp <= '1541055600000'::bigint) AND (end_stamp > '1541055600000'::bigint))
  • Rows Removed by Filter: 27932867
61. 1.278 4,118.492 ↑ 38.3 12,889 1

Materialize (cost=1,576,663.25..1,579,133.33 rows=494,016 width=28) (actual time=4,114.729..4,118.492 rows=12,889 loops=1)

62. 6.822 4,117.214 ↑ 38.3 12,889 1

Sort (cost=1,576,663.25..1,577,898.29 rows=494,016 width=28) (actual time=4,114.727..4,117.214 rows=12,889 loops=1)

  • Sort Key: t236_opp_line_item_fact_2.sid
  • Sort Method: quicksort Memory: 1391kB
63. 196.877 4,110.392 ↑ 38.3 12,889 1

Hash Join (cost=358,123.04..1,518,120.66 rows=494,016 width=28) (actual time=734.297..4,110.392 rows=12,889 loops=1)

  • Hash Cond: (t236_opp_line_item_fact_2.c804_opp_ownerid = t166_userrolehierarchy_7.sid)
64. 3,203.391 3,911.008 ↑ 1.2 2,767,952 1

Bitmap Heap Scan on t236_opp_line_item_fact t236_opp_line_item_fact_2 (cost=340,236.77..1,487,871.89 rows=3,220,505 width=32) (actual time=716.670..3,911.008 rows=2,767,952 loops=1)

  • Recheck Cond: ((c805_opp_close_date >= 20181101) AND (c805_opp_close_date <= 20190131) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
  • Rows Removed by Index Recheck: 8933455
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 85
  • Heap Blocks: exact=42453 lossy=319947
65. 707.617 707.617 ↑ 1.1 2,829,134 1

Bitmap Index Scan on ix_t236_opp_line_item_fact_closedate_timestamp (cost=0.00..339,431.64 rows=3,221,256 width=0) (actual time=707.617..707.617 rows=2,829,134 loops=1)

  • Index Cond: ((c805_opp_close_date >= 20181101) AND (c805_opp_close_date <= 20190131) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
66. 0.120 2.507 ↑ 2.5 737 1

Hash (cost=17,863.14..17,863.14 rows=1,851 width=4) (actual time=2.507..2.507 rows=737 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 42kB
67. 0.308 2.387 ↑ 2.5 737 1

HashAggregate (cost=17,844.63..17,863.14 rows=1,851 width=4) (actual time=2.316..2.387 rows=737 loops=1)

  • Group Key: t166_userrolehierarchy_7.sid
68. 0.691 2.079 ↑ 2.0 934 1

Nested Loop Semi Join (cost=60.05..17,839.98 rows=1,860 width=4) (actual time=0.150..2.079 rows=934 loops=1)

69. 0.348 0.454 ↑ 2.0 934 1

Bitmap Heap Scan on t166_userrolehierarchy t166_userrolehierarchy_7 (cost=59.63..6,838.33 rows=1,860 width=8) (actual time=0.125..0.454 rows=934 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=130
70. 0.106 0.106 ↑ 2.0 944 1

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..59.16 rows=1,860 width=0) (actual time=0.106..0.106 rows=944 loops=1)

  • Index Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
71. 0.934 0.934 ↑ 1.0 1 934

Index Scan using ix_t67_userroledim_sid_timestamp_partial on t67_userroledim t67_userroledim_7 (cost=0.42..5.90 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=934)

  • Index Cond: ((sid = t166_userrolehierarchy_7.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
72.          

SubPlan (forMerge Right Join)

73. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.98..17.04 rows=1 width=0) (never executed)

74. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_t166_userrolehierarchy_sid_ancestor_end_start_timestamp on t166_userrolehierarchy t166_userrolehierarchy_5 (cost=0.56..8.58 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t236_opp_line_item_fact_3.c804_opp_ownerid) AND (c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
75. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_t67_userroledim_sid_end_start_timestamp_partial on t67_userroledim t67_userroledim_5 (cost=0.42..8.44 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t166_userrolehierarchy_5.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
76. 0.752 2.088 ↑ 2.0 934 1

Nested Loop Semi Join (cost=60.05..17,839.98 rows=1,860 width=4) (actual time=0.125..2.088 rows=934 loops=1)

77. 0.305 0.402 ↑ 2.0 934 1

Bitmap Heap Scan on t166_userrolehierarchy t166_userrolehierarchy_6 (cost=59.63..6,838.33 rows=1,860 width=8) (actual time=0.112..0.402 rows=934 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=130
78. 0.097 0.097 ↑ 2.0 944 1

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..59.16 rows=1,860 width=0) (actual time=0.097..0.097 rows=944 loops=1)

  • Index Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
79. 0.934 0.934 ↑ 1.0 1 934

Index Scan using ix_t67_userroledim_sid_timestamp_partial on t67_userroledim t67_userroledim_6 (cost=0.42..5.90 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=934)

  • Index Cond: ((sid = t166_userrolehierarchy_6.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
80. 18.580 46.111 ↑ 1.0 101,598 1

Hash (cost=2,220.16..2,220.16 rows=101,614 width=16) (actual time=46.111..46.111 rows=101,598 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3409kB
81. 6.042 27.531 ↑ 1.0 101,598 1

Append (cost=0.00..2,220.16 rows=101,614 width=16) (actual time=0.132..27.531 rows=101,598 loops=1)

82. 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)
83. 21.487 21.487 ↑ 1.0 101,598 1

Seq Scan on usd_clari_conversion_rate cur177_t0_r0_3 (cost=0.00..2,220.16 rows=101,613 width=16) (actual time=0.130..21.487 rows=101,598 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
84.          

SubPlan (forHash Left Join)

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

86. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.98..17.04 rows=1 width=0) (never executed)

87. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_t166_userrolehierarchy_sid_ancestor_end_start_timestamp on t166_userrolehierarchy t166_userrolehierarchy_3 (cost=0.56..8.58 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t236_opp_line_item_fact_3.c804_opp_ownerid) AND (c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
88. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_t67_userroledim_sid_end_start_timestamp_partial on t67_userroledim t67_userroledim_3 (cost=0.42..8.44 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t166_userrolehierarchy_3.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
89. 0.000 2.143 ↑ 2.0 934 1

Nested Loop Semi Join (cost=60.05..17,839.98 rows=1,860 width=4) (actual time=0.131..2.143 rows=934 loops=1)

90. 0.330 0.427 ↑ 2.0 934 1

Bitmap Heap Scan on t166_userrolehierarchy t166_userrolehierarchy_4 (cost=59.63..6,838.33 rows=1,860 width=8) (actual time=0.114..0.427 rows=934 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=130
91. 0.097 0.097 ↑ 2.0 944 1

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..59.16 rows=1,860 width=0) (actual time=0.097..0.097 rows=944 loops=1)

  • Index Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
92. 1.868 1.868 ↑ 1.0 1 934

Index Scan using ix_t67_userroledim_sid_timestamp_partial on t67_userroledim t67_userroledim_4 (cost=0.42..5.90 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=934)

  • Index Cond: ((sid = t166_userrolehierarchy_4.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
93. 0.000 0.000 ↑ 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.000 rows=6 loops=1)

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

95. 0.014 4,403.428 ↑ 19,040.3 19 1

Hash Join (cost=19.68..12,229.28 rows=361,766 width=120) (actual time=4,399.470..4,403.428 rows=19 loops=1)

  • Hash Cond: (cte0.c4 = t234_opportunityforecastcategorypicklistdim.sid)
96. 4,403.405 4,403.405 ↑ 19,040.3 19 1

CTE Scan on bucketac cte0 (cost=0.00..7,235.32 rows=361,766 width=92) (actual time=4,399.452..4,403.405 rows=19 loops=1)

97. 0.006 0.009 ↑ 86.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
98. 0.003 0.003 ↑ 86.0 5 1

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

99. 0.009 22,260.521 ↑ 22,607.0 21 1

Subquery Scan on *SELECT* 2 (cost=19.68..21,976.72 rows=474,747 width=120) (actual time=22,259.951..22,260.521 rows=21 loops=1)

100. 0.018 22,260.512 ↑ 22,607.0 21 1

Hash Join (cost=19.68..16,042.39 rows=474,747 width=144) (actual time=22,259.945..22,260.512 rows=21 loops=1)

  • Hash Cond: (cte0_1.c6 = t234_opportunityforecastcategorypicklistdim_1.sid)
101. 22,260.464 22,260.464 ↑ 22,607.0 21 1

CTE Scan on bucketec cte0_1 (cost=0.00..9,494.94 rows=474,747 width=116) (actual time=22,259.905..22,260.464 rows=21 loops=1)

102. 0.018 0.030 ↑ 86.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
103. 0.012 0.012 ↑ 86.0 5 1

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