explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CQU6A

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 3,020.242 ↑ 3,565.1 16 1

Append (cost=1,485,501.48..1,487,629.34 rows=57,041 width=96) (actual time=2,956.099..3,020.242 rows=16 loops=1)

2.          

CTE picklist_0

3. 0.331 0.331 ↑ 1.0 6 1

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

Seq Scan on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_1 (cost=0.00..48.08 rows=7 width=4) (actual time=0.011..0.334 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. 21.819 1,137.579 ↑ 2.9 8,420 1

HashAggregate (cost=275,258.49..275,504.88 rows=24,639 width=4) (actual time=1,136.608..1,137.579 rows=8,420 loops=1)

  • Group Key: distinctclosedateowner.ownerid
8.          

CTE usercte

9. 204.234 679.286 ↑ 9.9 129,846 1

Hash Join (cost=59,737.33..236,612.06 rows=1,284,526 width=4) (actual time=157.343..679.286 rows=129,846 loops=1)

  • Hash Cond: (t166_userrolehierarchy.c662_user_role_id = t67_userroledim.sid)
10. 325.953 462.117 ↓ 1.0 1,322,750 1

Bitmap Heap Scan on t166_userrolehierarchy (cost=37,339.51..194,847.40 rows=1,284,526 width=8) (actual time=143.643..462.117 rows=1,322,750 loops=1)

  • Recheck Cond: ((c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=39465
11. 136.164 136.164 ↓ 1.0 1,326,418 1

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..37,018.38 rows=1,284,526 width=0) (actual time=136.164..136.164 rows=1,326,418 loops=1)

  • Index Cond: ((c663_ancestor_role_id = ANY ('{436,247157}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
12. 1.460 12.935 ↑ 9.3 8,370 1

Hash (cost=21,117.18..21,117.18 rows=78,051 width=4) (actual time=12.935..12.935 rows=8,370 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 1177kB
13. 6.844 11.475 ↑ 9.3 8,370 1

Bitmap Heap Scan on t67_userroledim (cost=4,261.99..21,117.18 rows=78,051 width=4) (actual time=5.087..11.475 rows=8,370 loops=1)

  • Recheck Cond: ((end_stamp = '32503680000000'::bigint) AND ((c377_portaltype IS NULL) OR (c377_portaltype = 'None'::text)))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
  • Rows Removed by Filter: 5666
  • Heap Blocks: exact=2151
14. 4.631 4.631 ↑ 5.6 14,066 1

Bitmap Index Scan on ix_t67_userroledim_sid_end_start_timestamp_partial (cost=0.00..4,242.48 rows=78,925 width=0) (actual time=4.631..4.631 rows=14,066 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
15. 0.000 1,115.760 ↑ 1.0 134,356 1

Nested Loop (cost=28,902.27..38,296.54 rows=139,956 width=4) (actual time=749.399..1,115.760 rows=134,356 loops=1)

16. 65.512 776.398 ↓ 604.4 120,875 1

HashAggregate (cost=28,901.83..28,903.83 rows=200 width=4) (actual time=749.354..776.398 rows=120,875 loops=1)

  • Group Key: usercte.c1
17. 710.886 710.886 ↑ 9.9 129,846 1

CTE Scan on usercte (cost=0.00..25,690.52 rows=1,284,526 width=4) (actual time=157.344..710.886 rows=129,846 loops=1)

18. 362.625 362.625 ↑ 11.0 1 120,875

Index Only Scan using ix_distinctclosedateowner_owner_closedate on distinctclosedateowner (cost=0.43..46.85 rows=11 width=4) (actual time=0.002..0.003 rows=1 loops=120,875)

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

CTE openwont1

20. 73.378 1,220.793 ↑ 2.2 104,716 1

Merge Right Join (cost=955,149.11..972,153.10 rows=226,092 width=34) (actual time=1,082.805..1,220.793 rows=104,716 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))
21. 6.564 61.264 ↑ 1.0 100,671 1

Merge Append (cost=0.31..8,299.20 rows=101,706 width=16) (actual time=0.033..61.264 rows=100,671 loops=1)

  • Sort Key: cur177_t1_r1.rate_date, cur177_t1_r1.sid
22. 0.011 0.014 ↓ 0.0 0 1

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

  • Sort Key: cur177_t1_r1.rate_date, cur177_t1_r1.sid
  • Sort Method: quicksort Memory: 25kB
23. 0.003 0.003 ↓ 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.003..0.003 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
24. 54.686 54.686 ↑ 1.0 100,671 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.018..54.686 rows=100,671 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
25. 10.293 1,085.505 ↑ 1.6 104,716 1

Materialize (cost=954,545.57..955,375.66 rows=166,018 width=56) (actual time=1,051.616..1,085.505 rows=104,716 loops=1)

26. 90.173 1,075.212 ↑ 1.6 104,716 1

Sort (cost=954,545.57..954,960.61 rows=166,018 width=56) (actual time=1,051.614..1,075.212 rows=104,716 loops=1)

  • Sort Key: t237_oppfact_1.c836_opp_close_date, t237_oppfact_1.c845_opp_currency_code
  • Sort Method: external merge Disk: 7152kB
27. 66.622 985.039 ↑ 1.6 104,716 1

Hash Left Join (cost=4,800.57..934,473.99 rows=166,018 width=56) (actual time=50.701..985.039 rows=104,716 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))
28. 7.036 871.414 ↑ 1.2 104,716 1

Nested Loop Left Join (cost=555.67..899,175.58 rows=121,905 width=56) (actual time=3.101..871.414 rows=104,716 loops=1)

29. 16.030 340.798 ↑ 1.2 104,716 1

Nested Loop (cost=555.10..24,878.58 rows=121,905 width=28) (actual time=3.087..340.798 rows=104,716 loops=1)

30. 4.132 4.808 ↓ 42.1 8,420 1

HashAggregate (cost=554.38..556.38 rows=200 width=4) (actual time=2.726..4.808 rows=8,420 loops=1)

  • Group Key: ownercte_1.c1
31. 0.676 0.676 ↑ 2.9 8,420 1

CTE Scan on ownercte ownercte_1 (cost=0.00..492.78 rows=24,639 width=4) (actual time=0.000..0.676 rows=8,420 loops=1)

32. 319.626 319.960 ↑ 1.2 12 8,420

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.038 rows=12 loops=8,420)

  • 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: 8
33.          

SubPlan (forIndex Scan)

34. 0.334 0.334 ↑ 1.0 7 1

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

35. 523.580 523.580 ↑ 1.0 1 104,716

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.004..0.005 rows=1 loops=104,716)

  • Index Cond: ((t237_oppfact.sid = sid) AND (t237_oppfact.sid = sid) AND (start_stamp <= '1540796399999'::bigint) AND (end_stamp > '1540796399999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
36. 19.335 47.003 ↓ 1.0 101,732 1

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

  • Buckets: 131072 Batches: 2 Memory Usage: 3412kB
37. 6.063 27.668 ↓ 1.0 101,732 1

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

38. 0.000 0.000 ↓ 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.000..0.000 rows=0 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
39. 21.605 21.605 ↓ 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.127..21.605 rows=101,732 loops=1)

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

SubPlan (forMerge Right Join)

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

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

43. 0.644 0.644 ↑ 2.9 8,420 1

CTE Scan on ownercte (cost=0.00..492.78 rows=24,639 width=4) (actual time=0.001..0.644 rows=8,420 loops=1)

44.          

CTE openwont1ids

45. 51.336 1,312.200 ↓ 523.6 104,716 1

HashAggregate (cost=5,087.07..5,089.07 rows=200 width=4) (actual time=1,295.353..1,312.200 rows=104,716 loops=1)

  • Group Key: cte0_5.c1
46. 1,260.864 1,260.864 ↑ 2.2 104,716 1

CTE Scan on openwont1 cte0_5 (cost=0.00..4,521.84 rows=226,092 width=4) (actual time=1,082.807..1,260.864 rows=104,716 loops=1)

47.          

CTE bucketab

48. 36.514 58.996 ↑ 2,055.4 11 1

HashAggregate (cost=7,913.22..8,139.31 rows=22,609 width=68) (actual time=58.961..58.996 rows=11 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
49. 22.482 22.482 ↑ 2.2 104,716 1

CTE Scan on openwont1 cte0_6 (cost=0.00..4,521.84 rows=226,092 width=64) (actual time=0.033..22.482 rows=104,716 loops=1)

50.          

CTE picklist_3

51. 0.464 0.464 ↑ 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.464 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
52.          

CTE bucketd

53. 3.950 2,961.189 ↑ 6,886.4 5 1

GroupAggregate (cost=223,258.07..224,463.19 rows=34,432 width=120) (actual time=2,956.087..2,961.189 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)
54. 6.755 2,957.239 ↑ 2.0 17,598 1

Sort (cost=222,703.40..222,789.48 rows=34,432 width=60) (actual time=2,955.934..2,957.239 rows=17,598 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: 2143kB
55. 16.466 2,950.484 ↑ 2.0 17,598 1

Nested Loop Left Join (cost=1,114.87..220,108.69 rows=34,432 width=60) (actual time=2,506.156..2,950.484 rows=17,598 loops=1)

56. 0.853 2,897.734 ↑ 1.4 17,598 1

Nested Loop Left Join (cost=560.20..209,910.28 rows=25,284 width=36) (actual time=2,506.140..2,897.734 rows=17,598 loops=1)

57. 9.340 2,826.489 ↑ 1.4 17,598 1

Hash Semi Join (cost=559.64..18,370.69 rows=25,284 width=20) (actual time=2,506.125..2,826.489 rows=17,598 loops=1)

  • Hash Cond: (t237_oppfact_2.c843_opp_stagename = cte0_9.c1)
58. 23.312 2,816.809 ↓ 1.1 89,025 1

Nested Loop (cost=559.44..17,879.05 rows=80,064 width=24) (actual time=2,505.732..2,816.809 rows=89,025 loops=1)

59. 3.872 1,143.177 ↓ 42.1 8,420 1

HashAggregate (cost=554.38..556.38 rows=200 width=4) (actual time=1,141.340..1,143.177 rows=8,420 loops=1)

  • Group Key: ownercte_3.c1
60. 1,139.305 1,139.305 ↑ 2.9 8,420 1

CTE Scan on ownercte ownercte_3 (cost=0.00..492.78 rows=24,639 width=4) (actual time=1,136.627..1,139.305 rows=8,420 loops=1)

61. 310.944 1,650.320 ↓ 1.2 11 8,420

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.173..0.196 rows=11 loops=8,420)

  • 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: 8
62.          

SubPlan (forIndex Scan)

63. 1,339.376 1,339.376 ↓ 523.6 104,716 1

CTE Scan on openwont1ids cte0_8 (cost=0.00..4.00 rows=200 width=4) (actual time=1,295.354..1,339.376 rows=104,716 loops=1)

64. 0.006 0.340 ↑ 1.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
65. 0.334 0.334 ↑ 1.0 6 1

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

66. 70.392 70.392 ↓ 0.0 0 17,598

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=17,598)

  • 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)
67. 0.000 35.196 ↑ 2.0 1 17,598

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

68. 0.000 0.000 ↓ 0.0 0 17,598

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=17,598)

  • 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))
69. 35.196 35.196 ↑ 1.0 1 17,598

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=17,598)

  • 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)
70.          

SubPlan (forNested Loop Left Join)

71. 0.467 0.467 ↑ 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.467 rows=13 loops=1)

72. 0.621 0.621 ↑ 2.9 8,420 1

CTE Scan on ownercte ownercte_2 (cost=0.00..492.78 rows=24,639 width=4) (actual time=0.000..0.621 rows=8,420 loops=1)

73. 0.011 2,961.206 ↑ 6,886.4 5 1

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

74. 2,961.195 2,961.195 ↑ 6,886.4 5 1

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

75. 0.018 59.033 ↑ 2,055.4 11 1

Hash Join (cost=19.68..782.73 rows=22,609 width=96) (actual time=58.989..59.033 rows=11 loops=1)

  • Hash Cond: (cte0_1.c6 = t234_opportunityforecastcategorypicklistdim.sid)
76. 59.006 59.006 ↑ 2,055.4 11 1

CTE Scan on bucketab cte0_1 (cost=0.00..452.18 rows=22,609 width=68) (actual time=58.963..59.006 rows=11 loops=1)

77. 0.003 0.009 ↑ 86.0 5 1

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

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