explain.depesz.com

PostgreSQL's explain analyze made readable

Result: j8ah

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 10,923.504 ↑ 35,306.8 6 1

Append (cost=1,841,377.57..1,848,931.90 rows=211,841 width=120) (actual time=6,562.731..10,923.504 rows=6 loops=1)

2.          

CTE docd_cte_0

3. 19.004 1,634.674 ↑ 2.8 13,301 1

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

  • Group Key: t67_distinct_opp_owner_closedate.c759_ownerid
4. 74.568 1,615.670 ↓ 1.3 90,868 1

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

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

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

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

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

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

8. 229.478 728.697 ↓ 1.0 389,844 1

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

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

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

10. 127.502 357.061 ↓ 1.2 389,678 1

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

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

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

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

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1186kB
14. 9.364 11.323 ↓ 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.067..11.323 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.959 1.959 ↓ 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.959..1.959 rows=26,451 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
16. 93.906 109.789 ↑ 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.670..109.789 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.883 15.883 ↓ 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.883..15.883 rows=188,605 loops=1)

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

CTE picklist_1

19. 0.013 0.013 ↑ 1.0 1 1

Seq Scan on t60_opportunitystagenamepicklistdim (cost=0.00..2.24 rows=1 width=4) (actual time=0.009..0.013 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.007..0.009 rows=1 loops=1)

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

CTE picklist_7

23. 0.024 0.024 ↑ 1.0 2 1

Seq Scan on t60_opportunitystagenamepicklistdim t60_opportunitystagenamepicklistdim_2 (cost=0.00..2.24 rows=2 width=4) (actual time=0.019..0.024 rows=2 loops=1)

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

CTE bucketac

25. 56.683 6,643.089 ↑ 14,318.2 5 1

