explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PgbK

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 290.007 ↑ 4,889.5 40 1

Append (cost=1,169,295.60..1,178,156.16 rows=195,580 width=120) (actual time=205.938..290.007 rows=40 loops=1)

2.          

CTE picklist_0

3. 0.276 0.276 ↑ 1.0 6 1

Seq Scan on t233_opportunitystagenamepicklistdim (cost=0.00..46.54 rows=6 width=4) (actual time=0.006..0.276 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_1

5. 0.300 0.300 ↑ 1.0 7 1

Seq Scan on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_1 (cost=0.00..48.08 rows=7 width=4) (actual time=0.006..0.300 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 picklist_4

7. 0.418 0.418 ↑ 1.0 13 1

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

CTE ownercte

9. 0.865 104.397 ↑ 31.6 360 1

HashAggregate (cost=51,005.98..51,119.73 rows=11,375 width=4) (actual time=104.337..104.397 rows=360 loops=1)

  • Group Key: distinctclosedateowner.ownerid
10. 17.959 103.532 ↑ 2.8 4,093 1

Hash Join (cost=21,065.63..50,977.55 rows=11,375 width=4) (actual time=25.128..103.532 rows=4,093 loops=1)

  • Hash Cond: (distinctclosedateowner.ownerid = t166_userrolehierarchy.sid)
11. 63.594 83.136 ↓ 1.3 196,760 1

Bitmap Heap Scan on distinctclosedateowner (cost=3,179.36..32,515.59 rows=149,749 width=4) (actual time=22.604..83.136 rows=196,760 loops=1)

  • Recheck Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
  • Heap Blocks: exact=17913
12. 19.542 19.542 ↓ 1.3 196,760 1

Bitmap Index Scan on ix_closedate_distinctclosedateowner (cost=0.00..3,141.92 rows=149,749 width=0) (actual time=19.542..19.542 rows=196,760 loops=1)

  • Index Cond: ((closedate <= 20190131) AND (closedate >= 20181101))
13. 0.097 2.437 ↑ 2.5 737 1

Hash (cost=17,863.13..17,863.13 rows=1,851 width=4) (actual time=2.437..2.437 rows=737 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 42kB
14. 0.288 2.340 ↑ 2.5 737 1

HashAggregate (cost=17,844.62..17,863.13 rows=1,851 width=4) (actual time=2.269..2.340 rows=737 loops=1)

  • Group Key: t166_userrolehierarchy.sid
15. 0.694 2.052 ↑ 2.0 934 1

Nested Loop Semi Join (cost=60.05..17,839.97 rows=1,860 width=4) (actual time=0.136..2.052 rows=934 loops=1)

16. 0.330 0.424 ↑ 2.0 934 1

Bitmap Heap Scan on t166_userrolehierarchy (cost=59.63..6,838.32 rows=1,860 width=8) (actual time=0.123..0.424 rows=934 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = 237423) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=130
17. 0.094 0.094 ↑ 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.094..0.094 rows=944 loops=1)

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

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

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

CTE bucketac

20. 1.351 207.551 ↑ 4,309.3 19 1

GroupAggregate (cost=410,258.99..414,148.10 rows=81,876 width=92) (actual time=205.894..207.551 rows=19 loops=1)

  • Group Key: t237_oppfact.c844_opp_forecastcategory, (CASE WHEN (t237_oppfact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t237_oppfact_1.c836_opp_close_date < 20181101) OR (t237_oppfact_1.c836_opp_close_date > 20190131)) THEN 'slipped'::text WHEN (hashed SubPlan 5) THEN CASE WHEN (hashed SubPlan 6) THEN 'won'::text WHEN (hashed SubPlan 7) THEN 'lost'::text ELSE CASE WHEN (t234_opportunityforecastcategorypicklistdim_2.correlated_app_order = t234_opportunityforecastcategorypicklistdim_3.correlated_app_order) THEN 'unchanged'::text WHEN (t234_opportunityforecastcategorypicklistdim_2.correlated_app_order > t234_opportunityforecastcategorypicklistdim_3.correlated_app_order) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
21. 1.689 206.200 ↑ 20.4 4,010 1

Sort (cost=410,002.76..410,207.45 rows=81,876 width=72) (actual time=205.876..206.200 rows=4,010 loops=1)

  • Sort Key: t237_oppfact.c844_opp_forecastcategory, (CASE WHEN (t237_oppfact_1.sid IS NULL) THEN 'deleted'::text WHEN ((t237_oppfact_1.c836_opp_close_date < 20181101) OR (t237_oppfact_1.c836_opp_close_date > 20190131)) THEN 'slipped'::text WHEN (hashed SubPlan 5) THEN CASE WHEN (hashed SubPlan 6) THEN 'won'::text WHEN (hashed SubPlan 7) THEN 'lost'::text ELSE CASE WHEN (t234_opportunityforecastcategorypicklistdim_2.correlated_app_order = t234_opportunityforecastcategorypicklistdim_3.correlated_app_order) THEN 'unchanged'::text WHEN (t234_opportunityforecastcategorypicklistdim_2.correlated_app_order > t234_opportunityforecastcategorypicklistdim_3.correlated_app_order) THEN 'upgraded'::text ELSE 'downgraded'::text END END ELSE 'lostOwnership'::text END)
  • Sort Method: quicksort Memory: 628kB
22. 9.837 204.511 ↑ 20.4 4,010 1

Hash Left Join (cost=4,794.31..399,961.20 rows=81,876 width=72) (actual time=152.600..204.511 rows=4,010 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))
23. 2.201 147.855 ↑ 15.0 4,010 1

Nested Loop Left Join (cost=296.71..378,332.95 rows=60,175 width=64) (actual time=105.082..147.855 rows=4,010 loops=1)

24. 1.129 137.634 ↑ 11.0 4,010 1

Hash Left Join (cost=296.71..363,485.62 rows=44,226 width=60) (actual time=105.066..137.634 rows=4,010 loops=1)

  • Hash Cond: (t237_oppfact_1.c844_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim_3.sid)
25. 0.130 136.497 ↑ 11.0 4,010 1

Nested Loop Left Join (cost=277.04..362,857.83 rows=44,226 width=60) (actual time=105.051..136.497 rows=4,010 loops=1)

26. 1.009 116.317 ↑ 11.0 4,010 1

Hash Left Join (cost=276.47..10,046.23 rows=44,226 width=28) (actual time=105.038..116.317 rows=4,010 loops=1)

  • Hash Cond: (t237_oppfact.c844_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim_2.sid)
27. 0.591 115.299 ↑ 11.0 4,010 1

Nested Loop (cost=256.80..9,418.45 rows=44,226 width=24) (actual time=105.019..115.299 rows=4,010 loops=1)

28. 0.172 104.628 ↓ 1.8 360 1

HashAggregate (cost=255.94..257.94 rows=200 width=4) (actual time=104.538..104.628 rows=360 loops=1)

  • Group Key: ownercte_1.c1
29. 104.456 104.456 ↑ 31.6 360 1

CTE Scan on ownercte ownercte_1 (cost=0.00..227.50 rows=11,375 width=4) (actual time=104.338..104.456 rows=360 loops=1)

30. 9.661 10.080 ↓ 2.2 11 360

Index Scan using ix_t237_oppfact_owner_closedate_timestamp on t237_oppfact (cost=0.86..45.75 rows=5 width=28) (actual time=0.010..0.028 rows=11 loops=360)

  • Index Cond: ((c832_opp_ownerid = ownercte_1.c1) AND (c836_opp_close_date >= 20181101) AND (c836_opp_close_date <= 20190131) AND (start_stamp <= '1541055600000'::bigint) AND (end_stamp > '1541055600000'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 8)))
  • Rows Removed by Filter: 2
