explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nVsO

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 7,537.496 ↑ 69,746.2 6 1

Append (cost=2,330,081.84..2,345,002.27 rows=418,477 width=120) (actual time=4,977.463..7,537.496 rows=6 loops=1)

2.          

CTE docd_cte_0

3. 18.320 1,576.232 ↑ 2.8 13,301 1

HashAggregate (cost=398,420.73..398,787.73 rows=36,700 width=4) (actual time=1,574.566..1,576.232 rows=13,301 loops=1)

  • Group Key: t67_distinct_opp_owner_closedate.c759_ownerid
4. 69.928 1,557.912 ↓ 1.3 90,868 1

Hash Join (cost=225,550.22..398,251.87 rows=67,544 width=4) (actual time=818.838..1,557.912 rows=90,868 loops=1)

  • Hash Cond: (t67_distinct_opp_owner_closedate.c759_ownerid = t55_userrolehierarchy.sid)
5. 679.634 679.634 ↓ 1.0 141,396 1

Seq Scan on t67_distinct_opp_owner_closedate (cost=0.00..156,103.63 rows=135,089 width=4) (actual time=0.165..679.634 rows=141,396 loops=1)

  • Filter: ((c760_closedate >= 20191102) AND (c760_closedate <= 20200131))
  • Rows Removed by Filter: 6026444
6. 51.541 808.350 ↑ 1.0 363,469 1

