On Mon, Aug 7, 2023 at 12:34 PM Tomas Vondra <tomas.von...@enterprisedb.com> wrote: > But then we call get_index_paths/build_index_path a little bit later, > and that decides to skip "lower SAOP" (which seems a bit strange, > because the column is "after" the equality, but meh). Anyway, at this > point we already decided what's a filter, ignoring the index clauses, > and not expecting any backsies.
I'm not surprised that it's due to the issue around "lower SAOP" clauses within get_index_paths/build_index_path. That whole approach seems rather ad-hoc to me. As you probably realize already, my own patch has to deal with lots of issues in the same area. > The simples fix seems to be to add these skipped SAOP clauses as > filters. We know it can be evaluated on the index ... Right. Obviously, my preferred solution to the problem at hand is to make everything into index quals via an approach like the one from my patch -- that works sensibly, no matter the length of the SAOP arrays. But even if you're willing to assume that that work will be in place for 17, there are still certain remaining gaps, that also seem important. Even my patch cannot always make SAOP clauses into index quals. There are specific remaining gaps that I hope that your patch will still cover. The simplest example is a similar NOT IN() inequality, like this: select ctid, * from tenk1 where thousand = 42 and tenthous not in (1, 3, 42, 43, 44, 45, 46, 47, 48, 49, 50); There is no way that my patch can handle this case. Where your patch seems to be unable to do better than master here, either -- just like with the "tenthous in ( )" variant. Once again, the inequality SAOP also ends up as table filter quals, not index filter quals. It would also be nice if we found a way of doing this, while still reliably avoiding all visibility checks (just like "real index quals" will) -- since that should be safe in this specific case. The MDAM paper describes a scheme for converting NOT IN() clauses into DNF single value predicates. But that's not going to happen for 17, and doesn't seem all that helpful with a query like this in any case. But it does suggest an argument in favor of visibility checks not being truly required for SAOP inequalities like this one (when they appear in index filters). I'm not sure if that idea is too particular to SAOP inequalities to be interesting -- just a suggestion. -- Peter Geoghegan
