explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jdBN

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 174.782 ↑ 4,387.8 13 1

Append (cost=1,237,889.57..1,240,017.43 rows=57,041 width=96) (actual time=172.807..174.782 rows=13 loops=1)

2.          

CTE picklist_0

3. 0.322 0.322 ↑ 1.0 6 1

Seq Scan on t233_opportunitystagenamepicklistdim (cost=0.00..46.54 rows=6 width=4) (actual time=0.009..0.322 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.299 0.299 ↑ 1.0 7 1

Seq Scan on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_1 (cost=0.00..48.08 rows=7 width=4) (actual time=0.005..0.299 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.817 10.351 ↑ 75.3 327 1

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

  • Group Key: distinctclosedateowner.ownerid
8.          

CTE usercte

9. 0.000 2.143 ↑ 2.0 941 1

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

10. 0.313 0.405 ↑ 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.405 rows=941 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=133
11. 0.092 0.092 ↑ 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.092..0.092 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.874 9.534 ↑ 26.4 5,309 1

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

14. 0.360 2.708 ↓ 3.7 744 1

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

  • Group Key: usercte.c1
15. 2.348 2.348 ↑ 2.0 941 1

CTE Scan on usercte (cost=0.00..37.24 rows=1,862 width=4) (actual time=0.130..2.348 rows=941 loops=1)

16. 5.952 5.952 ↑ 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.008 rows=7 loops=744)

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

CTE openwont1

18. 8.589 146.833 ↑ 68.8 3,285 1

Merge Right Join (cost=955,149.11..972,153.10 rows=226,092 width=34) (actual time=119.992..146.833 rows=3,285 loops=1)

  • Merge Cond: ((cur177_t1_r1.rate_date = t237_oppfact_1.c836_opp_close_date) AND (cur177_t1_r1.sid = t237_oppfact_1.c845_opp_currency_code))
19. 6.141 54.099 ↑ 1.1 95,726 1

Merge Append (cost=0.31..8,299.20 rows=101,706 width=16) (actual time=0.016..54.099 rows=95,726 loops=1)

  • Sort Key: cur177_t1_r1.rate_date, cur177_t1_r1.sid
20. 0.006 0.006 ↓ 0.0 0 1

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

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

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)

  • Filter: (to_iso_code = 'USD'::text)
22. 47.952 47.952 ↑ 1.1 95,726 1

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur177_t1_r1_1 (cost=0.29..7,027.85 rows=101,705 width=16) (actual time=0.010..47.952 rows=95,726 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
23. 0.351 84.122 ↑ 50.5 3,285 1

Materialize (cost=954,545.57..955,375.66 rows=166,018 width=56) (actual time=83.455..84.122 rows=3,285 loops=1)

24. 1.661 83.771 ↑ 50.5 3,285 1

Sort (cost=954,545.57..954,960.61 rows=166,018 width=56) (actual time=83.453..83.771 rows=3,285 loops=1)

  • Sort Key: t237_oppfact_1.c836_opp_close_date, t237_oppfact_1.c845_opp_currency_code
  • Sort Method: quicksort Memory: 558kB
25. 6.581 82.110 ↑ 50.5 3,285 1

Hash Left Join (cost=4,800.57..934,473.99 rows=166,018 width=56) (actual time=46.594..82.110 rows=3,285 loops=1)

  • Hash Cond: ((t237_oppfact.c845_opp_currency_code = cur177_t0_r0.sid) AND (t237_oppfact.c836_opp_close_date = cur177_t0_r0.rate_date))
26. 3.018 29.840 ↑ 37.1 3,285 1

Nested Loop Left Join (cost=555.67..899,175.58 rows=121,905 width=56) (actual time=0.425..29.840 rows=3,285 loops=1)

27. 0.438 10.397 ↑ 37.1 3,285 1

Nested Loop (cost=555.10..24,878.58 rows=121,905 width=28) (actual time=0.414..10.397 rows=3,285 loops=1)

28. 0.120 0.149 ↓ 1.6 327 1

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

  • Group Key: ownercte_1.c1
29. 0.029 0.029 ↑ 75.3 327 1

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

30. 9.508 9.810 ↑ 1.4 10 327

Index Scan using ix_t237_oppfact_owner_closedate_timestamp on t237_oppfact (cost=0.72..121.47 rows=14 width=32) (actual time=0.012..0.030 rows=10 loops=327)

  • Index Cond: ((c832_opp_ownerid = ownercte_1.c1) AND (c836_opp_close_date >= 20180201) AND (c836_opp_close_date <= 20180430) AND (start_stamp <= '1520668799999'::bigint) AND (end_stamp > '1520668799999'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 8)))
  • Rows Removed by Filter: 4
