explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ulUw

Settings
# exclusive inclusive rows x rows loops node
1. 0.037 6,766.546 ↑ 1.2 400 1

Nested Loop (cost=3,418,309,185.52..3,418,313,628.24 rows=470 width=19) (actual time=6,763.592..6,766.546 rows=400 loops=1)

2.          

CTE picklist_0

3. 0.048 0.048 ↑ 1.2 6 1

Index Only Scan using ix_gr_t233_opportunitystagenamepicklistdim on t233_opportunitystagenamepicklistdim (cost=0.28..25.78 rows=7 width=4) (actual time=0.020..0.048 rows=6 loops=1)

  • Index Cond: (correlated_value = ANY ('{"06 - Won, Deploy & Expand","6 - Won, Deploy & Expand","Closed - Won","06 - Won. Deploy & Expand",Won,"06 - Won, Deploy & Expand"}'::text[]))
  • Heap Fetches: 0
4.          

CTE picklist_1

5. 0.021 0.021 ↑ 1.0 7 1

Index Only Scan using ix_gr_t233_opportunitystagenamepicklistdim on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_1 (cost=0.28..26.06 rows=7 width=4) (actual time=0.005..0.021 rows=7 loops=1)

  • Index Cond: (correlated_value = ANY ('{"HPE Not Pursued",Lost,Close:Duplicate,"HP Not Pursued",Duplicate,Error,"Closed - Lost"}'::text[]))
  • Heap Fetches: 0
6.          

CTE picklist_4

7. 0.065 0.065 ↑ 1.1 13 1

Index Only Scan using ix_gr_t233_opportunitystagenamepicklistdim on t233_opportunitystagenamepicklistdim t233_opportunitystagenamepicklistdim_2 (cost=0.28..43.84 rows=14 width=4) (actual time=0.021..0.065 rows=13 loops=1)

  • Index Cond: (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[]))
  • Heap Fetches: 0
8.          

CTE ownercte

9. 7.111 1,647.461 ↑ 2.9 8,743 1

Unique (cost=598,894.01..749,342.58 rows=25,092 width=4) (actual time=1,490.883..1,647.461 rows=8,743 loops=1)

10. 40.385 1,640.350 ↓ 1.1 132,400 1

Merge Join (cost=598,894.01..749,043.53 rows=119,621 width=4) (actual time=1,490.882..1,640.350 rows=132,400 loops=1)

  • Merge Cond: (t728_userrolehierarchy.sid = t629_distinctcdo_opp_closedate_ownerid.c2616_ownerid)
11. 16.022 637.851 ↑ 9.6 137,320 1

Unique (cost=410,025.18..416,598.98 rows=1,314,759 width=4) (actual time=603.554..637.851 rows=137,320 loops=1)

12. 79.662 621.829 ↑ 9.0 145,870 1

Sort (cost=410,025.18..413,312.08 rows=1,314,759 width=4) (actual time=603.553..621.829 rows=145,870 loops=1)

  • Sort Key: t728_userrolehierarchy.sid
  • Sort Method: external merge Disk: 1992kB
13. 8.764 542.167 ↑ 9.0 146,026 1

Append (cost=60,527.36..258,427.81 rows=1,314,759 width=4) (actual time=149.099..542.167 rows=146,026 loops=1)

14. 172.393 520.162 ↑ 10.0 131,300 1

Hash Join (cost=60,527.36..233,894.85 rows=1,311,391 width=4) (actual time=149.099..520.162 rows=131,300 loops=1)

  • Hash Cond: (t728_userrolehierarchy.c2842_user_role_id = t67_userroledim.sid)
15. 206.973 335.577 ↓ 1.0 1,339,311 1

