From: Horimoto Yasuhiro <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
Date: Mon, 16 Mar 2020 15:05:33 +0900 (JST)
Message-ID: <20200316.150533.1702058889215288265.horimoto@clear-code.com> (raw)
In-Reply-To: <16303-889581eb40fc0691@postgresql.org>
References: <16303-889581eb40fc0691@postgresql.org>
I send a patch for this problem.
thanks!
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
> 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!
>
Attachments:fix_check_index_only_scan.patch (text/x-patch, 2.9K) downloaddiff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.cindex 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.outindex 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.sqlindex 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;
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, 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.150533.1702058889215288265.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