explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lMNO

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 561.824 ↑ 3,543.2 12 1

Append (cost=1,155,684.94..1,157,097.27 rows=42,519 width=96) (actual time=560.155..561.824 rows=12 loops=1)

2.          

CTE picklist_0

3. 0.301 0.301 ↑ 1.0 6 1

Seq Scan on t233_opportunitystagenamepicklistdim (cost=0.00..46.54 rows=6 width=4) (actual time=0.008..0.301 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.012..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. 1.059 110.403 ↑ 62.6 324 1

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

  • Group Key: distinctclosedateowner.ownerid
8. 22.842 109.344 ↑ 3.8 5,302 1

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

  • Hash Cond: (distinctclosedateowner.ownerid = t166_userrolehierarchy.sid)
9. 60.863 84.030 ↓ 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.310..84.030 rows=275,806 loops=1)

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

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

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

  • Buckets: 2048 Batches: 1 Memory Usage: 42kB
12. 0.280 2.376 ↑ 2.5 737 1

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

  • Group Key: t166_userrolehierarchy.sid
13. 0.720 2.096 ↑ 2.0 934 1

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

14. 0.348 0.442 ↑ 2.0 934 1

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

  • Recheck Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=130
15. 0.094 0.094 ↑ 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.094..0.094 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. 2.114 137.370 ↑ 4.9 1,963 1

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

19. 1.192 131.303 ↑ 4.1 1,963 1

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

20. 1.211 126.185 ↑ 3.4 1,963 1

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

21. 0.354 117.122 ↑ 3.4 1,963 1

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

22. 0.129 110.612 ↓ 1.6 324 1

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

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

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

24. 5.831 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.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.013..0.325 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.000 0.000 ↑ 1.0 6 1

CTE Scan on picklist_0 cte0_2 (cost=0.00..0.12 rows=6 width=4) (actual time=0.000..0.000 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.027 0.027 ↑ 62.6 324 1

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

38.          

CTE openwont1ids

39. 0.937 139.129 ↓ 9.8 1,963 1

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

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

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

41.          

CTE bucketab

42. 0.691 1.028 ↑ 119.9 8 1

HashAggregate (cost=335.62..345.20 rows=959 width=68) (actual time=1.025..1.028 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.337 0.337 ↑ 4.9 1,963 1

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

44.          

CTE picklist_3

45. 0.388 0.388 ↑ 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.388 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.485 560.753 ↑ 10,390.0 4 1

GroupAggregate (cost=965,098.22..966,552.82 rows=41,560 width=120) (actual time=560.145..560.753 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.735 560.268 ↑ 20.0 2,080 1

Sort (cost=964,641.92..964,745.82 rows=41,560 width=60) (actual time=560.133..560.268 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.215 559.533 ↑ 20.0 2,080 1

Nested Loop Left Join (cost=922.57..961,453.67 rows=41,560 width=60) (actual time=384.595..559.533 rows=2,080 loops=1)

50. 0.000 553.740 ↑ 16.7 2,080 1

Nested Loop Left Join (cost=466.27..947,959.39 rows=34,828 width=36) (actual time=384.583..553.740 rows=2,080 loops=1)

51. 9.848 547.624 ↑ 16.7 2,080 1

Hash Join (cost=465.71..704,281.02 rows=34,828 width=20) (actual time=384.576..547.624 rows=2,080 loops=1)

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

Nested Loop (cost=5.20..703,534.63 rows=73,025 width=24) (actual time=384.210..537.603 rows=130,155 loops=1)

53. 0.010 0.315 ↑ 1.0 6 1

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

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

55. 372.337 511.848 ↓ 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,134.03 rows=12,171 width=28) (actual time=63.980..85.308 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.511 139.511 ↓ 9.8 1,963 1

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

58. 0.047 0.173 ↓ 1.6 324 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
59. 0.105 0.126 ↓ 1.6 324 1

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

  • Group Key: ownercte_3.c1
60. 0.021 0.021 ↑ 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.021 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.021 0.021 ↑ 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.021 rows=324 loops=1)

68. 0.010 560.767 ↑ 10,390.0 4 1

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

69. 560.757 560.757 ↑ 10,390.0 4 1

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

70. 0.012 1.056 ↑ 119.9 8 1

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

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

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

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

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