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?

Continue reading Profiling stored procedures/functions

Waiting for 9.0 – extended frames for window functions

On 12th of February Tom Lane committed patch by Hitoshi Harada:

Log Message:
Extend the set of frame options supported for window functions.
This patch allows the frame to start from CURRENT ROW (in either RANGE or
ROWS mode), and it also adds support for ROWS n PRECEDING and ROWS n FOLLOWING
start and end points.  (RANGE value PRECEDING/FOLLOWING isn't there yet ---
the grammar works, but that's all.)
Hitoshi Harada, reviewed by Pavel Stehule

Continue reading Waiting for 9.0 – extended frames for window functions

Waiting for 8.4 – window functions

A long overdue post about new functionality. At this moment it is no longer such new, as it was committed on 28th of December (yes, I know, I should have written it earlier, Sorry).

On this day Tom Lane committed patch by Hitoshi Harada which adds support for so called window functions:

Support window functions a la SQL:2008.
Hitoshi Harada, with some kibitzing from Heikki and Tom.

Continue reading Waiting for 8.4 – window functions