The Postgres planner just got a new superpower: X-ray vision for your black-box functions.
For years, Postgres has been able to "inline" simple SQL functions. If you wrote CREATE FUNCTION ... LANGUAGE SQL AS 'SELECT ...', the planner would often rip out the function call and paste the raw SQL directly into the main query. This was magic. It meant that WHERE clauses could be pushed down, indexes could be used, and performance was excellent.
But if you stepped outside the safety of simple SQL functions—say, into the world of C extensions or complex dynamic SQL—you were often stuck with a "black box." The planner saw a function, executed it blindly, and only *then* applied your filters.
That changes in Postgres 19.
The "Black Box" Problem
Imagine you have a function that returns a large dataset, but you only want a few rows:
SELECT * FROM my_complex_function() WHERE id = 42;
Before Postgres 19, if my_complex_function wasn't a simple SQL function, the database would:
1. Run my_complex_function() to completion (generating, say, 1 million rows).
2. Filter the result to find id = 42.
This is painfully inefficient. You wanted an Index Scan, but you got a full execution followed by a filter.
The Solution: SupportRequestInlineInFrom
With this feature, Postgres now allows function authors (specifically in C extensions) to provide a "support function" that tells the planner: *"Hey, if anyone calls me in a FROM clause, here is the raw SQL query tree that represents what I'm doing."*
The planner can then take that query tree and inline it, just like it does for standard SQL functions.
The Impact
This is a game-changer for:
- Extensions: Many extensions provide table-like interfaces (e.g., foreign data wrappers, set-returning functions). They can now expose their internal logic to the planner.
- Dynamic SQL: Functions that generate SQL strings and execute them can now potentially participate in optimization.
Before vs. After
Scenario: A PL/pgSQL function foo_from_bar that executes a dynamic query on text_tbl (containing 1 million rows).
CREATE TABLE text_tbl (f1 text);
INSERT INTO text_tbl SELECT 'row_' || generate_series(1, 1000000);
INSERT INTO text_tbl SELECT 'common_row' FROM generate_series(1, 100);
CREATE INDEX text_tbl_idx ON text_tbl(f1);
ANALYZE text_tbl;
Here is the PL/pgSQL function I used for testing (designed to return 100 rows). Note that it uses dynamic SQL, which is normally opaque to the planner, and then linked it to the C support function test_inline_in_from_support_func using ALTER FUNCTION.
CREATE OR REPLACE FUNCTION foo_from_bar(colname TEXT, tablename TEXT, filter TEXT)
RETURNS SETOF TEXT
LANGUAGE plpgsql
AS $function$
DECLARE
sql TEXT;
BEGIN
sql := format('SELECT %I::text FROM %I', colname, tablename);
IF filter IS NOT NULL THEN
sql := CONCAT(sql, format(' WHERE %I::text = $1', colname));
END IF;
RETURN QUERY EXECUTE sql USING filter;
END;
$function$ STABLE;
ALTER FUNCTION foo_from_bar(TEXT, TEXT, TEXT)
SUPPORT test_inline_in_from_support_func;
And here is the query I ran:
SELECT * FROM foo_from_bar('f1', 'text_tbl', 'common_row');
Before (PG 18 and earlier)
-> Function Scan on public.foo_from_bar (cost=0.25..10.25 rows=1000 width=32)
Output: foo_from_bar
Function Call: foo_from_bar('f1'::text, 'text_tbl'::text, 'common_row'::text)
Buffers: shared hit=32 read=4
Execution Time: 0.353 ms
The function is treated as a black box. The planner estimates 1000 rows (default) and scans the function output.
After (PG 19)
-> Index Only Scan using text_tbl_idx on public.text_tbl (cost=0.42..8.44 rows=1 width=10)
Output: text_tbl.f1
Index Cond: (text_tbl.f1 = 'common_row'::text)
Buffers: shared hit=1 read=3
Execution Time: 0.064 ms
The planner "sees through" the function, realizes it's just a query on text_tbl, and uses the index directly. The execution time drops significantly (5.5x faster in this case).
I also ran a pgbench test (1000 iterations) to measure the end-to-end latency improvement:
- Before: 0.107 ms average latency
- After: 0.087 ms average latency
- Improvement: ~19% faster end-to-end (including network overhead)
Bonus: Outer WHERE Pushdown - Orders of magnitude faster!
Next, I ran a test where NULL was passed to the function (returning all 1 million rows) and applied an external WHERE clause:
SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL) WHERE foo_from_bar = 'common_row';
Before (PG 18):
-> Function Scan on public.foo_from_bar (cost=0.25..12.75 rows=5 width=32)
Output: foo_from_bar
Function Call: foo_from_bar('f1'::text, 'text_tbl'::text, NULL::text)
Filter: (foo_from_bar.foo_from_bar = 'common_row'::text)
Rows Removed by Filter: 1000000
Execution Time: 136.267 ms
The database scanned all 1 million rows from the function, then filtered them down to 100.
After (PG 19):
-> Index Only Scan using text_tbl_idx on public.text_tbl (cost=0.42..8.44 rows=1 width=10)
Output: text_tbl.f1
Index Cond: (text_tbl.f1 = 'common_row'::text)
Execution Time: 0.055 ms
The planner pushed the WHERE clause *inside* the function logic, triggering an index lookup (~2400x speedup).
Similarly, I tested adding a LIMIT 5 clause to the query and saw a ~20x speedup!!
- Before: The database scanned the function output until it got 5 rows (~1.091 ms).
- After: The planner pushed the limit down into the index scan, stopping immediately after finding 5 rows (~0.051 ms).
Testing Methodology
The benchmarks above were conducted using pgbench with the following parameters:
- Iterations: 1000 transactions per client.
- Concurrency: Single client (1 thread).
- Data Volume: The target table
text_tblcontained 1,000,100 rows. - Return Size: The query returned 100 rows per execution.
- Metric: Average latency reported by
pgbench(excluding connection overhead).
- Commits tested:
c0bc9af(before) andb140c8d(after).
The C support function (test_inline_in_from_support_func) used in this example is defined in src/test/regress/regress.c that is a part of the regression tests. To know more about support functions, see PostgreSQL Documentation on Function Optimization Information.
Discussion & Credits
Coincidentally a Linkedin thread mentioned that Set-Returning Functions (SRF) began their journey in Postgres 7.3 - that was 17 years ago! With this patch, the planner should be able to see through such a function and develop much better execution plans around them! Thanks to Paul A. Jungwirth (for initating the idea with a working patch) and Tom Lane (for in-depth review).
Note: This feature is currently in the master branch for Postgres 19. As with all development features, it is subject to change before the final release.
