On Tue, Apr 9, 2024 at 12:27 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Some of the most compelling cases for the transformation will involve
> > path keys. If the transformation enables the optimizer to build a
> > plain index scan (or index-only scan) with useful path keys, then that
> > might well lead to a far superior plan compared to what's possible
> > with BitmapOrs.
>
> I did not say it isn't a useful thing to have. I said the patch
> did not appear ready to go in.
Didn't mean to suggest otherwise. I just wanted to hear your thoughts
on this aspect of these sorts of transformations.
> > I understand that it'll still be possible to use OR expression
> > evaluation in such cases, without applying the transformation (via
> > filter quals), so in principle you don't need the transformation to
> > get an index scan that can (say) terminate the scan early due to the
> > presence of an "ORDER BY ... LIMIT n". But I suspect that that won't
> > work out much of the time, because the planner will believe (rightly
> > or wrongly) that the filter quals will incur too many heap page
> > accesses.
>
> That's probably related to the fact that we don't have a mechanism
> for evaluating non-indexed quals against columns that are retrievable
> from the index. We really oughta work on getting that done.
I agree that that is very important work, but I'm not sure that it
makes all that much difference here. Even if we had that improved
mechanism already, today, using index quals would still be strictly
better than expression evaluation. Index quals can allow nbtree to
skip over irrelevant parts of the index as the need arises, which is a
significant advantage in its own right.
ISTM that the planner should always prefer index quals over expression
evaluation, on general principle, even when there's no reason to think
it'll work out. At worst the executor has essentially the same
physical access patterns as the expression evaluation case. On the
other hand, providing nbtree with that context might end up being a
great deal faster.
--
Peter Geoghegan