> regression=# explain select * from t where lower(a) like 'z';
> QUERY PLAN
> ------------------------------------------------------------------------------
> Index Only Scan using t_lower_a_idx on t (cost=0.14..28.27 rows=7 width=32)
> Index Cond: ((lower(a)) ~~ 'z'::text)
> (2 rows)
>
I've tried to toy with the patch and remembered one related caveat.
If we have index for both returnable and nonreturnable attributes, IOS will not be choosen:
postgres=# create index on t using gist(a gist_trgm_ops) include (a);
postgres=# explain select * from t where a like 'z';
QUERY PLAN
---------------------------------------------------------------------
Index Scan using t_a_a1_idx on t (cost=0.12..8.14 rows=1 width=32)
Index Cond: (a ~~ 'z'::text)
(2 rows)
But with index
create index on t using gist(lower(a) gist_trgm_ops) include (a);
I observe IOS for
select * from t where lower(a) like 'z';
So lossiness of opclass kind of "defeats" returnable attribute. But lossiness of expression does not. I don't feel
condifentin surrounding code to say is it a bug or just a lack of feature. But maybe we would like to have equal
behaviorin both cases...
Thanks!
Best regards, Andrey Borodin.
