explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IJdV

Settings
# exclusive inclusive rows x rows loops node
1. 0.149 5,420.423 ↑ 1.2 400 1

Nested Loop (cost=2,432,437,593.98..2,432,442,036.71 rows=470 width=19) (actual time=5,417.773..5,420.423 rows=400 loops=1)

2.          

CTE picklist_0

3. 0.031 0.031 ↑ 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.008..0.031 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.019 0.019 ↑ 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.006..0.019 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.070 0.070 ↑ 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.070 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.127 1,733.741 ↑ 2.9 8,743 1

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

10. 41.314 1,726.614 ↓ 1.1 132,400 1

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

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

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

12. 77.032 670.352 ↑ 9.0 145,870 1

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

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

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

14. 175.134 569.123 ↑ 10.0 131,300 1

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

  • Hash Cond: (t728_userrolehierarchy.c2842_user_role_id = t67_userroledim.sid)
15. 248.862 381.509 ↓ 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=140.216..381.509 rows=1,339,311 loops=1)

  • Recheck Cond: ((c2843_ancestor_role_id = 436) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=40729
16. 132.647 132.647 ↓ 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=132.647..132.647 rows=1,342,130 loops=1)

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

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

  • Buckets: 131072 Batches: 1 Memory Usage: 1321kB
18. 6.771 11.156 ↑ 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.236..11.156 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.385 4.385 ↑ 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.385..4.385 rows=15,743 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
20. 15.521 15.521 ↓ 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.249..15.521 rows=14,726 loops=1)

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

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

22. 162.809 971.269 ↓ 1.2 286,335 1

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

  • Sort Key: t629_distinctcdo_opp_closedate_ownerid.c2616_ownerid
  • Sort Method: external merge Disk: 3904kB
23. 808.460 808.460 ↓ 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.177..808.460 rows=286,335 loops=1)

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

CTE bucketec

25. 123.254 5,363.955 ↑ 50.2 48,989 1

Nested Loop Left Join (cost=1,013,680.52..2,431,632,747.62 rows=2,459,601 width=164) (actual time=3,738.533..5,363.955 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. 312.559 4,217.089 ↑ 18.5 113,548 1

GroupAggregate (cost=1,011,403.78..1,079,823.49 rows=2,105,222 width=76) (actual time=3,735.624..4,217.089 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. 516.015 3,904.530 ↑ 2.7 785,203 1

Sort (cost=1,011,403.78..1,016,666.83 rows=2,105,222 width=32) (actual time=3,735.590..3,904.530 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. 144.278 3,388.515 ↑ 2.7 785,203 1

Merge Right Join (cost=653,062.67..689,553.14 rows=2,105,222 width=32) (actual time=2,885.525..3,388.515 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.817 50.740 ↑ 1.0 105,342 1

Merge Append (cost=0.31..7,793.41 rows=105,520 width=16) (actual time=0.021..50.740 rows=105,342 loops=1)

  • Sort Key: t716_clari_conversion_rate.sid, t716_clari_conversion_rate.rate_date
30. 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
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. 43.918 43.918 ↑ 1.0 105,342 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.016..43.918 rows=105,342 loops=1)

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

Materialize (cost=653,062.36..663,588.47 rows=2,105,222 width=32) (actual time=2,884.338..3,193.497 rows=785,203 loops=1)

34. 557.281 3,086.880 ↑ 2.7 785,203 1

Sort (cost=653,062.36..658,325.42 rows=2,105,222 width=32) (actual time=2,884.334..3,086.880 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. 159.802 2,529.599 ↑ 2.7 785,203 1

Nested Loop (cost=565.14..331,211.73 rows=2,105,222 width=32) (actual time=1,737.519..2,529.599 rows=785,203 loops=1)

36. 5.230 1,740.301 ↓ 43.7 8,743 1

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

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

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

38. 629.496 629.496 ↑ 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,649.52 rows=371 width=36) (actual time=0.010..0.072 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 ↑ 2.0 1 113,548

Subquery Scan on oli174_ft1 (cost=1,146.97..1,154.47 rows=2 width=16) (actual time=0.009..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 ↑ 300.0 1 113,548

Group (cost=1,146.97..1,150.72 rows=300 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 ↑ 75.0 4 113,548

Sort (cost=1,146.97..1,147.72 rows=300 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 ↑ 75.0 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,134.63 rows=300 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.022..0.071 rows=13 loops=1)

45. 0.663 0.663 ↑ 2.9 8,743 1

CTE Scan on ownercte (cost=0.00..501.84 rows=25,092 width=4) (actual time=0.001..0.663 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.020 0.020 ↑ 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.020 rows=7 loops=1)

48. 0.893 0.893 ↑ 2.9 8,743 1

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

49.          

CTE topsids

50. 0.028 5,417.804 ↑ 1.0 400 1

Limit (cost=55,405.54..55,407.53 rows=400 width=12) (actual time=5,417.748..5,417.804 rows=400 loops=1)

51. 4.204 5,417.776 ↑ 76.8 400 1

Sort (cost=55,405.54..55,558.27 rows=30,700 width=12) (actual time=5,417.747..5,417.776 rows=400 loops=1)

  • Sort Key: (unnest(cte0_3.c3)) DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 43kB
52. 10.161 5,413.572 ↓ 75.9 23,308 1

Hash Join (cost=1.11..55,392.86 rows=307 width=12) (actual time=3,739.042..5,413.572 rows=23,308 loops=1)

  • Hash Cond: (cte0_3.c6 = t234_opportunityforecastcategorypicklistdim.sid)
53. 5,403.399 5,403.399 ↑ 2.0 6,255 1

CTE Scan on bucketec cte0_3 (cost=0.00..55,341.02 rows=12,298 width=68) (actual time=3,739.008..5,403.399 rows=6,255 loops=1)

  • Filter: (c5 = 'newOpen'::text)
  • Rows Removed by Filter: 42734
54. 0.005 0.012 ↑ 1.0 5 1

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

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

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

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

CTE Scan on topsids (cost=0.00..8.00 rows=400 width=4) (actual time=5,417.750..5,417.874 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