explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xSFx

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 5,497.293 ↑ 1.2 400 1

Nested Loop (cost=2,415,893,534.71..2,415,897,977.43 rows=470 width=19) (actual time=5,494.856..5,497.293 rows=400 loops=1)

2.          

CTE picklist_0

3. 0.032 0.032 ↑ 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.009..0.032 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.066 0.066 ↑ 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.020..0.066 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.261 1,691.890 ↑ 2.9 8,743 1

Unique (cost=599,193.47..749,757.14 rows=25,092 width=4) (actual time=1,528.992..1,691.890 rows=8,743 loops=1)

10. 41.428 1,684.629 ↓ 1.1 132,401 1

Merge Join (cost=599,193.47..749,458.03 rows=119,644 width=4) (actual time=1,528.991..1,684.629 rows=132,401 loops=1)

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

Unique (cost=410,288.19..416,865.98 rows=1,315,557 width=4) (actual time=612.449..647.188 rows=137,320 loops=1)

12. 79.160 630.917 ↑ 9.0 145,873 1

Sort (cost=410,288.19..413,577.09 rows=1,315,557 width=4) (actual time=612.448..630.917 rows=145,873 loops=1)

  • Sort Key: t728_userrolehierarchy.sid
  • Sort Method: external merge Disk: 1992kB
13. 8.725 551.757 ↑ 9.0 146,034 1

Append (cost=60,579.54..258,593.46 rows=1,315,557 width=4) (actual time=153.250..551.757 rows=146,034 loops=1)

14. 174.729 529.713 ↑ 10.0 131,309 1

Hash Join (cost=60,579.54..234,052.52 rows=1,312,189 width=4) (actual time=153.250..529.713 rows=131,309 loops=1)

  • Hash Cond: (t728_userrolehierarchy.c2842_user_role_id = t67_userroledim.sid)
15. 210.043 342.232 ↓ 1.0 1,339,396 1

Bitmap Heap Scan on t728_userrolehierarchy (cost=39,574.50..203,967.33 rows=1,312,189 width=8) (actual time=140.041..342.232 rows=1,339,396 loops=1)

  • Recheck Cond: ((c2843_ancestor_role_id = 436) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=40785
16. 132.189 132.189 ↓ 1.0 1,343,232 1

Bitmap Index Scan on idx_t728_userrolehierarchy_c2843_ancestor_role_id_end_s36994704 (cost=0.00..39,246.45 rows=1,312,189 width=0) (actual time=132.189..132.189 rows=1,343,232 loops=1)

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

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

  • Buckets: 131072 Batches: 1 Memory Usage: 1321kB
18. 6.882 11.444 ↑ 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.486..11.444 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: 7092
  • Heap Blocks: exact=2281
19. 4.562 4.562 ↑ 4.9 15,751 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.562..4.562 rows=15,751 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
20. 13.319 13.319 ↓ 4.4 14,725 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.291..13.319 rows=14,725 loops=1)

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

Materialize (cost=188,905.28..190,101.73 rows=239,289 width=4) (actual time=915.276..996.013 rows=286,338 loops=1)

22. 172.076 969.899 ↓ 1.2 286,338 1

Sort (cost=188,905.28..189,503.50 rows=239,289 width=4) (actual time=915.271..969.899 rows=286,338 loops=1)

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

Seq Scan on t629_distinctcdo_opp_closedate_ownerid (cost=0.00..164,254.23 rows=239,289 width=4) (actual time=0.149..797.823 rows=286,338 loops=1)

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

CTE bucketec

25. 94.534 5,356.496 ↑ 49.3 48,989 1

Nested Loop Left Join (cost=1,017,429.17..2,415,089,271.10 rows=2,415,327 width=164) (actual time=3,749.502..5,356.496 rows=48,989 loops=1)

  • Filter: ((oli174_ft1.c1 IS NULL) OR (oli174_ft1.c3 < 20181101) OR (oli174_ft1.c3 > 20190131) OR (NOT (hashed SubPlan 9)))
  • Rows Removed by Filter: 64559
26. 321.463 4,238.092 ↑ 18.6 113,548 1

GroupAggregate (cost=1,015,162.46..1,083,721.76 rows=2,109,517 width=76) (actual time=3,746.401..4,238.092 rows=113,548 loops=1)

  • Group Key: t236_opp_line_item_fact.c801_opportunity_sid, t236_opp_line_item_fact.c819_opp_stagename, t236_opp_line_item_fact.c820_opp_forecastcategory
27. 518.892 3,916.629 ↑ 2.7 785,203 1

Sort (cost=1,015,162.46..1,020,436.25 rows=2,109,517 width=32) (actual time=3,746.371..3,916.629 rows=785,203 loops=1)

  • Sort Key: t236_opp_line_item_fact.c801_opportunity_sid, t236_opp_line_item_fact.c819_opp_stagename, t236_opp_line_item_fact.c820_opp_forecastcategory
  • Sort Method: external merge Disk: 31904kB
28. 142.818 3,397.737 ↑ 2.7 785,203 1

Merge Right Join (cost=656,047.31..692,626.72 rows=2,109,517 width=32) (actual time=2,894.016..3,397.737 rows=785,203 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))
29. 6.768 51.706 ↑ 1.0 105,346 1

Merge Append (cost=0.31..7,793.41 rows=105,520 width=16) (actual time=0.022..51.706 rows=105,346 loops=1)

  • Sort Key: t716_clari_conversion_rate.sid, t716_clari_conversion_rate.rate_date
30. 0.003 0.004 ↓ 0.0 0 1

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

  • Sort Key: t716_clari_conversion_rate.sid, t716_clari_conversion_rate.rate_date
  • Sort Method: quicksort Memory: 25kB
