explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ejTH

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 2,767.181 ↑ 1,374.0 11 1

Append (cost=879,571.51..880,075.72 rows=15,114 width=96) (actual time=2,735.765..2,767.181 rows=11 loops=1)

2.          

CTE picklist_0

3. 0.015 0.015 ↑ 1.0 1 1

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

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

CTE docd_cte_1

5. 19.708 1,631.002 ↑ 2.8 13,187 1

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

  • Group Key: t67_distinct_opp_owner_closedate.c759_ownerid
6. 88.162 1,611.294 ↑ 1.0 99,727 1

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

  • Hash Cond: (t67_distinct_opp_owner_closedate.c759_ownerid = t55_userrolehierarchy.sid)
7. 710.434 710.434 ↓ 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.316..710.434 rows=213,595 loops=1)

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

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

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

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

10. 225.471 712.229 ↓ 1.0 389,844 1

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

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

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

12. 128.655 341.095 ↓ 1.2 389,678 1

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

  • Hash Cond: (t55_userrolehierarchy.c697_user_role_id = t33_userroledim.sid)
13. 153.121 198.714 ↑ 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.009..198.714 rows=550,251 loops=1)

  • Recheck Cond: ((c698_ancestor_role_id = 437) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=50118
14. 45.593 45.593 ↑ 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.593..45.593 rows=550,257 loops=1)

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

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1186kB
16. 7.927 9.735 ↓ 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.917..9.735 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.808 1.808 ↓ 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.808..1.808 rows=26,451 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
18. 103.161 118.927 ↑ 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.423..118.927 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.766 15.766 ↓ 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.766..15.766 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.011..0.015 rows=1 loops=1)

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

CTE openwont1

23. 50.875 674.601 ↑ 1.4 55,107 1

Merge Right Join (cost=411,130.45..419,546.81 rows=79,314 width=34) (actual time=576.950..674.601 rows=55,107 loops=1)

  • Merge Cond: ((t54_clari_conversion_rate_1.sid = t56_oppfact_1.c701_opp_currency_code) AND (t54_clari_conversion_rate_1.rate_date = t56_oppfact_1.c713_opp_close_date))
24. 5.369 39.559 ↑ 1.0 65,896 1

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

  • Sort Key: t54_clari_conversion_rate_1.sid, t54_clari_conversion_rate_1.rate_date
25. 0.011 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.001 0.001 ↓ 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.001..0.001 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
27. 34.178 34.178 ↑ 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.051..34.178 rows=65,896 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
28. 5.586 583.056 ↑ 1.4 55,107 1

Materialize (cost=410,285.45..410,682.02 rows=79,314 width=56) (actual time=569.694..583.056 rows=55,107 loops=1)

29. 56.762 577.470 ↑ 1.4 55,107 1

Sort (cost=410,285.45..410,483.73 rows=79,314 width=56) (actual time=569.692..577.470 rows=55,107 loops=1)

  • Sort Key: t56_oppfact_1.c701_opp_currency_code, t56_oppfact_1.c713_opp_close_date
  • Sort Method: external sort Disk: 3768kB
30. 29.246 520.708 ↑ 1.4 55,107 1

Nested Loop Left Join (cost=18,270.95..401,118.65 rows=79,314 width=56) (actual time=257.552..520.708 rows=55,107 loops=1)

31. 15.629 326.141 ↑ 1.4 55,107 1

Merge Right Join (cost=18,270.39..25,298.75 rows=79,314 width=28) (actual time=257.531..326.141 rows=55,107 loops=1)

  • Merge Cond: ((t54_clari_conversion_rate.sid = t56_oppfact.c701_opp_currency_code) AND (t54_clari_conversion_rate.rate_date = t56_oppfact.c713_opp_close_date))
32. 5.451 42.702 ↑ 1.0 65,896 1

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

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

Sort (cost=0.01..0.02 rows=1 width=16) (actual time=0.002..0.002 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.249 37.249 ↑ 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.007..37.249 rows=65,896 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
36. 5.471 267.810 ↑ 1.4 55,107 1

Materialize (cost=18,270.08..18,666.65 rows=79,314 width=28) (actual time=254.124..267.810 rows=55,107 loops=1)

37. 55.141 262.339 ↑ 1.4 55,107 1

Sort (cost=18,270.08..18,468.36 rows=79,314 width=28) (actual time=254.119..262.339 rows=55,107 loops=1)

  • Sort Key: t56_oppfact.c701_opp_currency_code, t56_oppfact.c713_opp_close_date
  • Sort Method: external merge Disk: 2264kB
38. 16.316 207.198 ↑ 1.4 55,107 1

Nested Loop (cost=845.23..9,915.29 rows=79,314 width=28) (actual time=4.291..207.198 rows=55,107 loops=1)

39. 5.246 6.264 ↓ 65.9 13,187 1

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

  • Group Key: cte0_6.c1
40. 1.018 1.018 ↑ 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.000..1.018 rows=13,187 loops=1)

