pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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) download diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c index 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)

view thread (5+ messages)  latest in thread

Message-ID: <f293b91d-1d46-d386-b6bb-4b06ff5c667b@yeah.net>
Permalink:  ../f293b91d-1d46-d386-b6bb-4b06ff5c667b@yeah.net/
Also on:    postgresql.org/message-id/f293b91d-1d46-d386-b6bb-4b06ff5c667b@yeah.net

 ·  · 

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 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