Re: BUG #18399: Query plan optimization results in runtime error when hoisting cast from inside subquery

Makes sense, I figured there was a decent chance it may not be viable but
wanted to bring it up just in case it was useful. I didn't even think about
the case of just a single where clause but that does indeed sound like a
big problem for performance, although I wonder if the guarantees could be
relaxed in any way that would help the viability at all (such as this type
of optimization only being disallowed across query boundaries or something
similar). But regardless it sounds like there are more issues than just
this, and maybe just adding some documentation around this is good enough.
I appreciate both of your thoughts on it, thank you.

Best,
Sawyer Knoblich

On Tue, Mar 19, 2024 at 9:39 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> David Rowley <dgrowleyml(at)gmail(dot)com> writes:
> > There are quite a lot of things which would have to be restricted,
> > much more than you might think. Any conditions which could cause an
> > error would have to be evaluated last in a WHERE clause and that might
> > result in being unable to use indexes because some other (possibly
> > unindexed) expression would need to be evaluated first.
>
> That particular aspect might not be too awful, because there's already
> a good deal of pressure for index opclass members to not fail ---
> certainly I'd expect that all standard btree comparison functions
> could be marked non-failing. (Let us slide quietly past the question
> of exactly how strong the guarantee should be; for example any function
> that takes toastable argument types is potentially at risk of OOM,
> but do you really want to exclude numeric_eq and texteq from the
> set of safe operations? See also past discussions on how strict
> we should be about the related concept of leakproofness.)
>
> In any case I agree with your larger point that ordering things such
> that potentially-failing tests are always done last would be a
> performance disaster, even if it's possible at all.
>
> regards, tom lane
>