explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oQzc

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 552.895 ↑ 3,543.2 12 1

Append (cost=1,155,603.11..1,157,015.44 rows=42,519 width=96) (actual time=551.232..552.895 rows=12 loops=1)

2.          

CTE picklist_0

3. 0.320 0.320 ↑ 1.0 6 1

Seq Scan on t233_opportunitystagenamepicklistdim (cost=0.00..46.54 rows=6 width=4) (actual time=0.008..0.320 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.319 0.319 ↑ 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.319 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.062 110.861 ↑ 62.6 324 1

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

  • Group Key: distinctclosedateowner.ownerid
8. 22.965 109.799 ↑ 3.8 5,302 1

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

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

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

  • Recheck Cond: ((closedate <= 20180430) AND (closedate >= 20180201))
  • Heap Blocks: exact=13075
10. 23.104 23.104 ↓ 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.104..23.104 rows=275,806 loops=1)

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

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

  • Buckets: 2048 Batches: 1 Memory Usage: 42kB
12. 0.270 2.552 ↑ 2.5 737 1

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

  • Group Key: t166_userrolehierarchy.sid
13. 0.019 2.282 ↑ 2.0 934 1

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

14. 0.303 0.395 ↑ 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.395 rows=934 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=130
15. 0.092 0.092 ↑ 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.092..0.092 rows=944 loops=1)

  • Index Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
16. 1.868 1.868 ↑ 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.002..0.002 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. 2.074 137.752 ↑ 4.9 1,963 1

Nested Loop Left Join (cost=913.57..132,496.39 rows=9,589 width=34) (actual time=111.428..137.752 rows=1,963 loops=1)

19. 1.290 131.731 ↑ 4.1 1,963 1

Nested Loop Left Join (cost=457.30..129,177.08 rows=8,036 width=56) (actual time=111.387..131.731 rows=1,963 loops=1)

20. 1.080 126.515 ↑ 3.4 1,963 1

Nested Loop Left Join (cost=457.30..124,418.35 rows=6,734 width=56) (actual time=111.371..126.515 rows=1,963 loops=1)

21. 0.389 117.583 ↑ 3.4 1,963 1

Nested Loop (cost=456.73..67,581.86 rows=6,734 width=28) (actual time=111.358..117.583 rows=1,963 loops=1)

22. 0.117 111.038 ↓ 1.6 324 1

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

  • Group Key: ownercte_1.c1
23. 110.921 110.921 ↑ 62.6 324 1

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

24. 5.834 6.156 ↓ 6.0 6 324

Index Scan using t236_opp_line_item_fact_564_timestamp on t236_opp_line_item_fact (cost=0.72..335.61 rows=1 width=32) (actual time=0.011..0.019 rows=6 loops=324)

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

SubPlan (forIndex Scan)

26. 0.322 0.322 ↑ 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.322 rows=7 loops=1)

27. 7.852 7.852 ↑ 1.0 1 1,963

Index Scan using ix_t236_opp_line_item_fact_sid_timestamp on t236_opp_line_item_fact t236_opp_line_item_fact_1 (cost=0.56..8.43 rows=1 width=28) (actual time=0.004..0.004 rows=1 loops=1,963)

  • Index Cond: ((t236_opp_line_item_fact.sid = sid) AND (t236_opp_line_item_fact.sid = sid) AND (start_stamp <= '1540796399999'::bigint) AND (end_stamp > '1540796399999'::bigint))
  • Filter: (NOT deleted)
28. 0.000 3.926 ↑ 2.0 1 1,963

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

29. 0.000 0.000 ↓ 0.0 0 1,963

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

  • 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))
30. 3.926 3.926 ↑ 1.0 1 1,963

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur177_t0_r0_1 (cost=0.29..0.69 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1,963)

  • Index Cond: ((t236_opp_line_item_fact.c805_opp_close_date = rate_date) AND (t236_opp_line_item_fact.c821_opp_currency_code = sid))
  • Filter: (to_iso_code = 'USD'::text)
