explain.depesz.com

PostgreSQL's explain analyze made readable

Result: k0Lu

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 280.765 ↑ 4,382.3 13 1

Append (cost=1,263,588.72..1,265,713.81 rows=56,970 width=96) (actual time=278.738..280.765 rows=13 loops=1)

2.          

CTE picklist_0

3. 0.329 0.329 ↑ 1.0 6 1

Seq Scan on t233_opportunitystagenamepicklistdim (cost=0.00..46.54 rows=6 width=4) (actual time=0.008..0.329 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.322 0.322 ↑ 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.322 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. 1.117 113.201 ↑ 62.6 324 1

HashAggregate (cost=55,718.16..55,920.83 rows=20,267 width=4) (actual time=113.116..113.201 rows=324 loops=1)

  • Group Key: distinctclosedateowner.ownerid
8. 23.229 112.084 ↑ 3.8 5,302 1

Hash Join (cost=23,549.56..55,667.49 rows=20,267 width=4) (actual time=28.652..112.084 rows=5,302 loops=1)

  • Hash Cond: (distinctclosedateowner.ownerid = t166_userrolehierarchy.sid)
9. 62.428 86.357 ↓ 1.0 275,806 1

Bitmap Heap Scan on distinctclosedateowner (cost=5,663.29..36,755.51 rows=266,815 width=4) (actual time=26.135..86.357 rows=275,806 loops=1)

  • Recheck Cond: ((closedate <= 20180430) AND (closedate >= 20180201))
  • Heap Blocks: exact=13075
10. 23.929 23.929 ↓ 1.0 275,806 1

Bitmap Index Scan on ix_closedate_distinctclosedateowner (cost=0.00..5,596.58 rows=266,815 width=0) (actual time=23.929..23.929 rows=275,806 loops=1)

  • Index Cond: ((closedate <= 20180430) AND (closedate >= 20180201))
11. 0.093 2.498 ↑ 2.5 737 1

Hash (cost=17,863.14..17,863.14 rows=1,851 width=4) (actual time=2.498..2.498 rows=737 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 42kB
12. 0.282 2.405 ↑ 2.5 737 1

HashAggregate (cost=17,844.63..17,863.14 rows=1,851 width=4) (actual time=2.326..2.405 rows=737 loops=1)

  • Group Key: t166_userrolehierarchy.sid
13. 0.719 2.123 ↑ 2.0 934 1

Nested Loop Semi Join (cost=60.05..17,839.98 rows=1,860 width=4) (actual time=0.121..2.123 rows=934 loops=1)

14. 0.379 0.470 ↑ 2.0 934 1

Bitmap Heap Scan on t166_userrolehierarchy (cost=59.63..6,838.33 rows=1,860 width=8) (actual time=0.107..0.470 rows=934 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=130
15. 0.091 0.091 ↑ 2.0 944 1

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..59.16 rows=1,860 width=0) (actual time=0.091..0.091 rows=944 loops=1)

  • Index Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
16. 0.934 0.934 ↑ 1.0 1 934

Index Scan using ix_t67_userroledim_sid_timestamp_partial on t67_userroledim (cost=0.42..5.90 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=934)

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

CTE openwont1

18. 8.591 149.749 ↑ 68.7 3,283 1

Merge Right Join (cost=953,418.17..970,400.46 rows=225,684 width=34) (actual time=122.493..149.749 rows=3,283 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))
19. 6.319 54.851 ↑ 1.1 95,632 1

Merge Append (cost=0.31..8,292.46 rows=101,614 width=16) (actual time=0.026..54.851 rows=95,632 loops=1)

  • Sort Key: cur177_t1_r1.rate_date, cur177_t1_r1.sid
20. 0.008 0.010 ↓ 0.0 0 1

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

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

  • Filter: (to_iso_code = 'USD'::text)
22. 48.522 48.522 ↑ 1.1 95,632 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.015..48.522 rows=95,632 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
23. 0.299 86.281 ↑ 50.5 3,283 1

Materialize (cost=952,913.08..953,742.42 rows=165,868 width=56) (actual time=85.673..86.281 rows=3,283 loops=1)

24. 1.713 85.982 ↑ 50.5 3,283 1

Sort (cost=952,913.08..953,327.75 rows=165,868 width=56) (actual time=85.670..85.982 rows=3,283 loops=1)

  • Sort Key: t237_oppfact_1.c836_opp_close_date, t237_oppfact_1.c845_opp_currency_code
  • Sort Method: quicksort Memory: 558kB
25. 6.661 84.269 ↑ 50.5 3,283 1

Hash Left Join (cost=4,698.67..932,862.59 rows=165,868 width=56) (actual time=48.347..84.269 rows=3,283 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))
26. 2.797 30.213 ↑ 37.1 3,283 1