41. 184.601 184.618 ↑ 1.2 4 13,187

Index Scan using ix_t56_oppfact_notnullowner_closedate_timestamp on t56_oppfact (cost=0.58..45.29 rows=5 width=32) (actual time=0.007..0.014 rows=4 loops=13,187)

  • Index Cond: ((c703_opp_ownerid = cte0_6.c1) AND (c713_opp_close_date >= 20190202) AND (c713_opp_close_date <= 20190503) AND (end_stamp > '1552719599999'::bigint) AND (start_stamp <= '1552719599999'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 7)))
  • Rows Removed by Filter: 7
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. 165.321 165.321 ↑ 1.0 1 55,107

Index Scan using t56_oppfact_sidendstampunique on t56_oppfact t56_oppfact_1 (cost=0.56..4.73 rows=1 width=28) (actual time=0.003..0.003 rows=1 loops=55,107)

  • Index Cond: ((t56_oppfact.sid = sid) AND (t56_oppfact.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.001 0.001 ↑ 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.001 rows=1 loops=1)

47. 1.110 1.110 ↑ 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.000..1.110 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. 24.368 723.205 ↓ 275.5 55,107 1

HashAggregate (cost=1,784.57..1,786.57 rows=200 width=4) (actual time=715.600..723.205 rows=55,107 loops=1)

  • Group Key: cte0_7.c1
51. 698.837 698.837 ↑ 1.4 55,107 1

CTE Scan on openwont1 cte0_7 (cost=0.00..1,586.28 rows=79,314 width=4) (actual time=576.952..698.837 rows=55,107 loops=1)

52.          

CTE bucketab

53. 19.237 31.374 ↑ 793.1 10 1

HashAggregate (cost=2,775.99..2,855.30 rows=7,931 width=68) (actual time=31.359..31.374 rows=10 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. 12.137 12.137 ↑ 1.4 55,107 1

CTE Scan on openwont1 cte0_8 (cost=0.00..1,586.28 rows=79,314 width=64) (actual time=0.022..12.137 rows=55,107 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. 3.182 2,735.752 ↑ 7,183.0 1 1

GroupAggregate (cost=48,857.07..49,144.39 rows=7,183 width=120) (actual time=2,735.752..2,735.752 rows=1 loops=1)

  • Group Key: '1552719599999'::bigint, (CASE WHEN (t56_oppfact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 12)) THEN CASE WHEN (t56_oppfact_3.c713_opp_close_date > 20190503) THEN 'pulledIn'::text WHEN (t56_oppfact_3.c713_opp_close_date < 20190202) THEN 'pushedIn'::text WHEN ((t56_oppfact_3.c713_opp_close_date >= 20190202) AND (t56_oppfact_3.c713_opp_close_date <= 20190503) AND (hashed SubPlan 13) AND (t56_oppfact_3.c703_opp_ownerid IS NOT NULL)) THEN 'other'::text ELSE 'ownerChange'::text END ELSE 'other'::text END)
59. 4.749 2,732.570 ↓ 2.0 14,122 1

Sort (cost=48,012.38..48,030.33 rows=7,183 width=60) (actual time=2,731.604..2,732.570 rows=14,122 loops=1)

  • Sort Key: (CASE WHEN (t56_oppfact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 12)) THEN CASE WHEN (t56_oppfact_3.c713_opp_close_date > 20190503) THEN 'pulledIn'::text WHEN (t56_oppfact_3.c713_opp_close_date < 20190202) THEN 'pushedIn'::text WHEN ((t56_oppfact_3.c713_opp_close_date >= 20190202) AND (t56_oppfact_3.c713_opp_close_date <= 20190503) AND (hashed SubPlan 13) AND (t56_oppfact_3.c703_opp_ownerid IS NOT NULL)) THEN 'other'::text ELSE 'ownerChange'::text END ELSE 'other'::text END)
  • Sort Method: quicksort Memory: 1488kB
60. 0.761 2,727.821 ↓ 2.0 14,122 1

Nested Loop Left Join (cost=1,695.00..47,552.29 rows=7,183 width=60) (actual time=2,385.872..2,727.821 rows=14,122 loops=1)

61. 8.586 2,684.694 ↓ 2.0 14,122 1

Nested Loop Left Join (cost=849.74..13,702.13 rows=7,183 width=20) (actual time=2,385.860..2,684.694 rows=14,122 loops=1)

62. 11.842 2,647.864 ↓ 2.0 14,122 1

Hash Semi Join (cost=849.74..11,119.00 rows=7,183 width=20) (actual time=2,385.842..2,647.864 rows=14,122 loops=1)

  • Hash Cond: (t56_oppfact_2.c705_opp_stagename = cte0_13.c1)
63. 20.570 2,635.985 ↓ 1.4 118,864 1

Nested Loop (cost=849.71..10,812.79 rows=86,196 width=24) (actual time=2,385.534..2,635.985 rows=118,864 loops=1)

64. 5.814 1,639.577 ↓ 65.9 13,187 1

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

  • Group Key: cte0_12.c1
65. 1,633.763 1,633.763 ↑ 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,629.232..1,633.763 rows=13,187 loops=1)

