pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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) download 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;

view thread (6+ messages)  latest in thread

Message-ID: <20200316.150533.1702058889215288265.horimoto@clear-code.com>
Permalink:  ../20200316.150533.1702058889215288265.horimoto@clear-code.com/
Also on:    postgresql.org/message-id/20200316.150533.1702058889215288265.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, 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