31.          

SubPlan (forIndex Scan)

32. 0.302 0.302 ↑ 1.0 7 1

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

33. 16.425 16.425 ↑ 1.0 1 3,285

Index Scan using ix_t237_oppfact_sid_timestamp on t237_oppfact t237_oppfact_1 (cost=0.56..7.16 rows=1 width=28) (actual time=0.005..0.005 rows=1 loops=3,285)

  • Index Cond: ((t237_oppfact.sid = sid) AND (t237_oppfact.sid = sid) AND (start_stamp <= '1540796399999'::bigint) AND (end_stamp > '1540796399999'::bigint))
  • Filter: (NOT deleted)
34. 18.500 45.689 ↓ 1.0 101,732 1

Hash (cost=2,222.31..2,222.31 rows=101,706 width=16) (actual time=45.689..45.689 rows=101,732 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3412kB
35. 5.935 27.189 ↓ 1.0 101,732 1

Append (cost=0.00..2,222.31 rows=101,706 width=16) (actual time=0.131..27.189 rows=101,732 loops=1)

36. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on clari_conversion_rate cur177_t0_r0 (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)
37. 21.253 21.253 ↓ 1.0 101,732 1

Seq Scan on usd_clari_conversion_rate cur177_t0_r0_1 (cost=0.00..2,222.31 rows=101,705 width=16) (actual time=0.130..21.253 rows=101,732 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
38.          

SubPlan (forMerge Right Join)

39. 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.001..0.002 rows=6 loops=1)

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

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

42.          

CTE openwont1ids

43. 1.320 149.260 ↓ 16.4 3,285 1

HashAggregate (cost=5,087.07..5,089.07 rows=200 width=4) (actual time=148.909..149.260 rows=3,285 loops=1)

  • Group Key: cte0_5.c1
44. 147.940 147.940 ↑ 68.8 3,285 1

CTE Scan on openwont1 cte0_5 (cost=0.00..4,521.84 rows=226,092 width=4) (actual time=119.993..147.940 rows=3,285 loops=1)

45.          

CTE bucketab

46. 1.193 1.767 ↑ 2,826.1 8 1

HashAggregate (cost=7,913.22..8,139.31 rows=22,609 width=68) (actual time=1.730..1.767 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
47. 0.574 0.574 ↑ 68.8 3,285 1

CTE Scan on openwont1 cte0_6 (cost=0.00..4,521.84 rows=226,092 width=64) (actual time=0.002..0.574 rows=3,285 loops=1)

48.          

CTE picklist_3

49. 0.431 0.431 ↑ 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.431 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
50.          

CTE bucketd

51. 0.129 172.960 ↑ 6,886.4 5 1

GroupAggregate (cost=223,258.07..224,463.19 rows=34,432 width=120) (actual time=172.798..172.960 rows=5 loops=1)

  • Group Key: '1520668799999'::bigint, (CASE WHEN (t237_oppfact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 13)) THEN CASE WHEN (t237_oppfact_3.c836_opp_close_date > 20180430) THEN 'pulledIn'::text WHEN (t237_oppfact_3.c836_opp_close_date < 20180201) THEN 'pushedIn'::text WHEN (hashed SubPlan 14) THEN 'other'::text ELSE 'ownerChange'::text END ELSE 'other'::text END)
52. 0.232 172.831 ↑ 59.1 583 1

Sort (cost=222,703.40..222,789.48 rows=34,432 width=60) (actual time=172.780..172.831 rows=583 loops=1)

  • Sort Key: (CASE WHEN (t237_oppfact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 13)) THEN CASE WHEN (t237_oppfact_3.c836_opp_close_date > 20180430) THEN 'pulledIn'::text WHEN (t237_oppfact_3.c836_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: 70kB
53. 0.701 172.599 ↑ 59.1 583 1

Nested Loop Left Join (cost=1,114.87..220,108.69 rows=34,432 width=60) (actual time=161.582..172.599 rows=583 loops=1)

54. 0.124 170.271 ↑ 43.4 583 1

Nested Loop Left Join (cost=560.20..209,910.28 rows=25,284 width=36) (actual time=161.569..170.271 rows=583 loops=1)

55. 0.260 167.815 ↑ 43.4 583 1

Hash Semi Join (cost=559.64..18,370.69 rows=25,284 width=20) (actual time=161.561..167.815 rows=583 loops=1)

  • Hash Cond: (t237_oppfact_2.c843_opp_stagename = cte0_9.c1)
56. 0.387 167.226 ↑ 43.0 1,860 1

Nested Loop (cost=559.44..17,879.05 rows=80,064 width=24) (actual time=161.176..167.226 rows=1,860 loops=1)

57. 0.117 10.533 ↓ 1.6 327 1

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

  • Group Key: ownercte_3.c1
58. 10.416 10.416 ↑ 75.3 327 1

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

59. 6.397 156.306 ↑ 1.5 6 327

Index Scan using ix_t237_oppfact_owner_closedate_timestamp on t237_oppfact t237_oppfact_2 (cost=5.06..86.52 rows=9 width=28) (actual time=0.469..0.478 rows=6 loops=327)

  • Index Cond: ((c832_opp_ownerid = ownercte_3.c1) AND (c836_opp_close_date >= 20180201) AND (c836_opp_close_date <= 20180430) AND (start_stamp <= '1540796399999'::bigint) AND (end_stamp > '1540796399999'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 15)))
  • Rows Removed by Filter: 5
60.          

SubPlan (forIndex Scan)

61. 149.909 149.909 ↓ 16.4 3,285 1

CTE Scan on openwont1ids cte0_8 (cost=0.00..4.00 rows=200 width=4) (actual time=148.910..149.909 rows=3,285 loops=1)

62. 0.004 0.329 ↑ 1.0 6 1

Hash (cost=0.12..0.12 rows=6 width=4) (actual time=0.329..0.329 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
63. 0.325 0.325 ↑ 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.325 rows=6 loops=1)

64. 2.332 2.332 ↓ 0.0 0 583

Index Scan using ix_t237_oppfact_sid_timestamp on t237_oppfact t237_oppfact_3 (cost=0.56..7.57 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=583)

  • Index Cond: ((t237_oppfact_2.sid = sid) AND (t237_oppfact_2.sid = sid) AND (start_stamp <= '1520668799999'::bigint) AND (end_stamp > '1520668799999'::bigint))
  • Filter: (NOT deleted)
65. 0.000 1.166 ↑ 2.0 1 583

Append (cost=0.00..0.35 rows=2 width=16) (actual time=0.002..0.002 rows=1 loops=583)

66. 0.000 0.000 ↓ 0.0 0 583

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=583)

  • Filter: ((to_iso_code = 'USD'::text) AND (t237_oppfact_2.c845_opp_currency_code = sid) AND (t237_oppfact_2.c836_opp_close_date = rate_date))