GroupAggregate (cost=625,005.62..628,764.15 rows=71,591 width=96) (actual time=6,562.696..6,643.089 rows=5 loops=1)

  • Group Key: t56_oppfact.c706_opp_forecastcategory, (CASE WHEN (t56_oppfact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t56_oppfact_1.c713_opp_close_date < 20191102) OR (t56_oppfact_1.c713_opp_close_date > 20200131)) THEN 'slipped'::text WHEN ((t56_oppfact_1.c713_opp_close_date >= 20191102) AND (t56_oppfact_1.c713_opp_close_date <= 20200131) AND (hashed SubPlan 6) AND (t56_oppfact_1.c703_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.008 0.008 ↓ 0.0 0 1

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

  • Filter: (value = 'Value Not Assigned'::text)
  • Rows Removed by Filter: 5
28. 116.248 6,586.406 ↓ 2.5 177,981 1

Sort (cost=624,178.76..624,357.74 rows=71,591 width=72) (actual time=6,548.685..6,586.406 rows=177,981 loops=1)

  • Sort Key: t56_oppfact.c706_opp_forecastcategory, (CASE WHEN (t56_oppfact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t56_oppfact_1.c713_opp_close_date < 20191102) OR (t56_oppfact_1.c713_opp_close_date > 20200131)) THEN 'slipped'::text WHEN ((t56_oppfact_1.c713_opp_close_date >= 20191102) AND (t56_oppfact_1.c713_opp_close_date <= 20200131) AND (hashed SubPlan 6) AND (t56_oppfact_1.c703_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: 11448kB
29. 151.935 6,470.158 ↓ 2.5 177,981 1

Nested Loop Left Join (cost=603,513.60..615,469.34 rows=71,591 width=72) (actual time=6,111.178..6,470.158 rows=177,981 loops=1)

30. 38.983 6,317.154 ↓ 2.5 177,981 1

Hash Left Join (cost=602,687.81..611,600.92 rows=71,591 width=64) (actual time=6,106.967..6,317.154 rows=177,981 loops=1)

  • Hash Cond: (t56_oppfact_1.c706_opp_forecastcategory = t58_opportunityforecastcategorypicklistdim_4.sid)
31. 40.500 6,278.163 ↓ 2.5 177,981 1

Hash Left Join (cost=602,686.70..610,615.43 rows=71,591 width=64) (actual time=6,106.945..6,278.163 rows=177,981 loops=1)

  • Hash Cond: (t56_oppfact.c706_opp_forecastcategory = t58_opportunityforecastcategorypicklistdim_3.sid)
32. 46.736 6,237.648 ↓ 2.5 177,981 1

Merge Right Join (cost=602,685.58..609,629.94 rows=71,591 width=60) (actual time=6,106.896..6,237.648 rows=177,981 loops=1)

  • Merge Cond: ((t54_clari_conversion_rate_1.sid = t56_oppfact_1.c701_opp_currency_code) AND (t54_clari_conversion_rate_1.rate_date = t56_oppfact_1.c713_opp_close_date))
33. 4.624 39.884 ↑ 1.0 66,089 1

Merge Append (cost=0.31..5,835.47 rows=66,109 width=16) (actual time=0.025..39.884 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.006..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. 35.254 35.254 ↑ 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.019..35.254 rows=66,089 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
37. 18.037 6,151.028 ↓ 2.5 177,981 1

Materialize (cost=602,685.27..603,043.23 rows=71,591 width=56) (actual time=6,103.656..6,151.028 rows=177,981 loops=1)

38. 194.213 6,132.991 ↓ 2.5 177,981 1

Sort (cost=602,685.27..602,864.25 rows=71,591 width=56) (actual time=6,103.655..6,132.991 rows=177,981 loops=1)

  • Sort Key: t56_oppfact_1.c701_opp_currency_code, t56_oppfact_1.c713_opp_close_date
  • Sort Method: external sort Disk: 12880kB
39. 127.734 5,938.778 ↓ 2.5 177,981 1

Nested Loop Left Join (cost=233,958.89..594,462.35 rows=71,591 width=56) (actual time=5,174.397..5,938.778 rows=177,981 loops=1)

40. 35.669 5,277.101 ↓ 2.5 177,981 1

Merge Right Join (cost=233,958.33..240,723.71 rows=71,591 width=24) (actual time=5,174.374..5,277.101 rows=177,981 loops=1)

  • Merge Cond: ((t54_clari_conversion_rate.sid = t56_oppfact.c701_opp_currency_code) AND (t54_clari_conversion_rate.rate_date = t56_oppfact.c713_opp_close_date))
41. 4.583 41.832 ↑ 1.0 66,089 1

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

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

Seq Scan on t54_clari_conversion_rate (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)
44. 37.245 37.245 ↑ 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.008..37.245 rows=66,089 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
45. 178.326 5,199.600 ↓ 2.5 177,981 1

Sort (cost=233,958.01..234,136.99 rows=71,591 width=24) (actual time=5,170.915..5,199.600 rows=177,981 loops=1)

  • Sort Key: t56_oppfact.c701_opp_currency_code, t56_oppfact.c713_opp_close_date
  • Sort Method: external sort Disk: 7304kB
46. 23.840 5,021.274 ↓ 2.5 177,981 1

Nested Loop (cost=826.36..228,185.10 rows=71,591 width=24) (actual time=1,641.607..5,021.274 rows=177,981 loops=1)

47. 7.818 1,645.582 ↓ 66.5 13,301 1

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

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

49. 3,351.826 3,351.852 ↓ 2.6 13 13,301

Index Scan using t56_oppfact_239_timestamp on t56_oppfact (cost=0.61..1,136.74 rows=5 width=28) (actual time=0.155..0.252 rows=13 loops=13,301)

  • Index Cond: ((c703_opp_ownerid = cte0_7.c1) AND (c703_opp_ownerid IS NOT NULL) AND (start_stamp <= '1574582400000'::bigint) AND (end_stamp > '1574582400000'::bigint))
  • Filter: ((NOT deleted) AND (c713_opp_close_date >= 20191102) AND (c713_opp_close_date <= 20200131) AND (NOT (hashed SubPlan 9)))
  • Rows Removed by Filter: 270
50.          

SubPlan (for Index Scan)

51. 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.021..0.026 rows=2 loops=1)

52. 533.943 533.943 ↑ 1.0 1 177,981

Index Scan using t56_oppfact_sidendstampunique on t56_oppfact t56_oppfact_1 (cost=0.56..4.93 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=177,981)

  • Index Cond: ((t56_oppfact.sid = sid) AND (t56_oppfact.sid = sid) AND (end_stamp > '1575273599999'::bigint))
  • Filter: ((NOT deleted) AND (start_stamp <= '1575273599999'::bigint))
53. 0.006 0.015 ↑ 1.0 5 1

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

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

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

55. 0.005 0.008 ↑ 1.0 5 1

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

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

57. 0.000 0.000 ↓ 0.0 0 177,981

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

58.          

SubPlan (for Nested Loop Left Join)

59. 1.046 1.046 ↑ 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.000..1.046 rows=13,301 loops=1)

60. 0.014 0.014 ↑ 1.0 1 1

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

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

62.          

CTE bucketec

63. 0.021 4,280.324 ↑ 140,250.0 1 1

GroupAggregate (cost=807,506.61..813,817.86 rows=140,250 width=120) (actual time=4,280.324..4,280.324 rows=1 loops=1)

  • Group Key: (CASE WHEN (t56_oppfact_3.sid IS NULL) THEN 'newBucket'::text WHEN (t56_oppfact_3.c713_opp_close_date > 20200131) THEN 'pulledInBucket'::text WHEN (t56_oppfact_3.c713_opp_close_date < 20191102) THEN 'pushedInBucket'::text WHEN ((t56_oppfact_3.c713_opp_close_date >= 20191102) AND (t56_oppfact_3.c713_opp_close_date <= 20200131) AND (hashed SubPlan 11) AND (t56_oppfact_3.c703_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), t56_oppfact_2.c706_opp_forecastcategory
64. 0.018 4,280.303 ↑ 35,062.5 4 1

Sort (cost=806,680.82..807,031.44 rows=140,250 width=88) (actual time=4,280.303..4,280.303 rows=4 loops=1)

  • Sort Key: (CASE WHEN (t56_oppfact_3.sid IS NULL) THEN 'newBucket'::text WHEN (t56_oppfact_3.c713_opp_close_date > 20200131) THEN 'pulledInBucket'::text WHEN (t56_oppfact_3.c713_opp_close_date < 20191102) THEN 'pushedInBucket'::text WHEN ((t56_oppfact_3.c713_opp_close_date >= 20191102) AND (t56_oppfact_3.c713_opp_close_date <= 20200131) AND (hashed SubPlan 11) AND (t56_oppfact_3.c703_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), t56_oppfact_2.c706_opp_forecastcategory
  • Sort Method: quicksort Memory: 25kB
65. 171.206 4,280.285 ↑ 35,062.5 4 1

Nested Loop Left Join (cost=245,915.54..787,978.10 rows=140,250 width=88) (actual time=3,526.543..4,280.285 rows=4 loops=1)

  • Filter: ((t56_oppfact_3.sid IS NULL) OR (t56_oppfact_3.c713_opp_close_date < 20191102) OR (t56_oppfact_3.c713_opp_close_date > 20200131) OR (NOT (hashed SubPlan 14)) OR (t56_oppfact_3.c703_opp_ownerid IS NULL))
  • Rows Removed by Filter: 198548
66. 44.170 3,512.366 ↓ 1.4 198,552 1

Merge Right Join (cost=244,263.44..251,987.53 rows=143,280 width=28) (actual time=3,357.700..3,512.366 rows=198,552 loops=1)

  • Merge Cond: ((t54_clari_conversion_rate_2.sid = t56_oppfact_2.c701_opp_currency_code) AND (t54_clari_conversion_rate_2.rate_date = t56_oppfact_2.c713_opp_close_date))
67. 4.614 41.383 ↑ 1.0 66,089 1

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

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

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

  • Filter: (to_iso_code = 'USD'::text)
71. 19.966 3,426.813 ↓ 1.4 198,552 1

Materialize (cost=244,263.13..244,979.53 rows=143,280 width=28) (actual time=3,354.206..3,426.813 rows=198,552 loops=1)

72. 170.605 3,406.847 ↓ 1.4 198,552 1

Sort (cost=244,263.13..244,621.33 rows=143,280 width=28) (actual time=3,354.202..3,406.847 rows=198,552 loops=1)

  • Sort Key: t56_oppfact_2.c701_opp_currency_code, t56_oppfact_2.c713_opp_close_date
  • Sort Method: external merge Disk: 8144kB
73. 23.220 3,236.242 ↓ 1.4 198,552 1

Nested Loop (cost=826.31..228,562.29 rows=143,280 width=28) (actual time=4.020..3,236.242 rows=198,552 loops=1)

74. 6.444 7.481 ↓ 66.5 13,301 1

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

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

76. 3,205.541 3,205.541 ↓ 1.5 15 13,301

Index Scan using t56_oppfact_239_timestamp on t56_oppfact t56_oppfact_2 (cost=0.56..1,138.57 rows=10 width=32) (actual time=0.134..0.241 rows=15 loops=13,301)

  • Index Cond: ((c703_opp_ownerid = cte0_12.c1) AND (c703_opp_ownerid IS NOT NULL) AND (start_stamp <= '1575273599999'::bigint) AND (end_stamp > '1575273599999'::bigint))
  • Filter: ((NOT deleted) AND (c713_opp_close_date >= 20191102) AND (c713_opp_close_date <= 20200131))
  • Rows Removed by Filter: 268
77. 595.656 595.656 ↑ 1.0 1 198,552

Index Scan using t56_oppfact_sidendstampunique on t56_oppfact t56_oppfact_3 (cost=0.56..3.69 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=198,552)

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

SubPlan (for Nested Loop Left Join)

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

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

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

82. 1.056 1.056 ↑ 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.056 rows=13,301 loops=1)

83. 0.019 6,643.132 ↑ 14,318.2 5 1

Hash Left Join (cost=1.11..1,719.30 rows=71,591 width=96) (actual time=6,562.730..6,643.132 rows=5 loops=1)

  • Hash Cond: (cte0.c5 = t58_opportunityforecastcategorypicklistdim.sid)
84. 6,643.100 6,643.100 ↑ 14,318.2 5 1

CTE Scan on bucketac cte0 (cost=0.00..1,431.82 rows=71,591 width=92) (actual time=6,562.700..6,643.100 rows=5 loops=1)

85. 0.005 0.013 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
86. 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.008..0.008 rows=5 loops=1)

87. 0.009 4,280.369 ↑ 140,250.0 1 1

Subquery Scan on *SELECT* 2 (cost=1.11..5,120.24 rows=140,250 width=96) (actual time=4,280.367..4,280.369 rows=1 loops=1)

88. 0.018 4,280.360 ↑ 140,250.0 1 1

Hash Left Join (cost=1.11..3,367.11 rows=140,250 width=120) (actual time=4,280.359..4,280.360 rows=1 loops=1)

  • Hash Cond: (cte0_1.c7 = t58_opportunityforecastcategorypicklistdim_1.sid)
89. 4,280.328 4,280.328 ↑ 140,250.0 1 1

CTE Scan on bucketec cte0_1 (cost=0.00..2,805.00 rows=140,250 width=116) (actual time=4,280.327..4,280.328 rows=1 loops=1)

90. 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
91. 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.010..0.011 rows=5 loops=1)

Planning time : 8.633 ms
Execution time : 10,935.628 ms