explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pVPTn

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 3,608.981 ↑ 65.5 15 1

Append (cost=35,545,831.71..35,545,894.14 rows=982 width=96) (actual time=3,589.519..3,608.981 rows=15 loops=1)

2.          

CTE picklist_0

3. 0.341 0.341 ↑ 1.0 6 1

Seq Scan on t233_opportunitystagenamepicklistdim (cost=0.00..70.54 rows=6 width=4) (actual time=0.010..0.341 rows=6 loops=1)

  • Filter: (correlated_value = ANY ('{"06 - Won, Deploy & Expand","6 - Won, Deploy & Expand","Closed - Won","06 - Won. Deploy & Expand",Won,"06 - Won, Deploy & Expand"}'::text[]))
  • Rows Removed by Filter: 1225
4.          

CTE picklist_2

5. 0.362 0.362 ↑ 1.0 7 1

Seq Scan on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_1 (cost=0.00..72.08 rows=7 width=4) (actual time=0.018..0.362 rows=7 loops=1)

  • Filter: (correlated_value = ANY ('{"HPE Not Pursued",Lost,Close:Duplicate,"HP Not Pursued",Duplicate,Error,"Closed - Lost"}'::text[]))
  • Rows Removed by Filter: 1224
6.          

CTE ownercte

7. 52.765 2,154.523 ↑ 2.9 8,421 1

HashAggregate (cost=11,952,481.28..11,952,722.24 rows=24,096 width=4) (actual time=2,153.158..2,154.523 rows=8,421 loops=1)

  • Group Key: distinctclosedateowner.ownerid
8. 171.116 2,101.758 ↓ 1.1 134,360 1

Index Scan using ix_closedate_distinctclosedateowner on distinctclosedateowner (cost=0.43..11,952,177.45 rows=121,533 width=4) (actual time=0.085..2,101.758 rows=134,360 loops=1)

  • Index Cond: ((closedate <= 20180430) AND (closedate >= 20180201))
  • Filter: (SubPlan 3)
  • Rows Removed by Filter: 141446
9.          

SubPlan (forIndex Scan)

10. 277.876 1,930.642 ↓ 0.0 0 275,806

Nested Loop Semi Join (cost=0.98..96.42 rows=7 width=4) (actual time=0.007..0.007 rows=0 loops=275,806)

11. 1,103.224 1,103.224 ↑ 7.0 1 275,806

