explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OJ6g

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

Append (cost=1,637,728.71..1,639,853.80 rows=56,970 width=96) (actual time=2,711.732..2,772.950 rows=16 loops=1)

2.          

CTE picklist_0

3. 0.336 0.336 ↑ 1.0 6 1

Seq Scan on t233_opportunitystagenamepicklistdim (cost=0.00..46.54 rows=6 width=4) (actual time=0.009..0.336 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.337 0.337 ↑ 1.0 7 1

Seq Scan on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_1 (cost=0.00..48.08 rows=7 width=4) (actual time=0.010..0.337 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. 16.436 931.651 ↑ 3.0 8,167 1

HashAggregate (cost=430,553.01..430,797.38 rows=24,437 width=4) (actual time=930.635..931.651 rows=8,167 loops=1)

  • Group Key: distinctclosedateowner.ownerid
8. 59.717 915.215 ↑ 1.6 93,387 1

Hash Join (cost=396,855.55..430,178.64 rows=149,749 width=4) (actual time=785.513..915.215 rows=93,387 loops=1)

  • Hash Cond: (distinctclosedateowner.ownerid = t166_userrolehierarchy.sid)
9. 73.632 93.097 ↓ 1.3 196,760 1

Bitmap Heap Scan on distinctclosedateowner (cost=3,179.36..32,515.59 rows=149,749 width=4) (actual time=22.586..93.097 rows=196,760 loops=1)

  • Recheck Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
  • Heap Blocks: exact=17913
10. 19.465 19.465 ↓ 1.3 196,760 1

Bitmap Index Scan on ix_closedate_distinctclosedateowner (cost=0.00..3,141.92 rows=149,749 width=0) (actual time=19.465..19.465 rows=196,760 loops=1)

  • Index Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
11. 16.634 762.401 ↑ 1.6 120,769 1

Hash (cost=390,467.94..390,467.94 rows=195,540 width=4) (actual time=762.401..762.401 rows=120,769 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2089kB
12. 15.106 745.767 ↑ 1.6 120,769 1

Unique (cost=384,052.60..390,467.94 rows=195,540 width=4) (actual time=715.377..745.767 rows=120,769 loops=1)

13. 68.722 730.661 ↑ 9.9 129,697 1

Sort (cost=384,052.60..387,260.27 rows=1,283,067 width=4) (actual time=715.376..730.661 rows=129,697 loops=1)

  • Sort Key: t166_userrolehierarchy.sid
  • Sort Method: external merge Disk: 1768kB
14. 200.597 661.939 ↑ 9.9 129,697 1

Hash Join (cost=59,662.38..236,335.98 rows=1,283,067 width=4) (actual time=153.407..661.939 rows=129,697 loops=1)

  • Hash Cond: (t166_userrolehierarchy.c662_user_role_id = t67_userroledim.sid)
15. 316.203 449.089 ↓ 1.0 1,321,874 1

Bitmap Heap Scan on t166_userrolehierarchy (cost=37,264.57..194,593.57 rows=1,283,067 width=8) (actual time=140.416..449.089 rows=1,321,874 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=39327
16. 132.886 132.886 ↓ 1.0 1,324,436 1

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..36,943.80 rows=1,283,067 width=0) (actual time=132.886..132.886 rows=1,324,436 loops=1)

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

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

  • Buckets: 131072 Batches: 2 Memory Usage: 1177kB
18. 6.233 10.831 ↑ 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=5.024..10.831 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: 5587
  • Heap Blocks: exact=2140
19. 4.598 4.598 ↑ 5.6 13,978 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.598..4.598 rows=13,978 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
20.          

CTE openwont1

21. 68.999 1,191.666 ↑ 2.2 100,746 1

Merge Right Join (cost=952,573.82..969,556.11 rows=225,684 width=34) (actual time=1,059.220..1,191.666 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))
22. 6.606 63.625 ↑ 1.0 100,539 1

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

  • Sort Key: cur177_t1_r1.rate_date, cur177_t1_r1.sid
23. 0.010 0.014 ↓ 0.0 0 1

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

  • Sort Key: cur177_t1_r1.rate_date, cur177_t1_r1.sid
  • Sort Method: quicksort Memory: 25kB
24. 0.004 0.004 ↓ 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.004..0.004 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
25. 57.005 57.005 ↑ 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..57.005 rows=100,539 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
26. 9.917 1,058.423 ↑ 1.6 100,746 1

Materialize (cost=951,974.90..952,804.24 rows=165,868 width=56) (actual time=1,027.241..1,058.423 rows=100,746 loops=1)

27. 86.493 1,048.506 ↑ 1.6 100,746 1

Sort (cost=951,974.90..952,389.57 rows=165,868 width=56) (actual time=1,027.239..1,048.506 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
28. 62.284 962.013 ↑ 1.6 100,746 1

Hash Left Join (cost=4,792.49..931,924.42 rows=165,868 width=56) (actual time=50.020..962.013 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))
29. 12.533 852.863 ↑ 1.2 100,746 1

Nested Loop Left Join (cost=551.12..896,631.04 rows=121,905 width=56) (actual time=2.772..852.863 rows=100,746 loops=1)

30. 13.664 336.600 ↑ 1.2 100,746 1

Nested Loop (cost=550.55..24,874.04 rows=121,905 width=28) (actual time=2.759..336.600 rows=100,746 loops=1)

31. 3.830 4.423 ↓ 40.8 8,167 1

HashAggregate (cost=549.83..551.83 rows=200 width=4) (actual time=2.396..4.423 rows=8,167 loops=1)

  • Group Key: ownercte_1.c1
32. 0.593 0.593 ↑ 3.0 8,167 1

CTE Scan on ownercte ownercte_1 (cost=0.00..488.74 rows=24,437 width=4) (actual time=0.001..0.593 rows=8,167 loops=1)

33. 318.175 318.513 ↑ 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.039 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 7)))
  • Rows Removed by Filter: 7
