From: Horimoto Yasuhiro <horimoto@clear-code.com>
To: david.g.johnston@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16303: A condtion whether an index-only scan is possible includes a wrong
Date: Mon, 16 Mar 2020 17:22:30 +0900 (JST)
Message-ID: <20200316.172230.2295109568745072390.horimoto@clear-code.com> (raw)
In-Reply-To: <CAKFQuwaJYP75bMkRZfFqJZaYLQULC_kkLB_nYc2Y3p3oUPOGQg@mail.gmail.com>
References: <16303-889581eb40fc0691@postgresql.org>
<20200316.150533.1702058889215288265.horimoto@clear-code.com>
<CAKFQuwaJYP75bMkRZfFqJZaYLQULC_kkLB_nYc2Y3p3oUPOGQg@mail.gmail.com>
Hello,
Thank you for your comments.
I should have explained much more about this case. Sorry.
In this case, gistcanreturn() returns false:
https://github.com/postgres/postgres/blob/master/src/backend/access/gist/gistget.c#L798-L799
Because tsvector_ops provides GIST_COMPRESS_PROC:
https://github.com/postgres/postgres/blob/master/src/include/access/gist.h#L31https://github.com/postgres/postgres/blob/master/src/include/catalog/pg_amproc.dat#L530-L532
and doesn't provide GIST_FETCH_PROC:and provides GIST_COMPRESS_PROC:
https://github.com/postgres/postgres/blob/master/src/include/access/gist.h#L37https://github.com/postgres/postgres/blob/master/src/include/catalog/pg_amproc.dat#L524-L543
Reurning false from amcanreturn() function such as gistcanreturn() means the index can't support
index-only scans on the given column:
https://www.postgresql.org/docs/12/index-functions.html
bool
amcanreturn (Relation indexRelation, int attno);
Check whether the index can support index-only scans on the given column, by returning the
indexed column values for an index entry in the form of an IndexTuple.
So I think that choosing index-only scan for this case (false is returned by gistcanreturn() case)
isn't expected behavior.
Background:
We're developing a PostgreSQL index, PGroonga: https://pgroonga.github.io/
It supports index-only scans for most supported types but doesn't support
index-only scan for jsonb type. Because we can't reconstruct the original data
from data in indexed data.
We found the problem I reported with PGroonga:
https://github.com/pgroonga/pgroonga/issues/101
This problem causes an error because PGroonga can't return
IndexScanDesc::xs_itup but IndexScanDesc::xs_want_itup is true.
Because PostgreSQL requests index-only scan even when PGroonga
returns false by amcanreturn().
We've introduced the following workaround:
https://github.com/pgroonga/pgroonga/commit/0390f79f810c44422940c4ef701c186055b4899e
It returns a NULL tuple instead of real tuple data.
IMO, it's better that PostgreSQL doesn't choose index-only scan in this case.
Or index developers need to implement workaround in each index.
thanks!
From: "David G. Johnston" <david.g.johnston@gmail.com>
Subject: Re: BUG #16303: A condtion whether an index-only scan is possible includes a wrong
Date: Sun, 15 Mar 2020 23:35:53 -0700
> On Sunday, March 15, 2020, Horimoto Yasuhiro <horimoto@clear-code.com>
> wrote:
>
>> I send a patch for this problem.!
>>
>> From: PG Bug reporting form <noreply@postgresql.org>
>> Subject: BUG #16303: A condtion whether an index-only scan is possible
>> includes a wrong
>> Date: Mon, 16 Mar 2020 06:02:25 +0000
>>
>> > I think that the condition of whether an index-only scan is possible
>> > includes a wrong.
>> >
>> > For example, in the following case, the index has no data to return.
>> Because
>> > the query doesn't use specify columns.
>> > However, the query planner choice index-only scan.
>>
>>
>
>> > In my opinion, we expected that the query planner doesn't choose an
>> > index-only scan in the above case.
>> >
>>
>>
> I don't see a behavioral bug here. I would expect that any index would be
> an acceptable match for a query whose set of return columns is the empty
> set. The empty set is a subset of all sets.
>
> David J.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: horimoto@clear-code.com, david.g.johnston@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16303: A condtion whether an index-only scan is possible includes a wrong
In-Reply-To: <20200316.172230.2295109568745072390.horimoto@clear-code.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox