Optimize postgres function

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 = ''' || _beginning_date || '''

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

-
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.

-
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.

-
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.

-
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.

-
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.

-
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.

StackExchange Code Review Q#28315, answer score: 6