66. 241.041 975.838 ↓ 1.5 9 13,187

Index Scan using ix_t56_oppfact_notnullowner_closedate_timestamp on t56_oppfact t56_oppfact_2 (cost=5.06..49.77 rows=6 width=28) (actual time=0.063..0.074 rows=9 loops=13,187)

  • Index Cond: ((c703_opp_ownerid = cte0_12.c1) AND (c713_opp_close_date >= 20190202) AND (c713_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: 4
67.          

SubPlan (for Index Scan)

68. 734.797 734.797 ↓ 275.5 55,107 1

CTE Scan on openwont1ids cte0_11 (cost=0.00..4.00 rows=200 width=4) (actual time=715.601..734.797 rows=55,107 loops=1)

69. 0.019 0.037 ↑ 1.0 1 1

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

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

71. 0.000 28.244 ↑ 2.0 1 14,122

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

72. 0.000 0.000 ↓ 0.0 0 14,122

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=14,122)

  • Filter: ((to_iso_code = 'USD'::text) AND (t56_oppfact_2.c701_opp_currency_code = sid) AND (t56_oppfact_2.c713_opp_close_date = rate_date))
73. 28.244 28.244 ↑ 1.0 1 14,122

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.34 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=14,122)

  • Index Cond: ((t56_oppfact_2.c701_opp_currency_code = sid) AND (t56_oppfact_2.c713_opp_close_date = rate_date))
  • Filter: (to_iso_code = 'USD'::text)
74. 42.366 42.366 ↓ 0.0 0 14,122

Index Scan using t56_oppfact_sidendstampunique on t56_oppfact t56_oppfact_3 (cost=0.56..4.57 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=14,122)

  • Index Cond: ((t56_oppfact_2.sid = sid) AND (t56_oppfact_2.sid = sid) AND (end_stamp > '1552719599999'::bigint))
  • Filter: ((NOT deleted) AND (start_stamp <= '1552719599999'::bigint))
  • Rows Removed by Filter: 1
75.          

SubPlan (for Nested Loop Left Join)

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

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

78. 0.009 2,735.765 ↑ 7,183.0 1 1

Subquery Scan on *SELECT* 1 (cost=0.00..233.45 rows=7,183 width=96) (actual time=2,735.764..2,735.765 rows=1 loops=1)

79. 2,735.756 2,735.756 ↑ 7,183.0 1 1

CTE Scan on bucketd cte0 (cost=0.00..143.66 rows=7,183 width=120) (actual time=2,735.755..2,735.756 rows=1 loops=1)

80. 0.027 31.415 ↑ 793.1 10 1

Hash Left Join (cost=1.11..191.45 rows=7,931 width=72) (actual time=31.394..31.415 rows=10 loops=1)

  • Hash Cond: (cte0_1.c6 = t58_opportunityforecastcategorypicklistdim.sid)
81. 31.378 31.378 ↑ 793.1 10 1

CTE Scan on bucketab cte0_1 (cost=0.00..158.62 rows=7,931 width=68) (actual time=31.360..31.378 rows=10 loops=1)

82. 0.003 0.010 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
83. 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.006..0.007 rows=5 loops=1)

Planning time : 7.184 ms
Execution time : 2,775.076 ms