Re:Can adding an index make a non SARGable query SARGable?
by hotsauceror on Saturday October 19, 2019 @08:50AM
(#21298212)
Right. What you’d really need would be for the query optimizer to recognize your non-sargable expression and be able to look up a corresponding function index, and then have a mechanism for seeking on that. Indexing a computed column, like you said, wouldn’t actually solve the problem of having to evaluate each row to begin with.
[ Parent
| on HN ]
Re:Can adding an index make a non SARGable query SARGable?
by marzell on Saturday October 19, 2019 @12:39PM
(#21299310)
Does a function index somehow eliminate the need for RBAR evaluation? I don't see how it's functionally any different in this context from a computed column
Re:Can adding an index make a non SARGable query SARGable?
by hotsauceror on Sunday October 20, 2019 @12:06PM
(#21304973)
I don’t know how postgresql does it, but I’m interpreting it as yes, their engine has a way to actually replace nonsargable expressions in the query plan itself, with some other operation that performs a seek against the index on the filter. Otherwise you’re right, you’d still need an RBAR evaluation, which is really the crux of the issue. I wish they’d reframe this discussion as RBAR instead of sargability, because it covers so many more sins...