pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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#L31
https://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#L37
https://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.





view thread (6+ messages)  latest in thread

Message-ID: <20200316.172230.2295109568745072390.horimoto@clear-code.com>
Permalink:  ../20200316.172230.2295109568745072390.horimoto@clear-code.com/
Also on:    postgresql.org/message-id/20200316.172230.2295109568745072390.horimoto@clear-code.com

 · 

reply

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