explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ywey

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 15,961.988 ↑ 4,211.9 64 1

Append (cost=8,947,561.22..8,973,398.74 rows=269,560 width=120) (actual time=7,315.256..15,961.988 rows=64 loops=1)

2.          

CTE picklist_0

3. 0.015 0.015 ↑ 1.0 2 1

Seq Scan on t205_opportunitystagenamepicklistdim t205_opportunitystagenamepicklistdim_2 (cost=0.00..4.80 rows=2 width=4) (actual time=0.010..0.015 rows=2 loops=1)

  • Filter: (correlated_value = ANY ('{"Closed Won","Trial Won"}'::text[]))
  • Rows Removed by Filter: 62
4.          

CTE picklist_1

5. 0.024 0.024 ↑ 1.1 14 1

Seq Scan on t205_opportunitystagenamepicklistdim t205_opportunitystagenamepicklistdim_3 (cost=0.00..5.84 rows=15 width=4) (actual time=0.007..0.024 rows=14 loops=1)

  • Filter: (correlated_value = ANY ('{"16 - Closed / Invalid or Duplicate Oppty","Closed - Dead","Closed - Call later","13 \\u2013 Closed / Lost to No Decision",Churn,"12 - Closed / Lost to Competition","Trial Lost","Closed Lost","15 - Closed / Lost to Churn","14 - Closed / Mid Cycle Qualify Out","13 - Closed / Lost to No Decision","Stage 16 - Closed / Invalid or Duplicate","Closed - Free","11 - Delayed or Deprioritized","Closed - No Decision"}'::text[]))
  • Rows Removed by Filter: 50
6.          

CTE picklist_4

7. 0.048 0.048 ↑ 1.1 16 1

Seq Scan on t205_opportunitystagenamepicklistdim t205_opportunitystagenamepicklistdim_4 (cost=0.00..6.00 rows=17 width=4) (actual time=0.018..0.048 rows=16 loops=1)

  • Filter: (correlated_value = ANY ('{"16 - Closed / Invalid or Duplicate Oppty","Closed - Dead","Closed - Call later","13 \\u2013 Closed / Lost to No Decision",Churn,"12 - Closed / Lost to Competition","Trial Lost","Closed Lost","15 - Closed / Lost to Churn","Closed Won","14 - Closed / Mid Cycle Qualify Out","13 - Closed / Lost to No Decision","Stage 16 - Closed / Invalid or Duplicate","Trial Won","Closed - Free","11 - Delayed or Deprioritized","Closed - No Decision"}'::text[]))
  • Rows Removed by Filter: 48
8.          

CTE ownercte

9. 2.769 17.001 ↑ 2.1 570 1

HashAggregate (cost=3,201.10..3,212.98 rows=1,188 width=4) (actual time=16.939..17.001 rows=570 loops=1)

  • Group Key: distinctclosedateowner.ownerid
10. 3.800 14.232 ↓ 1.2 19,843 1

Hash Join (cost=1,136.51..3,160.69 rows=16,163 width=4) (actual time=4.850..14.232 rows=19,843 loops=1)

  • Hash Cond: (distinctclosedateowner.ownerid = t209_userrolehierarchy.sid)
11. 5.765 7.256 ↓ 1.2 19,843 1

Bitmap Heap Scan on distinctclosedateowner (cost=346.09..2,198.54 rows=16,163 width=4) (actual time=1.660..7.256 rows=19,843 loops=1)

  • Recheck Cond: ((closedate <= 20181231) AND (closedate >= 20181001))
  • Heap Blocks: exact=1353
12. 1.491 1.491 ↓ 1.2 19,843 1

Bitmap Index Scan on ix_distinctclosedateowner_closedate (cost=0.00..342.05 rows=16,163 width=0) (actual time=1.491..1.491 rows=19,843 loops=1)

  • Index Cond: ((closedate <= 20181231) AND (closedate >= 20181001))
13. 0.317 3.176 ↓ 1.4 2,110 1