Nested Loop Left Join (cost=457.30..897,569.21 rows=121,905 width=56) (actual time=0.452..30.213 rows=3,283 loops=1)

27. 0.470 11.001 ↑ 37.1 3,283 1

Nested Loop (cost=456.73..24,780.21 rows=121,905 width=28) (actual time=0.440..11.001 rows=3,283 loops=1)

28. 0.136 0.163 ↓ 1.6 324 1

HashAggregate (cost=456.01..458.01 rows=200 width=4) (actual time=0.082..0.163 rows=324 loops=1)

  • Group Key: ownercte_1.c1
29. 0.027 0.027 ↑ 62.6 324 1

CTE Scan on ownercte ownercte_1 (cost=0.00..405.34 rows=20,267 width=4) (actual time=0.000..0.027 rows=324 loops=1)

30. 10.042 10.368 ↑ 1.4 10 324

Index Scan using ix_t237_oppfact_owner_closedate_timestamp on t237_oppfact (cost=0.72..121.47 rows=14 width=32) (actual time=0.013..0.032 rows=10 loops=324)

  • 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: 4
31.          

SubPlan (forIndex Scan)

32. 0.326 0.326 ↑ 1.0 7 1

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

33. 16.415 16.415 ↑ 1.0 1 3,283

Index Scan using ix_t237_oppfact_sid_timestamp on t237_oppfact t237_oppfact_1 (cost=0.56..7.15 rows=1 width=28) (actual time=0.005..0.005 rows=1 loops=3,283)

  • Index Cond: ((t237_oppfact.sid = sid) AND (t237_oppfact.sid = sid) AND (start_stamp <= '1540796399999'::bigint) AND (end_stamp > '1540796399999'::bigint))
  • Filter: (NOT deleted)
34. 19.340 47.395 ↑ 1.0 101,598 1

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

  • Buckets: 131072 Batches: 2 Memory Usage: 3409kB
35. 6.095 28.055 ↑ 1.0 101,598 1

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

36. 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)
37. 21.959 21.959 ↑ 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.115..21.959 rows=101,598 loops=1)

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

SubPlan (forMerge Right Join)

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

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

41. 0.024 0.024 ↑ 62.6 324 1

CTE Scan on ownercte (cost=0.00..405.34 rows=20,267 width=4) (actual time=0.000..0.024 rows=324 loops=1)

42.          

CTE openwont1ids

43. 1.447 152.249 ↓ 16.4 3,283 1

HashAggregate (cost=5,077.89..5,079.89 rows=200 width=4) (actual time=151.834..152.249 rows=3,283 loops=1)

  • Group Key: cte0_5.c1
44. 150.802 150.802 ↑ 68.7 3,283 1

CTE Scan on openwont1 cte0_5 (cost=0.00..4,513.68 rows=225,684 width=4) (actual time=122.495..150.802 rows=3,283 loops=1)

45.          

CTE bucketab

46. 1.160 1.815 ↑ 2,821.0 8 1

HashAggregate (cost=7,898.94..8,124.62 rows=22,568 width=68) (actual time=1.774..1.815 rows=8 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
47. 0.655 0.655 ↑ 68.7 3,283 1

CTE Scan on openwont1 cte0_6 (cost=0.00..4,513.68 rows=225,684 width=64) (actual time=0.002..0.655 rows=3,283 loops=1)

48.          

CTE picklist_3

49. 0.401 0.401 ↑ 1.0 13 1

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

CTE bucketd

51. 0.157 278.907 ↑ 6,880.4 5 1

GroupAggregate (cost=222,706.91..223,910.98 rows=34,402 width=120) (actual time=278.728..278.907 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)
52. 0.200 278.750 ↑ 59.0 583 1

Sort (cost=222,250.61..222,336.62 rows=34,402 width=60) (actual time=278.711..278.750 rows=583 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: 70kB
53. 0.675 278.550 ↑ 59.0 583 1

Nested Loop Left Join (cost=918.13..219,658.39 rows=34,402 width=60) (actual time=267.422..278.550 rows=583 loops=1)

54. 0.023 276.283 ↑ 43.4 583 1

Nested Loop Left Join (cost=461.83..209,559.28 rows=25,284 width=36) (actual time=267.409..276.283 rows=583 loops=1)

55. 0.248 273.928 ↑ 43.4 583 1

Hash Semi Join (cost=461.27..18,272.32 rows=25,284 width=20) (actual time=267.402..273.928 rows=583 loops=1)

  • Hash Cond: (t237_oppfact_2.c843_opp_stagename = cte0_9.c1)
56. 0.530 273.342 ↑ 43.1 1,858 1

Nested Loop (cost=461.07..17,780.68 rows=80,064 width=24) (actual time=267.021..273.342 rows=1,858 loops=1)

57. 0.140 113.404 ↓ 1.6 324 1

HashAggregate (cost=456.01..458.01 rows=200 width=4) (actual time=113.340..113.404 rows=324 loops=1)

  • Group Key: ownercte_3.c1
58. 113.264 113.264 ↑ 62.6 324 1

CTE Scan on ownercte ownercte_3 (cost=0.00..405.34 rows=20,267 width=4) (actual time=113.117..113.264 rows=324 loops=1)

59. 6.423 159.408 ↑ 1.5 6 324

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.483..0.492 rows=6 loops=324)

  • 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: 5
60.          

SubPlan (forIndex Scan)

61. 152.985 152.985 ↓ 16.4 3,283 1

CTE Scan on openwont1ids cte0_8 (cost=0.00..4.00 rows=200 width=4) (actual time=151.835..152.985 rows=3,283 loops=1)

62. 0.005 0.338 ↑ 1.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
63. 0.333 0.333 ↑ 1.0 6 1

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

64. 2.332 2.332 ↓ 0.0 0 583

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=583)

  • 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)
65. 0.000 1.166 ↑ 2.0 1 583

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

66. 0.000 0.000 ↓ 0.0 0 583

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=583)

  • 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))
67. 1.166 1.166 ↑ 1.0 1 583

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=583)

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

SubPlan (forNested Loop Left Join)

69. 0.403 0.403 ↑ 1.0 13 1

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

70. 0.023 0.023 ↑ 62.6 324 1

CTE Scan on ownercte ownercte_2 (cost=0.00..405.34 rows=20,267 width=4) (actual time=0.001..0.023 rows=324 loops=1)

71. 0.011 278.922 ↑ 6,880.4 5 1

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

72. 278.911 278.911 ↑ 6,880.4 5 1

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

73. 0.016 1.842 ↑ 2,821.0 8 1

Hash Join (cost=19.68..781.35 rows=22,568 width=96) (actual time=1.797..1.842 rows=8 loops=1)

  • Hash Cond: (cte0_1.c6 = t234_opportunityforecastcategorypicklistdim.sid)
74. 1.817 1.817 ↑ 2,821.0 8 1

CTE Scan on bucketab cte0_1 (cost=0.00..451.36 rows=22,568 width=68) (actual time=1.775..1.817 rows=8 loops=1)

75. 0.005 0.009 ↑ 86.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
76. 0.004 0.004 ↑ 86.0 5 1

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