Optimize postgres function

Question

This function code works very slowly. How can I speed it up?

CREATE OR REPLACE FUNCTION bill."ReportIngredients"(
 _from date,
 _to date,
 _beginning_date date,
 _has_inventory boolean,
 _inventory_id uuid,
 _restaurant_id uuid,
 _stock_id uuid,
 _ingredientIds uuid [],
 _sort_by character varying,
 _limit integer,
 _offset integer
 )
 RETURNS TABLE(
 json json
 ) AS
$BODY$
declare
 ingredientFilter character varying = '';
 ingredient_id uuid;
 ss_date date;
begin
 if ( _ingredientIds is not null ) then
 ingredientFilter = 'and i.id IN (';
 FOREACH ingredient_id in array _ingredientIds loop
 ingredientFilter := ingredientFilter || '''' || ingredient_id || ''',';
 end loop;
 Select trim(trailing ',' from ingredientFilter) into ingredientFilter;
 ingredientFilter := ingredientFilter || ') ';
 end if;
 if ( _has_inventory ) then
 return query execute
 'select array_to_json(array_agg(row_to_json(t)))
 From (
 Select i.id, i.title,
 (
 (
 SELECT coalesce(sum(ii.delta_count), 0)
 FROM inventory_ingredients ii
 Inner Join inventories inven On inven.id = ii.inventory_id
 WHERE ii.ingredient_id = i.id
 And inven.is_active = true
 And inven.stock_id = ''' || _stock_id || '''
 And inven.id = ''' || _inventory_id || '''
 ) + (
 SELECT coalesce(sum(ii.count), 0)
 FROM invoice_ingredients ii
 Inner Join invoices invo On invo.id = ii.invoice_id
 WHERE ii.is_active = true
 And ii.ingredient_id = i.id
 And invo.is_active = true
 And invo.restaurant_id = ''' || _restaurant_id || '''
 And invo.receiver_id = ''' || _stock_id || '''
 And invo.date >= ''' || _beginning_date || '''
 And invo.date < ''' || _from || '''
 ) + (
 SELECT coalesce(sum(ri.count), 0)
 FROM relocation_ingredients ri
 Inner Join relocations r On r.id = ri.relocation_id
 WHERE ri.ingredient_id = i.id
 And r.is_active = true
 And r.restaurant_id = ''' || _restaurant_id || '''
 And r.receiver_stock_id = ''' || _stock_id || '''
 And r.date >= ''' || _beginning_date || '''
 And r.date < ''' || _from || '''
 ) - (
 SELECT coalesce(sum(wi.count), 0)
 FROM write_off_ingredients wi
 Inner Join write_offs w On w.id = wi.write_off_id
 WHERE wi.ingredient_id = i.id
 And w.is_active = true
 And w.stock_id = ''' || _stock_id || '''
 And w.date >= ''' || _beginning_date || '''
 And w.date < ''' || _from || '''
 ) - (
 SELECT coalesce(sum(ri.count), 0)
 FROM relocation_ingredients ri
 Inner Join relocations r On r.id = ri.relocation_id
 WHERE ri.ingredient_id = i.id
 And r.is_active = true
 And r.restaurant_id = ''' || _restaurant_id || '''
 And r.sender_stock_id = ''' || _stock_id || '''
 And r.date >= ''' || _beginning_date || '''
 And r.date < ''' || _from || '''
 ) - (
 Select ((
 SELECT coalesce(sum(bc.count), 0)
 FROM bill_calculations bc
 Inner Join bill.bill_course_solds bcs on bcs.id = bc.object_id
 Inner Join bill.bills b on b.id = bcs.bill_id
 WHERE bc.ingredient_id = i.id
 And bc.stock_id = ''' || _stock_id || '''
 And bc.date >= ''' || _beginning_date || '''
 And bc.date < ''' || _from || '''
 And bc.calculate_type = ''subtract''
 And b.bill_type <> 5
 ) - (
 SELECT coalesce(sum(bc.count), 0)
 FROM bill_calculations bc
 Inner Join bill.bill_course_resigns bcr on bcr.id = bc.object_id
 Inner Join bill.bill_course_solds bcs on bcs.id = bcr.bill_course_sold_id
 Inner Join bill.bills b on b.id = bcs.bill_id
 WHERE bc.ingredient_id = i.id
 And bc.stock_id = ''' || _stock_id || '''
 And bc.date >= ''' || _beginning_date || '''
 And bc.date < ''' || _from || '''
 And bc.calculate_type = ''add''
 And b.bill_type <> 5
 )) AS sum
 )
 ) AS start_count,
 (
 SELECT coalesce(sum(ii.count), 0)
 FROM invoice_ingredients ii
 Inner Join invoices invo On invo.id = ii.invoice_id
 WHERE ii.is_active = true
 And ii.ingredient_id = i.id
 And invo.is_active = true
 And invo.restaurant_id = ''' || _restaurant_id || '''
 And invo.receiver_id = ''' || _stock_id || '''
 And invo.date >= ''' || _from || '''
 And invo.date <= ''' || _to || '''
 ) AS invoice_count,
 (
 SELECT coalesce(sum(ri.count), 0)
 FROM relocation_ingredients ri
 Inner Join relocations r On r.id = ri.relocation_id
 WHERE ri.ingredient_id = i.id
 And r.is_active = true
 And r.restaurant_id = ''' || _restaurant_id || '''
 And r.receiver_stock_id = ''' || _stock_id || '''
 And r.date >= ''' || _from || '''
 And r.date <= ''' || _to || '''
 ) AS relocation_in_count,
 (
 SELECT coalesce(sum(wi.count), 0)
 FROM write_off_ingredients wi
 Inner Join write_offs w On w.id = wi.write_off_id
 WHERE wi.ingredient_id = i.id
 And w.is_active = true
 And w.stock_id = ''' || _stock_id || '''
 And w.date >= ''' || _from || '''
 And w.date <= ''' || _to || '''
 ) AS write_off_count,
 (
 SELECT coalesce(sum(ri.count), 0)
 FROM relocation_ingredients ri
 Inner Join relocations r On r.id = ri.relocation_id
 WHERE ri.ingredient_id = i.id
 And r.is_active = true
 And r.restaurant_id = ''' || _restaurant_id || '''
 And r.sender_stock_id = ''' || _stock_id || '''
 And r.date >= ''' || _from || '''
 And r.date <= ''' || _to || '''
 ) AS relocation_out_count,
 (
 Select ((
 SELECT coalesce(sum(bc.count), 0)
 FROM bill_calculations bc
 Inner Join bill.bill_course_solds bcs on bcs.id = bc.object_id
 Inner Join bill.bills b on b.id = bcs.bill_id
 WHERE bc.ingredient_id = i.id
 And bc.stock_id = ''' || _stock_id || '''
 And bc.date >= ''' || _from || '''
 And bc.date <= ''' || _to || '''
 And bc.calculate_type = ''subtract''
 And b.bill_type <> 5
 ) - (
 SELECT coalesce(sum(bc.count), 0)
 FROM bill_calculations bc
 Inner Join bill.bill_course_resigns bcr on bcr.id = bc.object_id
 Inner Join bill.bill_course_solds bcs on bcs.id = bcr.bill_course_sold_id
 Inner Join bill.bills b on b.id = bcs.bill_id
 WHERE bc.ingredient_id = i.id
 And bc.stock_id = ''' || _stock_id || '''
 And bc.date >= ''' || _from || '''
 And bc.date <= ''' || _to || '''
 And bc.calculate_type = ''add''
 And b.bill_type <> 5
 )) AS sum
 ) AS solds_count,
 (
 SELECT coalesce(sum(bc.count), 0)
 FROM bill_calculations bc
 Inner Join bill.bill_course_resigns bcr on bcr.id = bc.object_id
 Inner Join bill.bill_course_solds bcs on bcs.id = bcr.bill_course_sold_id
 Inner Join bill.bills b on b.id = bcs.bill_id
 WHERE bc.ingredient_id = i.id
 And bc.stock_id = ''' || _stock_id || '''
 And bc.date >= ''' || _from || '''
 And bc.date <= ''' || _to || '''
 And bc.calculate_type = ''add''
 And b.bill_type <> 5
 ) AS resign_count
 From ingredients i
 Where i.is_active = true
 And i.restaurant_id = ''' || _restaurant_id || '''
 ' || ingredientFilter || '
 Group by i.id
 order by ' || _sort_by || '
 limit ' || _limit || '
 offset ' || _offset || '
 ) t';
 else
 return query execute
 'select array_to_json(array_agg(row_to_json(t)))
 From (
 Select i.id, i.title,
 (
 (
 SELECT coalesce(sum(ii.count), 0)
 FROM invoice_ingredients ii
 Inner Join invoices invo On invo.id = ii.invoice_id
 WHERE ii.is_active = true
 And ii.ingredient_id = i.id
 And invo.is_active = true
 And invo.restaurant_id = ''' || _restaurant_id || '''
 And invo.receiver_id = ''' || _stock_id || '''
 And invo.date >= ''' || _beginning_date || '''
 And invo.date < ''' || _from || '''
 ) + (
 SELECT coalesce(sum(ri.count), 0)
 FROM relocation_ingredients ri
 Inner Join relocations r On r.id = ri.relocation_id
 WHERE ri.ingredient_id = i.id
 And r.is_active = true
 And r.restaurant_id = ''' || _restaurant_id || '''
 And r.receiver_stock_id = ''' || _stock_id || '''
 And r.date >= ''' || _beginning_date || '''
 And r.date < ''' || _from || '''
 ) - (
 SELECT coalesce(sum(wi.count), 0)
 FROM write_off_ingredients wi
 Inner Join write_offs w On w.id = wi.write_off_id
 WHERE wi.ingredient_id = i.id
 And w.is_active = true
 And w.stock_id = ''' || _stock_id || '''
 And w.date >= ''' || _beginning_date || '''
 And w.date < ''' || _from || '''
 ) - (
 SELECT coalesce(sum(ri.count), 0)
 FROM relocation_ingredients ri
 Inner Join relocations r On r.id = ri.relocation_id
 WHERE ri.ingredient_id = i.id
 And r.is_active = true
 And r.restaurant_id = ''' || _restaurant_id || '''
 And r.sender_stock_id = ''' || _stock_id || '''
 And r.date >= ''' || _beginning_date || '''
 And r.date < ''' || _from || '''
 ) - (
 Select ((
 SELECT coalesce(sum(bc.count), 0)
 FROM bill_calculations bc
 Inner Join bill.bill_course_solds bcs on bcs.id = bc.object_id
 Inner Join bill.bills b on b.id = bcs.bill_id
 WHERE bc.ingredient_id = i.id
 And bc.stock_id = ''' || _stock_id || '''
 And bc.date >= ''' || _beginning_date || '''
 And bc.date < ''' || _from || '''
 And bc.calculate_type = ''subtract''
 And b.bill_type <> 5
 ) - (
 SELECT coalesce(sum(bc.count), 0)
 FROM bill_calculations bc
 Inner Join bill.bill_course_resigns bcr on bcr.id = bc.object_id
 Inner Join bill.bill_course_solds bcs on bcs.id = bcr.bill_course_sold_id
 Inner Join bill.bills b on b.id = bcs.bill_id
 WHERE bc.ingredient_id = i.id
 And bc.stock_id = ''' || _stock_id || '''
 And bc.date >= ''' || _beginning_date || '''
 And bc.date < ''' || _from || '''
 And bc.calculate_type = ''add''
 And b.bill_type <> 5
 )) AS sum
 )
 ) AS start_count,
 (
 SELECT coalesce(sum(ii.count), 0)
 FROM invoice_ingredients ii
 Inner Join invoices invo On invo.id = ii.invoice_id
 WHERE ii.is_active = true
 And ii.ingredient_id = i.id
 And invo.is_active = true
 And invo.restaurant_id = ''' || _restaurant_id || '''
 And invo.receiver_id = ''' || _stock_id || '''
 And invo.date >= ''' || _from || '''
 And invo.date <= ''' || _to || '''
 ) AS invoice_count,
 (
 SELECT coalesce(sum(ri.count), 0)
 FROM relocation_ingredients ri
 Inner Join relocations r On r.id = ri.relocation_id
 WHERE ri.ingredient_id = i.id
 And r.is_active = true
 And r.restaurant_id = ''' || _restaurant_id || '''
 And r.receiver_stock_id = ''' || _stock_id || '''
 And r.date >= ''' || _from || '''
 And r.date <= ''' || _to || '''
 ) AS relocation_in_count,
 (
 SELECT coalesce(sum(wi.count), 0)
 FROM write_off_ingredients wi
 Inner Join write_offs w On w.id = wi.write_off_id
 WHERE wi.ingredient_id = i.id
 And w.is_active = true
 And w.stock_id = ''' || _stock_id || '''
 And w.date >= ''' || _from || '''
 And w.date <= ''' || _to || '''
 ) AS write_off_count,
 (
 SELECT coalesce(sum(ri.count), 0)
 FROM relocation_ingredients ri
 Inner Join relocations r On r.id = ri.relocation_id
 WHERE ri.ingredient_id = i.id
 And r.is_active = true
 And r.restaurant_id = ''' || _restaurant_id || '''
 And r.sender_stock_id = ''' || _stock_id || '''
 And r.date >= ''' || _from || '''
 And r.date <= ''' || _to || '''
 ) AS relocation_out_count,
 (
 Select ((
 SELECT coalesce(sum(bc.count), 0)
 FROM bill_calculations bc
 Inner Join bill.bill_course_solds bcs on bcs.id = bc.object_id
 Inner Join bill.bills b on b.id = bcs.bill_id
 WHERE bc.ingredient_id = i.id
 And bc.stock_id = ''' || _stock_id || '''
 And bc.date >= ''' || _from || '''
 And bc.date <= ''' || _to || '''
 And bc.calculate_type = ''subtract''
 And b.bill_type <> 5
 ) - (
 SELECT coalesce(sum(bc.count), 0)
 FROM bill_calculations bc
 Inner Join bill.bill_course_resigns bcr on bcr.id = bc.object_id
 Inner Join bill.bill_course_solds bcs on bcs.id = bcr.bill_course_sold_id
 Inner Join bill.bills b on b.id = bcs.bill_id
 WHERE bc.ingredient_id = i.id
 And bc.stock_id = ''' || _stock_id || '''
 And bc.date >= ''' || _from || '''
 And bc.date <= ''' || _to || '''
 And bc.calculate_type = ''add''
 And b.bill_type <> 5
 )) AS sum
 ) AS solds_count,
 (
 SELECT coalesce(sum(bc.count), 0)
 FROM bill_calculations bc
 Inner Join bill.bill_course_resigns bcr on bcr.id = bc.object_id
 Inner Join bill.bill_course_solds bcs on bcs.id = bcr.bill_course_sold_id
 Inner Join bill.bills b on b.id = bcs.bill_id
 WHERE bc.ingredient_id = i.id
 And bc.stock_id = ''' || _stock_id || '''
 And bc.date >= ''' || _from || '''
 And bc.date <= ''' || _to || '''
 And bc.calculate_type = ''add''
 And b.bill_type <> 5
 ) AS resign_count
 From ingredients i
 Where i.is_active = true
 And i.restaurant_id = ''' || _restaurant_id || '''
 ' || ingredientFilter || '
 Group by i.id
 order by ' || _sort_by || '
 limit ' || _limit || '
 offset ' || _offset || '
 ) t';
 end if;
