Custom function ROWS hint ignored due to inlining?
Hi,
I have a custom function where the ROWS hint is getting ignored. I think it’s because the function is getting inlined, but I’d like a second opinion.
Here’s my working (contrived) example.
CREATE TABLE my_table (
id int primary key GENERATED ALWAYS AS IDENTITY,
base_value int NOT NULL
);
INSERT INTO my_table (base_value) VALUES (42);
CREATE OR REPLACE FUNCTION fn_get_deltas(base_value int)
RETURNS TABLE (delta int, total int) AS $$
SELECT
generate_series(1, CASE base_value WHEN 42 THEN 4 ELSE 20 END),
base_value + generate_series(1, CASE base_value WHEN 42 THEN 4 ELSE 20 END)
$$ LANGUAGE sql IMMUTABLE PARALLEL SAFE ROWS 10;
EXPLAIN
SELECT base_value, delta, total
FROM my_table
CROSS JOIN LATERAL (SELECT delta, total FROM fn_get_deltas(base_value)) AS foo
+------------------------------------------------------------------+
| QUERY PLAN |
|------------------------------------------------------------------|
| Nested Loop (cost=0.00..107427.80 rows=2260000 width=12) |
| -> Seq Scan on my_table (cost=0.00..32.60 rows=2260 width=4) |
| -> Result (cost=0.00..27.52 rows=1000 width=8) |
| -> ProjectSet (cost=0.00..5.02 rows=1000 width=4) |
| -> Result (cost=0.00..0.01 rows=1 width=0) |
+------------------------------------------------------------------+
The plan estimates 1000 rows from the CROSS JOIN despite the “ROWS 10” hint on my function. I think this is because the planner never sees fn_get_deltas() — it has been inlined by the query preprocessor because fn_get_deltas() meets the criteria for inlining (https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions). Instead of 10 rows, the planner uses its default assumption of 1000 rows.
If I change the function to VOLATILE to prevent inlining, I get this plan.
+-------------------------------------------------------------------------+
| QUERY PLAN |
|-------------------------------------------------------------------------|
| Nested Loop (cost=0.25..484.85 rows=22600 width=12) |
| -> Seq Scan on my_table (cost=0.00..32.60 rows=2260 width=4) |
| -> Function Scan on fn_get_deltas (cost=0.25..0.35 rows=10 width=8) |
+-------------------------------------------------------------------------+
I would prefer to have the function inlined for better performance, but I can declare it VOLATILE if that’s necessary to give decent estimates to the planner. Am I correctly reading the situation? If so, is there another solution that allows inlining *and* making the ROWS hint visible to the planner?
Thanks a bunch
Philip
Philip Semanchuk <philip@americanefficient.com> writes:
I have a custom function where the ROWS hint is getting ignored. I think it’s because the function is getting inlined, but I’d like a second opinion.
Yeah, I believe you're right about that.
I would prefer to have the function inlined for better performance, but
I can declare it VOLATILE if that’s necessary to give decent estimates
to the planner. Am I correctly reading the situation? If so, is there
another solution that allows inlining *and* making the ROWS hint visible
to the planner?
No, but you could experiment with changing the function to plpgsql
instead of SQL. Not sure about the relative performance of those
cases, but it's worth trying it both ways.
regards, tom lane