31. 0.000 3.926 ↑ 2.0 1 1,963

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

32. 0.000 0.000 ↓ 0.0 0 1,963

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

  • 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. 3.926 3.926 ↑ 1.0 1 1,963

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur177_t1_r1_1 (cost=0.29..0.32 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1,963)

  • Index Cond: ((t236_opp_line_item_fact_1.c805_opp_close_date = rate_date) AND (t236_opp_line_item_fact_1.c821_opp_currency_code = sid))
  • Filter: (to_iso_code = 'USD'::text)
34.          

SubPlan (forNested Loop Left Join)

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

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.020 0.020 ↑ 62.6 324 1

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

38.          

CTE openwont1ids

39. 0.878 139.452 ↓ 9.8 1,963 1

HashAggregate (cost=215.75..217.75 rows=200 width=4) (actual time=139.253..139.452 rows=1,963 loops=1)

  • Group Key: cte0_5.c1
40. 138.574 138.574 ↑ 4.9 1,963 1

CTE Scan on openwont1 cte0_5 (cost=0.00..191.78 rows=9,589 width=4) (actual time=111.430..138.574 rows=1,963 loops=1)

41.          

CTE bucketab

42. 0.668 1.017 ↑ 119.9 8 1

HashAggregate (cost=335.62..345.20 rows=959 width=68) (actual time=1.015..1.017 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
43. 0.349 0.349 ↑ 4.9 1,963 1

CTE Scan on openwont1 cte0_6 (cost=0.00..191.78 rows=9,589 width=64) (actual time=0.003..0.349 rows=1,963 loops=1)

44.          

CTE picklist_3

45. 0.392 0.392 ↑ 1.0 13 1

Seq Scan on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_2 (cost=0.00..57.31 rows=13 width=4) (actual time=0.005..0.392 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. 0.482 551.829 ↑ 10,390.0 4 1

GroupAggregate (cost=965,016.39..966,470.99 rows=41,560 width=120) (actual time=551.220..551.829 rows=4 loops=1)

  • Group Key: '1520668799999'::bigint, (CASE WHEN (t236_opp_line_item_fact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 12)) THEN CASE WHEN (t236_opp_line_item_fact_3.c805_opp_close_date > 20180430) THEN 'pulledIn'::text WHEN (t236_opp_line_item_fact_3.c805_opp_close_date < 20180201) THEN 'pushedIn'::text WHEN (hashed SubPlan 13) THEN 'other'::text ELSE 'ownerChange'::text END ELSE 'other'::text END)
48. 0.766 551.347 ↑ 20.0 2,080 1

Sort (cost=964,560.09..964,663.99 rows=41,560 width=60) (actual time=551.201..551.347 rows=2,080 loops=1)

  • Sort Key: (CASE WHEN (t236_opp_line_item_fact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 12)) THEN CASE WHEN (t236_opp_line_item_fact_3.c805_opp_close_date > 20180430) THEN 'pulledIn'::text WHEN (t236_opp_line_item_fact_3.c805_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: 259kB
49. 1.237 550.581 ↑ 20.0 2,080 1

Nested Loop Left Join (cost=922.57..961,371.84 rows=41,560 width=60) (actual time=376.312..550.581 rows=2,080 loops=1)

50. 0.000 544.762 ↑ 16.7 2,080 1

Nested Loop Left Join (cost=466.27..947,877.56 rows=34,828 width=36) (actual time=376.302..544.762 rows=2,080 loops=1)

51. 9.673 538.766 ↑ 16.7 2,080 1

Hash Join (cost=465.71..704,213.02 rows=34,828 width=20) (actual time=376.295..538.766 rows=2,080 loops=1)

  • Hash Cond: (t236_opp_line_item_fact_2.c804_opp_ownerid = ownercte_3.c1)
52. 25.512 528.928 ↓ 1.8 130,155 1

Nested Loop (cost=5.20..703,466.63 rows=73,025 width=24) (actual time=375.939..528.928 rows=130,155 loops=1)

53. 0.012 0.334 ↑ 1.0 6 1

HashAggregate (cost=0.14..0.20 rows=6 width=4) (actual time=0.327..0.334 rows=6 loops=1)

  • Group Key: cte0_9.c1
54. 0.322 0.322 ↑ 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.322 rows=6 loops=1)

55. 363.231 503.082 ↓ 1.8 21,692 6

Index Scan using ix_t236_opp_line_item_fact_closedate_stage_timestamp on t236_opp_line_item_fact t236_opp_line_item_fact_2 (cost=5.06..117,122.70 rows=12,171 width=28) (actual time=62.599..83.847 rows=21,692 loops=6)

  • Index Cond: ((c805_opp_close_date >= 20180201) AND (c805_opp_close_date <= 20180430) AND (c819_opp_stagename = cte0_9.c1) AND (start_stamp <= '1540796399999'::bigint) AND (end_stamp > '1540796399999'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 14)))
  • Rows Removed by Filter: 28
