Referencing the example given in the documentation for index-only
scans [0], we consider an index:
CREATE INDEX tab_f_x ON tab (f(x));
This index currently will not be used for an index-scan for the
following query since the planner isn't smart enough to know that "x"
is not needed:
SELECT f(x) FROM tab WHERE f(x) < 1;
However, any function applied to a column for an index expression is
required to be immutable so as far as I can tell the planner doesn't
have to be very smart to know that the index can indeed be used for an
index-only scan (without having "x" included).
One interesting use-case for this is to be able to create
space-efficient indexes for raw log data. For example, for each type
of message (which might be encoded as JSON), one could create a
partial index with the relevant fields extracted and converted into
native data types and use index-only scanning to query. This is not
particularly attractive today because the message itself would need to
be added to the index effectively duplicating the log data.
In the same vein, being able to add this auxiliary data (which is
basically immutable expressions on one or more columns) explicitly
using INCLUDE would make the technique actually reliable. This is not
possible right now since expression are not supported as included
columns.
What's required in order to move forward on these capabilities?
[0] https://www.postgresql.org/docs/current/indexes-index-only-scans.html