explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lBt

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 2,767.093 ↑ 3,618.7 15 1

Append (cost=1,757,160.99..1,758,957.53 rows=54,281 width=96) (actual time=2,709.167..2,767.093 rows=15 loops=1)

2.          

CTE picklist_0

3. 0.523 0.523 ↑ 1.0 6 1

Seq Scan on t233_opportunitystagenamepicklistdim (cost=0.00..46.54 rows=6 width=4) (actual time=0.010..0.523 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.328 0.328 ↑ 1.0 7 1

Seq Scan on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_1 (cost=0.00..48.08 rows=7 width=4) (actual time=0.014..0.328 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.503 945.141 ↑ 3.0 8,167 1

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

  • Group Key: distinctclosedateowner.ownerid
8. 58.256 928.638 ↑ 1.6 93,387 1

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

  • Hash Cond: (distinctclosedateowner.ownerid = t166_userrolehierarchy.sid)
9. 73.549 92.609 ↓ 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.135..92.609 rows=196,760 loops=1)

  • Recheck Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
  • Heap Blocks: exact=17913
10. 19.060 19.060 ↓ 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.060..19.060 rows=196,760 loops=1)

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

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

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

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

13. 68.088 744.850 ↑ 9.9 129,697 1

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

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

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

  • Hash Cond: (t166_userrolehierarchy.c662_user_role_id = t67_userroledim.sid)
15. 327.960 463.141 ↓ 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=142.496..463.141 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. 135.181 135.181 ↓ 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=135.181..135.181 rows=1,324,436 loops=1)

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

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

  • Buckets: 131072 Batches: 2 Memory Usage: 1177kB
18. 6.947 11.385 ↑ 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=4.921..11.385 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.438 4.438 ↑ 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.438..4.438 rows=13,978 loops=1)

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

CTE openwont1

21. 67.625 1,823.999 ↓ 6.3 71,448 1

Nested Loop Left Join (cost=1,101.22..144,633.81 rows=11,316 width=34) (actual time=949.007..1,823.999 rows=71,448 loops=1)

22. 36.653 1,612.896 ↓ 7.5 71,448 1

Nested Loop Left Join (cost=551.12..140,697.82 rows=9,505 width=56) (actual time=948.964..1,612.896 rows=71,448 loops=1)

23. 7.467 1,433.347 ↓ 8.9 71,448 1

Nested Loop Left Join (cost=551.12..135,515.69 rows=7,984 width=56) (actual time=948.947..1,433.347 rows=71,448 loops=1)

24. 10.026 1,140.088 ↓ 8.9 71,448 1

Nested Loop (cost=550.55..68,452.96 rows=7,984 width=28) (actual time=948.930..1,140.088 rows=71,448 loops=1)

25. 3.707 950.388 ↓ 40.8 8,167 1

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

  • Group Key: ownercte_1.c1
26. 946.681 946.681 ↑ 3.0 8,167 1

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

27. 179.343 179.674 ↓ 9.0 9 8,167

Index Scan using t236_opp_line_item_fact_564_timestamp on t236_opp_line_item_fact (cost=0.72..339.50 rows=1 width=32) (actual time=0.010..0.022 rows=9 loops=8,167)

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

SubPlan (forIndex Scan)

29. 0.331 0.331 ↑ 1.0 7 1

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

30. 285.792 285.792 ↑ 1.0 1 71,448

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.39 rows=1 width=28) (actual time=0.003..0.004 rows=1 loops=71,448)

  • 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)
  • Rows Removed by Filter: 0
31. 0.000 142.896 ↑ 2.0 1 71,448

Append (cost=0.00..0.63 rows=2 width=16) (actual time=0.002..0.002 rows=1 loops=71,448)

32. 0.000 0.000 ↓ 0.0 0 71,448

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=71,448)

  • 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))
33. 142.896 142.896 ↑ 1.0 1 71,448

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

  • 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)
34. 0.000 142.896 ↑ 2.0 1 71,448

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

35. 0.000 0.000 ↓ 0.0 0 71,448

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=71,448)

  • 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))
36. 142.896 142.896 ↑ 1.0 1 71,448

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=71,448)

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

SubPlan (forNested Loop Left Join)

38. 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.000..0.001 rows=6 loops=1)

39. 0.001 0.001 ↑ 1.0 6 1

CTE Scan on picklist_0 cte0_3 (cost=0.00..0.12 rows=6 width=4) (actual time=0.001..0.001 rows=6 loops=1)

40. 0.580 0.580 ↑ 3.0 8,167 1

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

41.          

CTE openwont1ids

42. 42.438 1,900.672 ↓ 357.2 71,448 1

HashAggregate (cost=254.61..256.61 rows=200 width=4) (actual time=1,889.272..1,900.672 rows=71,448 loops=1)

  • Group Key: cte0_5.c1
43. 1,858.234 1,858.234 ↓ 6.3 71,448 1

