Re: slow performance with cursor - Mailing list pgsql-performance

Below is the stored procedure, is there any scope for improvement?

CREATE OR REPLACE PROCEDURE "new_api_pkg$get_menu_details_p"(
i_user_id bigint,
i_menu_item_id bigint,
INOUT o_menu refcursor,
INOUT o_item refcursor,
INOUT o_choice refcursor)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    IF i_user_id IS NOT NULL THEN
        OPEN o_menu FOR
            SELECT
                mi.menu_item_id, mi.menu_item_name, mi.menu_item_title, mi.restaurant_id, case when mi.image !=null then 'Y'  when mi.image is null then 'N' end as has_image,
0.0 AS rating, 0 AS votes, 0 AS own_rating
            FROM menu_item AS mi
            WHERE mi.menu_item_id = i_menu_item_id AND mi.active = 'Y';
    ELSE
        OPEN o_menu FOR
            SELECT mi.menu_item_id, mi.menu_item_name, mi.menu_item_title, mi.restaurant_id, case when mi.image !=null then 'Y'  when mi.image is null then 'N' end as has_image,
0.0 AS rating, 0 AS votes, 0 AS own_rating
            FROM menu_item AS mi
            WHERE mi.menu_item_id = i_menu_item_id AND mi.active = 'Y';
    END IF;
    OPEN o_item FOR
        SELECT
            c.menu_item_variant_id, c.menu_item_variant_type_id, c.package_type_code, c.packages_only, c.price,
            CASE
                WHEN c.package_type_code = 'P' THEN
                (SELECT  SUM(miv1.calories) FROM package_component AS pkg_cpm1
                         INNER JOIN menu_item_variant AS miv1   ON pkg_cpm1.component_id = miv1.menu_item_variant_id WHERE pkg_cpm1.package_id = c.menu_item_variant_id)
                ELSE c.calories
                END AS calories, c.size_id, c.parent_menu_item_variant_id, d.menu_item_variant_type_desc, d.menu_item_variant_type_desc_ar, e.size_desc, e.size_desc_ar,15 AS preparation_time,
           
            (SELECT STRING_AGG(CONCAT_WS('', mi.menu_item_name, ' ', s.size_desc), ' + '::TEXT ORDER BY pc.component_id)
             FROM package_component AS pc, menu_item_variant AS miv, menu_item AS mi, menu_item_variant_type AS mivt, item_size AS s
             WHERE pc.component_id = miv.menu_item_variant_id AND miv.menu_item_id = mi.menu_item_id AND miv.size_id = s.size_id
             AND pc.package_id = c.menu_item_variant_id AND mivt.is_hidden = 'false' AND mivt.menu_item_variant_type_id = miv.menu_item_variant_type_id
             GROUP BY pc.package_id) AS package_name
        FROM menu_item AS a, menu_item_variant AS c, menu_item_variant_type AS d, item_size AS e
        WHERE a.menu_item_id = c.menu_item_id AND c.menu_item_variant_type_id = d.menu_item_variant_type_id AND d.is_hidden = 'false'
        AND c.size_id = e.size_id AND a.menu_item_id = i_menu_item_id AND a.active = 'Y' AND c.deleted = 'N'
        ORDER BY c.menu_item_variant_id;
    OPEN o_choice FOR
        SELECT
            c.choice_id, c.choice_name, c.choice_name_ar, c.calories
        FROM choice AS c, menu_item_choice AS mc, menu_item AS mi
        WHERE c.choice_id = mc.choice_id AND mc.menu_item_id = mi.menu_item_id AND mc.menu_item_id = i_menu_item_id AND mi.active = 'Y';
END;
$BODY$;