explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WV1C

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 79.372 ↑ 955.5 13 1

Append (cost=1,770,690.99..1,771,169.71 rows=12,421 width=96) (actual time=77.437..79.372 rows=13 loops=1)

2.          

CTE picklist_0

3. 0.306 0.306 ↑ 1.0 6 1

Seq Scan on t233_opportunitystagenamepicklistdim (cost=0.00..46.54 rows=6 width=4) (actual time=0.009..0.306 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.279 0.279 ↑ 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.279 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 openwont1

7. 4.256 56.348 ↑ 15.0 3,283 1

Nested Loop Left Join (cost=17,846.18..1,290,705.25 rows=49,206 width=34) (actual time=2.422..56.348 rows=3,283 loops=1)

8. 2.105 43.572 ↑ 11.0 3,283 1

Nested Loop Left Join (cost=17,845.91..439,602.73 rows=36,164 width=56) (actual time=2.385..43.572 rows=3,283 loops=1)

9. 3.192 34.901 ↑ 8.1 3,283 1

Nested Loop Left Join (cost=17,845.91..430,126.65 rows=26,579 width=56) (actual time=2.373..34.901 rows=3,283 loops=1)

10. 0.569 15.294 ↑ 8.1 3,283 1

Nested Loop (cost=17,845.35..239,832.09 rows=26,579 width=28) (actual time=2.363..15.294 rows=3,283 loops=1)

11. 0.329 2.196 ↑ 2.5 737 1

HashAggregate (cost=17,844.63..17,863.14 rows=1,851 width=4) (actual time=2.050..2.196 rows=737 loops=1)

  • Group Key: t166_userrolehierarchy_2.sid
12. 0.649 1.867 ↑ 2.0 934 1

Nested Loop Semi Join (cost=60.05..17,839.98 rows=1,860 width=4) (actual time=0.098..1.867 rows=934 loops=1)

13. 0.205 0.284 ↑ 2.0 934 1

Bitmap Heap Scan on t166_userrolehierarchy t166_userrolehierarchy_2 (cost=59.63..6,838.33 rows=1,860 width=8) (actual time=0.091..0.284 rows=934 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=130
14. 0.079 0.079 ↑ 2.0 944 1

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..59.16 rows=1,860 width=0) (actual time=0.079..0.079 rows=944 loops=1)

  • Index Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
15. 0.934 0.934 ↑ 1.0 1 934

Index Scan using ix_t67_userroledim_sid_timestamp_partial on t67_userroledim t67_userroledim_2 (cost=0.42..5.90 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=934)

  • Index Cond: ((sid = t166_userrolehierarchy_2.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
16. 12.247 12.529 ↑ 3.5 4 737

Index Scan using ix_t237_oppfact_owner_closedate_timestamp on t237_oppfact (cost=0.72..119.78 rows=14 width=32) (actual time=0.008..0.017 rows=4 loops=737)

  • Index Cond: ((c832_opp_ownerid = t166_userrolehierarchy_2.sid) 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 7)))
  • Rows Removed by Filter: 2
17.          

SubPlan (forIndex Scan)

18. 0.282 0.282 ↑ 1.0 7 1

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

19. 16.415 16.415 ↑ 1.0 1 3,283

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

  • Index Cond: ((t237_oppfact.sid = sid) AND (t237_oppfact.sid = sid) AND (start_stamp <= '1540796399999'::bigint) AND (end_stamp > '1540796399999'::bigint))
  • Filter: (NOT deleted)
20. 0.000 6.566 ↑ 2.0 1 3,283

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

21. 0.000 0.000 ↓ 0.0 0 3,283

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=3,283)

  • Filter: ((to_iso_code = 'USD'::text) AND (t237_oppfact.c845_opp_currency_code = sid) AND (t237_oppfact.c836_opp_close_date = rate_date))
22. 6.566 6.566 ↑ 1.0 1 3,283

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur177_t0_r0_1 (cost=0.29..0.34 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=3,283)

  • Index Cond: ((t237_oppfact.c836_opp_close_date = rate_date) AND (t237_oppfact.c845_opp_currency_code = sid))
  • Filter: (to_iso_code = 'USD'::text)
23. 0.000 6.566 ↑ 2.0 1 3,283

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

24. 0.000 0.000 ↓ 0.0 0 3,283

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=3,283)

  • Filter: ((to_iso_code = 'USD'::text) AND (t237_oppfact_1.c845_opp_currency_code = sid) AND (t237_oppfact_1.c836_opp_close_date = rate_date))
25. 6.566 6.566 ↑ 1.0 1 3,283

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=3,283)

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

