Re: BUG #16303: A condtion whether an index-only scan is possibleincludes a wrong - Mailing list pgsql-bugs
From | Horimoto Yasuhiro |
---|---|
Subject | Re: BUG #16303: A condtion whether an index-only scan is possibleincludes a wrong |
Date | |
Msg-id | 20200316.150533.1702058889215288265.horimoto@clear-code.com Whole thread Raw |
In response to | BUG #16303: A condtion whether an index-only scan is possible includes a wrong (PG Bug reporting form <noreply@postgresql.org>) |
Responses |
Re: BUG #16303: A condtion whether an index-only scan is possibleincludes a wrong
("David G. Johnston" <david.g.johnston@gmail.com>)
|
List | pgsql-bugs |
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! > 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;
pgsql-bugs by date: