Re: SupportRequestSimplify and SQL SRF - Mailing list pgsql-hackers
| From | Ronan Dunklau |
|---|---|
| Subject | Re: SupportRequestSimplify and SQL SRF |
| Date | |
| Msg-id | CAA8M49pzo+QNcgvSYaxuR0+P3S_YZJ866s2k0_5JLREPh_n1eQ@mail.gmail.com Whole thread Raw |
| In response to | Re: SupportRequestSimplify and SQL SRF (Tom Lane <tgl@sss.pgh.pa.us>) |
| List | pgsql-hackers |
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,
pgsql-hackers by date: