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 <[email protected]>
wrote in <[email protected]>
> 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);
