Improve performance when querying Functions #3349
Description
Given this graphql query:
{
my_function(
args: {
p_customer_id: 3050
}
limit: 50
where: { code: { _eq: "10000056724" } }
) {
code
}
}Hasura console Analyse feature reveals the following generated SQL query:
WITH "public_my_function__result" AS (
SELECT
*
FROM
"public"."my_function"(
('3050') :: integer
)
)
SELECT
coalesce(json_agg("root"), '[]') AS "root"
FROM
(
SELECT
row_to_json(
(
SELECT
"_1_e"
FROM
(
SELECT
"_0_root.base"."code" AS "code"
) AS "_1_e"
)
) AS "root"
FROM
(
SELECT
*
FROM
"public_my_function__result"
WHERE
(
(
"public_my_function__result"."code"
) = (('10000056724') :: text)
)
) AS "_0_root.base"
LIMIT
50
) AS "_2_root"When my_function() returns many records, it comes to be very heavy for Postgresql because the select inside WITH clause will return every possible records and only after they will be filtered by the where condition, causing a time consuming CTE Scan.
It would be more efficient if the where conditions - and any other possible join/limit/order clauses/... - would be applyed inside the WITH clause, something like that:
WITH "public_my_function__result" AS (
SELECT
*
FROM
"public"."my_function"(
('3050') :: integer
)
WHERE code = '10000056724'
LIMIT 50
)
...
...