explain.depesz.com

PostgreSQL's explain analyze made readable

Result: w7Vz

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 9,741.934 ↑ 2,478.0 44 1

Append (cost=6,227,967,851.70..6,228,073,782.07 rows=109,034 width=144) (actual time=6,296.052..9,741.934 rows=44 loops=1)

2.          

CTE picklist_0

3. 0.048 0.048 ↑ 1.2 6 1

Index Only Scan using ix_gr_t233_opportunitystagenamepicklistdim on t233_opportunitystagenamepicklistdim (cost=0.28..25.78 rows=7 width=4) (actual time=0.020..0.048 rows=6 loops=1)

  • Index Cond: (correlated_value = ANY ('{"06 - Won, Deploy & Expand","6 - Won, Deploy & Expand","Closed - Won","06 - Won. Deploy & Expand",Won,"06 - Won, Deploy & Expand"}'::text[]))
  • Heap Fetches: 0
4.          

CTE picklist_1

5. 0.021 0.021 ↑ 1.0 7 1

Index Only Scan using ix_gr_t233_opportunitystagenamepicklistdim on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_1 (cost=0.28..26.06 rows=7 width=4) (actual time=0.004..0.021 rows=7 loops=1)

  • Index Cond: (correlated_value = ANY ('{"HPE Not Pursued",Lost,Close:Duplicate,"HP Not Pursued",Duplicate,Error,"Closed - Lost"}'::text[]))
  • Heap Fetches: 0
6.          

CTE picklist_4

7. 0.066 0.066 ↑ 1.1 13 1

Index Only Scan using ix_gr_t233_opportunitystagenamepicklistdim on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_2 (cost=0.28..43.84 rows=14 width=4) (actual time=0.020..0.066 rows=13 loops=1)

  • Index Cond: (correlated_value = ANY ('{"06 - Won, Deploy & Expand",Lost,"6 - Won, Deploy & Expand",Close:Duplicate,"HP Not Pursued","06 - Won, Deploy & Expand",Error,"HPE Not Pursued","Closed - Won","06 - Won. Deploy & Expand",Won,Duplicate,"Closed - Lost"}'::text[]))
  • Heap Fetches: 0
8.          

CTE ownercte

9. 7.297 1,800.901 ↑ 2.9 8,739 1

Unique (cost=608,901.33..758,942.12 rows=25,092 width=4) (actual time=1,645.711..1,800.901 rows=8,739 loops=1)

10. 41.098 1,793.604 ↓ 1.1 132,351 1

Merge Join (cost=608,901.33..758,643.52 rows=119,442 width=4) (actual time=1,645.710..1,793.604 rows=132,351 loops=1)

  • Merge Cond: (t728_userrolehierarchy.sid = t629_distinctcdo_opp_closedate_ownerid.c2616_ownerid)
11. 16.387 783.203 ↑ 9.6 137,320 1

Unique (cost=420,315.97..426,880.11 rows=1,312,828 width=4) (actual time=748.597..783.203 rows=137,320 loops=1)

12. 80.795 766.816 ↑ 9.0 145,810 1

Sort (cost=420,315.97..423,598.04 rows=1,312,828 width=4) (actual time=748.597..766.816 rows=145,810 loops=1)

  • Sort Key: t728_userrolehierarchy.sid
  • Sort Method: external merge Disk: 1992kB
13. 9.811 686.021 ↑ 9.0 145,937 1

Append (cost=60,794.50..268,953.27 rows=1,312,828 width=4) (actual time=154.862..686.021 rows=145,937 loops=1)

14. 207.317 662.653 ↑ 10.0 131,217 1

Hash Join (cost=60,794.50..244,439.61 rows=1,309,460 width=4) (actual time=154.861..662.653 rows=131,217 loops=1)

  • Hash Cond: (t728_userrolehierarchy.c2842_user_role_id = t67_userroledim.sid)
15. 309.599 442.734 ↓ 1.0 1,338,682 1

