Horton

Re: Index-only scan returns incorrect results when using a composite GIST index with a gist_trgm_ops column.

Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>

View thread View on postgr.es
From: Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
To: sk@zsrv.org
Cc: david@nlpgo.com, pgsql-bugs@lists.postgresql.org, pgsql-hackers@postgresql.org
Date: 2018-01-18T05:48:51Z
Lists: pgsql-bugs, pgsql-hackers

Attachments

Hello,

Gist imposes the ninth strategy to perform index only scan but
planner is not considering that.

At Wed, 17 Jan 2018 22:26:15 +0300, Sergei Kornilov <sk@zsrv.org> 
wrote in <412861516217175@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.

Please find the attached patch.

regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

Commits

  1. Improve stability of recently-added regression test case.

  2. Fix IOS planning when only some index columns can return an attribute.

  3. Ability to advance replication slots

  4. Allow no-op GiST support functions to be omitted.