Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1jDl1M-0004at-HH for pgsql-bugs@arkaria.postgresql.org; Mon, 16 Mar 2020 08:22:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1jDl1L-0000R8-AS for pgsql-bugs@arkaria.postgresql.org; Mon, 16 Mar 2020 08:22:47 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1jDl1K-0000R0-H4 for pgsql-bugs@lists.postgresql.org; Mon, 16 Mar 2020 08:22:47 +0000 Received: from mail.clear-code.com ([153.126.206.245]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jDl1G-0006OQ-IY for pgsql-bugs@lists.postgresql.org; Mon, 16 Mar 2020 08:22:44 +0000 Received: from localhost (KD106181176027.au-net.ne.jp [106.181.176.27]) by mail.clear-code.com (Postfix) with ESMTPSA id 1CA1E81A; Mon, 16 Mar 2020 17:22:36 +0900 (JST) DKIM-Filter: OpenDKIM Filter v2.11.0 mail.clear-code.com 1CA1E81A DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=clear-code.com; s=default; t=1584346956; bh=Zt07TxAIUSC/0bkJ00jw06bMSYhpaLpAV2H9Y0hH4mM=; h=Date:To:Cc:Subject:From:In-Reply-To:References:From; b=wUVqNKM3NCwhLQ0Qf7q/zvlSCSPSyWguPZUrTTq+7I2BYS2KwIc4jGHIDplQoia38 DCizw+EyrefNvsnM1iy00JVoDXm1YnhIoXUlrW/Bo4OJWLbrMD6vg/Xn2umJoKZxPg JrtKIYQb49Xu7sClqNhD0fBp+SS8gxTmfM7xabOQ= Date: Mon, 16 Mar 2020 17:22:30 +0900 (JST) Message-Id: <20200316.172230.2295109568745072390.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 From: Horimoto Yasuhiro In-Reply-To: References: <16303-889581eb40fc0691@postgresql.org> <20200316.150533.1702058889215288265.horimoto@clear-code.com> X-Mailer: Mew version 6.8 on Emacs 26.1 Mime-Version: 1.0 Content-Type: Text/Plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Rspamd-Queue-Id: 1CA1E81A X-Spamd-Result: default: False [1.40 / 999.00]; ARC_NA(0.00)[]; FROM_HAS_DN(0.00)[]; MV_CASE(0.50)[]; FREEMAIL_ENVRCPT(0.00)[gmail.com]; TAGGED_RCPT(0.00)[]; MIME_GOOD(-0.10)[text/plain]; TO_DN_NONE(0.00)[]; TO_MATCH_ENVRCPT_ALL(0.00)[]; RCPT_COUNT_TWO(0.00)[2]; MID_CONTAINS_FROM(1.00)[]; FREEMAIL_TO(0.00)[gmail.com]; RCVD_COUNT_ZERO(0.00)[0]; FROM_EQ_ENVFROM(0.00)[]; MIME_TRACE(0.00)[0:+]; ASN(0.00)[asn:2516, ipnet:106.181.0.0/16, country:JP] X-Rspamd-Server: mail.clear-code.com List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk 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" 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 > wrote: > >> I send a patch for this problem.! >> >> From: PG Bug reporting form >> 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.