MOCKSTACKS
EN
Questions And Answers

More Tutorials









postgreSQLregr_slope(Y, X) : slope of the least-squares-fit linear equation determined by
the (X, Y) pairs


To illustrate how to use regr_slope(Y,X), I applied it to a real world problem. In Java, if you don't clean up memory properly, the garbage can get stuck and fill up the memory. You dump statistics every hour about memory utilization of different classes and load it into a postgres database for analysis.

All memory leak candidates will have a trend of consuming more memory as more time passes. If you plot this trend, you would imagine a line going up and to the left:

 ^
 |
s | Legend:
i | * - data point
z | -- - trend
e |
( |
b | *
y | --
t | --
e | * -- *
s | --
) | *-- *
 | -- *
 | -- *
 --------------------------------------->
 time

Suppose you have a table containing heap dump histogram data (a mapping of classes to how much memory they consume):

CREATE TABLE heap_histogram (
 -- when the heap histogram was taken
 histwhen timestamp without time zone NOT NULL,
 -- the object type bytes are referring to
 -- ex: java.util.String
 class character varying NOT NULL,
 -- the size in bytes used by the above class
 bytes integer NOT NULL
);

To compute the slope for each class, we group by over the class. The HAVING clause > 0 ensures that we get only candidates with a positive slop (a line going up and to the left). We sort by the slope descending so that we get the classes with the largest rate of memory increase at the top.

-- epoch returns seconds
SELECT class, REGR_SLOPE(bytes,extract(epoch from histwhen)) as slope
 FROM public.heap_histogram
 GROUP BY class
 HAVING REGR_SLOPE(bytes,extract(epoch from histwhen)) > 0
 ORDER BY slope DESC ;

Output:

 class | slope
---------------------------+----------------------
 java.util.ArrayList | 71.7993806279174
 java.util.HashMap | 49.0324576155785
 java.lang.String | 31.7770770326123
 joe.schmoe.BusinessObject | 23.2036817108056
 java.lang.ThreadLocal | 20.9013528767851

From the output we see that java.util.ArrayList's memory consumption is increasing the fastest at 71.799 bytes per second and is potentially part of the memory leak.


Conclusion

In this page (written and validated by ) you learned about postgreSQL regr slope Y X . What's Next? If you are interested in completing postgreSQL tutorial, your next topic will be learning about: postgreSQL Backup and Restore.



Incorrect info or code snippet? We take very seriously the accuracy of the information provided on our website. We also make sure to test all snippets and examples provided for each section. If you find any incorrect information, please send us an email about the issue: mockstacks@gmail.com.


Share On:


Mockstacks was launched to help beginners learn programming languages; the site is optimized with no Ads as, Ads might slow down the performance. We also don't track any personal information; we also don't collect any kind of data unless the user provided us a corrected information. Almost all examples have been tested. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. By using Mockstacks.com, you agree to have read and accepted our terms of use, cookies and privacy policy.