31.          

SubPlan (forIndex Scan)

32. 0.419 0.419 ↑ 1.0 13 1

CTE Scan on picklist_4 cte0_4 (cost=0.00..0.26 rows=13 width=4) (actual time=0.011..0.419 rows=13 loops=1)

33. 0.006 0.009 ↑ 86.0 5 1

Hash (cost=14.30..14.30 rows=430 width=8) (actual time=0.009..0.009 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
34. 0.003 0.003 ↑ 86.0 5 1

Seq Scan on t234_opportunityforecastcategorypicklistdim t234_opportunityforecastcategorypicklistdim_2 (cost=0.00..14.30 rows=430 width=8) (actual time=0.003..0.003 rows=5 loops=1)

35. 20.050 20.050 ↑ 1.0 1 4,010

Index Scan using ix_t237_oppfact_sid_timestamp on t237_oppfact t237_oppfact_1 (cost=0.56..7.97 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=4,010)

  • Index Cond: ((t237_oppfact.sid = sid) AND (t237_oppfact.sid = sid) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
  • Filter: (NOT deleted)
36. 0.007 0.008 ↑ 86.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.001 0.001 ↑ 86.0 5 1

Seq Scan on t234_opportunityforecastcategorypicklistdim t234_opportunityforecastcategorypicklistdim_3 (cost=0.00..14.30 rows=430 width=8) (actual time=0.001..0.001 rows=5 loops=1)

38. 0.000 8.020 ↑ 2.0 1 4,010

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

39. 0.000 0.000 ↓ 0.0 0 4,010

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=4,010)

  • 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))
40. 8.020 8.020 ↑ 1.0 1 4,010

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=4,010)

  • 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)
