explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A6j2

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 5,544.009 ↑ 423.0 21 1

Append (cost=1,377,346.55..1,377,881.30 rows=8,884 width=96) (actual time=5,247.871..5,544.009 rows=21 loops=1)

2.          

CTE picklist_0

3. 0.023 0.023 ↑ 1.0 2 1

Seq Scan on t205_opportunitystagenamepicklistdim t205_opportunitystagenamepicklistdim_1 (cost=0.00..4.80 rows=2 width=4) (actual time=0.016..0.023 rows=2 loops=1)

  • Filter: (correlated_value = ANY ('{"Closed Won","Trial Won"}'::text[]))
  • Rows Removed by Filter: 62
4.          

CTE picklist_2

5. 0.028 0.028 ↑ 1.1 14 1

Seq Scan on t205_opportunitystagenamepicklistdim t205_opportunitystagenamepicklistdim_2 (cost=0.00..5.84 rows=15 width=4) (actual time=0.007..0.028 rows=14 loops=1)

  • Filter: (correlated_value = ANY ('{"16 - Closed / Invalid or Duplicate Oppty","Closed - Dead","Closed - Call later","13 \\u2013 Closed / Lost to No Decision",Churn,"12 - Closed / Lost to Competition","Trial Lost","Closed Lost","15 - Closed / Lost to Churn","14 - Closed / Mid Cycle Qualify Out","13 - Closed / Lost to No Decision","Stage 16 - Closed / Invalid or Duplicate","Closed - Free","11 - Delayed or Deprioritized","Closed - No Decision"}'::text[]))
  • Rows Removed by Filter: 50
6.          

CTE ownercte

7. 2.758 17.069 ↑ 2.1 570 1

HashAggregate (cost=3,201.10..3,212.98 rows=1,188 width=4) (actual time=17.019..17.069 rows=570 loops=1)

  • Group Key: distinctclosedateowner.ownerid
8. 3.935 14.311 ↓ 1.2 19,843 1

Hash Join (cost=1,136.51..3,160.69 rows=16,163 width=4) (actual time=4.925..14.311 rows=19,843 loops=1)

  • Hash Cond: (distinctclosedateowner.ownerid = t209_userrolehierarchy.sid)
9. 5.626 7.143 ↓ 1.2 19,843 1

Bitmap Heap Scan on distinctclosedateowner (cost=346.09..2,198.54 rows=16,163 width=4) (actual time=1.678..7.143 rows=19,843 loops=1)

  • Recheck Cond: ((closedate <= 20181231) AND (closedate >= 20181001))
  • Heap Blocks: exact=1353
10. 1.517 1.517 ↓ 1.2 19,843 1

Bitmap Index Scan on ix_distinctclosedateowner_closedate (cost=0.00..342.05 rows=16,163 width=0) (actual time=1.517..1.517 rows=19,843 loops=1)

  • Index Cond: ((closedate <= 20181231) AND (closedate >= 20181001))
11. 0.325 3.233 ↓ 1.4 2,111 1

Hash (cost=771.30..771.30 rows=1,529 width=4) (actual time=3.233..3.233 rows=2,111 loops=1)

  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 107kB
12. 0.677 2.908 ↓ 1.4 2,111 1

HashAggregate (cost=756.01..771.30 rows=1,529 width=4) (actual time=2.709..2.908 rows=2,111 loops=1)

  • Group Key: t209_userrolehierarchy.sid
13. 0.621 2.231 ↓ 1.0 2,560 1

Hash Join (cost=231.84..749.67 rows=2,535 width=4) (actual time=0.794..2.231 rows=2,560 loops=1)

  • Hash Cond: (t209_userrolehierarchy.c808_user_role_id = t37_userroledim.sid)
14. 0.854 1.201 ↓ 1.0 2,560 1

