explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Xwig

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 3,561.757 ↑ 3,303.2 10 1

Append (cost=1,318,422.72..1,319,520.83 rows=33,032 width=96) (actual time=3,509.510..3,561.757 rows=10 loops=1)

2.          

CTE picklist_0

3. 0.017 0.017 ↑ 1.0 1 1

Seq Scan on t60_opportunitystagenamepicklistdim (cost=0.00..2.48 rows=1 width=4) (actual time=0.012..0.017 rows=1 loops=1)

  • Filter: (correlated_value = 'Win - 100%'::text)
  • Rows Removed by Filter: 18
4.          

CTE docd_cte_1

5. 19.600 1,651.108 ↑ 2.8 13,187 1

HashAggregate (cost=405,855.62..406,231.02 rows=37,540 width=4) (actual time=1,649.455..1,651.108 rows=13,187 loops=1)

  • Group Key: t67_distinct_opp_owner_closedate.c759_ownerid
6. 88.163 1,631.508 ↑ 1.0 99,727 1

Hash Join (cost=225,550.22..405,604.64 rows=100,391 width=4) (actual time=802.647..1,631.508 rows=99,727 loops=1)

  • Hash Cond: (t67_distinct_opp_owner_closedate.c759_ownerid = t55_userrolehierarchy.sid)
7. 742.446 742.446 ↓ 1.1 213,595 1

Seq Scan on t67_distinct_opp_owner_closedate (cost=0.00..156,103.63 rows=200,782 width=4) (actual time=0.307..742.446 rows=213,595 loops=1)

  • Filter: ((c760_closedate >= 20190202) AND (c760_closedate <= 20190503))
  • Rows Removed by Filter: 5954245
8. 51.411 800.899 ↑ 1.0 363,469 1