Bitmap Heap Scan on t728_userrolehierarchy (cost=39,522.32..203,815.18 rows=1,311,391 width=8) (actual time=136.431..335.577 rows=1,339,311 loops=1)

  • Recheck Cond: ((c2843_ancestor_role_id = 436) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=40729
16. 128.604 128.604 ↓ 1.0 1,342,130 1

Bitmap Index Scan on idx_t728_userrolehierarchy_c2843_ancestor_role_id_end_s36994704 (cost=0.00..39,194.47 rows=1,311,391 width=0) (actual time=128.604..128.604 rows=1,342,130 loops=1)

  • Index Cond: ((c2843_ancestor_role_id = 436) AND (end_stamp = '32503680000000'::bigint))
17. 1.330 12.192 ↑ 9.0 8,424 1

Hash (cost=20,053.11..20,053.11 rows=76,155 width=4) (actual time=12.192..12.192 rows=8,424 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 1321kB
18. 6.695 10.862 ↑ 9.0 8,424 1

Bitmap Heap Scan on t67_userroledim (cost=4,111.65..20,053.11 rows=76,155 width=4) (actual time=5.018..10.862 rows=8,424 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: 7084
  • Heap Blocks: exact=2279
19. 4.167 4.167 ↑ 4.9 15,743 1

Bitmap Index Scan on ix_t67_userroledim_sid_end_start_timestamp_partial (cost=0.00..4,092.61 rows=77,169 width=0) (actual time=4.167..4.167 rows=15,743 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
20. 13.241 13.241 ↓ 4.4 14,726 1

Index Scan using ix_t12_userdim_usertype_nullrole_end_start on t12_userdim (cost=0.29..11,385.38 rows=3,368 width=4) (actual time=0.263..13.241 rows=14,726 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer))
21. 25.843 962.114 ↓ 1.2 286,335 1

Materialize (cost=188,868.83..190,065.04 rows=239,242 width=4) (actual time=886.063..962.114 rows=286,335 loops=1)

22. 170.142 936.271 ↓ 1.2 286,335 1

Sort (cost=188,868.83..189,466.93 rows=239,242 width=4) (actual time=886.057..936.271 rows=286,335 loops=1)

  • Sort Key: t629_distinctcdo_opp_closedate_ownerid.c2616_ownerid
  • Sort Method: external merge Disk: 3904kB
23. 766.129 766.129 ↓ 1.2 286,335 1

Seq Scan on t629_distinctcdo_opp_closedate_ownerid (cost=0.00..164,222.32 rows=239,242 width=4) (actual time=0.145..766.129 rows=286,335 loops=1)

  • Filter: ((c2620_closedate >= 20181101) AND (c2620_closedate <= 20190131))
  • Rows Removed by Filter: 6202244
24.          

CTE bucketac

25. 140.302 6,569.667 ↑ 17.1 92,561 1

Nested Loop Left Join (cost=615,353.99..3,417,524,128.10 rows=1,581,125 width=164) (actual time=3,091.575..6,569.667 rows=92,561 loops=1)

26. 149.917 3,373.852 ↑ 11.1 92,561 1

GroupAggregate (cost=611,487.30..639,813.65 rows=1,030,049 width=44) (actual time=3,088.561..3,373.852 rows=92,561 loops=1)

  • Group Key: t236_opp_line_item_fact.c801_opportunity_sid, t236_opp_line_item_fact.c820_opp_forecastcategory, t234_opportunityforecastcategorypicklistdim.correlated_app_order
27. 310.849 3,223.935 ↑ 2.5 419,442 1

Sort (cost=611,487.30..614,062.43 rows=1,030,049 width=28) (actual time=3,088.534..3,223.935 rows=419,442 loops=1)

  • Sort Key: t236_opp_line_item_fact.c801_opportunity_sid, t236_opp_line_item_fact.c820_opp_forecastcategory, t234_opportunityforecastcategorypicklistdim.correlated_app_order
  • Sort Method: external merge Disk: 17184kB
28. 81.816 2,913.086 ↑ 2.5 419,442 1

Hash Left Join (cost=448,230.45..483,967.86 rows=1,030,049 width=28) (actual time=2,549.157..2,913.086 rows=419,442 loops=1)

  • Hash Cond: (t236_opp_line_item_fact.c820_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim.sid)
29. 79.577 2,831.259 ↑ 2.5 419,442 1

Merge Right Join (cost=448,229.34..470,333.02 rows=1,030,049 width=24) (actual time=2,549.130..2,831.259 rows=419,442 loops=1)

  • Merge Cond: ((t716_clari_conversion_rate.sid = t236_opp_line_item_fact.c821_opp_currency_code) AND (t716_clari_conversion_rate.rate_date = t236_opp_line_item_fact.c805_opp_close_date))
30. 6.833 49.779 ↑ 1.0 105,100 1

Merge Append (cost=0.31..7,793.41 rows=105,520 width=16) (actual time=0.023..49.779 rows=105,100 loops=1)

  • Sort Key: t716_clari_conversion_rate.sid, t716_clari_conversion_rate.rate_date
31. 0.004 0.005 ↓ 0.0 0 1

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

  • Sort Key: t716_clari_conversion_rate.sid, t716_clari_conversion_rate.rate_date
  • Sort Method: quicksort Memory: 25kB
32. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on t716_clari_conversion_rate (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)
33. 42.941 42.941 ↑ 1.0 105,100 1

Index Scan using t716_clari_conversion_rate_usd_sidratedateunique on t716_clari_conversion_rate_usd (cost=0.29..6,474.39 rows=105,519 width=16) (actual time=0.017..42.941 rows=105,100 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
34. 36.495 2,701.903 ↑ 2.5 419,442 1

Materialize (cost=448,229.03..453,379.27 rows=1,030,049 width=24) (actual time=2,547.945..2,701.903 rows=419,442 loops=1)

35. 274.981 2,665.408 ↑ 2.5 419,442 1

Sort (cost=448,229.03..450,804.15 rows=1,030,049 width=24) (actual time=2,547.942..2,665.408 rows=419,442 loops=1)

  • Sort Key: t236_opp_line_item_fact.c821_opp_currency_code, t236_opp_line_item_fact.c805_opp_close_date
  • Sort Method: external merge Disk: 15512kB
36. 53.777 2,390.427 ↑ 2.5 419,442 1

Nested Loop (cost=565.45..324,230.59 rows=1,030,049 width=24) (actual time=1,651.798..2,390.427 rows=419,442 loops=1)

37. 6.037 1,654.696 ↓ 43.7 8,743 1

HashAggregate (cost=564.57..566.57 rows=200 width=4) (actual time=1,651.670..1,654.696 rows=8,743 loops=1)

  • Group Key: ownercte_1.c1
38. 1,648.659 1,648.659 ↑ 2.9 8,743 1

CTE Scan on ownercte ownercte_1 (cost=0.00..501.84 rows=25,092 width=4) (actual time=1,490.886..1,648.659 rows=8,743 loops=1)

39. 681.885 681.954 ↑ 3.8 48 8,743

Index Scan using ix_t236_opp_line_item_fact_owner_closedate_timestamp on t236_opp_line_item_fact (cost=0.88..1,616.51 rows=181 width=28) (actual time=0.014..0.078 rows=48 loops=8,743)

  • Index Cond: ((c804_opp_ownerid = ownercte_1.c1) AND (c805_opp_close_date >= 20181101) AND (c805_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: 22
40.          

SubPlan (forIndex Scan)

41. 0.069 0.069 ↑ 1.1 13 1

CTE Scan on picklist_4 cte0_2 (cost=0.00..0.28 rows=14 width=4) (actual time=0.022..0.069 rows=13 loops=1)

42. 0.003 0.011 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=8) (actual time=0.011..0.011 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
43. 0.008 0.008 ↑ 1.0 5 1

Seq Scan on t234_opportunityforecastcategorypicklistdim (cost=0.00..1.05 rows=5 width=8) (actual time=0.006..0.008 rows=5 loops=1)

44. 0.000 3,054.513 ↑ 2.0 1 92,561

Subquery Scan on oli174_ft1 (cost=3,301.80..3,317.15 rows=2 width=84) (actual time=0.033..0.033 rows=1 loops=92,561)

  • Filter: (t236_opp_line_item_fact.c801_opportunity_sid = oli174_ft1.c1)
45. 277.683 3,054.513 ↑ 307.0 1 92,561

GroupAggregate (cost=3,301.80..3,313.31 rows=307 width=84) (actual time=0.033..0.033 rows=1 loops=92,561)

  • Group Key: t236_opp_line_item_fact_1.c801_opportunity_sid, t236_opp_line_item_fact_1.c805_opp_close_date, t236_opp_line_item_fact_1.c804_opp_ownerid, t236_opp_line_item_fact_1.c819_opp_stagename, t234_opportunityforecastcategorypicklistdim_1.correlated_app_order
46. 277.683 2,776.830 ↑ 61.4 5 92,561

Sort (cost=3,301.80..3,302.57 rows=307 width=40) (actual time=0.030..0.030 rows=5 loops=92,561)

  • Sort Key: t236_opp_line_item_fact_1.c805_opp_close_date, t236_opp_line_item_fact_1.c804_opp_ownerid, t236_opp_line_item_fact_1.c819_opp_stagename, t234_opportunityforecastcategorypicklistdim_1.correlated_app_order
  • Sort Method: quicksort Memory: 25kB
47. 462.805 2,499.147 ↑ 61.4 5 92,561

Nested Loop Left Join (cost=0.56..3,289.12 rows=307 width=40) (actual time=0.010..0.027 rows=5 loops=92,561)

  • Join Filter: (t236_opp_line_item_fact_1.c820_opp_forecastcategory = t234_opportunityforecastcategorypicklistdim_1.sid)
  • Rows Removed by Join Filter: 21
48. 327.868 2,036.342 ↑ 61.4 5 92,561

Nested Loop Left Join (cost=0.56..3,265.03 rows=307 width=40) (actual time=0.009..0.022 rows=5 loops=92,561)

49. 740.488 740.488 ↑ 61.4 5 92,561

Index Scan using t236_opp_line_item_fact_561_timestamp on t236_opp_line_item_fact t236_opp_line_item_fact_1 (cost=0.56..1,170.18 rows=307 width=36) (actual time=0.006..0.008 rows=5 loops=92,561)

  • Index Cond: ((t236_opp_line_item_fact.c801_opportunity_sid = c801_opportunity_sid) AND (start_stamp <= '1549007999999'::bigint) AND (end_stamp > '1549007999999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
50. 0.000 967.986 ↑ 2.0 1 483,993

Append (cost=0.00..6.80 rows=2 width=16) (actual time=0.002..0.002 rows=1 loops=483,993)

51. 0.000 0.000 ↓ 0.0 0 483,993

Seq Scan on t716_clari_conversion_rate t716_clari_conversion_rate_1 (cost=0.00..0.00 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=483,993)

  • 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))
52. 967.986 967.986 ↑ 1.0 1 483,993

Index Scan using t716_clari_conversion_rate_usd_sidratedateunique on t716_clari_conversion_rate_usd t716_clari_conversion_rate_usd_1 (cost=0.29..6.80 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=483,993)

  • Index Cond: ((t236_opp_line_item_fact_1.c821_opp_currency_code = sid) AND (t236_opp_line_item_fact_1.c805_opp_close_date = rate_date))
  • Filter: (to_iso_code = 'USD'::text)
53. 0.000 0.000 ↑ 1.0 5 483,993

Materialize (cost=0.00..1.07 rows=5 width=8) (actual time=0.000..0.000 rows=5 loops=483,993)

54. 0.004 0.004 ↑ 1.0 5 1

Seq Scan on t234_opportunityforecastcategorypicklistdim t234_opportunityforecastcategorypicklistdim_1 (cost=0.00..1.05 rows=5 width=8) (actual time=0.004..0.004 rows=5 loops=1)

55.          

SubPlan (forNested Loop Left Join)

56. 0.925 0.925 ↑ 2.9 8,743 1

CTE Scan on ownercte (cost=0.00..501.84 rows=25,092 width=4) (actual time=0.001..0.925 rows=8,743 loops=1)

57. 0.050 0.050 ↑ 1.2 6 1

CTE Scan on picklist_0 cte0 (cost=0.00..0.14 rows=7 width=4) (actual time=0.021..0.050 rows=6 loops=1)

58. 0.025 0.025 ↑ 1.0 7 1

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

59.          

CTE topsids

60. 0.033 6,763.649 ↑ 1.0 400 1

Limit (cost=35,616.60..35,618.59 rows=400 width=12) (actual time=6,763.568..6,763.649 rows=400 loops=1)

61. 25.038 6,763.616 ↑ 49.5 400 1

Sort (cost=35,616.60..35,715.10 rows=19,800 width=12) (actual time=6,763.567..6,763.616 rows=400 loops=1)

  • Sort Key: (unnest(cte0_3.c3)) DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 43kB
62. 78.006 6,738.578 ↓ 923.9 182,928 1

Hash Join (cost=1.11..35,609.04 rows=198 width=12) (actual time=3,091.639..6,738.578 rows=182,928 loops=1)

  • Hash Cond: (cte0_3.c4 = t234_opportunityforecastcategorypicklistdim_2.sid)
63. 6,660.565 6,660.565 ↓ 5.7 44,892 1

CTE Scan on bucketac cte0_3 (cost=0.00..35,575.31 rows=7,906 width=68) (actual time=3,091.614..6,660.565 rows=44,892 loops=1)

  • Filter: (c5 = 'slipped'::text)
  • Rows Removed by Filter: 47669
64. 0.005 0.007 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=4) (actual time=0.007..0.007 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
65. 0.002 0.002 ↑ 1.0 5 1

Seq Scan on t234_opportunityforecastcategorypicklistdim t234_opportunityforecastcategorypicklistdim_2 (cost=0.00..1.05 rows=5 width=4) (actual time=0.002..0.002 rows=5 loops=1)

66. 6,763.709 6,763.709 ↑ 1.0 400 1

CTE Scan on topsids (cost=0.00..8.00 rows=400 width=4) (actual time=6,763.570..6,763.709 rows=400 loops=1)

67. 2.800 2.800 ↑ 1.0 1 400

Index Scan using t200_opportunitylineitemdim_sidendstampunique on t200_opportunitylineitemdim (cost=0.56..11.08 rows=1 width=23) (actual time=0.006..0.007 rows=1 loops=400)

  • Index Cond: ((sid = topsids.c1) AND (end_stamp > '1549007999999'::bigint))
  • Filter: ((NOT deleted) AND (start_stamp <= '1549007999999'::bigint))
  • Rows Removed by Filter: 0