Hash (cost=771.30..771.30 rows=1,529 width=4) (actual time=3.176..3.176 rows=2,110 loops=1)

  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 107kB
14. 0.681 2.859 ↓ 1.4 2,110 1

HashAggregate (cost=756.01..771.30 rows=1,529 width=4) (actual time=2.647..2.859 rows=2,110 loops=1)

  • Group Key: t209_userrolehierarchy.sid
15. 0.589 2.178 ↓ 1.0 2,559 1

Hash Join (cost=231.84..749.67 rows=2,535 width=4) (actual time=0.767..2.178 rows=2,559 loops=1)

  • Hash Cond: (t209_userrolehierarchy.c808_user_role_id = t37_userroledim.sid)
16. 0.862 1.188 ↓ 1.0 2,559 1

Bitmap Heap Scan on t209_userrolehierarchy (cost=171.12..654.15 rows=2,535 width=8) (actual time=0.359..1.188 rows=2,559 loops=1)

  • Recheck Cond: ((c809_ancestor_role_id = ANY ('{496,30}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=302
17. 0.326 0.326 ↓ 1.0 2,581 1

Bitmap Index Scan on ix_t209_userrolehierarchy_ancestor_timestamp (cost=0.00..170.49 rows=2,535 width=0) (actual time=0.326..0.326 rows=2,581 loops=1)

  • Index Cond: ((c809_ancestor_role_id = ANY ('{496,30}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
18. 0.062 0.401 ↑ 1.2 437 1

Hash (cost=54.43..54.43 rows=503 width=4) (actual time=0.401..0.401 rows=437 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
19. 0.339 0.339 ↑ 1.2 437 1

Seq Scan on t37_userroledim (cost=0.00..54.43 rows=503 width=4) (actual time=0.009..0.339 rows=437 loops=1)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c419_portaltype IS NULL) OR (c419_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 442
20.          

CTE bucketac

21. 201.339 7,689.877 ↑ 7,300.5 41 1

GroupAggregate (cost=3,903,380.15..3,917,597.85 rows=299,320 width=92) (actual time=7,315.206..7,689.877 rows=41 loops=1)

  • Group Key: t203_opp_split_fact.c771_opp_stagename, (CASE WHEN (t203_opp_split_fact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t203_opp_split_fact_1.c782_opp_close_date < 20181001) OR (t203_opp_split_fact_1.c782_opp_close_date > 20181231)) THEN 'slipped'::text WHEN (hashed SubPlan 5) THEN CASE WHEN (hashed SubPlan 6) THEN 'won'::text WHEN (hashed SubPlan 7) THEN 'lost'::text ELSE CASE WHEN (t205_opportunitystagenamepicklistdim_5.correlated_app_order = t205_opportunitystagenamepicklistdim_6.correlated_app_order) THEN 'unchanged'::text WHEN (t205_opportunitystagenamepicklistdim_5.correlated_app_order > t205_opportunitystagenamepicklistdim_6.correlated_app_order) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
22. 490.756 7,488.538 ↓ 1.9 553,871 1

Sort (cost=3,903,353.03..3,904,101.33 rows=299,320 width=72) (actual time=7,313.096..7,488.538 rows=553,871 loops=1)

  • Sort Key: t203_opp_split_fact.c771_opp_stagename, (CASE WHEN (t203_opp_split_fact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t203_opp_split_fact_1.c782_opp_close_date < 20181001) OR (t203_opp_split_fact_1.c782_opp_close_date > 20181231)) THEN 'slipped'::text WHEN (hashed SubPlan 5) THEN CASE WHEN (hashed SubPlan 6) THEN 'won'::text WHEN (hashed SubPlan 7) THEN 'lost'::text ELSE CASE WHEN (t205_opportunitystagenamepicklistdim_5.correlated_app_order = t205_opportunitystagenamepicklistdim_6.correlated_app_order) THEN 'unchanged'::text WHEN (t205_opportunitystagenamepicklistdim_5.correlated_app_order > t205_opportunitystagenamepicklistdim_6.correlated_app_order) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
  • Sort Method: external merge Disk: 34872kB
23. 337.040 6,997.782 ↓ 1.9 553,871 1

Hash Left Join (cost=3,848,380.08..3,863,849.89 rows=299,320 width=72) (actual time=6,219.800..6,997.782 rows=553,871 loops=1)

  • Hash Cond: (t203_opp_split_fact_1.c771_opp_stagename = t205_opportunitystagenamepicklistdim_6.sid)
24. 127.782 6,660.619 ↓ 1.9 553,871 1

Hash Left Join (cost=3,848,347.53..3,854,666.23 rows=299,320 width=60) (actual time=6,219.506..6,660.619 rows=553,871 loops=1)

  • Hash Cond: (t203_opp_split_fact.c771_opp_stagename = t205_opportunitystagenamepicklistdim_5.sid)
25. 178.148 6,532.805 ↓ 1.9 553,871 1

Merge Left Join (cost=3,848,342.09..3,850,747.78 rows=299,320 width=56) (actual time=6,219.460..6,532.805 rows=553,871 loops=1)

  • Merge Cond: ((t203_opp_split_fact_1.c778_opp_currency_code = cur290_t1_r1.sid) AND (t203_opp_split_fact_1.c782_opp_close_date = cur290_t1_r1.rate_date))
26. 645.168 6,302.074 ↓ 1.9 553,871 1

Sort (cost=3,847,033.66..3,847,781.96 rows=299,320 width=52) (actual time=6,209.939..6,302.074 rows=553,871 loops=1)

  • Sort Key: t203_opp_split_fact_1.c778_opp_currency_code, t203_opp_split_fact_1.c782_opp_close_date
  • Sort Method: external merge Disk: 37832kB
27. 292.629 5,656.906 ↓ 1.9 553,871 1

Nested Loop Left Join (cost=1,905,492.22..3,809,574.52 rows=299,320 width=52) (actual time=3,349.526..5,656.906 rows=553,871 loops=1)

28. 169.669 3,702.664 ↓ 1.9 553,871 1

Merge Left Join (cost=1,905,491.65..1,907,897.34 rows=299,320 width=24) (actual time=3,349.507..3,702.664 rows=553,871 loops=1)

  • Merge Cond: ((t203_opp_split_fact.c778_opp_currency_code = cur290_t0_r0.sid) AND (t203_opp_split_fact.c782_opp_close_date = cur290_t0_r0.rate_date))
29. 364.950 3,478.172 ↓ 1.9 553,871 1

Sort (cost=1,904,183.22..1,904,931.52 rows=299,320 width=24) (actual time=3,339.931..3,478.172 rows=553,871 loops=1)

  • Sort Key: t203_opp_split_fact.c778_opp_currency_code, t203_opp_split_fact.c782_opp_close_date
  • Sort Method: external merge Disk: 20496kB
30. 109.651 3,113.222 ↓ 1.9 553,871 1

Hash Join (cost=172,370.83..1,870,819.08 rows=299,320 width=24) (actual time=638.895..3,113.222 rows=553,871 loops=1)

  • Hash Cond: (t203_opp_split_fact.c712_opportunity_split_ownerid = ownercte_1.c1)
31. 2,369.762 2,986.212 ↑ 1.1 554,244 1

Bitmap Heap Scan on t203_opp_split_fact (cost=172,339.60..1,867,006.01 rows=598,639 width=28) (actual time=621.527..2,986.212 rows=554,244 loops=1)

  • Recheck Cond: ((c782_opp_close_date >= 20181001) AND (c782_opp_close_date <= 20181231) AND (start_stamp <= '1540796400000'::bigint) AND (end_stamp > '1540796400000'::bigint))
  • Rows Removed by Index Recheck: 6116773
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 8)))
  • Rows Removed by Filter: 695813
  • Heap Blocks: exact=22307 lossy=189440
32. 616.395 616.395 ↓ 1.1 1,409,365 1

Bitmap Index Scan on ix_t203_opp_split_fact_closedate_timestamp (cost=0.00..172,189.56 rows=1,297,955 width=0) (actual time=616.395..616.395 rows=1,409,365 loops=1)

  • Index Cond: ((c782_opp_close_date >= 20181001) AND (c782_opp_close_date <= 20181231) AND (start_stamp <= '1540796400000'::bigint) AND (end_stamp > '1540796400000'::bigint))
33.          

SubPlan (forBitmap Heap Scan)

34. 0.055 0.055 ↑ 1.1 16 1

CTE Scan on picklist_4 cte0_4 (cost=0.00..0.34 rows=17 width=4) (actual time=0.022..0.055 rows=16 loops=1)

35. 0.073 17.359 ↓ 2.9 570 1

Hash (cost=28.73..28.73 rows=200 width=4) (actual time=17.359..17.359 rows=570 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
36. 0.178 17.286 ↓ 2.9 570 1

HashAggregate (cost=26.73..28.73 rows=200 width=4) (actual time=17.229..17.286 rows=570 loops=1)

  • Group Key: ownercte_1.c1
37. 17.108 17.108 ↑ 2.1 570 1

CTE Scan on ownercte ownercte_1 (cost=0.00..23.76 rows=1,188 width=4) (actual time=16.940..17.108 rows=570 loops=1)

38. 51.181 54.823 ↓ 39.4 566,852 1

Sort (cost=1,308.43..1,344.39 rows=14,387 width=16) (actual time=9.353..54.823 rows=566,852 loops=1)

  • Sort Key: cur290_t0_r0.sid, cur290_t0_r0.rate_date
  • Sort Method: quicksort Memory: 1057kB
39. 0.815 3.642 ↑ 1.0 14,351 1

Append (cost=0.00..314.83 rows=14,387 width=16) (actual time=0.017..3.642 rows=14,351 loops=1)

40. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on clari_conversion_rate cur290_t0_r0 (cost=0.00..0.00 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
41. 2.825 2.825 ↑ 1.0 14,351 1

Seq Scan on usd_clari_conversion_rate cur290_t0_r0_1 (cost=0.00..314.83 rows=14,386 width=16) (actual time=0.014..2.825 rows=14,351 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
42. 1,661.613 1,661.613 ↑ 1.0 1 553,871

Index Scan using ix_t203_opp_split_fact_sid_timestamp on t203_opp_split_fact t203_opp_split_fact_1 (cost=0.57..6.34 rows=1 width=28) (actual time=0.003..0.003 rows=1 loops=553,871)

  • Index Cond: ((t203_opp_split_fact.sid = sid) AND (t203_opp_split_fact.sid = sid) AND (start_stamp <= '1541404799999'::bigint) AND (end_stamp > '1541404799999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
43. 48.887 52.583 ↓ 39.2 563,668 1

Sort (cost=1,308.43..1,344.39 rows=14,387 width=16) (actual time=9.291..52.583 rows=563,668 loops=1)

  • Sort Key: cur290_t1_r1.sid, cur290_t1_r1.rate_date
  • Sort Method: quicksort Memory: 1057kB
44. 0.837 3.696 ↑ 1.0 14,351 1

Append (cost=0.00..314.83 rows=14,387 width=16) (actual time=0.015..3.696 rows=14,351 loops=1)

45. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on clari_conversion_rate cur290_t1_r1 (cost=0.00..0.00 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
46. 2.857 2.857 ↑ 1.0 14,351 1

Seq Scan on usd_clari_conversion_rate cur290_t1_r1_1 (cost=0.00..314.83 rows=14,386 width=16) (actual time=0.012..2.857 rows=14,351 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
47. 0.009 0.032 ↑ 1.0 64 1

Hash (cost=4.64..4.64 rows=64 width=8) (actual time=0.032..0.032 rows=64 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
48. 0.023 0.023 ↑ 1.0 64 1

Seq Scan on t205_opportunitystagenamepicklistdim t205_opportunitystagenamepicklistdim_5 (cost=0.00..4.64 rows=64 width=8) (actual time=0.005..0.023 rows=64 loops=1)

49. 0.010 0.022 ↑ 1.0 64 1

Hash (cost=4.64..4.64 rows=64 width=8) (actual time=0.022..0.022 rows=64 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
50. 0.012 0.012 ↑ 1.0 64 1

Seq Scan on t205_opportunitystagenamepicklistdim t205_opportunitystagenamepicklistdim_6 (cost=0.00..4.64 rows=64 width=8) (actual time=0.003..0.012 rows=64 loops=1)

51.          

SubPlan (forHash Left Join)

52. 0.054 0.054 ↑ 2.1 570 1

CTE Scan on ownercte (cost=0.00..23.76 rows=1,188 width=4) (actual time=0.001..0.054 rows=570 loops=1)

53. 0.017 0.017 ↑ 1.0 2 1

CTE Scan on picklist_0 cte0_2 (cost=0.00..0.04 rows=2 width=4) (actual time=0.012..0.017 rows=2 loops=1)

54. 0.030 0.030 ↑ 1.1 14 1

CTE Scan on picklist_1 cte0_3 (cost=0.00..0.30 rows=15 width=4) (actual time=0.008..0.030 rows=14 loops=1)

55.          

CTE bucketec

56. 42.937 8,271.874 ↑ 23,611.1 23 1

GroupAggregate (cost=5,003,648.44..5,026,728.32 rows=543,056 width=116) (actual time=8,209.711..8,271.874 rows=23 loops=1)

  • Group Key: (CASE WHEN (t203_opp_split_fact_3.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 10)) THEN CASE WHEN (t203_opp_split_fact_3.c782_opp_close_date > 20181231) THEN 'pulledInBucket'::text WHEN (t203_opp_split_fact_3.c782_opp_close_date < 20181001) THEN 'pushedInBucket'::text WHEN (hashed SubPlan 11) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END ELSE 'otherBucket'::text END), (CASE WHEN (hashed SubPlan 12) THEN 'won'::text WHEN (hashed SubPlan 13) THEN 'lost'::text ELSE 'newOpen'::text END), t203_opp_split_fact_2.c771_opp_stagename
57. 306.166 8,228.937 ↑ 4.1 133,517 1

Sort (cost=5,003,620.94..5,004,978.58 rows=543,056 width=88) (actual time=8,209.664..8,228.937 rows=133,517 loops=1)

  • Sort Key: (CASE WHEN (t203_opp_split_fact_3.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 10)) THEN CASE WHEN (t203_opp_split_fact_3.c782_opp_close_date > 20181231) THEN 'pulledInBucket'::text WHEN (t203_opp_split_fact_3.c782_opp_close_date < 20181001) THEN 'pushedInBucket'::text WHEN (hashed SubPlan 11) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END ELSE 'otherBucket'::text END), (CASE WHEN (hashed SubPlan 12) THEN 'won'::text WHEN (hashed SubPlan 13) THEN 'lost'::text ELSE 'newOpen'::text END), t203_opp_split_fact_2.c771_opp_stagename
  • Sort Method: external merge Disk: 7352kB
58. 418.248 7,922.771 ↑ 4.1 133,517 1

Nested Loop Left Join (cost=1,931,547.36..4,899,917.85 rows=543,056 width=88) (actual time=3,731.570..7,922.771 rows=133,517 loops=1)

  • Filter: ((t203_opp_split_fact_3.sid IS NULL) OR (t203_opp_split_fact_3.c782_opp_close_date < 20181001) OR (t203_opp_split_fact_3.c782_opp_close_date > 20181231) OR (NOT (hashed SubPlan 14)))
  • Rows Removed by Filter: 905526
59. 306.597 4,387.334 ↓ 1.8 1,039,043 1

Merge Left Join (cost=1,931,492.57..1,936,106.39 rows=586,960 width=24) (actual time=3,724.743..4,387.334 rows=1,039,043 loops=1)

  • Merge Cond: ((t203_opp_split_fact_2.c778_opp_currency_code = cur290_t0_r0_2.sid) AND (t203_opp_split_fact_2.c782_opp_close_date = cur290_t0_r0_2.rate_date))
60. 737.866 3,983.587 ↓ 1.8 1,039,043 1

Sort (cost=1,930,184.14..1,931,651.54 rows=586,960 width=24) (actual time=3,715.429..3,983.587 rows=1,039,043 loops=1)

  • Sort Key: t203_opp_split_fact_2.c778_opp_currency_code, t203_opp_split_fact_2.c782_opp_close_date
  • Sort Method: external merge Disk: 38616kB
61. 210.576 3,245.721 ↓ 1.8 1,039,043 1

Hash Join (cost=172,514.27..1,861,904.86 rows=586,960 width=24) (actual time=695.685..3,245.721 rows=1,039,043 loops=1)

  • Hash Cond: (t203_opp_split_fact_2.c712_opportunity_split_ownerid = ownercte_4.c1)
62. 2,349.966 3,034.855 ↑ 1.1 1,039,720 1

Bitmap Heap Scan on t203_opp_split_fact t203_opp_split_fact_2 (cost=172,483.04..1,854,457.50 rows=1,173,921 width=28) (actual time=695.380..3,034.855 rows=1,039,720 loops=1)

  • Recheck Cond: ((c782_opp_close_date >= 20181001) AND (c782_opp_close_date <= 20181231) AND (start_stamp <= '1541404799999'::bigint) AND (end_stamp > '1541404799999'::bigint))
  • Rows Removed by Index Recheck: 6064354
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 419440
  • Heap Blocks: exact=46076 lossy=189370
63. 684.889 684.889 ↓ 1.3 1,666,451 1

Bitmap Index Scan on ix_t203_opp_split_fact_closedate_timestamp (cost=0.00..172,189.56 rows=1,272,635 width=0) (actual time=684.889..684.889 rows=1,666,451 loops=1)

  • Index Cond: ((c782_opp_close_date >= 20181001) AND (c782_opp_close_date <= 20181231) AND (start_stamp <= '1541404799999'::bigint) AND (end_stamp > '1541404799999'::bigint))
64. 0.080 0.290 ↓ 2.9 570 1

Hash (cost=28.73..28.73 rows=200 width=4) (actual time=0.290..0.290 rows=570 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
65. 0.168 0.210 ↓ 2.9 570 1

HashAggregate (cost=26.73..28.73 rows=200 width=4) (actual time=0.165..0.210 rows=570 loops=1)

  • Group Key: ownercte_4.c1
66. 0.042 0.042 ↑ 2.1 570 1

CTE Scan on ownercte ownercte_4 (cost=0.00..23.76 rows=1,188 width=4) (actual time=0.001..0.042 rows=570 loops=1)

67. 93.477 97.150 ↓ 73.1 1,051,960 1

Sort (cost=1,308.43..1,344.39 rows=14,387 width=16) (actual time=9.058..97.150 rows=1,051,960 loops=1)

  • Sort Key: cur290_t0_r0_2.sid, cur290_t0_r0_2.rate_date
  • Sort Method: quicksort Memory: 1057kB
68. 0.837 3.673 ↑ 1.0 14,351 1

Append (cost=0.00..314.83 rows=14,387 width=16) (actual time=0.020..3.673 rows=14,351 loops=1)

69. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on clari_conversion_rate cur290_t0_r0_2 (cost=0.00..0.00 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
70. 2.834 2.834 ↑ 1.0 14,351 1

Seq Scan on usd_clari_conversion_rate cur290_t0_r0_3 (cost=0.00..314.83 rows=14,386 width=16) (actual time=0.016..2.834 rows=14,351 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
71. 3,117.129 3,117.129 ↑ 1.0 1 1,039,043

Index Scan using ix_t203_opp_split_fact_sid_timestamp on t203_opp_split_fact t203_opp_split_fact_3 (cost=0.57..5.02 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=1,039,043)

  • Index Cond: ((t203_opp_split_fact_2.sid = sid) AND (t203_opp_split_fact_2.sid = sid) AND (start_stamp <= '1540796400000'::bigint) AND (end_stamp > '1540796400000'::bigint))
  • Filter: (NOT deleted)
72.          

SubPlan (forNested Loop Left Join)

73. 0.002 0.002 ↑ 1.1 16 1

CTE Scan on picklist_4 cte0_5 (cost=0.00..0.34 rows=17 width=4) (actual time=0.001..0.002 rows=16 loops=1)

74. 0.000 0.000 ↓ 0.0 0

CTE Scan on ownercte ownercte_2 (cost=0.00..23.76 rows=1,188 width=4) (never executed)

75. 0.001 0.001 ↑ 1.0 2 1

CTE Scan on picklist_0 cte0_6 (cost=0.00..0.04 rows=2 width=4) (actual time=0.000..0.001 rows=2 loops=1)

76. 0.003 0.003 ↑ 1.1 14 1

CTE Scan on picklist_1 cte0_7 (cost=0.00..0.30 rows=15 width=4) (actual time=0.001..0.003 rows=14 loops=1)

77. 0.054 0.054 ↑ 2.1 570 1

CTE Scan on ownercte ownercte_3 (cost=0.00..23.76 rows=1,188 width=4) (actual time=0.001..0.054 rows=570 loops=1)

78. 0.042 7,689.985 ↑ 2,336.1 41 1

Hash Join (cost=5.44..8,072.11 rows=95,782 width=107) (actual time=7,315.254..7,689.985 rows=41 loops=1)

  • Hash Cond: (cte0.c4 = t205_opportunitystagenamepicklistdim.sid)
79. 7,689.908 7,689.908 ↑ 7,300.5 41 1

CTE Scan on bucketac cte0 (cost=0.00..5,986.40 rows=299,320 width=92) (actual time=7,315.208..7,689.908 rows=41 loops=1)

80. 0.015 0.035 ↑ 1.0 64 1

Hash (cost=4.64..4.64 rows=64 width=23) (actual time=0.035..0.035 rows=64 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
81. 0.020 0.020 ↑ 1.0 64 1

Seq Scan on t205_opportunitystagenamepicklistdim (cost=0.00..4.64 rows=64 width=23) (actual time=0.008..0.020 rows=64 loops=1)

82. 0.020 8,271.995 ↑ 7,555.6 23 1

Subquery Scan on *SELECT* 2 (cost=5.44..16,813.03 rows=173,778 width=107) (actual time=8,209.794..8,271.995 rows=23 loops=1)

83. 0.027 8,271.975 ↑ 7,555.6 23 1

Hash Join (cost=5.44..14,640.80 rows=173,778 width=131) (actual time=8,209.786..8,271.975 rows=23 loops=1)

  • Hash Cond: (cte0_1.c6 = t205_opportunitystagenamepicklistdim_1.sid)
84. 8,271.890 8,271.890 ↑ 23,611.1 23 1

CTE Scan on bucketec cte0_1 (cost=0.00..10,861.12 rows=543,056 width=116) (actual time=8,209.714..8,271.890 rows=23 loops=1)

85. 0.019 0.058 ↑ 1.0 64 1

Hash (cost=4.64..4.64 rows=64 width=23) (actual time=0.058..0.058 rows=64 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
86. 0.039 0.039 ↑ 1.0 64 1

Seq Scan on t205_opportunitystagenamepicklistdim t205_opportunitystagenamepicklistdim_1 (cost=0.00..4.64 rows=64 width=23) (actual time=0.010..0.039 rows=64 loops=1)