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 resultResult  (cost=0.00..5.13 rows=1000 width=0) (actual time=38859.253..38859.254 rows=1 loops=1)  Total runtime: 38859.296 ms
Optimize postgres function
optimization;sql;postgresql