explain.depesz.com

PostgreSQL's explain analyze made readable

Result: P744

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 2,947.593 ↑ 2,299.7 15 1

Append (cost=1,230,989.26..1,232,138.75 rows=34,495 width=96) (actual time=2,888.921..2,947.593 rows=15 loops=1)

2.          

CTE picklist_0

3. 0.349 0.349 ↑ 1.0 6 1

Seq Scan on t233_opportunitystagenamepicklistdim (cost=0.00..46.54 rows=6 width=4) (actual time=0.007..0.349 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.340 0.340 ↑ 1.0 7 1

Seq Scan on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_1 (cost=0.00..48.08 rows=7 width=4) (actual time=0.014..0.340 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.295 1,133.267 ↑ 2.9 8,420 1

HashAggregate (cost=275,260.00..275,506.39 rows=24,639 width=4) (actual time=1,132.470..1,133.267 rows=8,420 loops=1)

  • Group Key: distinctclosedateowner.ownerid
8.          

CTE owners

9. 199.368 669.110 ↑ 9.9 129,851 1

Hash Join (cost=59,737.43..236,613.36 rows=1,284,535 width=4) (actual time=160.871..669.110 rows=129,851 loops=1)

  • Hash Cond: (t166_userrolehierarchy.c662_user_role_id = t67_userroledim.sid)
10. 317.005 456.963 ↓ 1.0 1,322,758 1

Bitmap Heap Scan on t166_userrolehierarchy (cost=37,339.61..194,848.64 rows=1,284,535 width=8) (actual time=147.355..456.963 rows=1,322,758 loops=1)

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

Bitmap Index Scan on ix_t166_userrolehierarchy_ancestor_end_start_timestamp (cost=0.00..37,018.48 rows=1,284,535 width=0) (actual time=139.958..139.958 rows=1,326,426 loops=1)

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

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

  • Buckets: 131072 Batches: 2 Memory Usage: 1177kB
13. 6.765 11.329 ↑ 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.026..11.329 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.564 4.564 ↑ 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.564..4.564 rows=14,066 loops=1)

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

Nested Loop (cost=28,902.47..38,296.74 rows=139,956 width=4) (actual time=737.271..1,111.972 rows=134,356 loops=1)

16. 66.048 765.942 ↓ 604.4 120,880 1

HashAggregate (cost=28,902.04..28,904.04 rows=200 width=4) (actual time=737.243..765.942 rows=120,880 loops=1)

  • Group Key: owners.c1
17. 699.894 699.894 ↑ 9.9 129,851 1

CTE Scan on owners (cost=0.00..25,690.70 rows=1,284,535 width=4) (actual time=160.873..699.894 rows=129,851 loops=1)

18. 362.640 362.640 ↑ 11.0 1 120,880

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,880)

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

CTE openwont1

20. 76.431 2,039.885 ↓ 9.4 73,092 1

Nested Loop Left Join (cost=1,110.31..113,298.91 rows=7,758 width=34) (actual time=1,137.486..2,039.885 rows=73,092 loops=1)

21. 38.386 1,816.647 ↓ 11.1 73,092 1

Nested Loop Left Join (cost=555.67..110,407.57 rows=6,562 width=56) (actual time=1,137.434..1,816.647 rows=73,092 loops=1)

22. 15.560 1,632.077 ↓ 13.2 73,092 1

Nested Loop Left Join (cost=555.67..106,034.07 rows=5,551 width=56) (actual time=1,137.419..1,632.077 rows=73,092 loops=1)

23. 16.874 1,324.149 ↓ 13.2 73,092 1

Nested Loop (cost=555.10..58,957.93 rows=5,551 width=28) (actual time=1,137.404..1,324.149 rows=73,092 loops=1)

24. 4.065 1,138.875 ↓ 42.1 8,420 1

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

  • Group Key: ownercte_1.c1
25. 1,134.810 1,134.810 ↑ 2.9 8,420 1

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

26. 168.058 168.400 ↓ 9.0 9 8,420

Index Scan using t236_opp_line_item_fact_564_timestamp on t236_opp_line_item_fact (cost=0.72..292.00 rows=1 width=32) (actual time=0.010..0.020 rows=9 loops=8,420)

  • Index Cond: ((c804_opp_ownerid = ownercte_1.c1) AND (start_stamp <= '1520668799999'::bigint) AND (end_stamp > '1520668799999'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 8)) AND (c805_opp_close_date >= 20180201) AND (c805_opp_close_date <= 20180430))
  • Rows Removed by Filter: 5
27.          

SubPlan (forIndex Scan)

28. 0.342 0.342 ↑ 1.0 7 1

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

29. 292.368 292.368 ↑ 1.0 1 73,092

