Oy vey. Adventures in SQL

This is for the PostgreSQL database.

I learned a valuable lesson early last year (although I haven't changed my old code yet). Do not put a SQL function in the WHERE clause of a query if that function does not need to be evaluated for each possible row in a table. This error was simply a lack of thinking about what the query must be doing before I started writing functions. As an example, take these simplistic functions:

CREATE FUNCTION is_valid (client_id) RETURNS Boolean AS $$
SELECT EXISTS (SELECT 1 FROM clients WHERE client_id = $1);
$$ LANGUAGE sql;

CREATE FUNCTION get_client_address (client_id) RETURNS TABLE (client_name text, client_address text) AS $$
SELECT c.name, c.address FROM clients c WHERE id = $1 AND is_valid($1);
$$ LANGUAGE sql;

Ignoring the fact that having is_valid in get_client_address is of no value, notice that is_valid only needs to be evaluated ONCE to determine if the if the parameter given to get_client_address exists in the client table. But, having it in the WHERE clause means that it will be evaluated for each and every row of the client table.

The query of get_client_address should look like this:
SELECT c.name, c.address FROM clients c, is_valid($1) v WHERE id = $1 AND v;

When is_valid is put in the FROM clause, it is evaluated once, before the query starts searching for rows. For large client tables, the time saved not evaluating is_valid could be HUGE depending the complexity of the functions involved.

Benchmarking my functions (execution duration is being logged), has given me a list of the old functions that need to be rewritten!

But this is a lesson I learned already. Now there is a new lesson for which I still only have the question and not the answer.

I thought postgresql would "in-line" function bodies if the function results where immutable. That is, if a function always returned the same results for the same parameters, postgresql would not go through the overhead of calling the immutable function when it was used inside another function. I thought that when function 2 was created, the immutable functions innards would be placed in the function definition (via appropriate references of the functions catalog).

Guess not. I have a boat load of "helper functions" that I call from more complex functions. Using the performance tools available to me, I find that my immutable functions are being called each time. And some of my helper functions call other functions.

In one case, execution time for a function that used a helper function I named is_current in the WHERE clause took 1373.455ms to run. Rewriting the function to not use the helper function, the time dropped to 9.795ms. YIKES.

In my benching marking, the is_current was called with a composite type called time_ranges and a timestamps.

CREATE OR REPLACE
FUNCTION is_current(timestamps, timestamps, timestamps) RETURNS Boolean AS $$
SELECT $1 <= $3 AND $3 < $2
$$ LANGUAGE sql;

CREATE OR REPLACE
FUNCTION is_current(time_ranges, event_times) RETURNS boolean AS $$
SELECT is_current(($1).starting, ($1).ending, $2 )
$$ LANGUAGE sql;

is_current is overloaded, so my original functions calls the second is_current, which calls the "real" is_current. I handle timestamps in different ways throughout the project for various and rational reasons, so these helper functions and a few others for time handling seemed like a great idea.

Problem is that postgresql doesn't seem to work like I thought it did. So my helper functions are drastically slowing down my database server.

I still think there is a way to do this, though. So I'm off to studying to documentation again.

EDIT TO ADD: I found the issue. If a function is declared to be strict when it is created, it is forever prevented from being in-lined. other things prevent in-lining, too, but this is the one that got me. A strict function is not called at all if any of its parameters are null. Instead, the dispatching mechanism just returns null. But that means we can't bypass the dispatching mechanism. So, the function must be called (or not) depending on the parameters which change in the SELECT and WHERE clauses.