From: | Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> |
---|---|
To: | sk(at)zsrv(dot)org |
Cc: | david(at)nlpgo(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Index-only scan returns incorrect results when using a composite GIST index with a gist_trgm_ops column. |
Date: | 2018-01-18 05:48:51 |
Message-ID: | 20180118.144851.24081723.horiguchi.kyotaro@lab.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-hackers |
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(at)zsrv(dot)org>
wrote in <412861516217175(at)web38o(dot)yandex(dot)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
Attachment | Content-Type | Size |
---|---|---|
fix_check_index_only_v0.patch | text/x-patch | 1.6 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Andrey Borodin | 2018-01-18 07:57:38 | Re: Index-only scan returns incorrect results when using a composite GIST index with a gist_trgm_ops column. |
Previous Message | Tom Lane | 2018-01-18 02:51:21 | Re: BUG #15006: "make check" error if current user is "user" |
From | Date | Subject | |
---|---|---|---|
Next Message | Etsuro Fujita | 2018-01-18 06:40:04 | Re: [HACKERS] postgres_fdw bug in 9.6 |
Previous Message | Ashutosh Sharma | 2018-01-18 04:57:33 | Re: Test-cases for exclusion constraints is missing in alter_table.sql file |