Custom function ROWS hint ignored due to inlining?
От | Philip Semanchuk |
---|---|
Тема | Custom function ROWS hint ignored due to inlining? |
Дата | |
Msg-id | 76B16E5F-59D0-4C97-8DBA-4B3BB21E2009@americanefficient.com обсуждение исходный текст |
Ответы |
Re: Custom function ROWS hint ignored due to inlining?
|
Список | 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, butI’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 plannernever sees fn_get_deltas() — it has been inlined by the query preprocessor because fn_get_deltas() meets the criteriafor inlining (https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions). Instead of 10 rows, the planner uses itsdefault 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 togive decent estimates to the planner. Am I correctly reading the situation? If so, is there another solution that allowsinlining *and* making the ROWS hint visible to the planner? Thanks a bunch Philip
В списке pgsql-general по дате отправления: