SQL function can get called twice per RPC call, depending on Prefer: count parameter

[sadruddin]

Environment

• PostgreSQL version: 12.7
• PostgREST version: 8.0.0
• Operating system: Linux Red Hat 7.3

Description of issue

We noticed that when calling one of our heavy-duty functions via rpc/, the request was taking twice as much time as expected. We traced down the issue to the constructed query not using CTE:

WITH pgrst_source AS (SELECT "api"."our_function"() AS pgrst_scalar)
SELECT null::bigint AS total_result_set,
       pg_catalog.count(_postgrest_t) AS page_total,  
       coalesce(string_agg(to_json(_postgrest_t.pgrst_scalar)::text, ','), 'null')::character varying AS body,
       nullif(current_setting('response.headers', true), '') AS response_headers,
       nullif(current_setting('response.status', true), '') AS response_status
FROM (SELECT "pgrst_source".*FROM "pgrst_source"    ) _postgrest_t

We managed to circumvent the issue by using Prefer: count=exact (or estimated), which would both result in the following more optimized query (using CTE):

WITH pgrst_source AS (SELECT "api"."our_function"() AS pgrst_scalar) ,
pgrst_source_count AS (SELECT 1 FROM "pgrst_source")
SELECT (SELECT pg_catalog.count(*) FROM pgrst_source_count) AS total_result_set,
       pg_catalog.count(_postgrest_t) AS page_total,
       coalesce(string_agg(to_json(_postgrest_t.pgrst_scalar)::text, ','), 'null')::character varying AS body,
       nullif(current_setting('response.headers', true), '') AS response_headers,
       nullif(current_setting('response.status', true), '') AS response_status
FROM (SELECT "pgrst_source".*FROM "pgrst_source"    ) _postgrest_t

the key being the addition of the pgrst_source_count AS (SELECT 1 FROM "pgrst_source") line.

It is not very intuitive that tweaking a count parameter would result in a better optimized query for an RPC call. In the end, the cause of the duplicated function call (at least in our case) seems to be the pg_catalog.count(_postgrest_t) AS page_total line (commenting it divides the execution time by 2), which doesn't seem to be adding any value in this context.

Is there any more straightforward way than using this count parameter to ensure that functions are only ran once via RPC? Or does it make sense to provide an option to produce a simplified query without pg_catalog.count for RPC?

Thanks for a great tool!

[wolfgangwalther]

We noticed that when calling one of our heavy-duty functions via rpc/, the request was taking twice as much time as expected.

The problem here is not the query, but the planner not knowing that the function you are calling is costly. It assumes it is cheap enough to "optimize" the query by inlining the CTE.

For such a function you can play with the COST parameter in CREATE FUNCTION:

COST execution_cost

A positive number giving the estimated execution cost for the function, in units of cpu_operator_cost. If the function returns a set, this is the cost per returned row. If the cost is not specified, 1 unit is assumed for C-language and internal functions, and 100 units for functions in all other languages. Larger values cause the planner to try to avoid evaluating the function more often than necessary.

Set this to something higher than 100 and see how that affects your query execution.

[steve-chavez]

I also remember a similar issue where a function was called many times: #1378 (comment).

(Related PostgREST/postgrest-docs#255)

Set this to something higher than 100 and see how that affects your query execution.

Additionally, if the function is VOLATILE, try changing it to STABLE or IMMUTABLE.

[wolfgangwalther]

Additionally, if the function is VOLATILE, try changing it to STABLE or IMMUTABLE.

I'm not sure whether that will do. I assume / hope that the function must be STABLE or IMMUTABLE already, because a VOLATILE function could have side-effects and should only be executed once here. If that holds true, it should be the other way around: Changing it to VOLATILE could do the trick.

If we really have a case of a VOLATILE function being executed twice, then we have a more serious problem than just a higher execution time / performance, right?

[steve-chavez]

Hmm, we have this test

postgrest/test/spec/Feature/Query/RpcSpec.hs

Lines 535 to 556 in c803c4d

	it "executes the proc exactly once per request" $ do
	-- callcounter is persistent even with rollback, because it uses a sequence
	-- reset counter first to make test repeatable
	request methodPost "/rpc/reset_sequence"
	[("Prefer", "tx=commit")]
	[json|{"name": "callcounter_count", "value": 1}|]
	`shouldRespondWith`
	""
	{ matchStatus = 204
	, matchHeaders = [ matchHeaderAbsent hContentType ]
	}
	-- now the test
	post "/rpc/callcounter"
	[json|{}|]
	`shouldRespondWith`
	[json|1|]
	post "/rpc/callcounter"
	[json|{}|]
	`shouldRespondWith`
	[json|2|]

If we really have a case of a VOLATILE function being executed twice, then we have a more serious problem than just a higher execution time / performance, right?

Yeah, in that case all is missing is a reproducible test case, then we can do a fix.

[wolfgangwalther]

Hmm, we have this test

This test proves my point, though?

The callcounter proc is VOLATILE - and the test proves it's only run once. This is required, because otherwise side effects might be applied twice.

However for a STABLE or IMMUTABLE function there are no side effects to be expected - and therefore it's safe to run it twice. Now, it's just a matter of performance: Is running it twice faster than materializing the result? And that's something that the planner decides based on the data / stats it receives. For functions this can be adjusted via the COST setting as suggested.

You can force to never inline, but always materialize (and therefore only run once), the function by making it VOLATILE. But this is hardly a good idea, just like with all other forced "optimizations". It might depend on context, i.e. the query, whether materializing or not is faster - and that's something you don't know in advance when defining the function. COST works in all cases, because it affects the planning - but still let's the planner have a choice, when the overall queries dictates a different solution would be better.