From: | Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru> |
---|---|
To: | Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> |
Cc: | sk(at)zsrv(dot)org, david(at)nlpgo(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org, pgsql-hackers <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 22:16:56 |
Message-ID: | CAPpHfdvJeYrnAsXhKRfO_NMDUaWQyK+wyhcv4zOdRzTdfNCkkw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-hackers |
On Thu, Jan 18, 2018 at 8:48 AM, Kyotaro HORIGUCHI <
horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> 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.
>
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
From | Date | Subject | |
---|---|---|---|
Next Message | Kyotaro HORIGUCHI | 2018-01-19 01:00:36 | Re: Index-only scan returns incorrect results when using a composite GIST index with a gist_trgm_ops column. |
Previous Message | PG Bug reporting form | 2018-01-18 22:01:59 | BUG #15015: Zilib1.dll library missing if only command line tools installed |
From | Date | Subject | |
---|---|---|---|
Next Message | Fabien COELHO | 2018-01-18 22:29:59 | Re: Re: [HACKERS] pgbench randomness initialization |
Previous Message | Tom Lane | 2018-01-18 22:09:19 | Re: [HACKERS] Refactor handling of database attributes between pg_dump and pg_dumpall |