31. 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)
32. 44.934 44.934 ↑ 1.0 105,346 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..44.934 rows=105,346 loops=1)

  • Filter: (to_iso_code = 'USD'::text)
33. 106.508 3,203.213 ↑ 2.7 785,203 1

Materialize (cost=656,047.00..666,594.59 rows=2,109,517 width=32) (actual time=2,892.880..3,203.213 rows=785,203 loops=1)

34. 572.289 3,096.705 ↑ 2.7 785,203 1

Sort (cost=656,047.00..661,320.79 rows=2,109,517 width=32) (actual time=2,892.877..3,096.705 rows=785,203 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: 31912kB
35. 169.167 2,524.416 ↑ 2.7 785,203 1

Nested Loop (cost=565.14..333,511.26 rows=2,109,517 width=32) (actual time=1,696.643..2,524.416 rows=785,203 loops=1)

36. 6.256 1,699.524 ↓ 43.7 8,743 1

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

  • Group Key: ownercte_2.c1
37. 1,693.268 1,693.268 ↑ 2.9 8,743 1

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

38. 655.725 655.725 ↑ 4.1 90 8,743

Index Scan using ix_t236_opp_line_item_fact_owner_closedate_timestamp on t236_opp_line_item_fact (cost=0.56..1,661.01 rows=371 width=36) (actual time=0.011..0.075 rows=90 loops=8,743)

  • Index Cond: ((c804_opp_ownerid = ownercte_2.c1) AND (c805_opp_close_date >= 20181101) AND (c805_opp_close_date <= 20190131) AND (start_stamp <= '1549007999999'::bigint) AND (end_stamp > '1549007999999'::bigint))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 10
39. 0.000 1,021.932 ↑ 1.0 1 113,548

Subquery Scan on oli174_ft1 (cost=1,136.95..1,144.30 rows=1 width=16) (actual time=0.008..0.009 rows=1 loops=113,548)

  • Filter: (t236_opp_line_item_fact.c801_opportunity_sid = oli174_ft1.c1)
40. 113.548 1,021.932 ↑ 294.0 1 113,548

Group (cost=1,136.95..1,140.62 rows=294 width=16) (actual time=0.008..0.009 rows=1 loops=113,548)

  • Group Key: t236_opp_line_item_fact_1.c801_opportunity_sid, t236_opp_line_item_fact_1.c819_opp_stagename, t236_opp_line_item_fact_1.c805_opp_close_date, t236_opp_line_item_fact_1.c804_opp_ownerid
41. 227.096 908.384 ↑ 73.5 4 113,548

Sort (cost=1,136.95..1,137.68 rows=294 width=16) (actual time=0.008..0.008 rows=4 loops=113,548)

  • Sort Key: t236_opp_line_item_fact_1.c819_opp_stagename, t236_opp_line_item_fact_1.c805_opp_close_date, t236_opp_line_item_fact_1.c804_opp_ownerid
  • Sort Method: quicksort Memory: 25kB
42. 681.288 681.288 ↑ 73.5 4 113,548

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,124.89 rows=294 width=16) (actual time=0.004..0.006 rows=4 loops=113,548)

  • Index Cond: ((t236_opp_line_item_fact.c801_opportunity_sid = c801_opportunity_sid) AND (start_stamp <= '1541055600000'::bigint) AND (end_stamp > '1541055600000'::bigint))
  • Filter: (NOT deleted)
43.          

SubPlan (forNested Loop Left Join)

44. 0.071 0.071 ↑ 1.1 13 1

CTE Scan on picklist_4 cte0 (cost=0.00..0.28 rows=14 width=4) (actual time=0.021..0.071 rows=13 loops=1)

45. 0.689 0.689 ↑ 2.9 8,743 1

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

46. 0.033 0.033 ↑ 1.2 6 1

CTE Scan on picklist_0 cte0_1 (cost=0.00..0.14 rows=7 width=4) (actual time=0.010..0.033 rows=6 loops=1)

47. 0.022 0.022 ↑ 1.0 7 1

CTE Scan on picklist_1 cte0_2 (cost=0.00..0.14 rows=7 width=4) (actual time=0.006..0.022 rows=7 loops=1)

48. 1.123 1.123 ↑ 2.9 8,743 1

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

49.          

CTE topsids

50. 0.031 5,494.904 ↑ 1.0 400 1

Limit (cost=54,408.23..54,410.22 rows=400 width=12) (actual time=5,494.831..5,494.904 rows=400 loops=1)

51. 26.317 5,494.873 ↑ 75.5 400 1

Sort (cost=54,408.23..54,558.47 rows=30,200 width=12) (actual time=5,494.831..5,494.873 rows=400 loops=1)

  • Sort Key: (unnest(cte0_3.c3)) DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 43kB
52. 68.628 5,468.556 ↓ 665.0 200,843 1

Hash Join (cost=1.11..54,395.79 rows=302 width=12) (actual time=3,750.490..5,468.556 rows=200,843 loops=1)

  • Hash Cond: (cte0_3.c6 = t234_opportunityforecastcategorypicklistdim.sid)
53. 5,399.918 5,399.918 ↓ 3.4 41,521 1

CTE Scan on bucketec cte0_3 (cost=0.00..54,344.86 rows=12,077 width=68) (actual time=3,750.461..5,399.918 rows=41,521 loops=1)

  • Filter: (c4 = 'newBucket'::text)
  • Rows Removed by Filter: 7468
54. 0.004 0.010 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
55. 0.006 0.006 ↑ 1.0 5 1

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

56. 5,494.975 5,494.975 ↑ 1.0 400 1

CTE Scan on topsids (cost=0.00..8.00 rows=400 width=4) (actual time=5,494.834..5,494.975 rows=400 loops=1)

57. 2.400 2.400 ↑ 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.005..0.006 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