explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nEoW

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 19,293.103 ↑ 70,459.8 6 1

Append (cost=3,138,418.52..3,153,491.60 rows=422,759 width=120) (actual time=10,833.688..19,293.103 rows=6 loops=1)

2.          

CTE docd_cte_0

3. 19.166 1,720.216 ↑ 2.8 13,301 1

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

  • Group Key: t67_distinct_opp_owner_closedate.c759_ownerid
4. 71.204 1,701.050 ↓ 1.3 90,868 1

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

  • Hash Cond: (t67_distinct_opp_owner_closedate.c759_ownerid = t55_userrolehierarchy.sid)
5. 769.271 769.271 ↓ 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.180..769.271 rows=141,396 loops=1)

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

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

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

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

8. 225.413 755.189 ↓ 1.0 389,844 1

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

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

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

10. 129.845 369.123 ↓ 1.2 389,678 1

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

  • Hash Cond: (t55_userrolehierarchy.c697_user_role_id = t33_userroledim.sid)
11. 176.387 224.119 ↑ 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=57.095..224.119 rows=550,251 loops=1)

  • Recheck Cond: ((c698_ancestor_role_id = 437) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=50118
12. 47.732 47.732 ↑ 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=47.732..47.732 rows=550,257 loops=1)

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

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

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

Bitmap Heap Scan on t33_userroledim (cost=720.57..8,207.82 rows=25,843 width=4) (actual time=2.064..11.142 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.951 1.951 ↓ 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.951..1.951 rows=26,451 loops=1)

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

Bitmap Heap Scan on t47_userdim (cost=5,627.51..49,276.97 rows=61,566 width=4) (actual time=19.860..125.731 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. 17.158 17.158 ↓ 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=17.158..17.158 rows=188,605 loops=1)

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

CTE picklist_1

19. 0.017 0.017 ↑ 1.0 1 1

Seq Scan on t60_opportunitystagenamepicklistdim (cost=0.00..2.24 rows=1 width=4) (actual time=0.011..0.017 rows=1 loops=1)

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

CTE picklist_2

21. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on t60_opportunitystagenamepicklistdim t60_opportunitystagenamepicklistdim_1 (cost=0.00..2.24 rows=1 width=4) (actual time=0.006..0.009 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.24 rows=2 width=4) (actual time=0.016..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. 118.181 11,000.651 ↑ 28,698.6 5 1

GroupAggregate (cost=1,178,129.75..1,185,663.13 rows=143,493 width=96) (actual time=10,833.654..11,000.651 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. 230.053 10,882.470 ↓ 2.5 365,267 1

Sort (cost=1,177,302.89..1,177,661.62 rows=143,493 width=72) (actual time=10,802.021..10,882.470 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: 23488kB
29. 307.827 10,652.417 ↓ 2.5 365,267 1

Nested Loop Left Join (cost=1,141,352.15..1,159,125.27 rows=143,493 width=72) (actual time=9,932.821..10,652.417 rows=365,267 loops=1)

30. 81.048 10,343.470 ↓ 2.5 365,267 1

Hash Left Join (cost=1,140,526.35..1,152,201.01 rows=143,493 width=64) (actual time=9,928.640..10,343.470 rows=365,267 loops=1)

  • Hash Cond: (t52_opp_line_item_fact_1.c651_opp_forecastcategory = t58_opportunityforecastcategorypicklistdim_4.sid)
31. 87.331 10,262.413 ↓ 2.5 365,267 1

Hash Left Join (cost=1,140,525.24..1,150,227.06 rows=143,493 width=64) (actual time=9,928.603..10,262.413 rows=365,267 loops=1)

  • Hash Cond: (t52_opp_line_item_fact.c651_opp_forecastcategory = t58_opportunityforecastcategorypicklistdim_3.sid)
32. 91.507 10,175.068 ↓ 2.5 365,267 1

Merge Right Join (cost=1,140,524.13..1,148,253.11 rows=143,493 width=60) (actual time=9,928.563..10,175.068 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.618 44.427 ↑ 1.0 66,089 1

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

  • Sort Key: t54_clari_conversion_rate_1.sid, t54_clari_conversion_rate_1.rate_date
34. 0.005 0.006 ↓ 0.0 0 1

Sort (cost=0.01..0.02 rows=1 width=16) (actual time=0.005..0.006 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.001 0.001 ↓ 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.001..0.001 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
36. 38.803 38.803 ↑ 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.018..38.803 rows=66,089 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
37. 56.799 10,039.134 ↓ 2.5 365,267 1

Materialize (cost=1,140,523.81..1,141,241.28 rows=143,493 width=56) (actual time=9,924.940..10,039.134 rows=365,267 loops=1)

38. 381.624 9,982.335 ↓ 2.5 365,267 1

Sort (cost=1,140,523.81..1,140,882.55 rows=143,493 width=56) (actual time=9,924.937..9,982.335 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. 245.207 9,600.711 ↓ 2.5 365,267 1

Nested Loop Left Join (cost=430,828.11..1,123,326.19 rows=143,493 width=56) (actual time=8,006.867..9,600.711 rows=365,267 loops=1)

40. 74.524 8,259.703 ↓ 2.5 365,267 1

Merge Right Join (cost=430,827.55..438,556.53 rows=143,493 width=24) (actual time=8,006.830..8,259.703 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.614 46.406 ↑ 1.0 66,089 1

Merge Append (cost=0.31..5,835.47 rows=66,109 width=16) (actual time=0.011..46.406 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. 40.789 40.789 ↑ 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..40.789 rows=66,089 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
45. 36.449 8,138.773 ↓ 2.5 365,267 1

Materialize (cost=430,827.24..431,544.70 rows=143,493 width=24) (actual time=8,003.046..8,138.773 rows=365,267 loops=1)

46. 268.147 8,102.324 ↓ 2.5 365,267 1

Sort (cost=430,827.24..431,185.97 rows=143,493 width=24) (actual time=8,003.024..8,102.324 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. 52.141 7,834.177 ↓ 2.5 365,267 1

Nested Loop (cost=826.36..415,593.12 rows=143,493 width=24) (actual time=1,726.320..7,834.177 rows=365,267 loops=1)

48. 7.163 1,730.081 ↓ 66.5 13,301 1

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

  • Group Key: cte0_7.c1
49. 1,722.918 1,722.918 ↑ 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,718.466..1,722.918 rows=13,301 loops=1)

50. 6,051.929 6,051.955 ↓ 2.7 27 13,301

Index Scan using t52_opp_line_item_fact_296_timestamp on t52_opp_line_item_fact (cost=0.61..2,073.73 rows=10 width=28) (actual time=0.260..0.455 rows=27 loops=13,301)

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

SubPlan (for Index Scan)

52. 0.026 0.026 ↑ 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.026 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.76 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.002 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.012 0.012 ↑ 1.0 5 1

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

56. 0.006 0.009 ↑ 1.0 5 1

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

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

Seq Scan on t58_opportunityforecastcategorypicklistdim t58_opportunityforecastcategorypicklistdim_4 (cost=0.00..1.05 rows=5 width=8) (actual time=0.002..0.003 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.091 1.091 ↑ 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.091 rows=13,301 loops=1)

61. 0.019 0.019 ↑ 1.0 1 1

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

62. 0.010 0.010 ↑ 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.010 rows=1 loops=1)

63.          

CTE bucketec

64. 0.026 8,292.358 ↑ 279,266.0 1 1

GroupAggregate (cost=1,541,392.86..1,553,959.83 rows=279,266 width=120) (actual time=8,292.358..8,292.358 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.022 8,292.332 ↑ 39,895.1 7 1

Sort (cost=1,540,567.07..1,541,265.23 rows=279,266 width=88) (actual time=8,292.332..8,292.332 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. 322.062 8,292.310 ↑ 39,895.1 7 1

Nested Loop Left Join (cost=448,576.74..1,501,939.17 rows=279,266 width=88) (actual time=6,748.712..8,292.310 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. 85.794 6,750.782 ↓ 1.4 406,121 1

Merge Right Join (cost=446,924.64..456,208.02 rows=286,171 width=28) (actual time=6,462.419..6,750.782 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.313 45.725 ↑ 1.0 66,089 1

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

  • Sort Key: t54_clari_conversion_rate_2.sid, t54_clari_conversion_rate_2.rate_date
69. 0.002 0.010 ↓ 0.0 0 1

Sort (cost=0.01..0.02 rows=1 width=16) (actual time=0.010..0.010 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.008 0.008 ↓ 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.008..0.008 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
71. 40.402 40.402 ↑ 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.019..40.402 rows=66,089 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
72. 43.089 6,619.263 ↓ 1.4 406,121 1

Materialize (cost=446,924.32..448,355.18 rows=286,171 width=28) (actual time=6,458.717..6,619.263 rows=406,121 loops=1)

73. 300.430 6,576.174 ↓ 1.4 406,121 1

Sort (cost=446,924.32..447,639.75 rows=286,171 width=28) (actual time=6,458.714..6,576.174 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. 56.223 6,275.744 ↓ 1.4 406,121 1

Nested Loop (cost=826.31..414,138.40 rows=286,171 width=28) (actual time=4.243..6,275.744 rows=406,121 loops=1)

75. 6.867 7.954 ↓ 66.5 13,301 1

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

  • Group Key: cte0_12.c1
76. 1.087 1.087 ↑ 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.002..1.087 rows=13,301 loops=1)

77. 6,211.567 6,211.567 ↓ 1.6 31 13,301

Index Scan using t52_opp_line_item_fact_296_timestamp on t52_opp_line_item_fact t52_opp_line_item_fact_2 (cost=0.56..2,066.35 rows=20 width=32) (actual time=0.246..0.467 rows=31 loops=13,301)

  • Index Cond: ((c647_opp_ownerid = cte0_12.c1) AND (c647_opp_ownerid IS NOT NULL) AND (start_stamp <= '1575273599999'::bigint) AND (end_stamp > '1575273599999'::bigint))
  • Filter: ((NOT deleted) AND (c658_opp_close_date >= 20191102) AND (c658_opp_close_date <= 20200131))
  • Rows Removed by Filter: 440
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.61 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.002 0.002 ↑ 1.0 1 1

CTE Scan on picklist_1 cte0_9 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 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.100 1.100 ↑ 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.100 rows=13,301 loops=1)

84. 0.020 11,000.694 ↑ 28,698.6 5 1

Hash Left Join (cost=1.11..3,444.94 rows=143,493 width=96) (actual time=10,833.688..11,000.694 rows=5 loops=1)

  • Hash Cond: (cte0.c5 = t58_opportunityforecastcategorypicklistdim.sid)
85. 11,000.659 11,000.659 ↑ 28,698.6 5 1

CTE Scan on bucketac cte0 (cost=0.00..2,869.86 rows=143,493 width=92) (actual time=10,833.657..11,000.659 rows=5 loops=1)

86. 0.005 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
87. 0.010 0.010 ↑ 1.0 5 1

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

88. 0.008 8,292.405 ↑ 279,266.0 1 1

Subquery Scan on *SELECT* 2 (cost=1.11..10,194.33 rows=279,266 width=96) (actual time=8,292.404..8,292.405 rows=1 loops=1)

89. 0.021 8,292.397 ↑ 279,266.0 1 1

Hash Left Join (cost=1.11..6,703.50 rows=279,266 width=120) (actual time=8,292.396..8,292.397 rows=1 loops=1)

  • Hash Cond: (cte0_1.c7 = t58_opportunityforecastcategorypicklistdim_1.sid)
90. 8,292.362 8,292.362 ↑ 279,266.0 1 1

CTE Scan on bucketec cte0_1 (cost=0.00..5,585.32 rows=279,266 width=116) (actual time=8,292.361..8,292.362 rows=1 loops=1)

91. 0.003 0.014 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
92. 0.011 0.011 ↑ 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.011 rows=5 loops=1)

Planning time : 8.945 ms
Execution time : 19,313.939 ms