explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6Fpd

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 4,769.891 ↑ 34,294.8 6 1

Append (cost=1,384,399.22..1,391,737.05 rows=205,769 width=120) (actual time=3,391.063..4,769.891 rows=6 loops=1)

2.          

CTE docd_cte_0

3. 18.109 1,572.454 ↑ 2.8 13,301 1

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

  • Group Key: t67_distinct_opp_owner_closedate.c759_ownerid
4. 70.804 1,554.345 ↓ 1.3 90,868 1

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

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

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

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

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

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

8. 224.477 705.551 ↓ 1.0 389,844 1

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

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

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

10. 124.805 333.510 ↓ 1.2 389,678 1

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

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

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

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

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1186kB
14. 7.823 9.566 ↓ 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.837..9.566 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.743 1.743 ↓ 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.743..1.743 rows=26,451 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
16. 101.152 117.107 ↑ 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.657..117.107 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.955 15.955 ↓ 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.955..15.955 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.48 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.007 0.007 ↑ 1.0 1 1

Seq Scan on t60_opportunitystagenamepicklistdim t60_opportunitystagenamepicklistdim_1 (cost=0.00..2.48 rows=1 width=4) (actual time=0.004..0.007 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.48 rows=2 width=4) (actual time=0.018..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.806 3,469.020 ↑ 13,907.8 5 1

GroupAggregate (cost=398,362.28..402,013.08 rows=69,539 width=96) (actual time=3,391.030..3,469.020 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.023 0.023 ↓ 0.0 0 1

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

  • Filter: (value = 'Value Not Assigned'::text)
  • Rows Removed by Filter: 5
28. 114.722 3,412.214 ↓ 2.6 177,981 1

Sort (cost=397,535.42..397,709.27 rows=69,539 width=72) (actual time=3,377.532..3,412.214 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. 150.426 3,297.492 ↓ 2.6 177,981 1

Nested Loop Left Join (cost=377,300.28..389,090.06 rows=69,539 width=72) (actual time=2,934.635..3,297.492 rows=177,981 loops=1)

30. 42.861 3,145.964 ↓ 2.6 177,981 1

Hash Left Join (cost=376,474.48..385,308.85 rows=69,539 width=64) (actual time=2,930.610..3,145.964 rows=177,981 loops=1)

  • Hash Cond: (t56_oppfact_1.c706_opp_forecastcategory = t58_opportunityforecastcategorypicklistdim_4.sid)
31. 40.517 3,103.097 ↓ 2.6 177,981 1

Hash Left Join (cost=376,473.37..384,351.57 rows=69,539 width=64) (actual time=2,930.591..3,103.097 rows=177,981 loops=1)

  • Hash Cond: (t56_oppfact.c706_opp_forecastcategory = t58_opportunityforecastcategorypicklistdim_3.sid)
32. 47.607 3,062.567 ↓ 2.6 177,981 1

Merge Right Join (cost=376,472.26..383,394.30 rows=69,539 width=60) (actual time=2,930.550..3,062.567 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. 5.854 42.187 ↑ 1.0 66,089 1

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

  • Sort Key: t54_clari_conversion_rate_1.sid, t54_clari_conversion_rate_1.rate_date
34. 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_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. 36.328 36.328 ↑ 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.016..36.328 rows=66,089 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
37. 17.549 2,972.773 ↓ 2.6 177,981 1

Materialize (cost=376,471.95..376,819.64 rows=69,539 width=56) (actual time=2,927.084..2,972.773 rows=177,981 loops=1)

38. 192.133 2,955.224 ↓ 2.6 177,981 1

Sort (cost=376,471.95..376,645.79 rows=69,539 width=56) (actual time=2,927.083..2,955.224 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. 135.140 2,763.091 ↓ 2.6 177,981 1

Nested Loop Left Join (cost=14,031.75..368,499.09 rows=69,539 width=56) (actual time=1,990.760..2,763.091 rows=177,981 loops=1)

40. 36.423 2,094.008 ↓ 2.6 177,981 1

Merge Right Join (cost=14,031.19..20,779.38 rows=69,539 width=24) (actual time=1,990.739..2,094.008 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. 5.693 43.422 ↑ 1.0 66,089 1

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

  • Filter: (to_iso_code = 'USD'::text)
45. 156.434 2,014.163 ↓ 2.6 177,981 1

Sort (cost=14,030.87..14,204.72 rows=69,539 width=24) (actual time=1,987.154..2,014.163 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. 24.614 1,857.729 ↓ 2.6 177,981 1

Nested Loop (cost=826.36..8,438.01 rows=69,539 width=24) (actual time=1,578.183..1,857.729 rows=177,981 loops=1)

47. 5.483 1,580.396 ↓ 66.5 13,301 1

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

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

49. 252.694 252.719 ↓ 2.6 13 13,301

Index Scan using ix_t56_oppfact_notnullowner_closedate_timestamp on t56_oppfact (cost=0.61..38.00 rows=5 width=28) (actual time=0.006..0.019 rows=13 loops=13,301)

  • Index Cond: ((c703_opp_ownerid = cte0_7.c1) AND (c713_opp_close_date >= 20191102) AND (c713_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: 2
50.          

SubPlan (for Index Scan)

51. 0.025 0.025 ↑ 1.0 2 1

CTE Scan on picklist_7 cte0_5 (cost=0.00..0.04 rows=2 width=4) (actual time=0.019..0.025 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.99 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.005 0.013 ↑ 1.0 5 1

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

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

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

55. 0.004 0.006 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
56. 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.002..0.002 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.080 1.080 ↑ 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.080 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.008 0.008 ↑ 1.0 1 1

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

62.          

CTE bucketec

63. 0.017 1,300.795 ↑ 136,230.0 1 1

GroupAggregate (cost=577,459.52..583,589.87 rows=136,230 width=120) (actual time=1,300.795..1,300.795 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 1,300.778 ↑ 34,057.5 4 1

Sort (cost=576,633.73..576,974.30 rows=136,230 width=88) (actual time=1,300.778..1,300.778 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. 183.803 1,300.760 ↑ 34,057.5 4 1

Nested Loop Left Join (cost=25,306.94..558,495.75 rows=136,230 width=88) (actual time=537.624..1,300.760 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.071 520.252 ↓ 1.4 198,552 1

Merge Right Join (cost=23,654.83..31,334.26 rows=139,174 width=28) (actual time=367.420..520.252 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. 5.298 43.004 ↑ 1.0 66,089 1

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

  • Filter: (to_iso_code = 'USD'::text)
71. 21.678 433.177 ↓ 1.4 198,552 1

Materialize (cost=23,654.52..24,350.39 rows=139,174 width=28) (actual time=363.782..433.177 rows=198,552 loops=1)

72. 146.849 411.499 ↓ 1.4 198,552 1

Sort (cost=23,654.52..24,002.45 rows=139,174 width=28) (actual time=363.779..411.499 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. 32.319 264.650 ↓ 1.4 198,552 1

Nested Loop (cost=826.31..8,432.51 rows=139,174 width=28) (actual time=3.858..264.650 rows=198,552 loops=1)

74. 5.146 6.214 ↓ 66.5 13,301 1

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

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

76. 226.117 226.117 ↓ 1.7 15 13,301

Index Scan using ix_t56_oppfact_notnullowner_closedate_timestamp on t56_oppfact t56_oppfact_2 (cost=0.56..37.93 rows=9 width=32) (actual time=0.005..0.017 rows=15 loops=13,301)

  • Index Cond: ((c703_opp_ownerid = cte0_12.c1) AND (c713_opp_close_date >= 20191102) AND (c713_opp_close_date <= 20200131) AND (end_stamp > '1575273599999'::bigint) AND (start_stamp <= '1575273599999'::bigint))
  • Filter: (NOT deleted)
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.74 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.048 1.048 ↑ 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.048 rows=13,301 loops=1)

83. 0.019 3,469.060 ↑ 13,907.8 5 1

Hash Left Join (cost=1.11..1,670.04 rows=69,539 width=96) (actual time=3,391.062..3,469.060 rows=5 loops=1)

  • Hash Cond: (cte0.c5 = t58_opportunityforecastcategorypicklistdim.sid)
84. 3,469.026 3,469.026 ↑ 13,907.8 5 1

CTE Scan on bucketac cte0 (cost=0.00..1,390.78 rows=69,539 width=92) (actual time=3,391.032..3,469.026 rows=5 loops=1)

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

87. 0.005 1,300.828 ↑ 136,230.0 1 1

Subquery Scan on *SELECT* 2 (cost=1.11..4,973.51 rows=136,230 width=96) (actual time=1,300.827..1,300.828 rows=1 loops=1)

88. 0.015 1,300.823 ↑ 136,230.0 1 1

Hash Left Join (cost=1.11..3,270.63 rows=136,230 width=120) (actual time=1,300.822..1,300.823 rows=1 loops=1)

  • Hash Cond: (cte0_1.c7 = t58_opportunityforecastcategorypicklistdim_1.sid)
89. 1,300.797 1,300.797 ↑ 136,230.0 1 1

CTE Scan on bucketec cte0_1 (cost=0.00..2,724.60 rows=136,230 width=116) (actual time=1,300.797..1,300.797 rows=1 loops=1)

90. 0.001 0.011 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
91. 0.010 0.010 ↑ 1.0 5 1

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

Planning time : 8.608 ms
Execution time : 4,782.570 ms