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
David Rowley <dgrowleyml@gmail.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