67. 1.166 1.166 ↑ 1.0 1 583

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur177_t0_r0_3 (cost=0.29..0.35 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=583)

  • Index Cond: ((t237_oppfact_2.c836_opp_close_date = rate_date) AND (t237_oppfact_2.c845_opp_currency_code = sid))
  • Filter: (to_iso_code = 'USD'::text)
68.          

SubPlan (forNested Loop Left Join)

69. 0.435 0.435 ↑ 1.0 13 1

CTE Scan on picklist_3 cte0_7 (cost=0.00..0.26 rows=13 width=4) (actual time=0.010..0.435 rows=13 loops=1)

70. 0.026 0.026 ↑ 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.026 rows=327 loops=1)

71. 0.010 172.974 ↑ 6,886.4 5 1

Subquery Scan on *SELECT* 1 (cost=0.00..1,119.04 rows=34,432 width=96) (actual time=172.807..172.974 rows=5 loops=1)

72. 172.964 172.964 ↑ 6,886.4 5 1

CTE Scan on bucketd cte0 (cost=0.00..688.64 rows=34,432 width=120) (actual time=172.799..172.964 rows=5 loops=1)

73. 0.028 1.806 ↑ 2,826.1 8 1

Hash Join (cost=19.68..782.73 rows=22,609 width=96) (actual time=1.765..1.806 rows=8 loops=1)

  • Hash Cond: (cte0_1.c6 = t234_opportunityforecastcategorypicklistdim.sid)
74. 1.768 1.768 ↑ 2,826.1 8 1

CTE Scan on bucketab cte0_1 (cost=0.00..452.18 rows=22,609 width=68) (actual time=1.731..1.768 rows=8 loops=1)

75. 0.006 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.004 0.004 ↑ 86.0 5 1

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