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

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
*** a/src/backend/optimizer/path/indxpath.c
--- b/src/backend/optimizer/path/indxpath.c
***************
*** 1866,1871 **** check_index_only(RelOptInfo *rel, IndexOptInfo *index)
--- 1866,1872 ----
      bool        result;
      Bitmapset  *attrs_used = NULL;
      Bitmapset  *index_canreturn_attrs = NULL;
+     Bitmapset  *index_cannotreturn_attrs = NULL;
      ListCell   *lc;
      int            i;
  
***************
*** 1905,1911 **** check_index_only(RelOptInfo *rel, IndexOptInfo *index)
  
      /*
       * Construct a bitmapset of columns that the index can return back in an
!      * index-only scan.
       */
      for (i = 0; i < index->ncolumns; i++)
      {
--- 1906,1913 ----
  
      /*
       * Construct a bitmapset of columns that the index can return back in an
!      * index-only scan. We must have a value for all occurances of the same
!      * attribute since it can be used for rechecking.
       */
      for (i = 0; i < index->ncolumns; i++)
      {
***************
*** 1922,1932 **** check_index_only(RelOptInfo *rel, IndexOptInfo *index)
--- 1924,1942 ----
              index_canreturn_attrs =
                  bms_add_member(index_canreturn_attrs,
                                 attno - FirstLowInvalidHeapAttributeNumber);
+         else
+             index_cannotreturn_attrs =
+                 bms_add_member(index_cannotreturn_attrs,
+                                attno - FirstLowInvalidHeapAttributeNumber);
      }
  
+     index_canreturn_attrs = bms_del_members(index_canreturn_attrs,
+                                             index_cannotreturn_attrs);
+ 
      /* Do we have all the necessary attributes? */
      result = bms_is_subset(attrs_used, index_canreturn_attrs);
  
+ 
      bms_free(attrs_used);
      bms_free(index_canreturn_attrs);