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 1jDisw-0006qI-OC for pgsql-bugs@arkaria.postgresql.org; Mon, 16 Mar 2020 06:05:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1jDisv-00080n-H0 for pgsql-bugs@arkaria.postgresql.org; Mon, 16 Mar 2020 06:05:57 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1jDisu-0007zD-Qy for pgsql-bugs@lists.postgresql.org; Mon, 16 Mar 2020 06:05:57 +0000 Received: from 153.126.206.245.v6.sakura.ne.jp ([2401:2500:102:3039:153:126:206:245] helo=mail.clear-code.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jDisq-0008Gy-Ca for pgsql-bugs@lists.postgresql.org; Mon, 16 Mar 2020 06:05:56 +0000 Received: from localhost (KD106181176027.au-net.ne.jp [106.181.176.27]) by mail.clear-code.com (Postfix) with ESMTPSA id 8991B8F888 for ; Mon, 16 Mar 2020 15:05:42 +0900 (JST) DKIM-Filter: OpenDKIM Filter v2.11.0 mail.clear-code.com 8991B8F888 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=clear-code.com; s=default; t=1584338742; bh=6W67jURHsz4dshh3lcalicz11/VZjBg/0y4E8ZbNGuA=; h=Date:To:Subject:From:In-Reply-To:References:From; b=JWbjIsGicb8LGoO+L3YcwTKVhfdz1Vpt0CEk3rXvpCu7mmbAHyPDJyTbkxsZG1iNv pzK8J0HdDGE1vhMLFEYutC0Elzq73awptfS1/rger9/LVbRMdaBseBuumFUb3TeKUz aYLJ4wrYKCYwy7zm3PqxcAZovgiSny27aFjpJ4AE= Date: Mon, 16 Mar 2020 15:05:33 +0900 (JST) Message-Id: <20200316.150533.1702058889215288265.horimoto@clear-code.com> To: 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: <16303-889581eb40fc0691@postgresql.org> References: <16303-889581eb40fc0691@postgresql.org> X-Mailer: Mew version 6.8 on Emacs 26.1 Mime-Version: 1.0 Content-Type: Multipart/Mixed; boundary="--Next_Part(Mon_Mar_16_15_05_33_2020_951)--" Content-Transfer-Encoding: 7bit X-Rspamd-Queue-Id: 8991B8F888 X-Spamd-Result: default: False [1.40 / 999.00]; ARC_NA(0.00)[]; FROM_HAS_DN(0.00)[]; MV_CASE(0.50)[]; TO_MATCH_ENVRCPT_ALL(0.00)[]; MIME_GOOD(-0.10)[multipart/mixed,text/plain,text/x-patch]; TO_DN_NONE(0.00)[]; RCPT_COUNT_ONE(0.00)[1]; MID_CONTAINS_FROM(1.00)[]; RCVD_COUNT_ZERO(0.00)[0]; FROM_EQ_ENVFROM(0.00)[]; MIME_TRACE(0.00)[0:+,1:+,2:+]; 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 ----Next_Part(Mon_Mar_16_15_05_33_2020_951)-- Content-Type: Text/Plain; charset=us-ascii Content-Transfer-Encoding: 7bit I send a patch for this problem. thanks! 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 > The following bug has been logged on the website: > > Bug reference: 16303 > Logged by: Horimoto Yasuhiro > Email address: horimoto@clear-code.com > PostgreSQL version: 12.2 > Operating system: Debian 10.3 > Description: > > Hello, developers. > > 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. > > create table gist_count_tbl (tsv tsvector); > insert into gist_count_tbl values (null); > create index gist_count_tbl_index on gist_count_tbl using gist (tsv); > > vacuum analyze gist_count_tbl; > > set enable_seqscan=off; > set enable_bitmapscan=off; > set enable_indexonlyscan=on; > > explain (costs off) > select count(*) from gist_count_tbl; > QUERY PLAN > -------------------------------------------------------------------- > Aggregate > -> Index Only Scan using gist_count_tbl_index on gist_count_tbl > (2 rows) > > In my opinion, we expected that the query planner doesn't choose an > index-only scan in the above case. > > In fact, index_canreturn_attrs of > https://github.com/postgres/postgres/blob/master/src/backend/optimizer/path/indxpath.c#L1951 > is NULL in the above case. > > thanks! > ----Next_Part(Mon_Mar_16_15_05_33_2020_951)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="fix_check_index_only_scan.patch" diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c index 2a50272da6..8fd3df8d69 100644 --- a/src/backend/optimizer/path/indxpath.c +++ b/src/backend/optimizer/path/indxpath.c @@ -1960,8 +1960,12 @@ check_index_only(RelOptInfo *rel, IndexOptInfo *index) index_canreturn_attrs = bms_del_members(index_canreturn_attrs, index_cannotreturn_attrs); - /* Do we have all the necessary attributes? */ - result = bms_is_subset(attrs_used, index_canreturn_attrs); + if (index_canreturn_attrs == NULL) + /* We don't have indexes that can return attributes. */ + result = false; + else + /* Do we have all the necessary attributes? */ + result = bms_is_subset(attrs_used, index_canreturn_attrs); bms_free(attrs_used); bms_free(index_canreturn_attrs); diff --git a/src/test/regress/expected/gist.out b/src/test/regress/expected/gist.out index 90edb4061d..a1b9fea2c2 100644 --- a/src/test/regress/expected/gist.out +++ b/src/test/regress/expected/gist.out @@ -317,3 +317,30 @@ reset enable_seqscan; reset enable_bitmapscan; reset enable_indexonlyscan; drop table gist_tbl; +-- +-- Test Index-only plans on GiST indexes when columns needless query. +-- +create table gist_count_tbl (tsv tsvector); +insert into gist_count_tbl values (null); +create index gist_count_tbl_index on gist_count_tbl using gist (tsv); +vacuum analyze gist_count_tbl; +set enable_seqscan=off; +set enable_bitmapscan=off; +set enable_indexonlyscan=on; +-- Test that an index-only scan is not chosen, +-- when the query doesn't use specify columns. +-- Because the index has not data to return in this case. +explain (costs off) +select count(*) from gist_count_tbl; + QUERY PLAN +---------------------------------- + Aggregate + -> Seq Scan on gist_count_tbl +(2 rows) + +-- Clean up +reset enable_seqscan; +reset enable_bitmapscan; +reset enable_indexonlyscan; +drop index gist_count_tbl_index; +drop table gist_count_tbl; diff --git a/src/test/regress/sql/gist.sql b/src/test/regress/sql/gist.sql index b9d398ea94..09d4372a7f 100644 --- a/src/test/regress/sql/gist.sql +++ b/src/test/regress/sql/gist.sql @@ -148,3 +148,30 @@ reset enable_bitmapscan; reset enable_indexonlyscan; drop table gist_tbl; + +-- +-- Test Index-only plans on GiST indexes when columns needless query. +-- +create table gist_count_tbl (tsv tsvector); +insert into gist_count_tbl values (null); +create index gist_count_tbl_index on gist_count_tbl using gist (tsv); + +vacuum analyze gist_count_tbl; + +set enable_seqscan=off; +set enable_bitmapscan=off; +set enable_indexonlyscan=on; + +-- Test that an index-only scan is not chosen, +-- when the query doesn't use specify columns. +-- Because the index has not data to return in this case. +explain (costs off) +select count(*) from gist_count_tbl; + +-- Clean up +reset enable_seqscan; +reset enable_bitmapscan; +reset enable_indexonlyscan; + +drop index gist_count_tbl_index; +drop table gist_count_tbl; ----Next_Part(Mon_Mar_16_15_05_33_2020_951)----