From: Quan Zongliang <quanzongliang@yeah.net>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Bug: When user-defined AM is used, the index path cannot be selected correctly
Date: Wed, 17 Aug 2022 09:43:54 +0800
Message-ID: <f293b91d-1d46-d386-b6bb-4b06ff5c667b@yeah.net> (raw)
Hi,
1. When using extended PGroonga
CREATE EXTENSION pgroonga;
CREATE TABLE memos (
id boolean,
content varchar
);
CREATE INDEX idxA ON memos USING pgroonga (id);
2. Disable bitmapscan and seqscan:
SET enable_seqscan=off;
SET enable_indexscan=on;
SET enable_bitmapscan=off;
3. Neither ID = 'f' nor id= 't' can use the index correctly.
postgres=# explain select * from memos where id='f';
QUERY PLAN
--------------------------------------------------------------------------
Seq Scan on memos (cost=10000000000.00..10000000001.06 rows=3 width=33)
Filter: (NOT id)
(2 rows)
postgres=# explain select * from memos where id='t';
QUERY PLAN
--------------------------------------------------------------------------
Seq Scan on memos (cost=10000000000.00..10000000001.06 rows=3 width=33)
Filter: id
(2 rows)
postgres=# explain select * from memos where id>='t';
QUERY PLAN
-------------------------------------------------------------------
Index Scan using idxa on memos (cost=0.00..4.01 rows=2 width=33)
Index Cond: (id >= true)
(2 rows)
The reason is that these expressions are converted to BoolExpr and Var.
match_clause_to_indexcol does not use them to check boolean-index.
patch attached.
--
Quan Zongliang
Beijing Vastdata
Attachments:indxpath.patch (text/plain, 574B) downloaddiff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.cindex 7d176e7b00..31229ce16c 100644--- a/src/backend/optimizer/path/indxpath.c+++ b/src/backend/optimizer/path/indxpath.c@@ -2295,7 +2295,7 @@ match_clause_to_indexcol(PlannerInfo *root,
/* First check for boolean-index cases. */
opfamily = index->opfamily[indexcol];
- if (IsBooleanOpfamily(opfamily))+ if (IsBooleanOpfamily(opfamily) || IsA(clause, BoolExpr) || IsA(clause, Var))
{
iclause = match_boolean_index_clause(root, rinfo, indexcol, index);
if (iclause)
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-hackers@postgresql.org
Cc: quanzongliang@yeah.net
Subject: Re: Bug: When user-defined AM is used, the index path cannot be selected correctly
In-Reply-To: <f293b91d-1d46-d386-b6bb-4b06ff5c667b@yeah.net>
References: <f293b91d-1d46-d386-b6bb-4b06ff5c667b@yeah.net>
* 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