Re: Index only scans for expressional indices when querying for the expression

A-ha, interesting !

I think we have some specific use cases where it'd be worth the overhead,
I'd need to measure it, though;

Do you think there'd be room to accept a contribution for such
functionality with a disabled-by-default pg setting,
or are you skeptical it would ever be worth the trade-off ?

Thanks again,
Danny

On Thu, Aug 4, 2022 at 4:38 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Danny Shemesh <dany74q(at)gmail(dot)com> writes:
> > That is of course correct, but what I mean is that, I think that if one
> > would explicitly query f(x), and never for x directly, it would've been
> > theoretically possible to say that the index is covering for every f(x),
> > wouldn't it ?
>
> Theoretically, yeah, but we don't support that: an index-only scan
> will only be considered if x itself is available from the index.
> There are a couple of reasons for that, but the main one is that
> detecting whether an index matches the query would be far more expensive
> if it had to consider expression subtrees not just the base Vars.
>
> regards, tom lane
>