41. 18.774 46.208 ↑ 1.0 101,598 1

Hash (cost=2,220.16..2,220.16 rows=101,614 width=16) (actual time=46.208..46.208 rows=101,598 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3409kB
42. 5.920 27.434 ↑ 1.0 101,598 1

Append (cost=0.00..2,220.16 rows=101,614 width=16) (actual time=0.124..27.434 rows=101,598 loops=1)

43. 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)
44. 21.513 21.513 ↑ 1.0 101,598 1

Seq Scan on usd_clari_conversion_rate cur177_t0_r0_1 (cost=0.00..2,220.16 rows=101,613 width=16) (actual time=0.123..21.513 rows=101,598 loops=1)

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

SubPlan (forHash Left Join)

46. 0.027 0.027 ↑ 31.6 360 1

CTE Scan on ownercte (cost=0.00..227.50 rows=11,375 width=4) (actual time=0.000..0.027 rows=360 loops=1)

47. 0.281 0.281 ↑ 1.0 6 1

CTE Scan on picklist_0 cte0_2 (cost=0.00..0.12 rows=6 width=4) (actual time=0.010..0.281 rows=6 loops=1)

48. 0.303 0.303 ↑ 1.0 7 1

CTE Scan on picklist_1 cte0_3 (cost=0.00..0.14 rows=7 width=4) (actual time=0.007..0.303 rows=7 loops=1)

49.          

CTE bucketec

50. 0.136 82.335 ↑ 5,414.5 21 1

GroupAggregate (cost=699,023.73..703,856.15 rows=113,704 width=116) (actual time=82.190..82.335 rows=21 loops=1)

  • Group Key: (CASE WHEN (t237_oppfact_3.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 10)) THEN CASE WHEN (t237_oppfact_3.c836_opp_close_date > 20190131) THEN 'pulledInBucket'::text WHEN (t237_oppfact_3.c836_opp_close_date < 20181101) THEN 'pushedInBucket'::text WHEN (hashed SubPlan 11) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END ELSE 'otherBucket'::text END), (CASE WHEN (hashed SubPlan 12) THEN 'won'::text WHEN (hashed SubPlan 13) THEN 'lost'::text ELSE 'newOpen'::text END), t237_oppfact_2.c844_opp_forecastcategory
51. 0.266 82.199 ↑ 282.8 402 1

Sort (cost=698,767.21..699,051.47 rows=113,704 width=88) (actual time=82.175..82.199 rows=402 loops=1)

  • Sort Key: (CASE WHEN (t237_oppfact_3.sid IS NULL) THEN 'newBucket'::text WHEN (NOT (hashed SubPlan 10)) THEN CASE WHEN (t237_oppfact_3.c836_opp_close_date > 20190131) THEN 'pulledInBucket'::text WHEN (t237_oppfact_3.c836_opp_close_date < 20181101) THEN 'pushedInBucket'::text WHEN (hashed SubPlan 11) THEN 'otherBucket'::text ELSE 'gainedOwnershipBucket'::text END ELSE 'otherBucket'::text END), (CASE WHEN (hashed SubPlan 12) THEN 'won'::text WHEN (hashed SubPlan 13) THEN 'lost'::text ELSE 'newOpen'::text END), t237_oppfact_2.c844_opp_forecastcategory
  • Sort Method: quicksort Memory: 58kB
52. 5.449 81.933 ↑ 282.8 402 1

Hash Left Join (cost=5,010.90..683,776.46 rows=113,704 width=88) (actual time=46.792..81.933 rows=402 loops=1)

  • Hash Cond: ((t237_oppfact_2.c845_opp_currency_code = cur177_t0_r0_2.sid) AND (t237_oppfact_2.c836_opp_close_date = cur177_t0_r0_2.rate_date))
53. 2.646 30.536 ↑ 207.9 402 1

Nested Loop Left Join (cost=513.00..656,293.14 rows=83,567 width=44) (actual time=0.281..30.536 rows=402 loops=1)

  • Filter: ((t237_oppfact_3.sid IS NULL) OR (t237_oppfact_3.c836_opp_close_date < 20181101) OR (t237_oppfact_3.c836_opp_close_date > 20190131) OR (NOT (hashed SubPlan 14)))
  • Rows Removed by Filter: 4431
54. 1.160 8.530 ↑ 17.8 4,833 1

Nested Loop (cost=256.50..9,364.95 rows=86,114 width=28) (actual time=0.119..8.530 rows=4,833 loops=1)

55. 0.141 0.170 ↓ 1.8 360 1

HashAggregate (cost=255.94..257.94 rows=200 width=4) (actual time=0.102..0.170 rows=360 loops=1)

  • Group Key: ownercte_4.c1