SubPlan (forNested Loop Left Join)

27. 0.000 0.000 ↑ 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.000 rows=6 loops=1)

28. 0.000 0.000 ↑ 1.0 6 1

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

29. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.98..17.04 rows=1 width=0) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_t166_userrolehierarchy_sid_ancestor_end_start_timestamp on t166_userrolehierarchy (cost=0.56..8.58 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t237_oppfact_1.c832_opp_ownerid) AND (c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
31. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_t67_userroledim_sid_end_start_timestamp_partial on t67_userroledim (cost=0.42..8.44 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t166_userrolehierarchy.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
32. 0.000 1.954 ↑ 2.0 934 1

Nested Loop Semi Join (cost=60.05..17,839.98 rows=1,860 width=4) (actual time=0.097..1.954 rows=934 loops=1)

33. 0.183 0.260 ↑ 2.0 934 1

Bitmap Heap Scan on t166_userrolehierarchy t166_userrolehierarchy_1 (cost=59.63..6,838.33 rows=1,860 width=8) (actual time=0.090..0.260 rows=934 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=130
34. 0.077 0.077 ↑ 2.0 944 1

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..59.16 rows=1,860 width=0) (actual time=0.077..0.077 rows=944 loops=1)

  • Index Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
35. 1.868 1.868 ↑ 1.0 1 934

Index Scan using ix_t67_userroledim_sid_timestamp_partial on t67_userroledim t67_userroledim_1 (cost=0.42..5.90 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=934)

  • Index Cond: ((sid = t166_userrolehierarchy_1.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
36.          

CTE openwont1ids

37. 1.702 59.507 ↓ 16.4 3,283 1

HashAggregate (cost=1,107.13..1,109.13 rows=200 width=4) (actual time=59.079..59.507 rows=3,283 loops=1)

  • Group Key: cte0_5.c1
38. 57.805 57.805 ↑ 15.0 3,283 1

CTE Scan on openwont1 cte0_5 (cost=0.00..984.12 rows=49,206 width=4) (actual time=2.422..57.805 rows=3,283 loops=1)

39.          

CTE bucketab

40. 1.073 1.742 ↑ 615.1 8 1

HashAggregate (cost=1,722.21..1,771.42 rows=4,921 width=68) (actual time=1.727..1.742 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
41. 0.669 0.669 ↑ 15.0 3,283 1

CTE Scan on openwont1 cte0_6 (cost=0.00..984.12 rows=49,206 width=64) (actual time=0.001..0.669 rows=3,283 loops=1)

42.          

CTE picklist_3

43. 0.402 0.402 ↑ 1.0 13 1

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

CTE bucketd

45. 0.141 77.595 ↑ 1,500.0 5 1

GroupAggregate (cost=348,947.00..476,953.25 rows=7,500 width=120) (actual time=77.429..77.595 rows=5 loops=1)

  • Group Key: '1520668799999'::bigint, (CASE WHEN (t237_oppfact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 12)) 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 (alternatives: SubPlan 13 or hashed SubPlan 14) THEN 'other'::text ELSE 'ownerChange'::text END ELSE 'other'::text END)
46. 0.251 77.454 ↑ 12.9 583 1

Sort (cost=348,946.70..348,965.45 rows=7,500 width=60) (actual time=77.411..77.454 rows=583 loops=1)

  • Sort Key: (CASE WHEN (t237_oppfact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 12)) 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 (alternatives: SubPlan 13 or hashed SubPlan 14) THEN 'other'::text ELSE 'ownerChange'::text END ELSE 'other'::text END)
  • Sort Method: quicksort Memory: 70kB
47. 0.715 77.203 ↑ 12.9 583 1

Nested Loop Left Join (cost=17,850.74..348,463.98 rows=7,500 width=60) (actual time=64.089..77.203 rows=583 loops=1)

48. 0.036 72.992 ↑ 9.5 583 1

Nested Loop Left Join (cost=17,850.45..218,617.77 rows=5,512 width=36) (actual time=63.658..72.992 rows=583 loops=1)

49. 0.252 70.624 ↑ 9.5 583 1

Hash Semi Join (cost=17,849.89..176,916.54 rows=5,512 width=20) (actual time=63.651..70.624 rows=583 loops=1)

  • Hash Cond: (t237_oppfact_2.c843_opp_stagename = cte0_9.c1)
50. 0.584 70.058 ↑ 9.4 1,858 1

Nested Loop (cost=17,849.69..176,809.21 rows=17,456 width=24) (actual time=63.319..70.058 rows=1,858 loops=1)

51. 0.326 2.407 ↑ 2.5 737 1

HashAggregate (cost=17,844.63..17,863.14 rows=1,851 width=4) (actual time=2.285..2.407 rows=737 loops=1)

  • Group Key: t166_userrolehierarchy_5.sid
52. 0.731 2.081 ↑ 2.0 934 1

Nested Loop Semi Join (cost=60.05..17,839.98 rows=1,860 width=4) (actual time=0.125..2.081 rows=934 loops=1)

53. 0.320 0.416 ↑ 2.0 934 1

Bitmap Heap Scan on t166_userrolehierarchy t166_userrolehierarchy_5 (cost=59.63..6,838.33 rows=1,860 width=8) (actual time=0.112..0.416 rows=934 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=130
54. 0.096 0.096 ↑ 2.0 944 1

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..59.16 rows=1,860 width=0) (actual time=0.096..0.096 rows=944 loops=1)

  • Index Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
55. 0.934 0.934 ↑ 1.0 1 934

Index Scan using ix_t67_userroledim_sid_timestamp_partial on t67_userroledim t67_userroledim_5 (cost=0.42..5.90 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=934)

  • Index Cond: ((sid = t166_userrolehierarchy_5.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
56. 6.822 67.067 ↑ 3.0 3 737

Index Scan using ix_t237_oppfact_owner_closedate_timestamp on t237_oppfact t237_oppfact_2 (cost=5.06..85.78 rows=9 width=28) (actual time=0.087..0.091 rows=3 loops=737)

  • Index Cond: ((c832_opp_ownerid = t166_userrolehierarchy_5.sid) 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: 2
57.          

SubPlan (forIndex Scan)

58. 60.245 60.245 ↓ 16.4 3,283 1

CTE Scan on openwont1ids cte0_8 (cost=0.00..4.00 rows=200 width=4) (actual time=59.079..60.245 rows=3,283 loops=1)

59. 0.003 0.314 ↑ 1.0 6 1

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

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

61. 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.56 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)
62. 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)

63. 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))
64. 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)
65.          

SubPlan (forNested Loop Left Join)

66. 0.406 0.406 ↑ 1.0 13 1

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

67. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.98..17.04 rows=1 width=0) (never executed)

68. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_t166_userrolehierarchy_sid_ancestor_end_start_timestamp on t166_userrolehierarchy t166_userrolehierarchy_3 (cost=0.56..8.58 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t237_oppfact_3.c832_opp_ownerid) AND (c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
69. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_t67_userroledim_sid_end_start_timestamp_partial on t67_userroledim t67_userroledim_3 (cost=0.42..8.44 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t166_userrolehierarchy_3.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
70. 0.675 1.924 ↑ 2.0 934 1

Nested Loop Semi Join (cost=60.05..17,839.98 rows=1,860 width=4) (actual time=0.112..1.924 rows=934 loops=1)

71. 0.228 0.315 ↑ 2.0 934 1

Bitmap Heap Scan on t166_userrolehierarchy t166_userrolehierarchy_4 (cost=59.63..6,838.33 rows=1,860 width=8) (actual time=0.102..0.315 rows=934 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=130
72. 0.087 0.087 ↑ 2.0 944 1

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..59.16 rows=1,860 width=0) (actual time=0.087..0.087 rows=944 loops=1)

  • Index Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
73. 0.934 0.934 ↑ 1.0 1 934

Index Scan using ix_t67_userroledim_sid_timestamp_partial on t67_userroledim t67_userroledim_4 (cost=0.42..5.90 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=934)

  • Index Cond: ((sid = t166_userrolehierarchy_4.c662_user_role_id) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
74. 0.006 77.606 ↑ 1,500.0 5 1

Subquery Scan on *SELECT* 1 (cost=0.00..243.75 rows=7,500 width=96) (actual time=77.437..77.606 rows=5 loops=1)

75. 77.600 77.600 ↑ 1,500.0 5 1

CTE Scan on bucketd cte0 (cost=0.00..150.00 rows=7,500 width=120) (actual time=77.432..77.600 rows=5 loops=1)

76. 0.015 1.766 ↑ 615.1 8 1

Hash Join (cost=19.68..185.76 rows=4,921 width=96) (actual time=1.750..1.766 rows=8 loops=1)

  • Hash Cond: (cte0_1.c6 = t234_opportunityforecastcategorypicklistdim.sid)
77. 1.743 1.743 ↑ 615.1 8 1

CTE Scan on bucketab cte0_1 (cost=0.00..98.42 rows=4,921 width=68) (actual time=1.728..1.743 rows=8 loops=1)

78. 0.004 0.008 ↑ 86.0 5 1

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

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