Bitmap Heap Scan on t728_userrolehierarchy (cost=39,434.52..203,485.42 rows=1,309,460 width=8) (actual time=140.793..442.734 rows=1,338,682 loops=1)

  • Recheck Cond: ((c2843_ancestor_role_id = 436) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=40597
16. 133.135 133.135 ↓ 1.0 1,339,843 1

Bitmap Index Scan on idx_t728_userrolehierarchy_c2843_ancestor_role_id_end_s36994704 (cost=0.00..39,107.16 rows=1,309,460 width=0) (actual time=133.135..133.135 rows=1,339,843 loops=1)

  • Index Cond: ((c2843_ancestor_role_id = 436) AND (end_stamp = '32503680000000'::bigint))
17. 1.363 12.602 ↑ 9.1 8,424 1

Hash (cost=20,098.76..20,098.76 rows=76,817 width=4) (actual time=12.602..12.602 rows=8,424 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 1178kB
18. 7.005 11.239 ↑ 9.1 8,424 1

Bitmap Heap Scan on t67_userroledim (cost=4,145.56..20,098.76 rows=76,817 width=4) (actual time=5.146..11.239 rows=8,424 loops=1)

  • Recheck Cond: ((end_stamp = '32503680000000'::bigint) AND ((c377_portaltype IS NULL) OR (c377_portaltype = 'None'::text)))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
  • Rows Removed by Filter: 7043
  • Heap Blocks: exact=2254
19. 4.234 4.234 ↑ 5.0 15,482 1

Bitmap Index Scan on ix_t67_userroledim_sid_end_start_timestamp_partial (cost=0.00..4,126.35 rows=77,840 width=0) (actual time=4.234..4.234 rows=15,482 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
20. 13.557 13.557 ↓ 4.4 14,720 1

Index Scan using ix_t12_userdim_usertype_nullrole_end_start on t12_userdim (cost=0.29..11,385.38 rows=3,368 width=4) (actual time=0.283..13.557 rows=14,720 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
21. 26.022 969.303 ↓ 1.2 286,250 1

Materialize (cost=188,585.36..189,779.79 rows=238,885 width=4) (actual time=895.801..969.303 rows=286,250 loops=1)

22. 161.765 943.281 ↓ 1.2 286,250 1

Sort (cost=188,585.36..189,182.57 rows=238,885 width=4) (actual time=895.799..943.281 rows=286,250 loops=1)

  • Sort Key: t629_distinctcdo_opp_closedate_ownerid.c2616_ownerid
  • Sort Method: external merge Disk: 3904kB
23. 781.516 781.516 ↓ 1.2 286,250 1

Seq Scan on t629_distinctcdo_opp_closedate_ownerid (cost=0.00..163,976.82 rows=238,885 width=4) (actual time=0.152..781.516 rows=286,250 loops=1)

  • Filter: ((c2620_closedate >= 20181101) AND (c2620_closedate <= 20190131))
  • Rows Removed by Filter: 6192650
24.          

CTE bucketac

25. 20.588 6,327.636 ↑ 89,702.7 19 1

GroupAggregate (cost=3,629,095,623.75..3,629,172,319.59 rows=1,704,352 width=116) (actual time=6,296.023..6,327.636 rows=19 loops=1)

  • Group Key: t236_opp_line_item_fact.c820_opp_forecastcategory, (CASE WHEN (oli174_ft1.c1 IS NULL) THEN 'deleted'::text WHEN ((oli174_ft1.c2 < 20181101) OR (oli174_ft1.c2 > 20190131)) 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 (t234_opportunityforecastcategorypicklistdim_2.correlated_app_order = oli174_ft1.c7) THEN 'unchanged'::text WHEN (t234_opportunityforecastcategorypicklistdim_2.correlated_app_order > oli174_ft1.c7) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
26. 112.838 6,307.048 ↑ 18.4 92,561 1

Sort (cost=3,629,095,058.86..3,629,099,319.74 rows=1,704,352 width=60) (actual time=6,295.968..6,307.048 rows=92,561 loops=1)

  • Sort Key: t236_opp_line_item_fact.c820_opp_forecastcategory, (CASE WHEN (oli174_ft1.c1 IS NULL) THEN 'deleted'::text WHEN ((oli174_ft1.c2 < 20181101) OR (oli174_ft1.c2 > 20190131)) 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 (t234_opportunityforecastcategorypicklistdim_2.correlated_app_order = oli174_ft1.c7) THEN 'unchanged'::text WHEN (t234_opportunityforecastcategorypicklistdim_2.correlated_app_order > oli174_ft1.c7) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
  • Sort Method: external merge Disk: 4504kB
27. 53.662 6,194.210 ↑ 18.4 92,561 1

Nested Loop Left Join (cost=620,491.15..3,628,790,488.68 rows=1,704,352 width=60) (actual time=3,227.999..6,194.210 rows=92,561 loops=1)

28. 128.228 3,455.196 ↑ 11.1 92,561 1

GroupAggregate (cost=616,417.30..644,737.35 rows=1,029,820 width=28) (actual time=3,225.059..3,455.196 rows=92,561 loops=1)

  • Group Key: t236_opp_line_item_fact.c801_opportunity_sid, t236_opp_line_item_fact.c820_opp_forecastcategory, t234_opportunityforecastcategorypicklistdim_2.correlated_app_order
29. 269.926 3,326.968 ↑ 2.5 419,442 1

Sort (cost=616,417.30..618,991.85 rows=1,029,820 width=32) (actual time=3,225.045..3,326.968 rows=419,442 loops=1)

  • Sort Key: t236_opp_line_item_fact.c801_opportunity_sid, t236_opp_line_item_fact.c820_opp_forecastcategory, t234_opportunityforecastcategorypicklistdim_2.correlated_app_order
  • Sort Method: external merge Disk: 17192kB
30. 79.852 3,057.042 ↑ 2.5 419,442 1

Hash Left Join (cost=453,146.60..488,929.38 rows=1,029,820 width=32) (actual time=2,707.696..3,057.042 rows=419,442 loops=1)

  • Hash Cond: (t236_opp_line_item_fact.c820_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim_2.sid)
31. 82.525 2,977.180 ↑ 2.5 419,442 1

Merge Right Join (cost=453,145.49..475,304.43 rows=1,029,820 width=28) (actual time=2,707.672..2,977.180 rows=419,442 loops=1)

  • Merge Cond: ((t716_clari_conversion_rate.sid = t236_opp_line_item_fact.c821_opp_currency_code) AND (t716_clari_conversion_rate.rate_date = t236_opp_line_item_fact.c805_opp_close_date))
32. 6.899 47.995 ↑ 1.0 104,995 1

Merge Append (cost=0.31..7,779.93 rows=105,358 width=16) (actual time=0.021..47.995 rows=104,995 loops=1)

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

Seq Scan on t716_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)
35. 41.091 41.091 ↑ 1.0 104,995 1

Index Scan using t716_clari_conversion_rate_usd_sidratedateunique on t716_clari_conversion_rate_usd (cost=0.29..6,462.93 rows=105,357 width=16) (actual time=0.016..41.091 rows=104,995 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
36. 36.527 2,846.660 ↑ 2.5 419,442 1

Materialize (cost=453,145.17..458,294.27 rows=1,029,820 width=28) (actual time=2,706.514..2,846.660 rows=419,442 loops=1)

37. 264.076 2,810.133 ↑ 2.5 419,442 1

Sort (cost=453,145.17..455,719.72 rows=1,029,820 width=28) (actual time=2,706.513..2,810.133 rows=419,442 loops=1)

  • Sort Key: t236_opp_line_item_fact.c821_opp_currency_code, t236_opp_line_item_fact.c805_opp_close_date
  • Sort Method: external merge Disk: 15512kB
38. 56.752 2,546.057 ↑ 2.5 419,442 1

Nested Loop (cost=565.45..325,657.25 rows=1,029,820 width=28) (actual time=1,804.902..2,546.057 rows=419,442 loops=1)

39. 5.494 1,807.663 ↓ 43.7 8,739 1

HashAggregate (cost=564.57..566.57 rows=200 width=4) (actual time=1,804.776..1,807.663 rows=8,739 loops=1)

  • Group Key: ownercte_1.c1
40. 1,802.169 1,802.169 ↑ 2.9 8,739 1

CTE Scan on ownercte ownercte_1 (cost=0.00..501.84 rows=25,092 width=4) (actual time=1,645.713..1,802.169 rows=8,739 loops=1)

41. 681.571 681.642 ↑ 3.8 48 8,739

Index Scan using ix_t236_opp_line_item_fact_owner_closedate_timestamp on t236_opp_line_item_fact (cost=0.88..1,623.62 rows=183 width=32) (actual time=0.014..0.078 rows=48 loops=8,739)

  • Index Cond: ((c804_opp_ownerid = ownercte_1.c1) AND (c804_opp_ownerid IS NOT NULL) AND (c805_opp_close_date >= 20181101) AND (c805_opp_close_date <= 20190131) AND (start_stamp <= '1541055600000'::bigint) AND (end_stamp > '1541055600000'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 8)))
  • Rows Removed by Filter: 22
42.          

SubPlan (forIndex Scan)

43. 0.071 0.071 ↑ 1.1 13 1

CTE Scan on picklist_4 cte0_4 (cost=0.00..0.28 rows=14 width=4) (actual time=0.021..0.071 rows=13 loops=1)

44. 0.004 0.010 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
45. 0.006 0.006 ↑ 1.0 5 1

Seq Scan on t234_opportunityforecastcategorypicklistdim t234_opportunityforecastcategorypicklistdim_2 (cost=0.00..1.05 rows=5 width=8) (actual time=0.005..0.006 rows=5 loops=1)

46. 92.561 2,684.269 ↑ 2.0 1 92,561

Subquery Scan on oli174_ft1 (cost=3,508.96..3,523.03 rows=2 width=28) (actual time=0.028..0.029 rows=1 loops=92,561)

  • Filter: (t236_opp_line_item_fact.c801_opportunity_sid = oli174_ft1.c1)
47. 185.122 2,591.708 ↑ 331.0 1 92,561

GroupAggregate (cost=3,508.96..3,518.89 rows=331 width=36) (actual time=0.028..0.028 rows=1 loops=92,561)

  • Group Key: t236_opp_line_item_fact_1.c801_opportunity_sid, t236_opp_line_item_fact_1.c805_opp_close_date, t236_opp_line_item_fact_1.c804_opp_ownerid, t236_opp_line_item_fact_1.c819_opp_stagename, t234_opportunityforecastcategorypicklistdim_3.correlated_app_order
48. 277.683 2,406.586 ↑ 66.2 5 92,561

Sort (cost=3,508.96..3,509.79 rows=331 width=36) (actual time=0.026..0.026 rows=5 loops=92,561)

  • Sort Key: t236_opp_line_item_fact_1.c805_opp_close_date, t236_opp_line_item_fact_1.c804_opp_ownerid, t236_opp_line_item_fact_1.c819_opp_stagename, t234_opportunityforecastcategorypicklistdim_3.correlated_app_order
  • Sort Method: quicksort Memory: 25kB
49. 370.244 2,128.903 ↑ 66.2 5 92,561

Nested Loop Left Join (cost=0.56..3,495.11 rows=331 width=36) (actual time=0.009..0.023 rows=5 loops=92,561)

  • Join Filter: (t236_opp_line_item_fact_1.c820_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim_3.sid)
  • Rows Removed by Join Filter: 19
50. 225.348 1,758.659 ↑ 66.2 5 92,561

Nested Loop Left Join (cost=0.56..3,469.22 rows=331 width=36) (actual time=0.008..0.019 rows=5 loops=92,561)

51. 647.927 647.927 ↑ 66.2 5 92,561

Index Scan using t236_opp_line_item_fact_561_timestamp on t236_opp_line_item_fact t236_opp_line_item_fact_1 (cost=0.56..1,242.33 rows=331 width=32) (actual time=0.005..0.007 rows=5 loops=92,561)

  • Index Cond: ((t236_opp_line_item_fact.c801_opportunity_sid = c801_opportunity_sid) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
  • Filter: (NOT deleted)
52. 0.000 885.384 ↑ 2.0 1 442,692

Append (cost=0.00..6.71 rows=2 width=16) (actual time=0.002..0.002 rows=1 loops=442,692)

53. 0.000 0.000 ↓ 0.0 0 442,692

Seq Scan on t716_clari_conversion_rate t716_clari_conversion_rate_1 (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=442,692)

  • Filter: ((to_iso_code = 'USD'::text) AND (t236_opp_line_item_fact_1.c821_opp_currency_code = sid) AND (t236_opp_line_item_fact_1.c805_opp_close_date = rate_date))
54. 885.384 885.384 ↑ 1.0 1 442,692

Index Scan using t716_clari_conversion_rate_usd_sidratedateunique on t716_clari_conversion_rate_usd t716_clari_conversion_rate_usd_1 (cost=0.29..6.71 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=442,692)

  • Index Cond: ((t236_opp_line_item_fact_1.c821_opp_currency_code = sid) AND (t236_opp_line_item_fact_1.c805_opp_close_date = rate_date))
  • Filter: (to_iso_code = 'USD'::text)
55. 0.000 0.000 ↑ 1.0 5 442,692

Materialize (cost=0.00..1.07 rows=5 width=8) (actual time=0.000..0.000 rows=5 loops=442,692)

56. 0.005 0.005 ↑ 1.0 5 1

Seq Scan on t234_opportunityforecastcategorypicklistdim t234_opportunityforecastcategorypicklistdim_3 (cost=0.00..1.05 rows=5 width=8) (actual time=0.004..0.005 rows=5 loops=1)

57.          

SubPlan (forNested Loop Left Join)

58. 1.010 1.010 ↑ 2.9 8,739 1

CTE Scan on ownercte (cost=0.00..501.84 rows=25,092 width=4) (actual time=0.000..1.010 rows=8,739 loops=1)

59. 0.050 0.050 ↑ 1.2 6 1

CTE Scan on picklist_0 cte0_2 (cost=0.00..0.14 rows=7 width=4) (actual time=0.021..0.050 rows=6 loops=1)

60. 0.023 0.023 ↑ 1.0 7 1

CTE Scan on picklist_1 cte0_3 (cost=0.00..0.14 rows=7 width=4) (actual time=0.005..0.023 rows=7 loops=1)

61.          

CTE bucketec

62. 2.546 3,414.173 ↑ 106,280.7 25 1

GroupAggregate (cost=2,597,923,569.97..2,598,036,493.19 rows=2,657,017 width=140) (actual time=3,410.800..3,414.173 rows=25 loops=1)

  • Group Key: (CASE WHEN (oli174_ft1_1.c1 IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 10)) THEN CASE WHEN (oli174_ft1_1.c3 > 20190131) THEN 'pulledInBucket'::text WHEN (oli174_ft1_1.c3 < 20181101) 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), t236_opp_line_item_fact_2.c820_opp_forecastcategory
63. 6.742 3,411.627 ↑ 228.3 11,638 1

Sort (cost=2,597,923,004.77..2,597,929,647.31 rows=2,657,017 width=84) (actual time=3,410.788..3,411.627 rows=11,638 loops=1)

  • Sort Key: (CASE WHEN (oli174_ft1_1.c1 IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 10)) THEN CASE WHEN (oli174_ft1_1.c3 > 20190131) THEN 'pulledInBucket'::text WHEN (oli174_ft1_1.c3 < 20181101) 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), t236_opp_line_item_fact_2.c820_opp_forecastcategory
  • Sort Method: quicksort Memory: 1298kB
64. 75.409 3,404.885 ↑ 228.3 11,638 1

Nested Loop Left Join (cost=1,030,045.89..2,597,385,193.78 rows=2,657,017 width=84) (actual time=1,714.615..3,404.885 rows=11,638 loops=1)

  • Filter: ((oli174_ft1_1.c1 IS NULL) OR (oli174_ft1_1.c3 < 20181101) OR (oli174_ft1_1.c3 > 20190131) OR (NOT (hashed SubPlan 14)) OR (oli174_ft1_1.c4 IS NULL))
  • Rows Removed by Filter: 104077
65. 189.567 2,054.873 ↑ 18.5 115,715 1

GroupAggregate (cost=1,027,708.49..1,086,440.49 rows=2,135,709 width=28) (actual time=1,711.725..2,054.873 rows=115,715 loops=1)

  • Group Key: t236_opp_line_item_fact_2.c801_opportunity_sid, t236_opp_line_item_fact_2.c819_opp_stagename, t236_opp_line_item_fact_2.c820_opp_forecastcategory
66. 423.535 1,865.306 ↑ 3.2 664,955 1

Sort (cost=1,027,708.49..1,033,047.77 rows=2,135,709 width=32) (actual time=1,711.709..1,865.306 rows=664,955 loops=1)

  • Sort Key: t236_opp_line_item_fact_2.c801_opportunity_sid, t236_opp_line_item_fact_2.c819_opp_stagename, t236_opp_line_item_fact_2.c820_opp_forecastcategory
  • Sort Method: external merge Disk: 27280kB
67. 125.524 1,441.771 ↑ 3.2 664,955 1

Merge Right Join (cost=663,943.66..700,978.38 rows=2,135,709 width=32) (actual time=1,022.099..1,441.771 rows=664,955 loops=1)

  • Merge Cond: ((t716_clari_conversion_rate_2.sid = t236_opp_line_item_fact_2.c821_opp_currency_code) AND (t716_clari_conversion_rate_2.rate_date = t236_opp_line_item_fact_2.c805_opp_close_date))
68. 7.060 49.760 ↑ 1.0 105,237 1

Merge Append (cost=0.31..7,779.93 rows=105,358 width=16) (actual time=0.019..49.760 rows=105,237 loops=1)

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

Seq Scan on t716_clari_conversion_rate t716_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)
71. 42.696 42.696 ↑ 1.0 105,237 1

Index Scan using t716_clari_conversion_rate_usd_sidratedateunique on t716_clari_conversion_rate_usd t716_clari_conversion_rate_usd_2 (cost=0.29..6,462.93 rows=105,357 width=16) (actual time=0.014..42.696 rows=105,237 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
72. 93.222 1,266.487 ↑ 3.2 664,955 1

Materialize (cost=663,943.35..674,621.89 rows=2,135,709 width=32) (actual time=1,020.911..1,266.487 rows=664,955 loops=1)

73. 423.690 1,173.265 ↑ 3.2 664,955 1

Sort (cost=663,943.35..669,282.62 rows=2,135,709 width=32) (actual time=1,020.910..1,173.265 rows=664,955 loops=1)

  • Sort Key: t236_opp_line_item_fact_2.c821_opp_currency_code, t236_opp_line_item_fact_2.c805_opp_close_date
  • Sort Method: external merge Disk: 27280kB
74. 149.764 749.575 ↑ 3.2 664,955 1

Nested Loop (cost=565.14..337,213.23 rows=2,135,709 width=32) (actual time=2.865..749.575 rows=664,955 loops=1)

75. 4.760 5.559 ↓ 43.7 8,739 1

HashAggregate (cost=564.57..566.57 rows=200 width=4) (actual time=2.838..5.559 rows=8,739 loops=1)

  • Group Key: ownercte_4.c1
76. 0.799 0.799 ↑ 2.9 8,739 1

CTE Scan on ownercte ownercte_4 (cost=0.00..501.84 rows=25,092 width=4) (actual time=0.001..0.799 rows=8,739 loops=1)

77. 594.252 594.252 ↑ 5.0 76 8,739

Index Scan using ix_t236_opp_line_item_fact_owner_closedate_timestamp on t236_opp_line_item_fact t236_opp_line_item_fact_2 (cost=0.56..1,679.44 rows=379 width=36) (actual time=0.011..0.068 rows=76 loops=8,739)

  • Index Cond: ((c804_opp_ownerid = ownercte_4.c1) AND (c804_opp_ownerid IS NOT NULL) AND (c805_opp_close_date >= 20181101) AND (c805_opp_close_date <= 20190131) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
78. 0.000 1,272.865 ↑ 2.0 1 115,715

Subquery Scan on oli174_ft1_1 (cost=1,207.62..1,215.60 rows=2 width=16) (actual time=0.010..0.011 rows=1 loops=115,715)

  • Filter: (t236_opp_line_item_fact_2.c801_opportunity_sid = oli174_ft1_1.c1)
79. 115.715 1,272.865 ↑ 319.0 1 115,715

Group (cost=1,207.62..1,211.61 rows=319 width=16) (actual time=0.010..0.011 rows=1 loops=115,715)

  • Group Key: t236_opp_line_item_fact_3.c801_opportunity_sid, t236_opp_line_item_fact_3.c819_opp_stagename, t236_opp_line_item_fact_3.c805_opp_close_date, t236_opp_line_item_fact_3.c804_opp_ownerid
80. 347.145 1,157.150 ↑ 63.8 5 115,715

Sort (cost=1,207.62..1,208.42 rows=319 width=16) (actual time=0.009..0.010 rows=5 loops=115,715)

  • Sort Key: t236_opp_line_item_fact_3.c819_opp_stagename, t236_opp_line_item_fact_3.c805_opp_close_date, t236_opp_line_item_fact_3.c804_opp_ownerid
  • Sort Method: quicksort Memory: 25kB
81. 810.005 810.005 ↑ 63.8 5 115,715

Index Scan using t236_opp_line_item_fact_561_timestamp on t236_opp_line_item_fact t236_opp_line_item_fact_3 (cost=0.56..1,194.36 rows=319 width=16) (actual time=0.005..0.007 rows=5 loops=115,715)

  • Index Cond: ((t236_opp_line_item_fact_2.c801_opportunity_sid = c801_opportunity_sid) AND (start_stamp <= '1541055600000'::bigint) AND (end_stamp > '1541055600000'::bigint))
  • Filter: (NOT deleted)
82.          

SubPlan (forNested Loop Left Join)

83. 0.003 0.003 ↑ 1.1 13 1

CTE Scan on picklist_4 cte0_5 (cost=0.00..0.28 rows=14 width=4) (actual time=0.001..0.003 rows=13 loops=1)

84. 0.732 0.732 ↑ 2.9 8,739 1

CTE Scan on ownercte ownercte_2 (cost=0.00..501.84 rows=25,092 width=4) (actual time=0.001..0.732 rows=8,739 loops=1)

85. 0.002 0.002 ↑ 1.2 6 1

CTE Scan on picklist_0 cte0_6 (cost=0.00..0.14 rows=7 width=4) (actual time=0.000..0.002 rows=6 loops=1)

86. 0.000 0.000 ↑ 1.0 7 1

CTE Scan on picklist_1 cte0_7 (cost=0.00..0.14 rows=7 width=4) (actual time=0.000..0.000 rows=7 loops=1)

87. 1.001 1.001 ↑ 2.9 8,739 1

CTE Scan on ownercte ownercte_3 (cost=0.00..501.84 rows=25,092 width=4) (actual time=0.001..1.001 rows=8,739 loops=1)

88. 0.022 6,327.681 ↑ 2,242.6 19 1

Hash Join (cost=1.11..40,905.56 rows=42,609 width=120) (actual time=6,296.052..6,327.681 rows=19 loops=1)

  • Hash Cond: (cte0.c4 = t234_opportunityforecastcategorypicklistdim.sid)
89. 6,327.646 6,327.646 ↑ 89,702.7 19 1

CTE Scan on bucketac cte0 (cost=0.00..34,087.04 rows=1,704,352 width=116) (actual time=6,296.026..6,327.646 rows=19 loops=1)

90. 0.004 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
91. 0.009 0.009 ↑ 1.0 5 1

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

92. 0.016 3,414.245 ↑ 2,657.0 25 1

Subquery Scan on *SELECT* 2 (cost=1.11..64,599.83 rows=66,425 width=120) (actual time=3,410.846..3,414.245 rows=25 loops=1)

93. 0.026 3,414.229 ↑ 2,657.0 25 1

Hash Join (cost=1.11..63,769.52 rows=66,425 width=144) (actual time=3,410.839..3,414.229 rows=25 loops=1)

  • Hash Cond: (cte0_1.c6 = t234_opportunityforecastcategorypicklistdim_1.sid)
94. 3,414.183 3,414.183 ↑ 106,280.7 25 1

CTE Scan on bucketec cte0_1 (cost=0.00..53,140.34 rows=2,657,017 width=140) (actual time=3,410.802..3,414.183 rows=25 loops=1)

95. 0.004 0.020 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
96. 0.016 0.016 ↑ 1.0 5 1

Seq Scan on t234_opportunityforecastcategorypicklistdim t234_opportunityforecastcategorypicklistdim_1 (cost=0.00..1.05 rows=5 width=12) (actual time=0.014..0.016 rows=5 loops=1)