0

I'm trying to improve the performance of PostgreSQL functions and function calls inside functions.

In our application, PostgreSQL (9.6) queries are totally based on functions. For example, in order to fetch a lot of "items" some code as the lines below might be used:

CREATE OR REPLACE FUNCTION public.item_read_one(
    "itemID" integer)
  RETURNS jsonb AS
$BODY$
DECLARE
    outputvariable jsonb;
BEGIN
  SELECT row_to_json (my_subquery) FROM (
   SELECT
   id,
   "simpleField1",
   "simpleField2",
   item_status(id,"crucialField") AS status
   FROM item_table
   WHERE id = $1 AND deleted IS NOT TRUE)
 AS my_subquery into outputvariable ;
RETURN outputvariable;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.item_read_one(integer)
  OWNER TO some_user;

You see, inside the inner SELECT there is - beside some simple fields - another function call. These inner functions are usually plv8 based and contain a lot of JavaScript logic, including database queries. An example might look like such:

CREATE OR REPLACE FUNCTION public.item_status(
    "itemID" integer,
    "crucialField" text)
  RETURNS jsonb AS
$BODY$

var returnStatus = {};
var myVariable;
if(crucialField == 'foo') {
  myVariable = plv8.execute('SELECT something FROM other_table WHERE "itemID" = $1',[itemID]);
}

else if(crucialField == 'sweetKitten') {
  myVariable = plv8.execute('SELECT "somethingElse" FROM kitten WHERE "itemID" = $1',[itemID]);
}

/*
 A lot more JavaScript logic here. But you get the idea, I hope.
*/

return returnStatus;

$BODY$
  LANGUAGE plv8 IMMUTABLE
  COST 100;
ALTER FUNCTION public.item_status(integer, text)
  OWNER TO some_user;

Aside from the question, whether this design makes sense, the problem is: How to improve the performance?

The data returned by functions like item_status is pretty stable, so it could make sense to put it into an index, but I don't know how.

PostgreSQL version used is 9.6.

cis
  • 1,259
  • 15
  • 48
  • 3
    `item_read_one()` can be changed into a plain SQL function (instead of PL/pgSQL) which gives the optimizer more possibilities to optimize it. –  Nov 08 '16 at 09:06
  • 2
    You should **not** mark functions that retrieve values from the database as `immutable`. Your example only contains `select` statements, so you could define it as `stable` –  Nov 08 '16 at 09:08
  • @a_horse_with_no_name Thanks for those hints! The first one gave me about 10% improvement in one case. However, the other hint didn't affect the performance in may case. – cis Nov 08 '16 at 14:31
  • Not marking the function immutable has nothing to do with performance - it has to do with correctness. –  Nov 08 '16 at 14:40

0 Answers0