Hello everyone,
Quick question here about index-only scans and expressional indices, tested on postgres <= 14,
Say I have a column, and an expressional index on said column (
fiddle) - e.g.
create table t1 (x text);
create index idx_upper on t1 (upper(x));
I see that even if I query for upper(x) in itself, I won't achieve an index-only scan without creating a covering index that includes the manipulated field:
create index idx_upper_covering on t1 (upper(x)) include (x);
I wonder if it would've been possible to have an index-only scan for similar cases without having to include the base column ?
I believe the expressional index in itself could've been considered as covering, when querying for the expression explicitly.
The thing is, indices with include clauses do not support page deduplication, which causes the size of the index to bloat in our case, over 20x in size at times.
Also, it could've been beneficial when creating indices on complex types, say - indexing the first element on an array, or a specific nested element of a jsonb column, et-al.
Appreciate your time !
Danny