On Thu, Jan 18, 2018 at 8:48 AM, Kyotaro HORIGUCHI < horiguchi.kyot...@lab.ntt.co.jp> wrote:
> At Wed, 17 Jan 2018 22:26:15 +0300, Sergei Kornilov <s...@zsrv.org> > wrote in <412861516217...@web38o.yandex.ru> > > Hello > > I can reproduce on actual 9.6.6, 10.1 and fresh master build > > (9c7d06d60680c7f00d931233873dee81fdb311c6 commit). I did not check > > earlier versions > > > > set enable_indexonlyscan to off ; > > postgres=# SELECT w FROM words WHERE w LIKE '%e%'; > > w > > ------- > > Lorem > > Index scan result is correct. Affected only index only scan, > > > > PS: i find GIST(w gist_trgm_ops, w); some strange idea, but result > > is incorrect in any case. > > The cause is that gist_trgm_ops lacks "fetch" method but planner > is failing to find that. > > https://www.postgresql.org/docs/10/static/gist-extensibility.html > > The optional ninth method fetch is needed if the operator class > > wishes to support index-only scans. > > Index only scan is not usable in the case since the first index > column cannot be rechecked but check_index_only makes wrong > decision by the second occurance of "w'. There may be a chance > that recheck is not required but we cannot predict that until > actually acquire a tuple during execution. > I didn't get this point. Same column is indexed twice using two different opclasses. The first opclass doesn't support index-only scan, while the second opclass does support it. So, if the first opclass needs recheck then it can be rechecked using the value got from the second opclass. Thus, I think we can potentially support index-only scan in this case. Another thing is that it's probably hard to do in our current optimizer/executor/am infrastructure. And assuming that use-case is quite narrow, and it looks OK to just disable such feature as bug fix. ------ Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
