explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Pi8i

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 439.551 ↑ 2,874.6 12 1

Append (cost=983,375.85..984,525.34 rows=34,495 width=96) (actual time=437.876..439.551 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.010..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.342 0.342 ↑ 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.342 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. 0.885 11.132 ↑ 75.3 327 1

HashAggregate (cost=27,646.58..27,892.97 rows=24,639 width=4) (actual time=11.061..11.132 rows=327 loops=1)

  • Group Key: distinctclosedateowner.ownerid
8.          

CTE owners

9. 0.000 2.233 ↑ 2.0 941 1

Nested Loop Semi Join (cost=60.07..17,860.09 rows=1,862 width=4) (actual time=0.136..2.233 rows=941 loops=1)

10. 0.377 0.473 ↑ 2.0 941 1

Bitmap Heap Scan on t166_userrolehierarchy (cost=59.65..6,845.54 rows=1,862 width=8) (actual time=0.114..0.473 rows=941 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=133
11. 0.096 0.096 ↑ 2.0 951 1

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..59.18 rows=1,862 width=0) (actual time=0.096..0.096 rows=951 loops=1)

  • Index Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
12. 1.882 1.882 ↑ 1.0 1 941

Index Scan using ix_t67_userroledim_sid_end_start_timestamp_partial on t67_userroledim (cost=0.42..5.91 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=941)

  • Index Cond: ((sid = t166_userrolehierarchy.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
13. 0.754 10.247 ↑ 26.4 5,309 1

Nested Loop (cost=42.33..9,436.60 rows=139,956 width=4) (actual time=2.728..10.247 rows=5,309 loops=1)

14. 0.349 2.797 ↓ 3.7 744 1

HashAggregate (cost=41.90..43.90 rows=200 width=4) (actual time=2.697..2.797 rows=744 loops=1)

  • Group Key: owners.c1
15. 2.448 2.448 ↑ 2.0 941 1

CTE Scan on owners (cost=0.00..37.24 rows=1,862 width=4) (actual time=0.139..2.448 rows=941 loops=1)

16. 6.696 6.696 ↑ 1.6 7 744

Index Only Scan using ix_distinctclosedateowner_owner_closedate on distinctclosedateowner (cost=0.43..46.85 rows=11 width=4) (actual time=0.004..0.009 rows=7 loops=744)

  • Index Cond: ((ownerid = owners.c1) AND (closedate <= 20180430) AND (closedate >= 20180201))
  • Heap Fetches: 5309
17.          

CTE openwont1

18. 2.459 38.153 ↑ 3.9 1,966 1

Nested Loop Left Join (cost=1,110.31..113,298.91 rows=7,758 width=34) (actual time=11.760..38.153 rows=1,966 loops=1)

19. 1.188 31.740 ↑ 3.3 1,966 1

Nested Loop Left Join (cost=555.67..110,407.57 rows=6,562 width=56) (actual time=11.711..31.740 rows=1,966 loops=1)

20. 1.152 26.620 ↑ 2.8 1,966 1

Nested Loop Left Join (cost=555.67..106,034.07 rows=5,551 width=56) (actual time=11.692..26.620 rows=1,966 loops=1)

21. 0.369 17.604 ↑ 2.8 1,966 1

Nested Loop (cost=555.10..58,957.93 rows=5,551 width=28) (actual time=11.676..17.604 rows=1,966 loops=1)

22. 0.155 11.349 ↓ 1.6 327 1

HashAggregate (cost=554.38..556.38 rows=200 width=4) (actual time=11.286..11.349 rows=327 loops=1)

  • Group Key: ownercte_1.c1
23. 11.194 11.194 ↑ 75.3 327 1

CTE Scan on ownercte ownercte_1 (cost=0.00..492.78 rows=24,639 width=4) (actual time=11.063..11.194 rows=327 loops=1)

24. 5.540 5.886 ↓ 6.0 6 327

Index Scan using t236_opp_line_item_fact_564_timestamp on t236_opp_line_item_fact (cost=0.72..292.00 rows=1 width=32) (actual time=0.010..0.018 rows=6 loops=327)

  • 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 8)) AND (c805_opp_close_date >= 20180201) AND (c805_opp_close_date <= 20180430))
  • Rows Removed by Filter: 3
25.          

SubPlan (forIndex Scan)

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

27. 7.864 7.864 ↑ 1.0 1 1,966

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.47 rows=1 width=28) (actual time=0.004..0.004 rows=1 loops=1,966)

  • 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.932 ↑ 2.0 1 1,966

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

29. 0.000 0.000 ↓ 0.0 0 1,966

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,966)

  • 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.932 3.932 ↑ 1.0 1 1,966

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

  • 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.932 ↑ 2.0 1 1,966

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

32. 0.000 0.000 ↓ 0.0 0 1,966

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,966)

  • 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.932 3.932 ↑ 1.0 1 1,966

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,966)

  • 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.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.000..0.002 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 ↑ 75.3 327 1

CTE Scan on ownercte (cost=0.00..492.78 rows=24,639 width=4) (actual time=0.000..0.020 rows=327 loops=1)