Index Scan using ix_t166_userrolehierarchy_sid_ancestor_end_start_timestamp on t166_userrolehierarchy (cost=0.56..37.25 rows=7 width=8) (actual time=0.003..0.004 rows=1 loops=275,806)

  • Index Cond: ((sid = distinctclosedateowner.ownerid) AND (c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
12. 549.542 549.542 ↓ 0.0 0 274,771

Index Scan using ix_t67_userroledim_sid_end_start_timestamp_partial on t67_userroledim (cost=0.42..8.44 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=274,771)

  • Index Cond: ((sid = t166_userrolehierarchy.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
13.          

CTE openwont1

14. 25.842 960.984 ↓ 2.9 28,325 1

Nested Loop Left Join (cost=12,528.34..20,595,334.00 rows=9,639 width=34) (actual time=356.398..960.984 rows=28,325 loops=1)

15. 43.302 367.971 ↓ 4.1 28,325 1

HashAggregate (cost=9,049.86..9,119.71 rows=6,985 width=28) (actual time=356.280..367.971 rows=28,325 loops=1)

  • Group Key: t236_opp_line_item_fact.c801_opportunity_sid, t236_opp_line_item_fact.c820_opp_forecastcategory, t236_opp_line_item_fact.c819_opp_stagename
16. 23.812 324.669 ↓ 10.5 73,092 1

Nested Loop Left Join (cost=542.88..8,962.55 rows=6,985 width=28) (actual time=2.933..324.669 rows=73,092 loops=1)

17. 7.459 154.673 ↓ 10.5 73,092 1

Nested Loop (cost=542.88..3,453.25 rows=6,985 width=28) (actual time=2.910..154.673 rows=73,092 loops=1)

18. 3.426 4.057 ↓ 42.1 8,421 1

HashAggregate (cost=542.16..544.16 rows=200 width=4) (actual time=2.514..4.057 rows=8,421 loops=1)

  • Group Key: ownercte_1.c1
19. 0.631 0.631 ↑ 2.9 8,421 1

CTE Scan on ownercte ownercte_1 (cost=0.00..481.92 rows=24,096 width=4) (actual time=0.000..0.631 rows=8,421 loops=1)

20. 142.793 143.157 ↓ 9.0 9 8,421

Index Scan using ix_t236_opp_line_item_fact_owner_closedate_timestamp on t236_opp_line_item_fact (cost=0.72..14.54 rows=1 width=32) (actual time=0.008..0.017 rows=9 loops=8,421)

  • Index Cond: ((c804_opp_ownerid = ownercte_1.c1) AND (c805_opp_close_date >= 20180201) AND (c805_opp_close_date <= 20180430) AND (start_stamp <= '1520668799999'::bigint) AND (end_stamp > '1520668799999'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 8)))
  • Rows Removed by Filter: 0
21.          

SubPlan (forIndex Scan)

22. 0.364 0.364 ↑ 1.0 7 1

CTE Scan on picklist_2 cte0_4 (cost=0.00..0.14 rows=7 width=4) (actual time=0.018..0.364 rows=7 loops=1)

23. 0.000 146.184 ↑ 2.0 1 73,092

Append (cost=0.00..0.77 rows=2 width=16) (actual time=0.002..0.002 rows=1 loops=73,092)

24. 0.000 0.000 ↓ 0.0 0 73,092

Seq Scan on t539_clari_conversion_rate (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=73,092)

  • Filter: ((to_iso_code = 'USD'::text) AND (t236_opp_line_item_fact.c821_opp_currency_code = sid) AND (t236_opp_line_item_fact.c805_opp_close_date = rate_date))
25. 146.184 146.184 ↑ 1.0 1 73,092

Index Scan using t539_clari_conversion_rate_usd_sidratedateunique on t539_clari_conversion_rate_usd (cost=0.29..0.77 rows=1 width=16) (actual time=0.001..0.002 rows=1 loops=73,092)

  • Index Cond: ((t236_opp_line_item_fact.c821_opp_currency_code = sid) AND (t236_opp_line_item_fact.c805_opp_close_date = rate_date))
  • Filter: (to_iso_code = 'USD'::text)
26. 0.000 566.500 ↑ 1.0 1 28,325

Subquery Scan on oli174_ft1 (cost=2,936.05..2,947.09 rows=1 width=24) (actual time=0.020..0.020 rows=1 loops=28,325)

  • Filter: (t236_opp_line_item_fact.c801_opportunity_sid = oli174_ft1.c1)
27. 28.325 566.500 ↑ 276.0 1 28,325

GroupAggregate (cost=2,936.05..2,943.64 rows=276 width=32) (actual time=0.020..0.020 rows=1 loops=28,325)

  • Group Key: t236_opp_line_item_fact_1.c801_opportunity_sid, t236_opp_line_item_fact_1.c819_opp_stagename, t236_opp_line_item_fact_1.c805_opp_close_date, t236_opp_line_item_fact_1.c804_opp_ownerid
28. 84.975 538.175 ↑ 92.0 3 28,325

Sort (cost=2,936.05..2,936.74 rows=276 width=32) (actual time=0.018..0.019 rows=3 loops=28,325)

  • Sort Key: t236_opp_line_item_fact_1.c819_opp_stagename, t236_opp_line_item_fact_1.c805_opp_close_date, t236_opp_line_item_fact_1.c804_opp_ownerid
  • Sort Method: quicksort Memory: 25kB
29. 39.808 453.200 ↑ 92.0 3 28,325

Nested Loop Left Join (cost=0.56..2,924.86 rows=276 width=32) (actual time=0.010..0.016 rows=3 loops=28,325)

30. 226.600 226.600 ↑ 92.0 3 28,325

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,016.40 rows=276 width=28) (actual time=0.007..0.008 rows=3 loops=28,325)

  • Index Cond: ((t236_opp_line_item_fact.c801_opportunity_sid = c801_opportunity_sid) AND (start_stamp <= '1540796399999'::bigint) AND (end_stamp > '1540796399999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
31. 0.000 186.792 ↑ 2.0 1 93,396

Append (cost=0.00..6.89 rows=2 width=16) (actual time=0.002..0.002 rows=1 loops=93,396)

32. 0.000 0.000 ↓ 0.0 0 93,396

Seq Scan on t539_clari_conversion_rate t539_clari_conversion_rate_1 (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=93,396)

  • 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))
33. 186.792 186.792 ↑ 1.0 1 93,396

Index Scan using t539_clari_conversion_rate_usd_sidratedateunique on t539_clari_conversion_rate_usd t539_clari_conversion_rate_usd_1 (cost=0.29..6.89 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=93,396)

  • 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)
34.          

SubPlan (forNested Loop Left Join)

35. 0.002 0.002 ↑ 1.0 6 1

CTE Scan on picklist_0 cte0_2 (cost=0.00..0.12 rows=6 width=4) (actual time=0.001..0.002 rows=6 loops=1)

36. 0.000 0.000 ↑ 1.0 6 1

CTE Scan on picklist_0 cte0_3 (cost=0.00..0.12 rows=6 width=4) (actual time=0.000..0.000 rows=6 loops=1)

37. 0.669 0.669 ↑ 2.9 8,421 1

CTE Scan on ownercte (cost=0.00..481.92 rows=24,096 width=4) (actual time=0.000..0.669 rows=8,421 loops=1)

38.          

CTE openwont1ids

39. 17.543 989.659 ↓ 141.6 28,325 1

HashAggregate (cost=216.88..218.88 rows=200 width=4) (actual time=984.881..989.659 rows=28,325 loops=1)

  • Group Key: cte0_5.c1
40. 972.116 972.116 ↓ 2.9 28,325 1

CTE Scan on openwont1 cte0_5 (cost=0.00..192.78 rows=9,639 width=4) (actual time=356.401..972.116 rows=28,325 loops=1)

41.          

CTE bucketab

42. 9.265 14.405 ↑ 96.4 10 1

HashAggregate (cost=337.37..347.00 rows=964 width=68) (actual time=14.402..14.405 rows=10 loops=1)

  • Group Key: cte0_6.c5, CASE WHEN cte0_6.c6 THEN CASE WHEN cte0_6.c4 THEN 'convertedWon'::text ELSE 'convertedOpen'::text END ELSE CASE WHEN cte0_6.c4 THEN 'unconvertedWon'::text ELSE 'unconvertedOpen'::text END END, cte0_6.c3
43. 5.140 5.140 ↓ 2.9 28,325 1

CTE Scan on openwont1 cte0_6 (cost=0.00..192.78 rows=9,639 width=64) (actual time=0.004..5.140 rows=28,325 loops=1)

44.          

CTE picklist_3

45. 0.451 0.451 ↑ 1.0 13 1

Seq Scan on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_2 (cost=0.00..81.31 rows=13 width=4) (actual time=0.010..0.451 rows=13 loops=1)

  • Filter: (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[]))
  • Rows Removed by Filter: 1218