Index Scan using ix_t236_opp_line_item_fact_sid_timestamp on t236_opp_line_item_fact t236_opp_line_item_fact_1 (cost=0.56..8.47 rows=1 width=28) (actual time=0.004..0.004 rows=1 loops=73,092)

  • Index Cond: ((t236_opp_line_item_fact.sid = sid) AND (t236_opp_line_item_fact.sid = sid) AND (start_stamp <= '1540796399999'::bigint) AND (end_stamp > '1540796399999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
30. 0.000 146.184 ↑ 2.0 1 73,092

Append (cost=0.00..0.77 rows=2 width=16) (actual time=0.002..0.002 rows=1 loops=73,092)

31. 0.000 0.000 ↓ 0.0 0 73,092

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=73,092)

  • Filter: ((to_iso_code = 'USD'::text) AND (t236_opp_line_item_fact.c821_opp_currency_code = sid) AND (t236_opp_line_item_fact.c805_opp_close_date = rate_date))
32. 146.184 146.184 ↑ 1.0 1 73,092

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur177_t0_r0_1 (cost=0.29..0.77 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=73,092)

  • Index Cond: ((t236_opp_line_item_fact.c805_opp_close_date = rate_date) AND (t236_opp_line_item_fact.c821_opp_currency_code = sid))
  • Filter: (to_iso_code = 'USD'::text)
33. 0.000 146.184 ↑ 2.0 1 73,092

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

34. 0.000 0.000 ↓ 0.0 0 73,092

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=73,092)

  • Filter: ((to_iso_code = 'USD'::text) AND (t236_opp_line_item_fact_1.c821_opp_currency_code = sid) AND (t236_opp_line_item_fact_1.c805_opp_close_date = rate_date))
35. 146.184 146.184 ↑ 1.0 1 73,092

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=73,092)

  • Index Cond: ((t236_opp_line_item_fact_1.c805_opp_close_date = rate_date) AND (t236_opp_line_item_fact_1.c821_opp_currency_code = sid))
  • Filter: (to_iso_code = 'USD'::text)
36.          

SubPlan (forNested Loop Left Join)

37. 0.001 0.001 ↑ 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.001 rows=6 loops=1)

38. 0.002 0.002 ↑ 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.002 rows=6 loops=1)

39. 0.620 0.620 ↑ 2.9 8,420 1

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

40.          

CTE openwont1ids

41. 44.788 2,120.398 ↓ 365.5 73,092 1

HashAggregate (cost=174.56..176.56 rows=200 width=4) (actual time=2,107.357..2,120.398 rows=73,092 loops=1)

  • Group Key: cte0_5.c1
42. 2,075.610 2,075.610 ↓ 9.4 73,092 1

CTE Scan on openwont1 cte0_5 (cost=0.00..155.16 rows=7,758 width=4) (actual time=1,137.489..2,075.610 rows=73,092 loops=1)

43.          

CTE bucketab

44. 25.559 41.154 ↑ 77.6 10 1

HashAggregate (cost=271.53..279.29 rows=776 width=68) (actual time=41.153..41.154 rows=10 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
45. 15.595 15.595 ↓ 9.4 73,092 1

CTE Scan on openwont1 cte0_6 (cost=0.00..155.16 rows=7,758 width=64) (actual time=0.014..15.595 rows=73,092 loops=1)

46.          

CTE picklist_3

47. 0.468 0.468 ↑ 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.468 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
48.          

CTE bucketd

49. 12.101 2,906.387 ↑ 6,743.8 5 1

GroupAggregate (cost=840,396.01..841,576.18 rows=33,719 width=120) (actual time=2,888.911..2,906.387 rows=5 loops=1)

  • Group Key: '1520668799999'::bigint, (CASE WHEN (t236_opp_line_item_fact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 13)) THEN CASE WHEN (t236_opp_line_item_fact_3.c805_opp_close_date > 20180430) THEN 'pulledIn'::text WHEN (t236_opp_line_item_fact_3.c805_opp_close_date < 20180201) THEN 'pushedIn'::text WHEN (hashed SubPlan 14) THEN 'other'::text ELSE 'ownerChange'::text END ELSE 'other'::text END)
50. 30.619 2,894.286 ↓ 1.6 52,739 1

Sort (cost=839,841.34..839,925.64 rows=33,719 width=60) (actual time=2,888.806..2,894.286 rows=52,739 loops=1)

  • Sort Key: (CASE WHEN (t236_opp_line_item_fact_3.sid IS NULL) THEN 'trendNew'::text WHEN (NOT (hashed SubPlan 13)) THEN CASE WHEN (t236_opp_line_item_fact_3.c805_opp_close_date > 20180430) THEN 'pulledIn'::text WHEN (t236_opp_line_item_fact_3.c805_opp_close_date < 20180201) THEN 'pushedIn'::text WHEN (hashed SubPlan 14) THEN 'other'::text ELSE 'ownerChange'::text END ELSE 'other'::text END)
  • Sort Method: external merge Disk: 2576kB
51. 22.334 2,863.667 ↓ 1.6 52,739 1

Nested Loop Left Join (cost=1,119.31..837,305.46 rows=33,719 width=60) (actual time=2,407.724..2,863.667 rows=52,739 loops=1)

52. 31.008 2,734.798 ↓ 1.8 52,739 1

Nested Loop Left Join (cost=564.64..826,009.09 rows=28,523 width=36) (actual time=2,407.711..2,734.798 rows=52,739 loops=1)

