Thread: SupportRequestSimplify and SQL SRF
Hello,
I've been trying to play with support functions for a use-case of ours, and found some behaviors I don't know are expected or not.
The use case: we have some complicated queries, where whole-branches of the execution tree could be cut if some things were evaluated at planning time. Take the following simplified example:
CREATE OR REPLACE FUNCTION myfunction(t1_id int) AS $$
SELECT *
FROM sometable t1
JOIN sometable t2 on t2.t1_id = t1.id
WHERE id = t1_id AND t1.somecolumn IS NOT NULL
$$ language SQL;
If I were to incorporate this function in a larger query, the planner will choose a plan based on a generic value of t1_id and may estimate a large rowcount after inlining.
What I want to do is to evaluate whether id = t1_id AND somecolumn is NOT NULL at planification time, and replace the function by another one if this can be pruned altogether.
So, what I've been doing is to implement a support function for SupportRequestSimplify, and If the predicate doesn't match any row, replace the FuncExpr by a new one, calling a different function.
This seems to work great, but I have several questions:
1) Is it valid to make SPI calls in a support function to do this kind of simplification ?
2) My new FuncExpr doesn't get inlined. This is because in inline_set_returning_function, we check that after the call to eval_const_expressions we still call the same function. I think it would be better to first simplify the function if we can, and only then record the function oid and call the rest of the machinery. I tested that naively by calling eval_const_expressions early in inline_set_returning_function and it seems to do the trick. A proper patch would likely only call the support function at this stage.
What do you think ?
Ronan Dunklau <ronan_dunklau@ultimatesoftware.com> writes:
> What I want to do is to evaluate whether id = t1_id AND somecolumn is NOT
> NULL at planification time, and replace the function by another one if this
> can be pruned altogether.
Hm. There was never really any expectation that support functions
would be attached to PL functions --- since you have to write the
former in C, it seems a little odd for the supported function not
to also be C. Perhaps more to the point though, what simplification
knowledge is this support function bringing to bear that the planner
hasn't already got? It kinda feels like you are trying to solve
this in the wrong place.
> So, what I've been doing is to implement a support function for
> SupportRequestSimplify, and If the predicate doesn't match any row, replace
> the FuncExpr by a new one, calling a different function.
I'm confused. I don't see any SupportRequestSimplify call at all in the
code path for set-returning functions. Maybe there should be one,
but there is not.
> This seems to work great, but I have several questions:
> 1) Is it valid to make SPI calls in a support function to do this kind of
> simplification ?
Hmm, a bit scary maybe but we don't hesitate to const-simplify
functions that could contain SPI calls, so I don't see a big
problem in that aspect. I'd be more worried, if you're executing
some random SQL that way, about whether the SQL reliably does what
you want (in the face of variable search_path and the like).
> 2) My new FuncExpr doesn't get inlined. This is because in
> inline_set_returning_function, we check that after the call to
> eval_const_expressions we still call the same function.
Uh, what? I didn't check the back branches, but I see nothing
remotely like that in HEAD.
regards, tom lane
Hm. There was never really any expectation that support functions
would be attached to PL functions --- since you have to write the
former in C, it seems a little odd for the supported function not
to also be C. Perhaps more to the point though, what simplification
knowledge is this support function bringing to bear that the planner
hasn't already got? It kinda feels like you are trying to solve
this in the wrong place.
Some optimization aren't done by the planner, and could be added easily that way.
For example, the following query would have wrong estimates if the planner can't inject inferred values:
SELECT t2.*
FROM t1 JOIN t2 ON t1.id = t2.t1_id
WHERE t1.code = ? AND t1.col1 IS NOT NULL
UNION
SELECT t3.*
FROM t1 JOIN t3 ON t1.id = t3.t1_id
WHERE t1.code = ? AND t1.col1 IS NULL
At any given time, only one of those branch will be evaluated. I can either write a PL function which will force me to abandon all the benefits of inlining with regards to cost estimation, or keep it in SQL and fall back on a generic plan, which will evaluate an average number of rows for both cases.
With support functions, I was hoping to replace a function containing the above query to another depending of the matched t1 record: if col1 is NULL, then query directly t2 else query directly t3. By injecting the value directly when we know we have only one row (unique constraint on t1.code) we can optimize the whole thing away, and have sensible estimates based on the statistics of t1_id. But of course, I need to be able to use SPI calls to inject the value...
I'm not yet convinced it is a good idea either, but it is one I wanted to experiment with.
In the more generic case, the planner could possibly perform those kind of optimizations if it was able to identify JOINs between one unique row and other relations. If we were to work on a patch like this, would it be something that could be of interest, perhaps hidden behind a GUC ?
I'm confused. I don't see any SupportRequestSimplify call at all in the
code path for set-returning functions. Maybe there should be one,
but there is not.
Sorry, I should have checked on HEAD, I was working on REL_12_STABLE.
This simplification was done in eval_const_expressions, which in turn ended in calling simplify_function.
I have not looked at the code thoroughly on HEAD, but a quick test shows that it now does what I want and presumably simplifies it earlier.
> 1) Is it valid to make SPI calls in a support function to do this kind of
> simplification ?
Hmm, a bit scary maybe but we don't hesitate to const-simplify
functions that could contain SPI calls, so I don't see a big
problem in that aspect. I'd be more worried, if you're executing
some random SQL that way, about whether the SQL reliably does what
you want (in the face of variable search_path and the like).
Ok, I need to triple-check that, but that was my main worry.
> 2) My new FuncExpr doesn't get inlined. This is because in
> inline_set_returning_function, we check that after the call to
> eval_const_expressions we still call the same function.
Uh, what? I didn't check the back branches, but I see nothing
remotely like that in HEAD.
Sorry again, I should have checked HEAD. The code is different on HEAD, and works as expected: the replacement SRF ends up being inlined.
Again, thank you for your answer.
Best regards,