46.          

CTE bucketd

47. 3.707 3,594.510 ↑ 3.6 5 1

GroupAggregate (cost=2,996,445.45..2,996,985.65 rows=18 width=120) (actual time=3,589.504..3,594.510 rows=5 loops=1)

  • Group Key: '1520668799999'::bigint, (CASE WHEN (oli174_ft1_1.c1 IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 13)) THEN CASE WHEN (oli174_ft1_1.c3 > 20180430) THEN 'pulledIn'::text WHEN (oli174_ft1_1.c3 < 20180201) THEN 'pushedIn'::text WHEN (hashed SubPlan 14) THEN 'other'::text ELSE 'ownerChange'::text END ELSE 'other'::text END)
48. 6.599 3,590.803 ↑ 2.0 21,498 1

Sort (cost=2,995,903.00..2,996,010.97 rows=43,187 width=52) (actual time=3,589.461..3,590.803 rows=21,498 loops=1)

  • Sort Key: (CASE WHEN (oli174_ft1_1.c1 IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 13)) THEN CASE WHEN (oli174_ft1_1.c3 > 20180430) THEN 'pulledIn'::text WHEN (oli174_ft1_1.c3 < 20180201) THEN 'pushedIn'::text WHEN (hashed SubPlan 14) THEN 'other'::text ELSE 'ownerChange'::text END ELSE 'other'::text END)
  • Sort Method: quicksort Memory: 2448kB