56.          

SubPlan (forIndex Scan)

57. 139.851 139.851 ↓ 9.8 1,963 1

CTE Scan on openwont1ids cte0_8 (cost=0.00..4.00 rows=200 width=4) (actual time=139.255..139.851 rows=1,963 loops=1)

58. 0.042 0.165 ↓ 1.6 324 1

Hash (cost=458.01..458.01 rows=200 width=4) (actual time=0.165..0.165 rows=324 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
59. 0.097 0.123 ↓ 1.6 324 1

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

  • Group Key: ownercte_3.c1
60. 0.026 0.026 ↑ 62.6 324 1

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

61. 6.240 6.240 ↓ 0.0 0 2,080

Index Scan using ix_t236_opp_line_item_fact_sid_timestamp on t236_opp_line_item_fact t236_opp_line_item_fact_3 (cost=0.56..6.99 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=2,080)

  • Index Cond: ((t236_opp_line_item_fact_2.sid = sid) AND (t236_opp_line_item_fact_2.sid = sid) AND (start_stamp <= '1520668799999'::bigint) AND (end_stamp > '1520668799999'::bigint))
  • Filter: (NOT deleted)
62. 0.000 4.160 ↑ 2.0 1 2,080

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

63. 0.000 0.000 ↓ 0.0 0 2,080

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=2,080)

  • Filter: ((to_iso_code = 'USD'::text) AND (t236_opp_line_item_fact_2.c821_opp_currency_code = sid) AND (t236_opp_line_item_fact_2.c805_opp_close_date = rate_date))
64. 4.160 4.160 ↑ 1.0 1 2,080

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

  • Index Cond: ((t236_opp_line_item_fact_2.c805_opp_close_date = rate_date) AND (t236_opp_line_item_fact_2.c821_opp_currency_code = sid))
  • Filter: (to_iso_code = 'USD'::text)
65.          

SubPlan (forNested Loop Left Join)

66. 0.397 0.397 ↑ 1.0 13 1

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

67. 0.025 0.025 ↑ 62.6 324 1

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

68. 0.009 551.845 ↑ 10,390.0 4 1

Subquery Scan on *SELECT* 1 (cost=0.00..1,350.70 rows=41,560 width=96) (actual time=551.232..551.845 rows=4 loops=1)

69. 551.836 551.836 ↑ 10,390.0 4 1

CTE Scan on bucketd cte0 (cost=0.00..831.20 rows=41,560 width=120) (actual time=551.223..551.836 rows=4 loops=1)

70. 0.019 1.048 ↑ 119.9 8 1

Hash Join (cost=19.68..52.04 rows=959 width=96) (actual time=1.041..1.048 rows=8 loops=1)

  • Hash Cond: (cte0_1.c6 = t234_opportunityforecastcategorypicklistdim.sid)
71. 1.020 1.020 ↑ 119.9 8 1

CTE Scan on bucketab cte0_1 (cost=0.00..19.18 rows=959 width=68) (actual time=1.016..1.020 rows=8 loops=1)

72. 0.004 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
73. 0.005 0.005 ↑ 86.0 5 1

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