53. 18.334 2,598.312 ↓ 1.8 52,739 1

Hash Join (cost=564.08..620,950.28 rows=28,523 width=20) (actual time=2,407.702..2,598.312 rows=52,739 loops=1)

  • Hash Cond: (t236_opp_line_item_fact_2.c804_opp_ownerid = ownercte_3.c1)
54. 26.084 2,575.316 ↓ 2.0 117,967 1

Nested Loop (cost=5.20..620,158.12 rows=59,592 width=24) (actual time=2,402.976..2,575.316 rows=117,967 loops=1)

55. 0.009 0.360 ↑ 1.0 6 1

HashAggregate (cost=0.14..0.20 rows=6 width=4) (actual time=0.355..0.360 rows=6 loops=1)

  • Group Key: cte0_9.c1
56. 0.351 0.351 ↑ 1.0 6 1

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

57. 411.347 2,548.872 ↓ 2.0 19,661 6

Index Scan using ix_t236_opp_line_item_fact_closedate_stage_timestamp on t236_opp_line_item_fact t236_opp_line_item_fact_2 (cost=5.06..103,260.33 rows=9,932 width=28) (actual time=400.434..424.812 rows=19,661 loops=6)

  • Index Cond: ((c805_opp_close_date >= 20180201) AND (c805_opp_close_date <= 20180430) AND (c819_opp_stagename = cte0_9.c1) AND (start_stamp <= '1540796399999'::bigint) AND (end_stamp > '1540796399999'::bigint))
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 15)))
  • Rows Removed by Filter: 2060
58.          

SubPlan (forIndex Scan)

59. 2,137.525 2,137.525 ↓ 365.5 73,092 1

CTE Scan on openwont1ids cte0_8 (cost=0.00..4.00 rows=200 width=4) (actual time=2,107.360..2,137.525 rows=73,092 loops=1)

60. 1.166 4.662 ↓ 42.1 8,420 1

Hash (cost=556.38..556.38 rows=200 width=4) (actual time=4.662..4.662 rows=8,420 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 425kB
61. 2.867 3.496 ↓ 42.1 8,420 1

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

  • Group Key: ownercte_3.c1
62. 0.629 0.629 ↑ 2.9 8,420 1

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

63. 105.478 105.478 ↓ 0.0 0 52,739

Index Scan using ix_t236_opp_line_item_fact_sid_timestamp on t236_opp_line_item_fact t236_opp_line_item_fact_3 (cost=0.56..7.18 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=52,739)

  • Index Cond: ((t236_opp_line_item_fact_2.sid = sid) AND (t236_opp_line_item_fact_2.sid = sid) AND (start_stamp <= '1520668799999'::bigint) AND (end_stamp > '1520668799999'::bigint))
  • Filter: (NOT deleted)
64. 0.000 105.478 ↑ 2.0 1 52,739

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

65. 0.000 0.000 ↓ 0.0 0 52,739

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=52,739)

  • Filter: ((to_iso_code = 'USD'::text) AND (t236_opp_line_item_fact_2.c821_opp_currency_code = sid) AND (t236_opp_line_item_fact_2.c805_opp_close_date = rate_date))
66. 105.478 105.478 ↑ 1.0 1 52,739

Index Scan using ix_usd_clari_conversion_rate_date_isocode on usd_clari_conversion_rate cur177_t0_r0_3 (cost=0.29..0.34 rows=1 width=16) (actual time=0.001..0.002 rows=1 loops=52,739)

  • Index Cond: ((t236_opp_line_item_fact_2.c805_opp_close_date = rate_date) AND (t236_opp_line_item_fact_2.c821_opp_currency_code = sid))
  • Filter: (to_iso_code = 'USD'::text)
67.          

SubPlan (forNested Loop Left Join)

68. 0.471 0.471 ↑ 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.471 rows=13 loops=1)

69. 0.586 0.586 ↑ 2.9 8,420 1

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

70. 0.010 2,906.403 ↑ 6,743.8 5 1

Subquery Scan on *SELECT* 1 (cost=0.00..1,095.87 rows=33,719 width=96) (actual time=2,888.921..2,906.403 rows=5 loops=1)

71. 2,906.393 2,906.393 ↑ 6,743.8 5 1

CTE Scan on bucketd cte0 (cost=0.00..674.38 rows=33,719 width=120) (actual time=2,888.914..2,906.393 rows=5 loops=1)

72. 0.013 41.188 ↑ 77.6 10 1

Hash Join (cost=19.68..45.86 rows=776 width=96) (actual time=41.177..41.188 rows=10 loops=1)

  • Hash Cond: (cte0_1.c6 = t234_opportunityforecastcategorypicklistdim.sid)
73. 41.163 41.163 ↑ 77.6 10 1

CTE Scan on bucketab cte0_1 (cost=0.00..15.52 rows=776 width=68) (actual time=41.155..41.163 rows=10 loops=1)

74. 0.005 0.012 ↑ 86.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
75. 0.007 0.007 ↑ 86.0 5 1

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