From: | Philip Semanchuk <philip(at)americanefficient(dot)com> |
---|---|
To: | PostgreSQL General <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Custom function ROWS hint ignored due to inlining? |
Date: | 2022-10-19 19:02:42 |
Message-ID: | 76B16E5F-59D0-4C97-8DBA-4B3BB21E2009@americanefficient.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Raynsford | 2022-10-19 19:30:57 | Re: Column value derived from generated column in INSERT? |
Previous Message | David G. Johnston | 2022-10-19 18:58:07 | Re: Column value derived from generated column in INSERT? |