![[ CuTE kitten ]](/wp-content/uploads/2012/12/kitten600.jpg)
In PostgreSQL 8.4 we got CTE – Common Table Expressions. Since then we have this great tool available, but apparently for some people it's still black magic. CuTE, but still magic. I'll try to make it a bit less magical, and more understandable.
Tag: with
Profiling stored procedures/functions
One database that I am monitoring uses a lot of stored procedures. Some of them are fast, some of them are not so fast. I thought – is there a sensible way to diagnose which part of stored procedure take the most time?
I mean – I could just put the logic into application, and then every query would have it's own timing in Pg logs, but this is not practical. And I also believe that using stored procedures/functions is way better than using plain SQL due to a number of reasons.
So, I'm back to question – how to check which part of function takes most of the time?
Calculating backlog of events to handle
Yesterday on my favorite IRC channel fooqux asked interesting question. I took some more questions, and here is problem description:
We have a system which, every 5 minutes, takes a number of tasks to be done. Tasks are uniform. Within 5 minutes we can handle at most 100 tasks. Given the history of number of tasks added every 5 minutes, calculate backlog at any given moment.
Did you understand the problem? Well – I didn't. So, let's see the data, and expected output.
Waiting for 8.4 – Common Table Expressions (WITH queries)
On 4th of September Tom Lane committed another great patch. This one is very large, and even after applying – it's has some rough edges. There will be need for additional patches to make the functionality fully robust, but the fact that it got committed means that it will be available in final 8.4.
What does it do?
Continue reading Waiting for 8.4 – Common Table Expressions (WITH queries)