56. 0.029 0.029 ↑ 31.6 360 1

CTE Scan on ownercte ownercte_4 (cost=0.00..227.50 rows=11,375 width=4) (actual time=0.001..0.029 rows=360 loops=1)

57. 7.200 7.200 ↓ 1.3 13 360

Index Scan using ix_t237_oppfact_owner_closedate_timestamp on t237_oppfact t237_oppfact_2 (cost=0.56..45.44 rows=10 width=32) (actual time=0.007..0.020 rows=13 loops=360)

  • Index Cond: ((c832_opp_ownerid = ownercte_4.c1) AND (c836_opp_close_date >= 20181101) AND (c836_opp_close_date <= 20190131) AND (start_stamp <= '1542009599999'::bigint) AND (end_stamp > '1542009599999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
58. 19.332 19.332 ↑ 1.0 1 4,833

Index Scan using ix_t237_oppfact_sid_timestamp on t237_oppfact t237_oppfact_3 (cost=0.56..7.49 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=4,833)

  • Index Cond: ((t237_oppfact_2.sid = sid) AND (t237_oppfact_2.sid = sid) AND (start_stamp <= '1541055600000'::bigint) AND (end_stamp > '1541055600000'::bigint))
  • Filter: (NOT deleted)
59.          

SubPlan (forNested Loop Left Join)

60. 0.028 0.028 ↑ 31.6 360 1

CTE Scan on ownercte ownercte_3 (cost=0.00..227.50 rows=11,375 width=4) (actual time=0.000..0.028 rows=360 loops=1)

61. 18.500 45.907 ↑ 1.0 101,598 1

Hash (cost=2,220.16..2,220.16 rows=101,614 width=16) (actual time=45.907..45.907 rows=101,598 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3409kB
62. 6.077 27.407 ↑ 1.0 101,598 1

Append (cost=0.00..2,220.16 rows=101,614 width=16) (actual time=0.086..27.407 rows=101,598 loops=1)

63. 0.002 0.002 ↓ 0.0 0 1

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

  • Filter: (to_iso_code = 'USD'::text)
64. 21.328 21.328 ↑ 1.0 101,598 1

Seq Scan on usd_clari_conversion_rate cur177_t0_r0_3 (cost=0.00..2,220.16 rows=101,613 width=16) (actual time=0.084..21.328 rows=101,598 loops=1)

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

SubPlan (forHash Left Join)

66. 0.004 0.004 ↑ 1.0 13 1

CTE Scan on picklist_4 cte0_5 (cost=0.00..0.26 rows=13 width=4) (actual time=0.001..0.004 rows=13 loops=1)

67. 0.034 0.034 ↑ 31.6 360 1

CTE Scan on ownercte ownercte_2 (cost=0.00..227.50 rows=11,375 width=4) (actual time=0.001..0.034 rows=360 loops=1)

68. 0.002 0.002 ↑ 1.0 6 1

CTE Scan on picklist_0 cte0_6 (cost=0.00..0.12 rows=6 width=4) (actual time=0.001..0.002 rows=6 loops=1)

69. 0.001 0.001 ↑ 1.0 7 1

CTE Scan on picklist_1 cte0_7 (cost=0.00..0.14 rows=7 width=4) (actual time=0.000..0.001 rows=7 loops=1)

70. 0.034 207.604 ↑ 4,309.3 19 1

Hash Join (cost=19.68..2,782.99 rows=81,876 width=120) (actual time=205.938..207.604 rows=19 loops=1)

  • Hash Cond: (cte0.c4 = t234_opportunityforecastcategorypicklistdim.sid)
71. 207.560 207.560 ↑ 4,309.3 19 1

CTE Scan on bucketac cte0 (cost=0.00..1,637.52 rows=81,876 width=92) (actual time=205.897..207.560 rows=19 loops=1)

72. 0.004 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
73. 0.006 0.006 ↑ 86.0 5 1

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

74. 0.009 82.397 ↑ 5,414.5 21 1

Subquery Scan on *SELECT* 2 (cost=19.68..5,278.49 rows=113,704 width=120) (actual time=82.216..82.397 rows=21 loops=1)

75. 0.035 82.388 ↑ 5,414.5 21 1

Hash Join (cost=19.68..3,857.19 rows=113,704 width=144) (actual time=82.210..82.388 rows=21 loops=1)

  • Hash Cond: (cte0_1.c6 = t234_opportunityforecastcategorypicklistdim_1.sid)
76. 82.345 82.345 ↑ 5,414.5 21 1

CTE Scan on bucketec cte0_1 (cost=0.00..2,274.08 rows=113,704 width=116) (actual time=82.191..82.345 rows=21 loops=1)

77. 0.003 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
78. 0.005 0.005 ↑ 86.0 5 1

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