A query that joins against a set-returning (table-valued) function performs far worse than the equivalent query written against base tables. What is going on inside the optimizer, and what are your options?

## Where the plan goes wrong Cost-based optimization is estimation-driven: the planner predicts how many rows each operator produces and picks join order, join algorithms, and memory from those predictions. For a base table it has statistics — row counts, histograms, distinct counts, null fractions. For a table-valued function it usually has *nothing*, because the body is opaque at planning time. So it guesses with a constant. PostgreSQL's default is 1000 rows for a set-returning function without an explicit row estimate. SQL Server's multi-statement table-valued functions were fixed at 1 row before 2014 and 100 afterwards. Oracle's pipelined table functions default off a block-size-derived guess. All of these are arbitrary relative to your data. A cardinality error of three orders of magnitude does not degrade a plan gracefully; it inverts it. The planner sees "1000 rows" and chooses a nested loop with an index lookup on the other side — excellent for 1000 iterations, ruinous for 2 million. It sizes hash tables and sort memory for the guess, so the real data spills to disk. It may decline parallelism because the estimated work looks trivial. And because the error is at the leaf, it propagates upward through every join above it. ## Inline versus multi-statement The single most important distinction is whether the function can be **inlined** into the calling query. - An **inline** function is one SQL statement with no procedural control flow. Engines can substitute its text into the outer query, after which it is just a subquery: predicates push down, joins reorder freely, and the planner sees the underlying tables' statistics. Effectively the function disappears as a planning obstacle. - A **multi-statement** function has a procedural body building a result table. It must be executed and materialized before the outer query can use its rows. No pushdown, no reordering into it, and the only cardinality information is whatever default or declaration exists. This is why the same logic can be fast as a one-statement function and pathological as a procedural one. SQL Server 2019's *scalar UDF inlining* extended a comparable idea to scalar functions, which previously forced row-by-row invocation and blocked parallelism entirely — one of the biggest single-feature plan improvements in that engine's history. ## Scalar functions in predicates A related failure: `WHERE f(col) = 42`. Unless there is an expression index on `f(col)` and the function is deterministic, the engine cannot use an index on `col`, so it evaluates `f` for every row — a full scan plus a per-row function call. The fix is to make the predicate sargable: index the expression, restructure to `col = inverse(42)`, or store the computed value in a generated column and index that. ## The remedies, in order of preference 1. **Make it inlinable.** Collapse the body to a single SQL statement. This is usually the largest win and costs nothing at runtime. 2. **Tell the planner the truth.** Several engines accept an explicit row-count and per-call cost declaration on the routine; some support a callback that computes the estimate from the arguments. A static estimate that is roughly right beats a default that is wildly wrong. 3. **Materialize with statistics.** Write the function's output into a temporary table, gather statistics on it, then join against that. You pay writes and an analyze, but the planner now has real numbers — the standard technique for a multi-step ETL pipeline where one intermediate result feeds several later joins. 4. **Use an engine feature for late estimation.** Interleaved execution / adaptive plans re-plan the consumer after the function's actual cardinality is known. Where available this repairs the estimate automatically, but it does not exist everywhere and only helps at specific plan boundaries. 5. **Delete the abstraction.** If the function exists only for tidiness and its logic is a join, put the join in the query. A view is often the better encapsulation, because a view is expanded and optimized as part of the outer query rather than being a black box. ## Diagnosing it Run the plan with actual row counts and look at the function node: a large ratio between estimated and actual rows at that leaf, with the damage visible above it as a nested loop executing millions of times or a spilling hash. That estimate-versus-actual gap at the function is the fingerprint; everything above it is collateral damage.