49. 10.574 3,584.204 ↑ 2.0 21,498 1

Nested Loop Left Join (cost=47,814.79..2,992,577.97 rows=43,187 width=52) (actual time=3,459.072..3,584.204 rows=21,498 loops=1)

50. 19.180 3,465.007 ↑ 2.0 21,498 1

HashAggregate (cost=47,204.64..47,636.51 rows=43,187 width=20) (actual time=3,459.026..3,465.007 rows=21,498 loops=1)

  • Group Key: t236_opp_line_item_fact_2.c801_opportunity_sid
51. 14.493 3,445.827 ↓ 1.2 50,414 1

Merge Right Join (cost=36,345.98..46,880.73 rows=43,187 width=20) (actual time=3,366.644..3,445.827 rows=50,414 loops=1)

  • Merge Cond: ((t539_clari_conversion_rate_2.sid = t236_opp_line_item_fact_2.c821_opp_currency_code) AND (t539_clari_conversion_rate_2.rate_date = t236_opp_line_item_fact_2.c805_opp_close_date))
52. 6.403 60.772 ↑ 1.0 101,362 1

Merge Append (cost=0.31..9,553.62 rows=103,231 width=16) (actual time=0.023..60.772 rows=101,362 loops=1)

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

Seq Scan on t539_clari_conversion_rate t539_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)
55. 54.365 54.365 ↑ 1.0 101,362 1

Index Scan using t539_clari_conversion_rate_usd_sidratedateunique on t539_clari_conversion_rate_usd t539_clari_conversion_rate_usd_2 (cost=0.29..8,263.21 rows=103,230 width=16) (actual time=0.018..54.365 rows=101,362 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
56. 50.108 3,370.562 ↓ 1.2 50,414 1

Sort (cost=36,345.67..36,453.63 rows=43,187 width=20) (actual time=3,364.747..3,370.562 rows=50,414 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 sort Disk: 1872kB
57. 10.079 3,320.454 ↓ 1.2 50,414 1

Hash Semi Join (cost=547.42..33,020.63 rows=43,187 width=20) (actual time=3,160.741..3,320.454 rows=50,414 loops=1)

  • Hash Cond: (t236_opp_line_item_fact_2.c819_opp_stagename = cte0_9.c1)
58. 13.482 3,310.027 ↑ 1.7 62,454 1

Nested Loop (cost=547.23..32,262.72 rows=105,622 width=24) (actual time=3,160.378..3,310.027 rows=62,454 loops=1)

59. 3.443 2,159.710 ↓ 42.1 8,421 1

HashAggregate (cost=542.16..544.16 rows=200 width=4) (actual time=2,158.302..2,159.710 rows=8,421 loops=1)

  • Group Key: ownercte_3.c1
60. 2,156.267 2,156.267 ↑ 2.9 8,421 1

CTE Scan on ownercte ownercte_3 (cost=0.00..481.92 rows=24,096 width=4) (actual time=2,153.160..2,156.267 rows=8,421 loops=1)

61. 141.661 1,136.835 ↑ 2.7 7 8,421

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=5.06..158.40 rows=19 width=28) (actual time=0.127..0.135 rows=7 loops=8,421)

  • Index Cond: ((c804_opp_ownerid = ownercte_3.c1) AND (c805_opp_close_date >= 20180201) AND (c805_opp_close_date <= 20180430) AND (start_stamp <= '1540796399999'::bigint) AND (end_stamp > '1540796399999'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 15)))
  • Rows Removed by Filter: 2