Hash (cost=219,341.13..219,341.13 rows=378,407 width=4) (actual time=800.899..800.899 rows=363,469 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 2639kB
9. 47.845 749.488 ↑ 1.0 363,469 1

Unique (cost=213,665.03..215,557.06 rows=378,407 width=4) (actual time=655.266..749.488 rows=363,469 loops=1)

10. 224.171 701.643 ↓ 1.0 389,844 1

Sort (cost=213,665.03..214,611.04 rows=378,407 width=4) (actual time=655.265..701.643 rows=389,844 loops=1)

  • Sort Key: t55_userrolehierarchy.sid
  • Sort Method: external merge Disk: 5336kB
11. 27.124 477.472 ↓ 1.0 389,844 1

Append (cost=25,500.11..173,429.91 rows=378,407 width=4) (actual time=68.549..477.472 rows=389,844 loops=1)

12. 124.206 333.284 ↓ 1.2 389,678 1

Hash Join (cost=25,500.11..120,368.88 rows=316,841 width=4) (actual time=68.548..333.284 rows=389,678 loops=1)

  • Hash Cond: (t55_userrolehierarchy.c697_user_role_id = t33_userroledim.sid)
13. 149.973 195.823 ↑ 1.0 550,251 1

Bitmap Heap Scan on t55_userrolehierarchy (cost=16,969.25..109,211.80 rows=563,970 width=8) (actual time=55.150..195.823 rows=550,251 loops=1)

  • Recheck Cond: ((c698_ancestor_role_id = 437) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=50118
14. 45.850 45.850 ↑ 1.0 550,257 1

Bitmap Index Scan on idx_49_t55_userrolehierarchy_c698_ancestor_role_id_end_stamp_st (cost=0.00..16,828.26 rows=563,970 width=0) (actual time=45.850..45.850 rows=550,257 loops=1)

  • Index Cond: ((c698_ancestor_role_id = 437) AND (end_stamp = '32503680000000'::bigint))
15. 3.797 13.255 ↓ 1.0 26,450 1

Hash (cost=8,207.82..8,207.82 rows=25,843 width=4) (actual time=13.255..13.255 rows=26,450 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1186kB
16. 7.697 9.458 ↓ 1.0 26,450 1

Bitmap Heap Scan on t33_userroledim (cost=720.57..8,207.82 rows=25,843 width=4) (actual time=1.854..9.458 rows=26,450 loops=1)

  • Recheck Cond: ((end_stamp = '32503680000000'::bigint) AND ((c412_portaltype IS NULL) OR (c412_portaltype = 'None'::text)))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=735
17. 1.761 1.761 ↓ 1.0 26,451 1

Bitmap Index Scan on "idx_9_t33_userroledim_sid_start_stamp_end_stamp_(c412_portaltyp" (cost=0.00..714.11 rows=25,843 width=0) (actual time=1.761..1.761 rows=26,451 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
18. 101.245 117.064 ↑ 370.9 166 1

Bitmap Heap Scan on t47_userdim (cost=5,627.51..49,276.97 rows=61,566 width=4) (actual time=18.442..117.064 rows=166 loops=1)

  • Recheck Cond: ((c577_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c587_usertype = 'Standard'::text))
  • Rows Removed by Filter: 188439
  • Heap Blocks: exact=15559
19. 15.819 15.819 ↓ 1.0 188,605 1

Bitmap Index Scan on idx_6_t47_userdim_c577_userroleid_end_stamp_start_stamp_null (cost=0.00..5,612.12 rows=187,969 width=0) (actual time=15.819..15.819 rows=188,605 loops=1)

  • Index Cond: ((c577_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
20.          

CTE picklist_4

21. 0.015 0.015 ↑ 1.0 1 1

Seq Scan on t60_opportunitystagenamepicklistdim t60_opportunitystagenamepicklistdim_1 (cost=0.00..2.48 rows=1 width=4) (actual time=0.010..0.015 rows=1 loops=1)

  • Filter: (correlated_value = 'Lost, Cancelled - 0%'::text)
  • Rows Removed by Filter: 18
22.          

CTE openwont1

23. 80.397 1,016.044 ↑ 1.6 94,932 1

Merge Right Join (cost=784,750.97..795,356.99 rows=156,380 width=34) (actual time=876.460..1,016.044 rows=94,932 loops=1)

  • Merge Cond: ((t54_clari_conversion_rate_1.sid = t52_opp_line_item_fact_1.c641_opp_currency_code) AND (t54_clari_conversion_rate_1.rate_date = t52_opp_line_item_fact_1.c658_opp_close_date))
24. 5.370 40.967 ↑ 1.0 65,896 1

Merge Append (cost=0.31..5,835.47 rows=66,109 width=16) (actual time=0.029..40.967 rows=65,896 loops=1)

  • Sort Key: t54_clari_conversion_rate_1.sid, t54_clari_conversion_rate_1.rate_date
25. 0.009 0.012 ↓ 0.0 0 1

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

  • Sort Key: t54_clari_conversion_rate_1.sid, t54_clari_conversion_rate_1.rate_date
  • Sort Method: quicksort Memory: 25kB
26. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on t54_clari_conversion_rate t54_clari_conversion_rate_1 (cost=0.00..0.00 rows=1 width=16) (actual time=0.002..0.003 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
27. 35.585 35.585 ↑ 1.0 65,896 1

Index Scan using t54_clari_conversion_rate_usd_sidratedateunique on t54_clari_conversion_rate_usd t54_clari_conversion_rate_usd_1 (cost=0.29..5,009.08 rows=66,108 width=16) (actual time=0.016..35.585 rows=65,896 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
28. 10.175 893.630 ↑ 1.6 94,932 1

Materialize (cost=783,905.96..784,687.86 rows=156,380 width=56) (actual time=869.701..893.630 rows=94,932 loops=1)

29. 99.875 883.455 ↑ 1.6 94,932 1

Sort (cost=783,905.96..784,296.91 rows=156,380 width=56) (actual time=869.699..883.455 rows=94,932 loops=1)

  • Sort Key: t52_opp_line_item_fact_1.c641_opp_currency_code, t52_opp_line_item_fact_1.c658_opp_close_date
  • Sort Method: external sort Disk: 6496kB
30. 28.213 783.580 ↑ 1.6 94,932 1

Nested Loop Left Join (cost=35,505.02..765,066.52 rows=156,380 width=56) (actual time=381.864..783.580 rows=94,932 loops=1)

31. 23.761 470.571 ↑ 1.6 94,932 1

Merge Right Join (cost=35,504.46..43,373.83 rows=156,380 width=28) (actual time=381.838..470.571 rows=94,932 loops=1)

  • Merge Cond: ((t54_clari_conversion_rate.sid = t52_opp_line_item_fact.c641_opp_currency_code) AND (t54_clari_conversion_rate.rate_date = t52_opp_line_item_fact.c658_opp_close_date))
32. 5.535 43.254 ↑ 1.0 65,896 1

Merge Append (cost=0.31..5,835.47 rows=66,109 width=16) (actual time=0.010..43.254 rows=65,896 loops=1)

  • Sort Key: t54_clari_conversion_rate.sid, t54_clari_conversion_rate.rate_date
33. 0.003 0.003 ↓ 0.0 0 1

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

  • Sort Key: t54_clari_conversion_rate.sid, t54_clari_conversion_rate.rate_date
  • Sort Method: quicksort Memory: 25kB
34. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on t54_clari_conversion_rate (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
35. 37.716 37.716 ↑ 1.0 65,896 1

Index Scan using t54_clari_conversion_rate_usd_sidratedateunique on t54_clari_conversion_rate_usd (cost=0.29..5,009.08 rows=66,108 width=16) (actual time=0.006..37.716 rows=65,896 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
36. 10.137 403.556 ↑ 1.6 94,932 1

Materialize (cost=35,504.15..36,286.05 rows=156,380 width=28) (actual time=378.483..403.556 rows=94,932 loops=1)

37. 82.451 393.419 ↑ 1.6 94,932 1

Sort (cost=35,504.15..35,895.10 rows=156,380 width=28) (actual time=378.479..393.419 rows=94,932 loops=1)

  • Sort Key: t52_opp_line_item_fact.c641_opp_currency_code, t52_opp_line_item_fact.c658_opp_close_date
  • Sort Method: external merge Disk: 3896kB
38. 14.263 310.968 ↑ 1.6 94,932 1

Nested Loop (cost=845.24..18,267.70 rows=156,380 width=28) (actual time=4.348..310.968 rows=94,932 loops=1)

39. 5.567 6.591 ↓ 65.9 13,187 1

HashAggregate (cost=844.65..846.65 rows=200 width=4) (actual time=4.294..6.591 rows=13,187 loops=1)

  • Group Key: cte0_6.c1
40. 1.024 1.024 ↑ 2.8 13,187 1

CTE Scan on docd_cte_1 cte0_6 (cost=0.00..750.80 rows=37,540 width=4) (actual time=0.001..1.024 rows=13,187 loops=1)

41. 290.097 290.114 ↑ 1.6 7 13,187

Index Scan using ix_t52_opp_line_item_fact_notnullowner_closedate_timestamp on t52_opp_line_item_fact (cost=0.58..87.00 rows=11 width=32) (actual time=0.009..0.022 rows=7 loops=13,187)

  • Index Cond: ((c647_opp_ownerid = cte0_6.c1) AND (c658_opp_close_date >= 20190202) AND (c658_opp_close_date <= 20190503) AND (end_stamp > '1551513599999'::bigint) AND (start_stamp <= '1551513599999'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 7)))
  • Rows Removed by Filter: 12
42.          

SubPlan (for Index Scan)

43. 0.017 0.017 ↑ 1.0 1 1

CTE Scan on picklist_4 cte0_5 (cost=0.00..0.02 rows=1 width=4) (actual time=0.012..0.017 rows=1 loops=1)

44. 284.796 284.796 ↑ 1.0 1 94,932

Index Scan using t52_opp_line_item_fact_sidendstampunique on t52_opp_line_item_fact t52_opp_line_item_fact_1 (cost=0.56..4.60 rows=1 width=28) (actual time=0.003..0.003 rows=1 loops=94,932)

  • Index Cond: ((t52_opp_line_item_fact.sid = sid) AND (t52_opp_line_item_fact.sid = sid) AND (end_stamp > '1572073199999'::bigint))
  • Filter: ((NOT deleted) AND (start_stamp <= '1572073199999'::bigint))
  • Rows Removed by Filter: 0
45.          

SubPlan (for Merge Right Join)

46. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on picklist_0 cte0_2 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

47. 1.048 1.048 ↑ 2.8 13,187 1

CTE Scan on docd_cte_1 cte0_3 (cost=0.00..750.80 rows=37,540 width=4) (actual time=0.001..1.048 rows=13,187 loops=1)

48. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on picklist_0 cte0_4 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

49.          

CTE openwont1ids

50. 43.767 1,098.103 ↓ 474.7 94,932 1

HashAggregate (cost=3,518.55..3,520.55 rows=200 width=4) (actual time=1,084.313..1,098.103 rows=94,932 loops=1)

  • Group Key: cte0_7.c1
51. 1,054.336 1,054.336 ↑ 1.6 94,932 1

CTE Scan on openwont1 cte0_7 (cost=0.00..3,127.60 rows=156,380 width=4) (actual time=876.462..1,054.336 rows=94,932 loops=1)

52.          

CTE bucketab

53. 32.350 52.176 ↑ 1,737.6 9 1

HashAggregate (cost=5,473.30..5,629.68 rows=15,638 width=68) (actual time=52.155..52.176 rows=9 loops=1)

  • Group Key: cte0_8.c5, CASE WHEN cte0_8.c6 THEN CASE WHEN cte0_8.c4 THEN 'convertedWon'::text ELSE 'convertedOpen'::text END ELSE CASE WHEN cte0_8.c4 THEN 'unconvertedWon'::text ELSE 'unconvertedOpen'::text END END, cte0_8.c3
54. 19.826 19.826 ↑ 1.6 94,932 1

CTE Scan on openwont1 cte0_8 (cost=0.00..3,127.60 rows=156,380 width=64) (actual time=0.024..19.826 rows=94,932 loops=1)

55.          

CTE picklist_5

56. 0.000 0.000 ↓ 0.0 0

Seq Scan on t60_opportunitystagenamepicklistdim t60_opportunitystagenamepicklistdim_2 (cost=0.00..2.48 rows=2 width=4) (never executed)

  • Filter: (correlated_value = ANY ('{"Lost, Cancelled - 0%","Win - 100%"}'::text[]))
57.          

CTE bucketd

58. 21.706 3,509.523 ↑ 17,394.0 1 1

HashAggregate (cost=107,242.20..107,677.05 rows=17,394 width=120) (actual time=3,509.495..3,509.523 rows=1 loops=1)

  • Group Key: '1551513599999'::bigint, CASE WHEN (t52_opp_line_item_fact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 12)) THEN CASE WHEN (t52_opp_line_item_fact_3.c658_opp_close_date > 20190503) THEN 'pulledIn'::text WHEN (t52_opp_line_item_fact_3.c658_opp_close_date < 20190202) THEN 'pushedIn'::text WHEN ((t52_opp_line_item_fact_3.c658_opp_close_date >= 20190202) AND (t52_opp_line_item_fact_3.c658_opp_close_date <= 20190503) AND (hashed SubPlan 13) AND (t52_opp_line_item_fact_3.c647_opp_ownerid IS NOT NULL)) THEN 'other'::text ELSE 'ownerChange'::text END ELSE 'other'::text END
59. 38.011 3,487.817 ↓ 2.8 48,054 1

Nested Loop Left Join (cost=1,695.00..106,180.08 rows=17,394 width=60) (actual time=2,801.783..3,487.817 rows=48,054 loops=1)

60. 23.147 3,353.698 ↓ 2.8 48,054 1

Nested Loop Left Join (cost=849.75..28,069.81 rows=17,394 width=20) (actual time=2,801.766..3,353.698 rows=48,054 loops=1)

61. 23.284 3,234.443 ↓ 2.8 48,054 1

Hash Semi Join (cost=849.75..22,030.68 rows=17,394 width=20) (actual time=2,801.745..3,234.443 rows=48,054 loops=1)

  • Hash Cond: (t52_opp_line_item_fact_2.c650_opp_stagename = cte0_13.c1)
62. 34.700 3,211.137 ↓ 1.3 221,569 1

Nested Loop (cost=849.71..21,380.49 rows=173,961 width=24) (actual time=2,801.669..3,211.137 rows=221,569 loops=1)

63. 6.231 1,659.932 ↓ 65.9 13,187 1

HashAggregate (cost=844.65..846.65 rows=200 width=4) (actual time=1,656.877..1,659.932 rows=13,187 loops=1)

  • Group Key: cte0_12.c1
64. 1,653.701 1,653.701 ↑ 2.8 13,187 1

CTE Scan on docd_cte_1 cte0_12 (cost=0.00..750.80 rows=37,540 width=4) (actual time=1,649.456..1,653.701 rows=13,187 loops=1)

65. 395.828 1,516.505 ↓ 1.4 17 13,187

Index Scan using ix_t52_opp_line_item_fact_notnullowner_closedate_timestamp on t52_opp_line_item_fact t52_opp_line_item_fact_2 (cost=5.06..102.55 rows=12 width=28) (actual time=0.095..0.115 rows=17 loops=13,187)

  • Index Cond: ((c647_opp_ownerid = cte0_12.c1) AND (c658_opp_close_date >= 20190202) AND (c658_opp_close_date <= 20190503) AND (end_stamp > '1572073199999'::bigint) AND (start_stamp <= '1572073199999'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 14)))
  • Rows Removed by Filter: 7
66.          

SubPlan (for Index Scan)

67. 1,120.677 1,120.677 ↓ 474.7 94,932 1

CTE Scan on openwont1ids cte0_11 (cost=0.00..4.00 rows=200 width=4) (actual time=1,084.314..1,120.677 rows=94,932 loops=1)

68. 0.004 0.022 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=4) (actual time=0.022..0.022 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
69. 0.018 0.018 ↑ 1.0 1 1

CTE Scan on picklist_0 cte0_13 (cost=0.00..0.02 rows=1 width=4) (actual time=0.013..0.018 rows=1 loops=1)

70. 0.000 96.108 ↑ 2.0 1 48,054

Append (cost=0.00..0.33 rows=2 width=16) (actual time=0.002..0.002 rows=1 loops=48,054)

71. 0.000 0.000 ↓ 0.0 0 48,054

Seq Scan on t54_clari_conversion_rate t54_clari_conversion_rate_2 (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=48,054)

  • Filter: ((to_iso_code = 'USD'::text) AND (t52_opp_line_item_fact_2.c641_opp_currency_code = sid) AND (t52_opp_line_item_fact_2.c658_opp_close_date = rate_date))
72. 96.108 96.108 ↑ 1.0 1 48,054

Index Scan using t54_clari_conversion_rate_usd_sidratedateunique on t54_clari_conversion_rate_usd t54_clari_conversion_rate_usd_2 (cost=0.29..0.33 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=48,054)

  • Index Cond: ((t52_opp_line_item_fact_2.c641_opp_currency_code = sid) AND (t52_opp_line_item_fact_2.c658_opp_close_date = rate_date))
  • Filter: (to_iso_code = 'USD'::text)
73. 96.108 96.108 ↓ 0.0 0 48,054

Index Scan using t52_opp_line_item_fact_sidendstampunique on t52_opp_line_item_fact t52_opp_line_item_fact_3 (cost=0.56..4.42 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=48,054)

  • Index Cond: ((t52_opp_line_item_fact_2.sid = sid) AND (t52_opp_line_item_fact_2.sid = sid) AND (end_stamp > '1551513599999'::bigint))
  • Filter: ((NOT deleted) AND (start_stamp <= '1551513599999'::bigint))
  • Rows Removed by Filter: 1
74.          

SubPlan (for Nested Loop Left Join)

75. 0.000 0.000 ↓ 0.0 0

CTE Scan on picklist_5 cte0_9 (cost=0.00..0.04 rows=2 width=4) (never executed)

76. 0.000 0.000 ↓ 0.0 0

CTE Scan on docd_cte_1 cte0_10 (cost=0.00..750.80 rows=37,540 width=4) (never executed)

77. 0.013 3,509.538 ↑ 17,394.0 1 1

Subquery Scan on *SELECT* 1 (cost=0.00..565.30 rows=17,394 width=96) (actual time=3,509.510..3,509.538 rows=1 loops=1)

78. 3,509.525 3,509.525 ↑ 17,394.0 1 1

CTE Scan on bucketd cte0 (cost=0.00..347.88 rows=17,394 width=120) (actual time=3,509.497..3,509.525 rows=1 loops=1)

79. 0.017 52.215 ↑ 1,737.6 9 1

Hash Left Join (cost=1.11..376.43 rows=15,638 width=72) (actual time=52.189..52.215 rows=9 loops=1)

  • Hash Cond: (cte0_1.c6 = t58_opportunityforecastcategorypicklistdim.sid)
80. 52.182 52.182 ↑ 1,737.6 9 1

CTE Scan on bucketab cte0_1 (cost=0.00..312.76 rows=15,638 width=68) (actual time=52.157..52.182 rows=9 loops=1)

81. 0.009 0.016 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=12) (actual time=0.015..0.016 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
82. 0.007 0.007 ↑ 1.0 5 1

Seq Scan on t58_opportunityforecastcategorypicklistdim (cost=0.00..1.05 rows=5 width=12) (actual time=0.007..0.007 rows=5 loops=1)

Planning time : 7.225 ms
Execution time : 3,571.090 ms