38.          

CTE openwont1ids

39. 1.088 40.173 ↓ 9.8 1,966 1

HashAggregate (cost=174.56..176.56 rows=200 width=4) (actual time=39.934..40.173 rows=1,966 loops=1)

  • Group Key: cte0_5.c1
40. 39.085 39.085 ↑ 3.9 1,966 1

CTE Scan on openwont1 cte0_5 (cost=0.00..155.16 rows=7,758 width=4) (actual time=11.763..39.085 rows=1,966 loops=1)

41.          

CTE bucketab

42. 0.713 1.019 ↑ 97.0 8 1

HashAggregate (cost=271.53..279.29 rows=776 width=68) (actual time=1.015..1.019 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.306 0.306 ↑ 3.9 1,966 1

CTE Scan on openwont1 cte0_6 (cost=0.00..155.16 rows=7,758 width=64) (actual time=0.003..0.306 rows=1,966 loops=1)

44.          

CTE picklist_3

45. 0.414 0.414 ↑ 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.414 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.507 438.480 ↑ 8,429.8 4 1

GroupAggregate (cost=840,396.01..841,576.18 rows=33,719 width=120) (actual time=437.865..438.480 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 13)) 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 14) THEN 'other'::text ELSE 'ownerChange'::text END ELSE 'other'::text END)
48. 0.783 437.973 ↑ 16.2 2,080 1

Sort (cost=839,841.34..839,925.64 rows=33,719 width=60) (actual time=437.849..437.973 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 13)) 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 14) THEN 'other'::text ELSE 'ownerChange'::text END ELSE 'other'::text END)
  • Sort Method: quicksort Memory: 259kB
49. 1.388 437.190 ↑ 16.2 2,080 1

Nested Loop Left Join (cost=1,119.31..837,305.46 rows=33,719 width=60) (actual time=265.486..437.190 rows=2,080 loops=1)

50. 0.000 431.193 ↑ 13.7 2,080 1

Nested Loop Left Join (cost=564.64..826,009.09 rows=28,523 width=36) (actual time=265.475..431.193 rows=2,080 loops=1)

51. 10.022 425.111 ↑ 13.7 2,080 1

Hash Join (cost=564.08..620,950.28 rows=28,523 width=20) (actual time=265.467..425.111 rows=2,080 loops=1)

  • Hash Cond: (t236_opp_line_item_fact_2.c804_opp_ownerid = ownercte_3.c1)
52. 26.455 414.911 ↓ 2.2 130,155 1

Nested Loop (cost=5.20..620,158.12 rows=59,592 width=24) (actual time=265.087..414.911 rows=130,155 loops=1)

53. 0.011 0.334 ↑ 1.0 6 1

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

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

55. 347.457 388.122 ↓ 2.2 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..103,260.33 rows=9,932 width=28) (actual time=44.123..64.687 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 15)))
  • Rows Removed by Filter: 28
56.          

SubPlan (forIndex Scan)

57. 40.665 40.665 ↓ 9.8 1,966 1

CTE Scan on openwont1ids cte0_8 (cost=0.00..4.00 rows=200 width=4) (actual time=39.936..40.665 rows=1,966 loops=1)

58. 0.049 0.178 ↓ 1.6 327 1

Hash (cost=556.38..556.38 rows=200 width=4) (actual time=0.178..0.178 rows=327 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
59. 0.103 0.129 ↓ 1.6 327 1

HashAggregate (cost=554.38..556.38 rows=200 width=4) (actual time=0.100..0.129 rows=327 loops=1)

  • Group Key: ownercte_3.c1
60. 0.026 0.026 ↑ 75.3 327 1

CTE Scan on ownercte ownercte_3 (cost=0.00..492.78 rows=24,639 width=4) (actual time=0.001..0.026 rows=327 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..7.18 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.001..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.419 0.419 ↑ 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.419 rows=13 loops=1)

67. 0.030 0.030 ↑ 75.3 327 1

CTE Scan on ownercte ownercte_2 (cost=0.00..492.78 rows=24,639 width=4) (actual time=0.000..0.030 rows=327 loops=1)

68. 0.010 438.499 ↑ 8,429.8 4 1

Subquery Scan on *SELECT* 1 (cost=0.00..1,095.87 rows=33,719 width=96) (actual time=437.876..438.499 rows=4 loops=1)

69. 438.489 438.489 ↑ 8,429.8 4 1

CTE Scan on bucketd cte0 (cost=0.00..674.38 rows=33,719 width=120) (actual time=437.868..438.489 rows=4 loops=1)

70. 0.015 1.051 ↑ 97.0 8 1

Hash Join (cost=19.68..45.86 rows=776 width=96) (actual time=1.042..1.051 rows=8 loops=1)

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

CTE Scan on bucketab cte0_1 (cost=0.00..15.52 rows=776 width=68) (actual time=1.017..1.024 rows=8 loops=1)

72. 0.005 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
73. 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.005..0.007 rows=5 loops=1)