62.          

SubPlan (forIndex Scan)

63. 995.174 995.174 ↓ 141.6 28,325 1

CTE Scan on openwont1ids cte0_8 (cost=0.00..4.00 rows=200 width=4) (actual time=984.882..995.174 rows=28,325 loops=1)

64. 0.004 0.348 ↑ 1.0 6 1

Hash (cost=0.12..0.12 rows=6 width=4) (actual time=0.348..0.348 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
65. 0.344 0.344 ↑ 1.0 6 1

CTE Scan on picklist_0 cte0_9 (cost=0.00..0.12 rows=6 width=4) (actual time=0.012..0.344 rows=6 loops=1)

66. 0.000 107.490 ↓ 0.0 0 21,498

Subquery Scan on oli174_ft1_1 (cost=67.70..68.15 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=21,498)

  • Filter: (t236_opp_line_item_fact_2.c801_opportunity_sid = oli174_ft1_1.c1)
67. 0.000 107.490 ↓ 0.0 0 21,498

Group (cost=67.70..67.92 rows=18 width=16) (actual time=0.005..0.005 rows=0 loops=21,498)

  • 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
68. 42.996 107.490 ↓ 0.0 0 21,498

Sort (cost=67.70..67.74 rows=18 width=16) (actual time=0.005..0.005 rows=0 loops=21,498)

  • 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
69. 64.494 64.494 ↓ 0.0 0 21,498

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..67.32 rows=18 width=16) (actual time=0.003..0.003 rows=0 loops=21,498)

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

SubPlan (forNested Loop Left Join)

71. 0.457 0.457 ↑ 1.0 13 1

CTE Scan on picklist_3 cte0_7 (cost=0.00..0.26 rows=13 width=4) (actual time=0.013..0.457 rows=13 loops=1)

72. 0.676 0.676 ↑ 2.9 8,421 1

CTE Scan on ownercte ownercte_2 (cost=0.00..481.92 rows=24,096 width=4) (actual time=0.001..0.676 rows=8,421 loops=1)

73. 0.015 3,594.531 ↑ 3.6 5 1

Subquery Scan on *SELECT* 1 (cost=0.00..0.59 rows=18 width=96) (actual time=3,589.519..3,594.531 rows=5 loops=1)

74. 3,594.516 3,594.516 ↑ 3.6 5 1

CTE Scan on bucketd cte0 (cost=0.00..0.36 rows=18 width=120) (actual time=3,589.508..3,594.516 rows=5 loops=1)

75. 0.022 14.447 ↑ 96.4 10 1

Hash Join (cost=19.68..52.21 rows=964 width=96) (actual time=14.437..14.447 rows=10 loops=1)

  • Hash Cond: (cte0_1.c6 = t234_opportunityforecastcategorypicklistdim.sid)
76. 14.411 14.411 ↑ 96.4 10 1

CTE Scan on bucketab cte0_1 (cost=0.00..19.28 rows=964 width=68) (actual time=14.404..14.411 rows=10 loops=1)

77. 0.005 0.014 ↑ 86.0 5 1

Hash (cost=14.30..14.30 rows=430 width=36) (actual time=0.014..0.014 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
78. 0.009 0.009 ↑ 86.0 5 1

Seq Scan on t234_opportunityforecastcategorypicklistdim (cost=0.00..14.30 rows=430 width=36) (actual time=0.008..0.009 rows=5 loops=1)