Bitmap Heap Scan on t209_userrolehierarchy (cost=171.12..654.15 rows=2,535 width=8) (actual time=0.378..1.201 rows=2,560 loops=1)

  • Recheck Cond: ((c809_ancestor_role_id = ANY ('{496,30}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=302
15. 0.347 0.347 ↓ 1.0 2,583 1

Bitmap Index Scan on ix_t209_userrolehierarchy_ancestor_timestamp (cost=0.00..170.49 rows=2,535 width=0) (actual time=0.347..0.347 rows=2,583 loops=1)

  • Index Cond: ((c809_ancestor_role_id = ANY ('{496,30}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
16. 0.061 0.409 ↑ 1.2 437 1

Hash (cost=54.43..54.43 rows=503 width=4) (actual time=0.409..0.409 rows=437 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
17. 0.348 0.348 ↑ 1.2 437 1

Seq Scan on t37_userroledim (cost=0.00..54.43 rows=503 width=4) (actual time=0.010..0.348 rows=437 loops=1)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c419_portaltype IS NULL) OR (c419_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 442
18.          

CTE openwont1

19. 309.585 2,944.486 ↓ 3.2 386,320 1

Merge Left Join (cost=1,264,893.18..1,268,077.22 rows=121,814 width=34) (actual time=2,537.708..2,944.486 rows=386,320 loops=1)

  • Merge Cond: ((t202_oppfact_1.c765_opp_currency_code = cur290_t1_r1.sid) AND (t202_oppfact_1.c781_opp_close_date = cur290_t1_r1.rate_date))
20. 454.227 2,592.727 ↓ 3.2 386,320 1

Sort (cost=1,263,557.94..1,263,862.47 rows=121,814 width=52) (actual time=2,528.025..2,592.727 rows=386,320 loops=1)

  • Sort Key: t202_oppfact_1.c765_opp_currency_code, t202_oppfact_1.c781_opp_close_date
  • Sort Method: external merge Disk: 26432kB
21. 95.119 2,138.500 ↓ 3.2 386,320 1

Nested Loop Left Join (cost=445,714.98..1,249,103.11 rows=121,814 width=52) (actual time=631.672..2,138.500 rows=386,320 loops=1)

22. 119.771 884.421 ↓ 3.2 386,320 1

Merge Left Join (cost=445,714.41..446,766.71 rows=121,814 width=24) (actual time=631.657..884.421 rows=386,320 loops=1)

  • Merge Cond: ((t202_oppfact.c765_opp_currency_code = cur290_t0_r0.sid) AND (t202_oppfact.c781_opp_close_date = cur290_t0_r0.rate_date))
23. 272.893 722.556 ↓ 3.2 386,320 1

Sort (cost=444,405.99..444,710.52 rows=121,814 width=24) (actual time=621.970..722.556 rows=386,320 loops=1)

  • Sort Key: t202_oppfact.c765_opp_currency_code, t202_oppfact.c781_opp_close_date
  • Sort Method: external merge Disk: 14304kB
24. 49.826 449.663 ↓ 3.2 386,320 1

Nested Loop (cost=27.63..431,617.17 rows=121,814 width=24) (actual time=0.207..449.663 rows=386,320 loops=1)

25. 0.229 0.267 ↓ 2.9 570 1

HashAggregate (cost=26.73..28.73 rows=200 width=4) (actual time=0.149..0.267 rows=570 loops=1)

  • Group Key: ownercte_1.c1
26. 0.038 0.038 ↑ 2.1 570 1

CTE Scan on ownercte ownercte_1 (cost=0.00..23.76 rows=1,188 width=4) (actual time=0.000..0.038 rows=570 loops=1)

27. 399.539 399.570 ↓ 2.1 678 570

Index Scan using ix_t202_oppfact_owner_closedate_stage_timestamp on t202_oppfact (cost=0.90..2,154.77 rows=317 width=28) (actual time=0.009..0.701 rows=678 loops=570)

  • Index Cond: ((c725_opp_ownerid = ownercte_1.c1) AND (c781_opp_close_date >= 20180701) AND (c781_opp_close_date <= 20180930) AND (start_stamp <= '1533193199999'::bigint) AND (end_stamp > '1533193199999'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 7)))
  • Rows Removed by Filter: 52
28.          

SubPlan (forIndex Scan)

29. 0.031 0.031 ↑ 1.1 14 1

CTE Scan on picklist_2 cte0_4 (cost=0.00..0.30 rows=15 width=4) (actual time=0.007..0.031 rows=14 loops=1)

30. 38.323 42.094 ↓ 27.8 399,377 1

Sort (cost=1,308.43..1,344.39 rows=14,387 width=16) (actual time=9.480..42.094 rows=399,377 loops=1)

  • Sort Key: cur290_t0_r0.sid, cur290_t0_r0.rate_date
  • Sort Method: quicksort Memory: 1057kB
31. 0.875 3.771 ↑ 1.0 14,351 1

Append (cost=0.00..314.83 rows=14,387 width=16) (actual time=0.018..3.771 rows=14,351 loops=1)

32. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on clari_conversion_rate cur290_t0_r0 (cost=0.00..0.00 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
33. 2.893 2.893 ↑ 1.0 14,351 1

Seq Scan on usd_clari_conversion_rate cur290_t0_r0_1 (cost=0.00..314.83 rows=14,386 width=16) (actual time=0.014..2.893 rows=14,351 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
34. 1,158.960 1,158.960 ↑ 1.0 1 386,320

Index Scan using ix_t202_oppfact_sid_timestamp on t202_oppfact t202_oppfact_1 (cost=0.56..6.58 rows=1 width=28) (actual time=0.003..0.003 rows=1 loops=386,320)

  • Index Cond: ((t202_oppfact.sid = sid) AND (t202_oppfact.sid = sid) AND (start_stamp <= '1542700799999'::bigint) AND (end_stamp > '1542700799999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
35. 38.389 42.133 ↓ 27.7 399,181 1

Sort (cost=1,308.43..1,344.39 rows=14,387 width=16) (actual time=9.307..42.133 rows=399,181 loops=1)

  • Sort Key: cur290_t1_r1.sid, cur290_t1_r1.rate_date
  • Sort Method: quicksort Memory: 1057kB
36. 0.889 3.744 ↑ 1.0 14,351 1

Append (cost=0.00..314.83 rows=14,387 width=16) (actual time=0.020..3.744 rows=14,351 loops=1)

37. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on clari_conversion_rate cur290_t1_r1 (cost=0.00..0.00 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
38. 2.851 2.851 ↑ 1.0 14,351 1

Seq Scan on usd_clari_conversion_rate cur290_t1_r1_1 (cost=0.00..314.83 rows=14,386 width=16) (actual time=0.015..2.851 rows=14,351 loops=1)

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

SubPlan (forMerge Left Join)

40. 0.001 0.001 ↑ 1.0 2 1

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

41. 0.000 0.000 ↑ 1.0 2 1

CTE Scan on picklist_0 cte0_3 (cost=0.00..0.04 rows=2 width=4) (actual time=0.000..0.000 rows=2 loops=1)

42. 0.040 0.040 ↑ 2.1 570 1

CTE Scan on ownercte (cost=0.00..23.76 rows=1,188 width=4) (actual time=0.001..0.040 rows=570 loops=1)

43.          

CTE openwont1ids

44. 222.274 3,305.282 ↓ 1,931.6 386,320 1

HashAggregate (cost=2,740.82..2,742.82 rows=200 width=4) (actual time=3,210.696..3,305.282 rows=386,320 loops=1)

  • Group Key: cte0_5.c1
45. 3,083.008 3,083.008 ↓ 3.2 386,320 1

CTE Scan on openwont1 cte0_5 (cost=0.00..2,436.28 rows=121,814 width=4) (actual time=2,537.710..3,083.008 rows=386,320 loops=1)

46.          

CTE bucketab

47. 136.841 221.543 ↑ 761.3 16 1

HashAggregate (cost=4,263.49..4,385.30 rows=12,181 width=68) (actual time=221.519..221.543 rows=16 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
48. 84.702 84.702 ↓ 3.2 386,320 1

CTE Scan on openwont1 cte0_6 (cost=0.00..2,436.28 rows=121,814 width=64) (actual time=0.020..84.702 rows=386,320 loops=1)

49.          

CTE picklist_3

50. 0.040 0.040 ↑ 1.1 16 1

Seq Scan on t205_opportunitystagenamepicklistdim t205_opportunitystagenamepicklistdim_3 (cost=0.00..6.00 rows=17 width=4) (actual time=0.013..0.040 rows=16 loops=1)

  • Filter: (correlated_value = ANY ('{"16 - Closed / Invalid or Duplicate Oppty","Closed - Dead","Closed - Call later","13 \\u2013 Closed / Lost to No Decision",Churn,"12 - Closed / Lost to Competition","Trial Lost","Closed Lost","15 - Closed / Lost to Churn","Closed Won","14 - Closed / Mid Cycle Qualify Out","13 - Closed / Lost to No Decision","Stage 16 - Closed / Invalid or Duplicate","Trial Won","Closed - Free","11 - Delayed or Deprioritized","Closed - No Decision"}'::text[]))
  • Rows Removed by Filter: 48
51.          

CTE bucketd

52. 48.401 5,322.377 ↑ 997.2 5 1

GroupAggregate (cost=98,737.09..98,911.60 rows=4,986 width=120) (actual time=5,247.860..5,322.377 rows=5 loops=1)

  • Group Key: '1533193199999'::bigint, (CASE WHEN (t202_oppfact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 12)) THEN CASE WHEN (t202_oppfact_3.c781_opp_close_date > 20180930) THEN 'pulledIn'::text WHEN (t202_oppfact_3.c781_opp_close_date < 20180701) THEN 'pushedIn'::text WHEN (hashed SubPlan 13) THEN 'other'::text ELSE 'ownerChange'::text END ELSE 'other'::text END)
53. 110.703 5,273.976 ↓ 42.0 209,516 1

Sort (cost=98,709.97..98,722.44 rows=4,986 width=60) (actual time=5,247.736..5,273.976 rows=209,516 loops=1)

  • Sort Key: (CASE WHEN (t202_oppfact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 12)) THEN CASE WHEN (t202_oppfact_3.c781_opp_close_date > 20180930) THEN 'pulledIn'::text WHEN (t202_oppfact_3.c781_opp_close_date < 20180701) THEN 'pushedIn'::text WHEN (hashed SubPlan 13) THEN 'other'::text ELSE 'ownerChange'::text END ELSE 'other'::text END)
  • Sort Method: external merge Disk: 10248kB
54. 52.629 5,163.273 ↓ 42.0 209,516 1

Nested Loop Left Join (cost=59.52..98,403.74 rows=4,986 width=60) (actual time=3,505.130..5,163.273 rows=209,516 loops=1)

55. 40.090 4,691.529 ↓ 42.0 209,516 1

Nested Loop Left Join (cost=31.84..64,632.30 rows=4,986 width=20) (actual time=3,505.117..4,691.529 rows=209,516 loops=1)

56. 20.904 4,232.407 ↓ 42.0 209,516 1

Nested Loop (cost=31.84..62,981.43 rows=4,986 width=20) (actual time=3,505.084..4,232.407 rows=209,516 loops=1)

57. 0.255 17.443 ↓ 2.9 570 1

HashAggregate (cost=26.73..28.73 rows=200 width=4) (actual time=17.303..17.443 rows=570 loops=1)

  • Group Key: ownercte_3.c1
58. 17.188 17.188 ↑ 2.1 570 1

CTE Scan on ownercte ownercte_3 (cost=0.00..23.76 rows=1,188 width=4) (actual time=17.021..17.188 rows=570 loops=1)

59. 47.880 4,194.060 ↓ 184.0 368 570

Nested Loop (cost=5.11..314.74 rows=2 width=24) (actual time=6.192..7.358 rows=368 loops=570)

60. 0.000 1.140 ↑ 1.0 2 570

Unique (cost=0.05..0.06 rows=2 width=4) (actual time=0.002..0.002 rows=2 loops=570)

61. 1.140 1.140 ↑ 1.0 2 570

Sort (cost=0.05..0.06 rows=2 width=4) (actual time=0.002..0.002 rows=2 loops=570)

  • Sort Key: cte0_9.c1
  • Sort Method: quicksort Memory: 25kB
62. 0.000 0.000 ↑ 1.0 2 570

CTE Scan on picklist_0 cte0_9 (cost=0.00..0.04 rows=2 width=4) (actual time=0.000..0.000 rows=2 loops=570)

63. 755.248 4,145.040 ↓ 30.7 184 1,140

Index Scan using ix_t202_oppfact_owner_closedate_stage_timestamp on t202_oppfact t202_oppfact_2 (cost=5.06..157.28 rows=6 width=28) (actual time=3.183..3.636 rows=184 loops=1,140)

  • Index Cond: ((c725_opp_ownerid = ownercte_3.c1) AND (c781_opp_close_date >= 20180701) AND (c781_opp_close_date <= 20180930) AND (c758_opp_stagename = cte0_9.c1) AND (start_stamp <= '1542700799999'::bigint) AND (end_stamp > '1542700799999'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 14)))
  • Rows Removed by Filter: 285
64.          

SubPlan (forIndex Scan)

65. 3,389.792 3,389.792 ↓ 1,931.6 386,320 1

CTE Scan on openwont1ids cte0_8 (cost=0.00..4.00 rows=200 width=4) (actual time=3,210.697..3,389.792 rows=386,320 loops=1)

66. 209.516 419.032 ↑ 2.0 1 209,516

Append (cost=0.00..0.31 rows=2 width=16) (actual time=0.002..0.002 rows=1 loops=209,516)

67. 0.000 0.000 ↓ 0.0 0 209,516

Seq Scan on clari_conversion_rate cur290_t0_r0_2 (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=209,516)

  • Filter: ((to_iso_code = 'USD'::text) AND (t202_oppfact_2.c765_opp_currency_code = sid) AND (t202_oppfact_2.c781_opp_close_date = rate_date))
68. 209.516 209.516 ↑ 1.0 1 209,516

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur290_t0_r0_3 (cost=0.29..0.31 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=209,516)

  • Index Cond: ((t202_oppfact_2.c781_opp_close_date = rate_date) AND (t202_oppfact_2.c765_opp_currency_code = sid))
  • Filter: (to_iso_code = 'USD'::text)
69. 419.032 419.032 ↓ 0.0 0 209,516

Index Scan using ix_t202_oppfact_sid_timestamp on t202_oppfact t202_oppfact_3 (cost=0.56..6.75 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=209,516)

  • Index Cond: ((t202_oppfact_2.sid = sid) AND (t202_oppfact_2.sid = sid) AND (start_stamp <= '1533193199999'::bigint) AND (end_stamp > '1533193199999'::bigint))
  • Filter: (NOT deleted)
70.          

SubPlan (forNested Loop Left Join)

71. 0.045 0.045 ↑ 1.1 16 1

CTE Scan on picklist_3 cte0_7 (cost=0.00..0.34 rows=17 width=4) (actual time=0.015..0.045 rows=16 loops=1)

72. 0.038 0.038 ↑ 2.1 570 1

CTE Scan on ownercte ownercte_2 (cost=0.00..23.76 rows=1,188 width=4) (actual time=0.001..0.038 rows=570 loops=1)

73. 0.011 5,322.398 ↑ 997.2 5 1

Subquery Scan on *SELECT* 1 (cost=0.00..162.04 rows=4,986 width=96) (actual time=5,247.871..5,322.398 rows=5 loops=1)

74. 5,322.387 5,322.387 ↑ 997.2 5 1

CTE Scan on bucketd cte0 (cost=0.00..99.72 rows=4,986 width=120) (actual time=5,247.865..5,322.387 rows=5 loops=1)

75. 0.020 221.611 ↑ 243.6 16 1

Hash Join (cost=5.44..333.72 rows=3,898 width=83) (actual time=221.576..221.611 rows=16 loops=1)

  • Hash Cond: (cte0_1.c6 = t205_opportunitystagenamepicklistdim.sid)
76. 221.551 221.551 ↑ 761.3 16 1

CTE Scan on bucketab cte0_1 (cost=0.00..243.62 rows=12,181 width=68) (actual time=221.521..221.551 rows=16 loops=1)

77. 0.014 0.040 ↑ 1.0 64 1

Hash (cost=4.64..4.64 rows=64 width=23) (actual time=0.040..0.040 rows=64 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
78. 0.026 0.026 ↑ 1.0 64 1

Seq Scan on t205_opportunitystagenamepicklistdim (cost=0.00..4.64 rows=64 width=23) (actual time=0.007..0.026 rows=64 loops=1)