34.          

SubPlan (forIndex Scan)

35. 0.338 0.338 ↑ 1.0 7 1

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

36. 503.730 503.730 ↑ 1.0 1 100,746

Index Scan using ix_t237_oppfact_sid_timestamp on t237_oppfact t237_oppfact_1 (cost=0.56..7.14 rows=1 width=28) (actual time=0.005..0.005 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
37. 18.543 46.866 ↑ 1.0 101,598 1

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

  • Buckets: 131072 Batches: 2 Memory Usage: 3409kB
38. 6.866 28.323 ↑ 1.0 101,598 1

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

39. 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)
40. 21.456 21.456 ↑ 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.116..21.456 rows=101,598 loops=1)

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

SubPlan (forMerge Right Join)

42. 0.001 0.001 ↑ 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.001 rows=6 loops=1)

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

44. 0.618 0.618 ↑ 3.0 8,167 1

CTE Scan on ownercte (cost=0.00..488.74 rows=24,437 width=4) (actual time=0.000..0.618 rows=8,167 loops=1)

45.          

CTE openwont1ids

46. 45.341 1,274.124 ↓ 503.7 100,746 1

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

  • Group Key: cte0_5.c1
47. 1,228.783 1,228.783 ↑ 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,059.222..1,228.783 rows=100,746 loops=1)

48.          

CTE bucketab

49. 34.621 56.329 ↑ 2,051.6 11 1

HashAggregate (cost=7,898.94..8,124.62 rows=22,568 width=68) (actual time=56.286..56.329 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
50. 21.708 21.708 ↑ 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.037..21.708 rows=100,746 loops=1)

51.          

CTE picklist_3

52. 0.451 0.451 ↑ 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.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
53.          

CTE bucketd

54. 3.812 2,716.557 ↑ 6,880.4 5 1

GroupAggregate (cost=222,814.70..224,018.77 rows=34,402 width=120) (actual time=2,711.719..2,716.557 rows=5 loops=1)

  • Group Key: '1520668799999'::bigint, (CASE WHEN (t237_oppfact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 12)) 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 13) THEN 'other'::text ELSE 'ownerChange'::text END ELSE 'other'::text END)
55. 6.066 2,712.745 ↑ 2.0 16,905 1

Sort (cost=222,264.57..222,350.58 rows=34,402 width=60) (actual time=2,711.557..2,712.745 rows=16,905 loops=1)

  • Sort Key: (CASE WHEN (t237_oppfact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 12)) 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 13) THEN 'other'::text ELSE 'ownerChange'::text END ELSE 'other'::text END)
  • Sort Method: quicksort Memory: 2089kB
56. 16.655 2,706.679 ↑ 2.0 16,905 1

Nested Loop Left Join (cost=1,105.78..219,672.35 rows=34,402 width=60) (actual time=2,255.861..2,706.679 rows=16,905 loops=1)

57. 5.600 2,655.196 ↑ 1.5 16,905 1

Nested Loop Left Join (cost=555.66..209,479.42 rows=25,284 width=36) (actual time=2,255.843..2,655.196 rows=16,905 loops=1)

58. 8.872 2,581.976 ↑ 1.5 16,905 1

Hash Semi Join (cost=555.09..18,366.14 rows=25,284 width=20) (actual time=2,255.830..2,581.976 rows=16,905 loops=1)

  • Hash Cond: (t237_oppfact_2.c843_opp_stagename = cte0_9.c1)
59. 18.536 2,572.758 ↓ 1.0 83,696 1

Nested Loop (cost=554.90..17,874.50 rows=80,064 width=24) (actual time=2,255.432..2,572.758 rows=83,696 loops=1)

60. 3.861 937.156 ↓ 40.8 8,167 1

HashAggregate (cost=549.83..551.83 rows=200 width=4) (actual time=935.175..937.156 rows=8,167 loops=1)

  • Group Key: ownercte_3.c1
61. 933.295 933.295 ↑ 3.0 8,167 1

CTE Scan on ownercte ownercte_3 (cost=0.00..488.74 rows=24,437 width=4) (actual time=930.637..933.295 rows=8,167 loops=1)

62. 320.384 1,617.066 ↓ 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.173..0.198 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 14)))
  • Rows Removed by Filter: 7
63.          

SubPlan (forIndex Scan)

64. 1,296.682 1,296.682 ↓ 503.7 100,746 1

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

65. 0.004 0.346 ↑ 1.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
66. 0.342 0.342 ↑ 1.0 6 1

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

67. 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.55 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)
68. 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)

69. 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))
70. 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)
71.          

SubPlan (forNested Loop Left Join)

72. 0.453 0.453 ↑ 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.453 rows=13 loops=1)

73. 0.565 0.565 ↑ 3.0 8,167 1

CTE Scan on ownercte ownercte_2 (cost=0.00..488.74 rows=24,437 width=4) (actual time=0.001..0.565 rows=8,167 loops=1)

74. 0.013 2,716.575 ↑ 6,880.4 5 1

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

75. 2,716.562 2,716.562 ↑ 6,880.4 5 1

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

76. 0.024 56.375 ↑ 2,051.6 11 1

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

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

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

78. 0.004 0.011 ↑ 86.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
79. 0.007 0.007 ↑ 86.0 5 1

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