Hash (cost=219,341.13..219,341.13 rows=378,407 width=4) (actual time=808.350..808.350 rows=363,469 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 2639kB
7. 48.066 756.809 ↑ 1.0 363,469 1

Unique (cost=213,665.03..215,557.06 rows=378,407 width=4) (actual time=661.893..756.809 rows=363,469 loops=1)

8. 225.313 708.743 ↓ 1.0 389,844 1

Sort (cost=213,665.03..214,611.04 rows=378,407 width=4) (actual time=661.892..708.743 rows=389,844 loops=1)

  • Sort Key: t55_userrolehierarchy.sid
  • Sort Method: external merge Disk: 5336kB
9. 30.529 483.430 ↓ 1.0 389,844 1

Append (cost=25,500.11..173,429.91 rows=378,407 width=4) (actual time=69.124..483.430 rows=389,844 loops=1)

10. 125.046 335.185 ↓ 1.2 389,678 1

Hash Join (cost=25,500.11..120,368.88 rows=316,841 width=4) (actual time=69.123..335.185 rows=389,678 loops=1)

  • Hash Cond: (t55_userrolehierarchy.c697_user_role_id = t33_userroledim.sid)
11. 150.546 196.710 ↑ 1.0 550,251 1

Bitmap Heap Scan on t55_userrolehierarchy (cost=16,969.25..109,211.80 rows=563,970 width=8) (actual time=55.558..196.710 rows=550,251 loops=1)

  • Recheck Cond: ((c698_ancestor_role_id = 437) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=50118
12. 46.164 46.164 ↑ 1.0 550,257 1

Bitmap Index Scan on idx_49_t55_userrolehierarchy_c698_ancestor_role_id_end_stamp_st (cost=0.00..16,828.26 rows=563,970 width=0) (actual time=46.164..46.164 rows=550,257 loops=1)

  • Index Cond: ((c698_ancestor_role_id = 437) AND (end_stamp = '32503680000000'::bigint))
13. 3.819 13.429 ↓ 1.0 26,450 1

Hash (cost=8,207.82..8,207.82 rows=25,843 width=4) (actual time=13.429..13.429 rows=26,450 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1186kB
14. 7.900 9.610 ↓ 1.0 26,450 1

Bitmap Heap Scan on t33_userroledim (cost=720.57..8,207.82 rows=25,843 width=4) (actual time=1.804..9.610 rows=26,450 loops=1)

  • Recheck Cond: ((end_stamp = '32503680000000'::bigint) AND ((c412_portaltype IS NULL) OR (c412_portaltype = 'None'::text)))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=735
15. 1.710 1.710 ↓ 1.0 26,451 1

Bitmap Index Scan on "idx_9_t33_userroledim_sid_start_stamp_end_stamp_(c412_portaltyp" (cost=0.00..714.11 rows=25,843 width=0) (actual time=1.710..1.710 rows=26,451 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
16. 101.826 117.716 ↑ 370.9 166 1

Bitmap Heap Scan on t47_userdim (cost=5,627.51..49,276.97 rows=61,566 width=4) (actual time=18.537..117.716 rows=166 loops=1)

  • Recheck Cond: ((c577_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c587_usertype = 'Standard'::text))
  • Rows Removed by Filter: 188439
  • Heap Blocks: exact=15559
17. 15.890 15.890 ↓ 1.0 188,605 1

Bitmap Index Scan on idx_6_t47_userdim_c577_userroleid_end_stamp_start_stamp_null (cost=0.00..5,612.12 rows=187,969 width=0) (actual time=15.889..15.890 rows=188,605 loops=1)

  • Index Cond: ((c577_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
18.          

CTE picklist_1

19. 0.016 0.016 ↑ 1.0 1 1

Seq Scan on t60_opportunitystagenamepicklistdim (cost=0.00..2.48 rows=1 width=4) (actual time=0.012..0.016 rows=1 loops=1)

  • Filter: (correlated_value = 'Win - 100%'::text)
  • Rows Removed by Filter: 18
20.          

CTE picklist_2

21. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on t60_opportunitystagenamepicklistdim t60_opportunitystagenamepicklistdim_1 (cost=0.00..2.48 rows=1 width=4) (actual time=0.006..0.008 rows=1 loops=1)

  • Filter: (correlated_value = 'Lost, Cancelled - 0%'::text)
  • Rows Removed by Filter: 18
22.          

CTE picklist_7

23. 0.023 0.023 ↑ 1.0 2 1

Seq Scan on t60_opportunitystagenamepicklistdim t60_opportunitystagenamepicklistdim_2 (cost=0.00..2.48 rows=2 width=4) (actual time=0.017..0.023 rows=2 loops=1)

  • Filter: (correlated_value = ANY ('{"Lost, Cancelled - 0%","Win - 100%"}'::text[]))
  • Rows Removed by Filter: 17
24.          

CTE bucketac

25. 117.959 5,149.611 ↑ 28,408.0 5 1

GroupAggregate (cost=774,376.72..781,833.82 rows=142,040 width=96) (actual time=4,977.434..5,149.611 rows=5 loops=1)

  • Group Key: t52_opp_line_item_fact.c651_opp_forecastcategory, (CASE WHEN (t52_opp_line_item_fact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t52_opp_line_item_fact_1.c658_opp_close_date < 20191102) OR (t52_opp_line_item_fact_1.c658_opp_close_date > 20200131)) THEN 'slipped'::text WHEN ((t52_opp_line_item_fact_1.c658_opp_close_date >= 20191102) AND (t52_opp_line_item_fact_1.c658_opp_close_date <= 20200131) AND (hashed SubPlan 6) AND (t52_opp_line_item_fact_1.c647_opp_ownerid IS NOT NULL)) THEN CASE WHEN (hashed SubPlan 7) THEN 'won'::text WHEN (hashed SubPlan 8) THEN 'lost'::text ELSE CASE WHEN (CASE WHEN (t58_opportunityforecastcategorypicklistdim_3.correlated_app_order IS NULL) THEN cte0_6.c1 ELSE t58_opportunityforecastcategorypicklistdim_3.correlated_app_order END = CASE WHEN (t58_opportunityforecastcategorypicklistdim_4.correlated_app_order IS NULL) THEN cte0_6.c1 ELSE t58_opportunityforecastcategorypicklistdim_4.correlated_app_order END) THEN 'unchanged'::text WHEN (CASE WHEN (t58_opportunityforecastcategorypicklistdim_3.correlated_app_order IS NULL) THEN cte0_6.c1 ELSE t58_opportunityforecastcategorypicklistdim_3.correlated_app_order END > CASE WHEN (t58_opportunityforecastcategorypicklistdim_4.correlated_app_order IS NULL) THEN cte0_6.c1 ELSE t58_opportunityforecastcategorypicklistdim_4.correlated_app_order END) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
26.          

CTE nullgroupbyvaluecte

27. 0.007 0.007 ↓ 0.0 0 1

Seq Scan on t58_opportunityforecastcategorypicklistdim t58_opportunityforecastcategorypicklistdim_2 (cost=0.00..1.06 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=1)

  • Filter: (value = 'Value Not Assigned'::text)
  • Rows Removed by Filter: 5
28. 231.176 5,031.652 ↓ 2.6 365,267 1

Sort (cost=773,549.86..773,904.96 rows=142,040 width=72) (actual time=4,946.360..5,031.652 rows=365,267 loops=1)

  • Sort Key: t52_opp_line_item_fact.c651_opp_forecastcategory, (CASE WHEN (t52_opp_line_item_fact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t52_opp_line_item_fact_1.c658_opp_close_date < 20191102) OR (t52_opp_line_item_fact_1.c658_opp_close_date > 20200131)) THEN 'slipped'::text WHEN ((t52_opp_line_item_fact_1.c658_opp_close_date >= 20191102) AND (t52_opp_line_item_fact_1.c658_opp_close_date <= 20200131) AND (hashed SubPlan 6) AND (t52_opp_line_item_fact_1.c647_opp_ownerid IS NOT NULL)) THEN CASE WHEN (hashed SubPlan 7) THEN 'won'::text WHEN (hashed SubPlan 8) THEN 'lost'::text ELSE CASE WHEN (CASE WHEN (t58_opportunityforecastcategorypicklistdim_3.correlated_app_order IS NULL) THEN cte0_6.c1 ELSE t58_opportunityforecastcategorypicklistdim_3.correlated_app_order END = CASE WHEN (t58_opportunityforecastcategorypicklistdim_4.correlated_app_order IS NULL) THEN cte0_6.c1 ELSE t58_opportunityforecastcategorypicklistdim_4.correlated_app_order END) THEN 'unchanged'::text WHEN (CASE WHEN (t58_opportunityforecastcategorypicklistdim_3.correlated_app_order IS NULL) THEN cte0_6.c1 ELSE t58_opportunityforecastcategorypicklistdim_3.correlated_app_order END > CASE WHEN (t58_opportunityforecastcategorypicklistdim_4.correlated_app_order IS NULL) THEN cte0_6.c1 ELSE t58_opportunityforecastcategorypicklistdim_4.correlated_app_order END) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
  • Sort Method: external merge Disk: 23496kB
29. 303.803 4,800.476 ↓ 2.6 365,267 1

Nested Loop Left Join (cost=737,911.04..755,566.62 rows=142,040 width=72) (actual time=4,095.107..4,800.476 rows=365,267 loops=1)

30. 81.203 4,495.527 ↓ 2.6 365,267 1

Hash Left Join (cost=737,085.24..748,704.11 rows=142,040 width=64) (actual time=4,091.238..4,495.527 rows=365,267 loops=1)

  • Hash Cond: (t52_opp_line_item_fact_1.c651_opp_forecastcategory = t58_opportunityforecastcategorypicklistdim_4.sid)
31. 85.866 4,414.317 ↓ 2.6 365,267 1

Hash Left Join (cost=737,084.13..746,750.14 rows=142,040 width=64) (actual time=4,091.221..4,414.317 rows=365,267 loops=1)

  • Hash Cond: (t52_opp_line_item_fact.c651_opp_forecastcategory = t58_opportunityforecastcategorypicklistdim_3.sid)
32. 86.987 4,328.437 ↓ 2.6 365,267 1

Merge Right Join (cost=737,083.02..744,796.17 rows=142,040 width=60) (actual time=4,091.180..4,328.437 rows=365,267 loops=1)

  • Merge Cond: ((t54_clari_conversion_rate_1.sid = t52_opp_line_item_fact_1.c641_opp_currency_code) AND (t54_clari_conversion_rate_1.rate_date = t52_opp_line_item_fact_1.c658_opp_close_date))
33. 5.449 42.036 ↑ 1.0 66,089 1

Merge Append (cost=0.31..5,835.47 rows=66,109 width=16) (actual time=0.021..42.036 rows=66,089 loops=1)

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

Seq Scan on t54_clari_conversion_rate t54_clari_conversion_rate_1 (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)
36. 36.582 36.582 ↑ 1.0 66,089 1

Index Scan using t54_clari_conversion_rate_usd_sidratedateunique on t54_clari_conversion_rate_usd t54_clari_conversion_rate_usd_1 (cost=0.29..5,009.08 rows=66,108 width=16) (actual time=0.015..36.582 rows=66,089 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
37. 55.537 4,199.414 ↓ 2.6 365,267 1

Materialize (cost=737,082.71..737,792.91 rows=142,040 width=56) (actual time=4,087.727..4,199.414 rows=365,267 loops=1)

38. 374.908 4,143.877 ↓ 2.6 365,267 1

Sort (cost=737,082.71..737,437.81 rows=142,040 width=56) (actual time=4,087.726..4,143.877 rows=365,267 loops=1)

  • Sort Key: t52_opp_line_item_fact_1.c641_opp_currency_code, t52_opp_line_item_fact_1.c658_opp_close_date
  • Sort Method: external sort Disk: 26432kB
39. 203.350 3,768.969 ↓ 2.6 365,267 1

Nested Loop Left Join (cost=31,829.06..720,068.97 rows=142,040 width=56) (actual time=2,216.218..3,768.969 rows=365,267 loops=1)

40. 75.073 2,469.818 ↓ 2.6 365,267 1

Merge Right Join (cost=31,828.50..39,541.65 rows=142,040 width=24) (actual time=2,216.198..2,469.818 rows=365,267 loops=1)

  • Merge Cond: ((t54_clari_conversion_rate.sid = t52_opp_line_item_fact.c641_opp_currency_code) AND (t54_clari_conversion_rate.rate_date = t52_opp_line_item_fact.c658_opp_close_date))
41. 5.694 44.224 ↑ 1.0 66,089 1

Merge Append (cost=0.31..5,835.47 rows=66,109 width=16) (actual time=0.011..44.224 rows=66,089 loops=1)

  • Sort Key: t54_clari_conversion_rate.sid, t54_clari_conversion_rate.rate_date
42. 0.003 0.003 ↓ 0.0 0 1

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

  • Sort Key: t54_clari_conversion_rate.sid, t54_clari_conversion_rate.rate_date
  • Sort Method: quicksort Memory: 25kB
43. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on t54_clari_conversion_rate (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)
44. 38.527 38.527 ↑ 1.0 66,089 1

Index Scan using t54_clari_conversion_rate_usd_sidratedateunique on t54_clari_conversion_rate_usd (cost=0.29..5,009.08 rows=66,108 width=16) (actual time=0.007..38.527 rows=66,089 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
45. 38.092 2,350.521 ↓ 2.6 365,267 1

Materialize (cost=31,828.18..32,538.38 rows=142,040 width=24) (actual time=2,212.579..2,350.521 rows=365,267 loops=1)

46. 243.292 2,312.429 ↓ 2.6 365,267 1

Sort (cost=31,828.18..32,183.28 rows=142,040 width=24) (actual time=2,212.574..2,312.429 rows=365,267 loops=1)

  • Sort Key: t52_opp_line_item_fact.c641_opp_currency_code, t52_opp_line_item_fact.c658_opp_close_date
  • Sort Method: external merge Disk: 13504kB
47. 58.989 2,069.137 ↓ 2.6 365,267 1

Nested Loop (cost=826.36..16,756.94 rows=142,040 width=24) (actual time=1,582.209..2,069.137 rows=365,267 loops=1)

48. 5.548 1,584.516 ↓ 66.5 13,301 1

HashAggregate (cost=825.75..827.75 rows=200 width=4) (actual time=1,582.141..1,584.516 rows=13,301 loops=1)

  • Group Key: cte0_7.c1
49. 1,578.968 1,578.968 ↑ 2.8 13,301 1

CTE Scan on docd_cte_0 cte0_7 (cost=0.00..734.00 rows=36,700 width=4) (actual time=1,574.569..1,578.968 rows=13,301 loops=1)

50. 425.608 425.632 ↓ 2.7 27 13,301

Index Scan using ix_t52_opp_line_item_fact_notnullowner_closedate_timestamp on t52_opp_line_item_fact (cost=0.61..79.55 rows=10 width=28) (actual time=0.007..0.032 rows=27 loops=13,301)

  • Index Cond: ((c647_opp_ownerid = cte0_7.c1) AND (c658_opp_close_date >= 20191102) AND (c658_opp_close_date <= 20200131) AND (end_stamp > '1574582400000'::bigint) AND (start_stamp <= '1574582400000'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 9)))
  • Rows Removed by Filter: 3
51.          

SubPlan (for Index Scan)

52. 0.024 0.024 ↑ 1.0 2 1

CTE Scan on picklist_7 cte0_5 (cost=0.00..0.04 rows=2 width=4) (actual time=0.018..0.024 rows=2 loops=1)

53. 1,095.801 1,095.801 ↑ 1.0 1 365,267

Index Scan using t52_opp_line_item_fact_sidendstampunique on t52_opp_line_item_fact t52_opp_line_item_fact_1 (cost=0.56..4.78 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=365,267)

  • Index Cond: ((t52_opp_line_item_fact.sid = sid) AND (t52_opp_line_item_fact.sid = sid) AND (end_stamp > '1575273599999'::bigint))
  • Filter: ((NOT deleted) AND (start_stamp <= '1575273599999'::bigint))
54. 0.003 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
55. 0.011 0.011 ↑ 1.0 5 1

Seq Scan on t58_opportunityforecastcategorypicklistdim t58_opportunityforecastcategorypicklistdim_3 (cost=0.00..1.05 rows=5 width=8) (actual time=0.008..0.011 rows=5 loops=1)

56. 0.005 0.007 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
57. 0.002 0.002 ↑ 1.0 5 1

Seq Scan on t58_opportunityforecastcategorypicklistdim t58_opportunityforecastcategorypicklistdim_4 (cost=0.00..1.05 rows=5 width=8) (actual time=0.001..0.002 rows=5 loops=1)

58. 0.000 0.000 ↓ 0.0 0 365,267

CTE Scan on nullgroupbyvaluecte cte0_6 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=365,267)

59.          

SubPlan (for Nested Loop Left Join)

60. 1.119 1.119 ↑ 2.8 13,301 1

CTE Scan on docd_cte_0 cte0_2 (cost=0.00..734.00 rows=36,700 width=4) (actual time=0.001..1.119 rows=13,301 loops=1)

61. 0.018 0.018 ↑ 1.0 1 1

CTE Scan on picklist_1 cte0_3 (cost=0.00..0.02 rows=1 width=4) (actual time=0.014..0.018 rows=1 loops=1)

62. 0.009 0.009 ↑ 1.0 1 1

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

63.          

CTE bucketec

64. 0.030 2,387.800 ↑ 276,437.0 1 1

GroupAggregate (cost=1,137,012.09..1,149,451.76 rows=276,437 width=120) (actual time=2,387.800..2,387.800 rows=1 loops=1)

  • Group Key: (CASE WHEN (t52_opp_line_item_fact_3.sid IS NULL) THEN 'newBucket'::text WHEN (t52_opp_line_item_fact_3.c658_opp_close_date > 20200131) THEN 'pulledInBucket'::text WHEN (t52_opp_line_item_fact_3.c658_opp_close_date < 20191102) THEN 'pushedInBucket'::text WHEN ((t52_opp_line_item_fact_3.c658_opp_close_date >= 20191102) AND (t52_opp_line_item_fact_3.c658_opp_close_date <= 20200131) AND (hashed SubPlan 11) AND (t52_opp_line_item_fact_3.c647_opp_ownerid IS NOT NULL)) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END), (CASE WHEN (hashed SubPlan 12) THEN 'won'::text WHEN (hashed SubPlan 13) THEN 'lost'::text ELSE 'newOpen'::text END), t52_opp_line_item_fact_2.c651_opp_forecastcategory
65. 0.021 2,387.770 ↑ 39,491.0 7 1

Sort (cost=1,136,186.30..1,136,877.39 rows=276,437 width=88) (actual time=2,387.770..2,387.770 rows=7 loops=1)

  • Sort Key: (CASE WHEN (t52_opp_line_item_fact_3.sid IS NULL) THEN 'newBucket'::text WHEN (t52_opp_line_item_fact_3.c658_opp_close_date > 20200131) THEN 'pulledInBucket'::text WHEN (t52_opp_line_item_fact_3.c658_opp_close_date < 20191102) THEN 'pushedInBucket'::text WHEN ((t52_opp_line_item_fact_3.c658_opp_close_date >= 20191102) AND (t52_opp_line_item_fact_3.c658_opp_close_date <= 20200131) AND (hashed SubPlan 11) AND (t52_opp_line_item_fact_3.c647_opp_ownerid IS NOT NULL)) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END), (CASE WHEN (hashed SubPlan 12) THEN 'won'::text WHEN (hashed SubPlan 13) THEN 'lost'::text ELSE 'newOpen'::text END), t52_opp_line_item_fact_2.c651_opp_forecastcategory
  • Sort Method: quicksort Memory: 25kB
66. 272.074 2,387.749 ↑ 39,491.0 7 1

Nested Loop Left Join (cost=50,842.73..1,097,971.10 rows=276,437 width=88) (actual time=893.834..2,387.749 rows=7 loops=1)

  • Filter: ((t52_opp_line_item_fact_3.sid IS NULL) OR (t52_opp_line_item_fact_3.c658_opp_close_date < 20191102) OR (t52_opp_line_item_fact_3.c658_opp_close_date > 20200131) OR (NOT (hashed SubPlan 14)) OR (t52_opp_line_item_fact_3.c647_opp_ownerid IS NULL))
  • Rows Removed by Filter: 406114
67. 80.407 896.200 ↓ 1.4 406,121 1

Merge Right Join (cost=49,190.63..58,442.42 rows=283,272 width=28) (actual time=622.012..896.200 rows=406,121 loops=1)

  • Merge Cond: ((t54_clari_conversion_rate_2.sid = t52_opp_line_item_fact_2.c641_opp_currency_code) AND (t54_clari_conversion_rate_2.rate_date = t52_opp_line_item_fact_2.c658_opp_close_date))
68. 5.448 43.558 ↑ 1.0 66,089 1

Merge Append (cost=0.31..5,835.47 rows=66,109 width=16) (actual time=0.023..43.558 rows=66,089 loops=1)

  • Sort Key: t54_clari_conversion_rate_2.sid, t54_clari_conversion_rate_2.rate_date
69. 0.002 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: t54_clari_conversion_rate_2.sid, t54_clari_conversion_rate_2.rate_date
  • Sort Method: quicksort Memory: 25kB
70. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on t54_clari_conversion_rate t54_clari_conversion_rate_2 (cost=0.00..0.00 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
71. 38.103 38.103 ↑ 1.0 66,089 1

Index Scan using t54_clari_conversion_rate_usd_sidratedateunique on t54_clari_conversion_rate_usd t54_clari_conversion_rate_usd_2 (cost=0.29..5,009.08 rows=66,108 width=16) (actual time=0.016..38.103 rows=66,089 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
72. 43.795 772.235 ↓ 1.4 406,121 1

Materialize (cost=49,190.31..50,606.67 rows=283,272 width=28) (actual time=618.435..772.235 rows=406,121 loops=1)

73. 266.214 728.440 ↓ 1.4 406,121 1

Sort (cost=49,190.31..49,898.49 rows=283,272 width=28) (actual time=618.434..728.440 rows=406,121 loops=1)

  • Sort Key: t52_opp_line_item_fact_2.c641_opp_currency_code, t52_opp_line_item_fact_2.c658_opp_close_date
  • Sort Method: external merge Disk: 16648kB
74. 70.128 462.226 ↓ 1.4 406,121 1

Nested Loop (cost=826.31..16,757.94 rows=283,272 width=28) (actual time=3.985..462.226 rows=406,121 loops=1)

75. 5.280 6.369 ↓ 66.5 13,301 1

HashAggregate (cost=825.75..827.75 rows=200 width=4) (actual time=3.965..6.369 rows=13,301 loops=1)

  • Group Key: cte0_12.c1
76. 1.089 1.089 ↑ 2.8 13,301 1

CTE Scan on docd_cte_0 cte0_12 (cost=0.00..734.00 rows=36,700 width=4) (actual time=0.000..1.089 rows=13,301 loops=1)

77. 385.729 385.729 ↓ 1.6 31 13,301

Index Scan using ix_t52_opp_line_item_fact_notnullowner_closedate_timestamp on t52_opp_line_item_fact t52_opp_line_item_fact_2 (cost=0.56..79.45 rows=20 width=32) (actual time=0.006..0.029 rows=31 loops=13,301)

  • Index Cond: ((c647_opp_ownerid = cte0_12.c1) AND (c658_opp_close_date >= 20191102) AND (c658_opp_close_date <= 20200131) AND (end_stamp > '1575273599999'::bigint) AND (start_stamp <= '1575273599999'::bigint))
  • Filter: (NOT deleted)
78. 1,218.363 1,218.363 ↑ 1.0 1 406,121

Index Scan using t52_opp_line_item_fact_sidendstampunique on t52_opp_line_item_fact t52_opp_line_item_fact_3 (cost=0.56..3.63 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=406,121)

  • Index Cond: ((t52_opp_line_item_fact_2.sid = sid) AND (t52_opp_line_item_fact_2.sid = sid) AND (end_stamp > '1574582400000'::bigint))
  • Filter: ((NOT deleted) AND (start_stamp <= '1574582400000'::bigint))
  • Rows Removed by Filter: 0
79.          

SubPlan (for Nested Loop Left Join)

80. 0.000 0.000 ↓ 0.0 0

CTE Scan on docd_cte_0 cte0_8 (cost=0.00..734.00 rows=36,700 width=4) (never executed)

81. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on picklist_1 cte0_9 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

82. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on picklist_2 cte0_10 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

83. 1.111 1.111 ↑ 2.8 13,301 1

CTE Scan on docd_cte_0 cte0_11 (cost=0.00..734.00 rows=36,700 width=4) (actual time=0.001..1.111 rows=13,301 loops=1)

84. 0.017 5,149.648 ↑ 28,408.0 5 1

Hash Left Join (cost=1.11..3,410.07 rows=142,040 width=96) (actual time=4,977.463..5,149.648 rows=5 loops=1)

  • Hash Cond: (cte0.c5 = t58_opportunityforecastcategorypicklistdim.sid)
85. 5,149.618 5,149.618 ↑ 28,408.0 5 1

CTE Scan on bucketac cte0 (cost=0.00..2,840.80 rows=142,040 width=92) (actual time=4,977.436..5,149.618 rows=5 loops=1)

86. 0.005 0.013 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=12) (actual time=0.012..0.013 rows=5 loops=1)

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

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

88. 0.009 2,387.847 ↑ 276,437.0 1 1

Subquery Scan on *SELECT* 2 (cost=1.11..10,091.06 rows=276,437 width=96) (actual time=2,387.845..2,387.847 rows=1 loops=1)

89. 0.020 2,387.838 ↑ 276,437.0 1 1

Hash Left Join (cost=1.11..6,635.60 rows=276,437 width=120) (actual time=2,387.836..2,387.838 rows=1 loops=1)

  • Hash Cond: (cte0_1.c7 = t58_opportunityforecastcategorypicklistdim_1.sid)
90. 2,387.803 2,387.803 ↑ 276,437.0 1 1

CTE Scan on bucketec cte0_1 (cost=0.00..5,528.74 rows=276,437 width=116) (actual time=2,387.802..2,387.803 rows=1 loops=1)

91. 0.006 0.015 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=12) (actual time=0.015..0.015 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
92. 0.009 0.009 ↑ 1.0 5 1

Seq Scan on t58_opportunityforecastcategorypicklistdim t58_opportunityforecastcategorypicklistdim_1 (cost=0.00..1.05 rows=5 width=12) (actual time=0.009..0.009 rows=5 loops=1)

Planning time : 8.589 ms
Execution time : 7,557.967 ms