CTE Scan on openwont1 cte0_5 (cost=0.00..226.32 rows=11,316 width=4) (actual time=949.008..1,858.234 rows=71,448 loops=1)

44.          

CTE bucketab

45. 25.096 40.505 ↑ 113.2 10 1

HashAggregate (cost=396.06..407.38 rows=1,132 width=68) (actual time=40.500..40.505 rows=10 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
46. 15.409 15.409 ↓ 6.3 71,448 1

CTE Scan on openwont1 cte0_6 (cost=0.00..226.32 rows=11,316 width=64) (actual time=0.018..15.409 rows=71,448 loops=1)

47.          

CTE picklist_3

48. 0.424 0.424 ↑ 1.0 13 1

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

CTE bucketd

50. 12.066 2,726.539 ↑ 10,629.8 5 1

GroupAggregate (cost=1,179,053.66..1,180,913.87 rows=53,149 width=120) (actual time=2,709.156..2,726.539 rows=5 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)
51. 29.274 2,714.473 ↑ 1.0 51,240 1

Sort (cost=1,178,503.53..1,178,636.41 rows=53,149 width=60) (actual time=2,709.036..2,714.473 rows=51,240 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: external merge Disk: 2504kB
52. 26.178 2,685.199 ↑ 1.0 51,240 1

Nested Loop Left Join (cost=1,110.22..1,172,333.43 rows=53,149 width=60) (actual time=2,233.783..2,685.199 rows=51,240 loops=1)

53. 32.351 2,555.521 ↓ 1.1 51,240 1

Nested Loop Left Join (cost=560.10..1,155,269.08 rows=44,644 width=36) (actual time=2,233.764..2,555.521 rows=51,240 loops=1)

54. 19.125 2,420.690 ↓ 1.1 51,240 1

Hash Join (cost=559.53..855,739.64 rows=44,644 width=20) (actual time=2,233.755..2,420.690 rows=51,240 loops=1)

  • Hash Cond: (t236_opp_line_item_fact_2.c804_opp_ownerid = ownercte_3.c1)
55. 24.662 2,397.355 ↓ 1.3 118,137 1

Nested Loop (cost=5.20..854,819.42 rows=93,433 width=24) (actual time=2,229.481..2,397.355 rows=118,137 loops=1)

56. 0.012 0.539 ↑ 1.0 6 1

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

  • Group Key: cte0_9.c1
57. 0.527 0.527 ↑ 1.0 6 1

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

58. 457.561 2,372.154 ↓ 1.3 19,690 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..142,314.15 rows=15,572 width=28) (actual time=371.488..395.359 rows=19,690 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: 2031
59.          

SubPlan (forIndex Scan)

60. 1,914.593 1,914.593 ↓ 357.2 71,448 1

CTE Scan on openwont1ids cte0_8 (cost=0.00..4.00 rows=200 width=4) (actual time=1,889.274..1,914.593 rows=71,448 loops=1)

61. 1.063 4.210 ↓ 40.8 8,167 1

Hash (cost=551.83..551.83 rows=200 width=4) (actual time=4.210..4.210 rows=8,167 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 352kB
62. 2.555 3.147 ↓ 40.8 8,167 1

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

  • Group Key: ownercte_3.c1
63. 0.592 0.592 ↑ 3.0 8,167 1

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

64. 102.480 102.480 ↓ 0.0 0 51,240

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.70 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=51,240)

  • 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)
65. 0.000 102.480 ↑ 2.0 1 51,240

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

66. 0.000 0.000 ↓ 0.0 0 51,240

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=51,240)

  • 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))
67. 102.480 102.480 ↑ 1.0 1 51,240

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=51,240)

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

SubPlan (forNested Loop Left Join)

69. 0.427 0.427 ↑ 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.427 rows=13 loops=1)

70. 0.593 0.593 ↑ 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.593 rows=8,167 loops=1)

71. 0.010 2,726.554 ↑ 10,629.8 5 1

Subquery Scan on *SELECT* 1 (cost=0.00..1,727.34 rows=53,149 width=96) (actual time=2,709.166..2,726.554 rows=5 loops=1)

72. 2,726.544 2,726.544 ↑ 10,629.8 5 1

CTE Scan on bucketd cte0 (cost=0.00..1,062.98 rows=53,149 width=120) (actual time=2,709.159..2,726.544 rows=5 loops=1)

73. 0.011 40.535 ↑ 113.2 10 1

Hash Join (cost=19.68..57.88 rows=1,132 width=96) (actual time=40.523..40.535 rows=10 loops=1)

  • Hash Cond: (cte0_1.c6 = t234_opportunityforecastcategorypicklistdim.sid)
74. 40.514 40.514 ↑ 113.2 10 1

CTE Scan on bucketab cte0_1 (cost=0.00..22.64 rows=1,132 width=68) (actual time=40.503..40.514 rows=10 loops=1)

75. 0.003 0.010 ↑ 86.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
76. 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)