Re: SupportRequestSimplify and SQL SRF

> 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,

--

This e-mail message and any attachments to it are intended only for the
named recipients and may contain legally privileged and/or confidential
information. If you are not one of the intended recipients, do not
duplicate or forward this e-mail message.