end;
$BODY$
 LANGUAGE plpgsql STABLE
 COST 50
 ROWS 1000;
ALTER FUNCTION bill."ReportIngredients"(date, date, date, boolean, uuid, uuid, uuid, uuid[], character varying, integer, integer)
 OWNER TO developer;

EXPLAIN ANALYZE result

"Result (cost=0.00..5.13 rows=1000 width=0) (actual time=38859.253..38859.254 rows=1 loops=1)" "Total runtime: 38859.296 ms"

• Malachi: you have a lot of Nested Select Statements with multiple where statements, is there any way to reduce the Nested select statements? like Temp Tables, Table Variables, joins, etc?
• Phrancis: Ditto @Malachi; Temp tables or views may improve performance significantly, instead of nested SELECT statements. 39K ms seems excessive.

Answer 1

I will give you kudos on being very explicit in your programming. Here are my thoughts:

1. Not performance related but still a factor, I would suggest that you remain consistent with your casing of key words. Sure SQL is not case-sensitive, but it makes the code easier to read especially in my opinion if SQL key words are all caps.

2. Again likely not performance related, but I notice you reuse table aliases in multiple subqueries for different tables (e.g., ii forinventory_ingredients and invoice_ingredients both) this is not good practice as if you omitted a single parentheses you could get an unpredictable result set or an error from SQL that your table reference is ambiguous. These can be tricky to debug especially in a large script like this.

3. Nested SELECT subqueries, avoid if possible especially for long-running scripts, as it can lock those pages/tables for other users throughout execution. I suggested breaking those into common table expressions or temporary tables (if infrequent execution) or views (if frequent). Not completely familiar with the PostgreSQL syntax for those but same principles apply to all SQL. Note, this would also make your function query much simpler to read while you do your arithmetic calculations.

4. You can try to change the COST 50 to a different value. See section labeled execution_cost in this section of the manual for more details on how it works.

5. You use this type of operator throughout: inven.stock_id = ''' || _stock_id || '''. This is ugly, why do you concatenate empty values instead of just inven.stock_id LIKE '%_stock_id%'? SQL engine might interpret this weirdly and may optimize better if you use the LIKE operator.

6. Using comments within your script would help the next programmer understand your code better as to what it does in what order, etc.

I can't think of anything else but others are welcome to add/edit to this if warranted.