Re: Index-only scans vs. partially-retrievable indexes - Mailing list pgsql-hackers

From Andrey Borodin
Subject Re: Index-only scans vs. partially-retrievable indexes
Date
Msg-id 602391641208390@iva4-92c901fae84c.qloud-c.yandex.net
Whole thread Raw
In response to Index-only scans vs. partially-retrievable indexes  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Index-only scans vs. partially-retrievable indexes
List pgsql-hackers


> 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.



pgsql-hackers by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: Add Boolean node
Next
From: Andrey Borodin
Date:
Subject: Re: libpq compression (part 2)