explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cWbk

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 2,879.593 ↑ 3,560.6 16 1

Append (cost=1,480,706.15..1,482,831.24 rows=56,970 width=96) (actual time=2,818.782..2,879.593 rows=16 loops=1)

  • Planning time: 4.716 ms
  • Execution time: 2887.875 ms
2.          

CTE picklist_0

3. 0.303 0.303 ↑ 1.0 6 1

Seq Scan on t233_opportunitystagenamepicklistdim (cost=0.00..46.54 rows=6 width=4) (actual time=0.008..0.303 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.323 0.323 ↑ 1.0 7 1

Seq Scan on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_1 (cost=0.00..48.08 rows=7 width=4) (actual time=0.008..0.323 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. 15.684 1,076.086 ↑ 3.0 8,167 1

HashAggregate (cost=271,220.18..271,466.23 rows=24,605 width=4) (actual time=1,075.204..1,076.086 rows=8,167 loops=1)

  • Group Key: distinctclosedateowner.ownerid
8.          

CTE usercte

9. 197.938 649.114 ↑ 9.9 129,793 1

Hash Join (cost=59,712.37..236,520.46 rows=1,284,043 width=4) (actual time=148.029..649.114 rows=129,793 loops=1)

  • Hash Cond: (t166_userrolehierarchy.c662_user_role_id = t67_userroledim.sid)
10. 311.046 439.312 ↓ 1.0 1,322,319 1

Bitmap Heap Scan on t166_userrolehierarchy (cost=37,314.55..194,763.20 rows=1,284,043 width=8) (actual time=135.502..439.312 rows=1,322,319 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=39413
11. 128.266 128.266 ↓ 1.0 1,325,648 1

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..36,993.54 rows=1,284,043 width=0) (actual time=128.266..128.266 rows=1,325,648 loops=1)

  • Index Cond: ((c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
12. 1.389 11.864 ↑ 9.3 8,370 1

Hash (cost=21,117.18..21,117.18 rows=78,051 width=4) (actual time=11.864..11.864 rows=8,370 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 1177kB
13. 6.084 10.475 ↑ 9.3 8,370 1

Bitmap Heap Scan on t67_userroledim (cost=4,261.99..21,117.18 rows=78,051 width=4) (actual time=4.830..10.475 rows=8,370 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: 5638
  • Heap Blocks: exact=2150
14. 4.391 4.391 ↑ 5.6 14,038 1

Bitmap Index Scan on ix_t67_userroledim_sid_end_start_timestamp_partial (cost=0.00..4,242.48 rows=78,925 width=0) (actual time=4.391..4.391 rows=14,038 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
15. 77.791 1,060.402 ↓ 1.2 93,387 1

Nested Loop (cost=28,891.40..34,499.64 rows=80,033 width=4) (actual time=714.438..1,060.402 rows=93,387 loops=1)

16. 62.443 740.931 ↓ 604.2 120,840 1

HashAggregate (cost=28,890.97..28,892.97 rows=200 width=4) (actual time=714.406..740.931 rows=120,840 loops=1)

  • Group Key: usercte.c1
17. 678.488 678.488 ↑ 9.9 129,793 1

CTE Scan on usercte (cost=0.00..25,680.86 rows=1,284,043 width=4) (actual time=148.031..678.488 rows=129,793 loops=1)

18. 241.680 241.680 ↑ 6.0 1 120,840

Index Only Scan using ix_distinctclosedateowner_owner_closedate on distinctclosedateowner (cost=0.43..27.97 rows=6 width=4) (actual time=0.002..0.002 rows=1 loops=120,840)

  • Index Cond: ((ownerid = usercte.c1) AND (closedate <= 20190131) AND (closedate >= 20181101))
  • Heap Fetches: 93387
19.          

CTE openwont1

20. 68.674 1,150.890 ↑ 2.2 100,746 1

Merge Right Join (cost=954,541.38..971,523.67 rows=225,684 width=34) (actual time=1,017.677..1,150.890 rows=100,746 loops=1)

  • Merge Cond: ((cur177_t1_r1.rate_date = t237_oppfact_1.c836_opp_close_date) AND (cur177_t1_r1.sid = t237_oppfact_1.c845_opp_currency_code))
21. 7.765 67.096 ↑ 1.0 100,539 1

Merge Append (cost=0.31..8,292.46 rows=101,614 width=16) (actual time=0.031..67.096 rows=100,539 loops=1)

  • Sort Key: cur177_t1_r1.rate_date, cur177_t1_r1.sid
22. 0.011 0.012 ↓ 0.0 0 1

Sort (cost=0.01..0.02 rows=1 width=16) (actual time=0.012..0.012 rows=0 loops=1)

  • Sort Key: cur177_t1_r1.rate_date, cur177_t1_r1.sid
  • Sort Method: quicksort Memory: 25kB
23. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on clari_conversion_rate cur177_t1_r1 (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)
24. 59.319 59.319 ↑ 1.0 100,539 1

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur177_t1_r1_1 (cost=0.29..7,022.26 rows=101,613 width=16) (actual time=0.017..59.319 rows=100,539 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
25. 9.237 1,014.452 ↑ 1.6 100,746 1

Materialize (cost=953,938.68..954,768.02 rows=165,868 width=56) (actual time=983.812..1,014.452 rows=100,746 loops=1)

26. 87.720 1,005.215 ↑ 1.6 100,746 1

Sort (cost=953,938.68..954,353.35 rows=165,868 width=56) (actual time=983.810..1,005.215 rows=100,746 loops=1)

  • Sort Key: t237_oppfact_1.c836_opp_close_date, t237_oppfact_1.c845_opp_currency_code
  • Sort Method: external merge Disk: 6880kB
27. 62.661 917.495 ↑ 1.6 100,746 1

Hash Left Join (cost=4,796.27..933,888.20 rows=165,868 width=56) (actual time=48.916..917.495 rows=100,746 loops=1)

  • Hash Cond: ((t237_oppfact.c845_opp_currency_code = cur177_t0_r0.sid) AND (t237_oppfact.c836_opp_close_date = cur177_t0_r0.rate_date))
28. 79.971 809.265 ↑ 1.2 100,746 1

Nested Loop Left Join (cost=554.90..898,594.82 rows=121,905 width=56) (actual time=2.782..809.265 rows=100,746 loops=1)

29. 19.759 326.310 ↑ 1.2 100,746 1

Nested Loop (cost=554.34..24,877.82 rows=121,905 width=28) (actual time=2.768..326.310 rows=100,746 loops=1)

30. 3.760 4.372 ↓ 40.8 8,167 1

HashAggregate (cost=553.61..555.61 rows=200 width=4) (actual time=2.425..4.372 rows=8,167 loops=1)

  • Group Key: ownercte_1.c1
31. 0.612 0.612 ↑ 3.0 8,167 1

CTE Scan on ownercte ownercte_1 (cost=0.00..492.10 rows=24,605 width=4) (actual time=0.001..0.612 rows=8,167 loops=1)

32. 301.854 302.179 ↑ 1.2 12 8,167

Index Scan using ix_t237_oppfact_owner_closedate_timestamp on t237_oppfact (cost=0.72..121.47 rows=14 width=32) (actual time=0.012..0.037 rows=12 loops=8,167)

  • Index Cond: ((c832_opp_ownerid = ownercte_1.c1) AND (c836_opp_close_date >= 20180201) AND (c836_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: 7
33.          

SubPlan (forIndex Scan)

34. 0.325 0.325 ↑ 1.0 7 1

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

35. 402.984 402.984 ↑ 1.0 1 100,746

Index Scan using ix_t237_oppfact_sid_timestamp on t237_oppfact t237_oppfact_1 (cost=0.56..7.16 rows=1 width=28) (actual time=0.004..0.004 rows=1 loops=100,746)

  • Index Cond: ((t237_oppfact.sid = sid) AND (t237_oppfact.sid = sid) AND (start_stamp <= '1540796399999'::bigint) AND (end_stamp > '1540796399999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
36. 18.358 45.569 ↑ 1.0 101,598 1

Hash (cost=2,220.16..2,220.16 rows=101,614 width=16) (actual time=45.569..45.569 rows=101,598 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3409kB
37. 5.958 27.211 ↑ 1.0 101,598 1

Append (cost=0.00..2,220.16 rows=101,614 width=16) (actual time=0.112..27.211 rows=101,598 loops=1)

38. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on clari_conversion_rate cur177_t0_r0 (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)
39. 21.252 21.252 ↑ 1.0 101,598 1

Seq Scan on usd_clari_conversion_rate cur177_t0_r0_1 (cost=0.00..2,220.16 rows=101,613 width=16) (actual time=0.111..21.252 rows=101,598 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
40.          

SubPlan (forMerge Right Join)

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

42. 0.001 0.001 ↑ 1.0 6 1

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

43. 0.665 0.665 ↑ 3.0 8,167 1

CTE Scan on ownercte (cost=0.00..492.10 rows=24,605 width=4) (actual time=0.001..0.665 rows=8,167 loops=1)

44.          

CTE openwont1ids

45. 44.416 1,233.470 ↓ 503.7 100,746 1

HashAggregate (cost=5,077.89..5,079.89 rows=200 width=4) (actual time=1,218.968..1,233.470 rows=100,746 loops=1)

  • Group Key: cte0_5.c1
46. 1,189.054 1,189.054 ↑ 2.2 100,746 1

CTE Scan on openwont1 cte0_5 (cost=0.00..4,513.68 rows=225,684 width=4) (actual time=1,017.678..1,189.054 rows=100,746 loops=1)

47.          

CTE bucketab

48. 34.422 55.955 ↑ 2,051.6 11 1

HashAggregate (cost=7,898.94..8,124.62 rows=22,568 width=68) (actual time=55.905..55.955 rows=11 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
49. 21.533 21.533 ↑ 2.2 100,746 1

CTE Scan on openwont1 cte0_6 (cost=0.00..4,513.68 rows=225,684 width=64) (actual time=0.034..21.533 rows=100,746 loops=1)

50.          

CTE picklist_3

51. 0.468 0.468 ↑ 1.0 13 1

Seq Scan on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_2 (cost=0.00..57.31 rows=13 width=4) (actual time=0.010..0.468 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
52.          

CTE bucketd

53. 3.807 2,823.568 ↑ 6,880.4 5 1

GroupAggregate (cost=223,155.73..224,359.80 rows=34,402 width=120) (actual time=2,818.756..2,823.568 rows=5 loops=1)

  • Group Key: '1520668799999'::bigint, (CASE WHEN (t237_oppfact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 13)) THEN CASE WHEN (t237_oppfact_3.c836_opp_close_date > 20180430) THEN 'pulledIn'::text WHEN (t237_oppfact_3.c836_opp_close_date < 20180201) THEN 'pushedIn'::text WHEN (hashed SubPlan 14) THEN 'other'::text ELSE 'ownerChange'::text END ELSE 'other'::text END)
54. 6.640 2,819.761 ↑ 2.0 16,905 1

Sort (cost=222,601.82..222,687.83 rows=34,402 width=60) (actual time=2,818.599..2,819.761 rows=16,905 loops=1)

  • Sort Key: (CASE WHEN (t237_oppfact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 13)) THEN CASE WHEN (t237_oppfact_3.c836_opp_close_date > 20180430) THEN 'pulledIn'::text WHEN (t237_oppfact_3.c836_opp_close_date < 20180201) THEN 'pushedIn'::text WHEN (hashed SubPlan 14) THEN 'other'::text ELSE 'ownerChange'::text END ELSE 'other'::text END)
  • Sort Method: quicksort Memory: 2089kB
55. 16.609 2,813.121 ↑ 2.0 16,905 1

Nested Loop Left Join (cost=1,113.34..220,009.60 rows=34,402 width=60) (actual time=2,365.088..2,813.121 rows=16,905 loops=1)

56. 5.169 2,761.578 ↑ 1.5 16,905 1

Nested Loop Left Join (cost=559.44..209,812.89 rows=25,284 width=36) (actual time=2,365.069..2,761.578 rows=16,905 loops=1)

57. 8.992 2,688.789 ↑ 1.5 16,905 1

Hash Semi Join (cost=558.87..18,369.92 rows=25,284 width=20) (actual time=2,365.052..2,688.789 rows=16,905 loops=1)

  • Hash Cond: (t237_oppfact_2.c843_opp_stagename = cte0_9.c1)
58. 21.583 2,679.487 ↓ 1.0 83,696 1

Nested Loop (cost=558.68..17,878.28 rows=80,064 width=24) (actual time=2,364.689..2,679.487 rows=83,696 loops=1)

59. 4.062 1,081.673 ↓ 40.8 8,167 1

HashAggregate (cost=553.61..555.61 rows=200 width=4) (actual time=1,079.423..1,081.673 rows=8,167 loops=1)

  • Group Key: ownercte_3.c1
60. 1,077.611 1,077.611 ↑ 3.0 8,167 1

CTE Scan on ownercte ownercte_3 (cost=0.00..492.10 rows=24,605 width=4) (actual time=1,075.205..1,077.611 rows=8,167 loops=1)

61. 315.219 1,576.231 ↓ 1.1 10 8,167

Index Scan using ix_t237_oppfact_owner_closedate_timestamp on t237_oppfact t237_oppfact_2 (cost=5.06..86.52 rows=9 width=28) (actual time=0.169..0.193 rows=10 loops=8,167)

  • Index Cond: ((c832_opp_ownerid = ownercte_3.c1) AND (c836_opp_close_date >= 20180201) AND (c836_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: 7
62.          

SubPlan (forIndex Scan)

63. 1,261.012 1,261.012 ↓ 503.7 100,746 1

CTE Scan on openwont1ids cte0_8 (cost=0.00..4.00 rows=200 width=4) (actual time=1,218.970..1,261.012 rows=100,746 loops=1)

64. 0.004 0.310 ↑ 1.0 6 1

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

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

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

66. 67.620 67.620 ↓ 0.0 0 16,905

Index Scan using ix_t237_oppfact_sid_timestamp on t237_oppfact t237_oppfact_3 (cost=0.56..7.56 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=16,905)

  • Index Cond: ((t237_oppfact_2.sid = sid) AND (t237_oppfact_2.sid = sid) AND (start_stamp <= '1520668799999'::bigint) AND (end_stamp > '1520668799999'::bigint))
  • Filter: (NOT deleted)
67. 0.000 33.810 ↑ 2.0 1 16,905

Append (cost=0.00..0.35 rows=2 width=16) (actual time=0.002..0.002 rows=1 loops=16,905)

68. 0.000 0.000 ↓ 0.0 0 16,905

Seq Scan on clari_conversion_rate cur177_t0_r0_2 (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=16,905)

  • Filter: ((to_iso_code = 'USD'::text) AND (t237_oppfact_2.c845_opp_currency_code = sid) AND (t237_oppfact_2.c836_opp_close_date = rate_date))
69. 33.810 33.810 ↑ 1.0 1 16,905

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur177_t0_r0_3 (cost=0.29..0.35 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=16,905)

  • Index Cond: ((t237_oppfact_2.c836_opp_close_date = rate_date) AND (t237_oppfact_2.c845_opp_currency_code = sid))
  • Filter: (to_iso_code = 'USD'::text)
70.          

SubPlan (forNested Loop Left Join)

71. 0.470 0.470 ↑ 1.0 13 1

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

72. 0.654 0.654 ↑ 3.0 8,167 1

CTE Scan on ownercte ownercte_2 (cost=0.00..492.10 rows=24,605 width=4) (actual time=0.001..0.654 rows=8,167 loops=1)

73. 0.013 2,823.596 ↑ 6,880.4 5 1

Subquery Scan on *SELECT* 1 (cost=0.00..1,118.07 rows=34,402 width=96) (actual time=2,818.780..2,823.596 rows=5 loops=1)

74. 2,823.583 2,823.583 ↑ 6,880.4 5 1

CTE Scan on bucketd cte0 (cost=0.00..688.04 rows=34,402 width=120) (actual time=2,818.771..2,823.583 rows=5 loops=1)

75. 0.018 55.992 ↑ 2,051.6 11 1

Hash Join (cost=19.68..781.35 rows=22,568 width=96) (actual time=55.936..55.992 rows=11 loops=1)

  • Hash Cond: (cte0_1.c6 = t234_opportunityforecastcategorypicklistdim.sid)
76. 55.962 55.962 ↑ 2,051.6 11 1

CTE Scan on bucketab cte0_1 (cost=0.00..451.36 rows=22,568 width=68) (actual time=55.908..55.962 rows=11 loops=1)

77. 0.003 0.012 ↑ 86.0 5 1

Hash (cost=14.30..14.30 rows=430 width=36) (actual time=0.012..0.012 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.006..0.009 rows=5 loops=1)