One-row postgres query as CTE/subquery much slower when passing subquery field into function / maybe related to inlining?

I’m using postgres 13.3 with inner and outer queries that both only produce a single row (just some stats about row counts).

I can’t figure out why Query2 below is so much slower than Query1 (they should basically be almost exactly the same, maybe a few ms difference at most)…

Query1: This query takes 49 seconds:

WITH t1 AS (         SELECT             (SELECT COUNT(*) FROM racing.all_computable_xformula_bday_combos) AS all_count,             (SELECT COUNT(*) FROM racing.xday_todo_all) AS todo_count,             (SELECT COUNT(*) FROM racing.xday) AS xday_row_count         OFFSET 0 -- this is to prevent inlining )  SELECT             t1.all_count,             t1.all_count-t1.todo_count AS done_count,             t1.todo_count,             t1.xday_row_count FROM t1 

Query2: This query takes 4 minutes and 30 seconds (only one line difference):

WITH t1 AS (         SELECT             (SELECT COUNT(*) FROM racing.all_computable_xformula_bday_combos) AS all_count,             (SELECT COUNT(*) FROM racing.xday_todo_all) AS todo_count,             (SELECT COUNT(*) FROM racing.xday) AS xday_row_count         OFFSET 0 -- this is to prevent inlining )  SELECT             t1.all_count,             t1.all_count-t1.todo_count AS done_count,             t1.todo_count,             t1.xday_row_count,             -- the line below is the only difference to Query1:             util.divide_ints_and_get_percentage_string(todo_count, all_count) AS todo_percentage FROM t1 
  • Before this point, and with some extra columns in the outer the query (which should have made almost zero difference), the whole query was insanely slow, like 25 minutes, which I think was due to inlining maybe? Hence the OFFSET 0 being added into both queries (which does help a lot).
  • I’ve also been swapping between using the above CTEs vs subqueries, but with the OFFSET 0 included it doesn’t seem to make any difference.

Here’s the definitions of the functions being called in Query2:

CREATE OR REPLACE FUNCTION util.ratio_to_percentage_string(FLOAT, INTEGER) RETURNS TEXT AS $  $   BEGIN     RETURN ROUND($  1::NUMERIC * 100, $  2)::TEXT || '%'; END; $  $   LANGUAGE plpgsql IMMUTABLE;   CREATE OR REPLACE FUNCTION util.divide_ints_and_get_percentage_string(BIGINT, BIGINT) RETURNS TEXT AS $  $   BEGIN          RETURN CASE          WHEN $  2 > 0 THEN util.ratio_to_percentage_string($  1::FLOAT / $  2::FLOAT, 2)         ELSE 'divide_by_zero'          END         ;  END; $  $   LANGUAGE plpgsql IMMUTABLE; 
  • As you can see it’s a very simple function, which is only being called once, from the single row the whole thing produces… how can this cause such a massive slowdown? And why is it affecting whether postgres inlines the initial subquery/WITH (or whatever else might be going on here)

EXPLAIN ANALYZE outputs:

  • Query1: https://explain.depesz.com/s/bq7u
  • Query2: https://explain.depesz.com/s/9w3rY