pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Chinmay Kanchi <cgkanchi@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: GIN disagrees with seq scan when NOT and AND operators are combined
Date: Fri, 09 Oct 2026 16:27:44 -0400
Message-ID: <2924325.1791577664@sss.pgh.pa.us> (raw)
In-Reply-To: <CAJqPDh9Cm=x8eYZ1Jf=_avd2WEqc=0t_f7_SO4igo-CKPi25_Q@mail.gmail.com>
References: <CAJqPDh9Cm=x8eYZ1Jf=_avd2WEqc=0t_f7_SO4igo-CKPi25_Q@mail.gmail.com>

Chinmay Kanchi <cgkanchi@gmail.com> writes:
> When working on pg_lion (https://github.com/cgkanchi/pg_lion), I stumbled
> upon a bug where GIN returns fewer rows when multiple ! operators are
> specified. I was able to repro this on all versions from 16 to trunk.

Thanks for the report.  (For the archives' sake, note this repro
requires "set enable_seqscan = 0", else you get a seqscan plan
and no bug.)

I'm not too familiar with this code, but it looks like
extract_jsp_bool_expr concludes that !($.b == 20) is not supported
as a GIN scan condition, and then the next recursion level up does
the wrong thing with the negated && condition.  The attached quick
hack seems to fix it.  I didn't look around for other instances
of the same thinko, but there might be some.

			regards, tom lane

Attachments:

wip-fix-jsonb-gin-not-semantics.patch (text/x-diff, 1.1K) download diff --git a/src/backend/utils/adt/jsonb_gin.c b/src/backend/utils/adt/jsonb_gin.c index f5dbd5589d3..3efc4943d65 100644 --- a/src/backend/utils/adt/jsonb_gin.c +++ b/src/backend/utils/adt/jsonb_gin.c @@ -591,6 +591,7 @@ extract_jsp_bool_expr(JsonPathGinContext *cxt, JsonPathGinPath path, case jpiAnd: /* expr && expr */ case jpiOr: /* expr || expr */ { + bool is_and = (not ^ (jsp->type == jpiAnd)); JsonPathItem arg; JsonPathGinNode *larg; JsonPathGinNode *rarg; @@ -604,13 +605,18 @@ extract_jsp_bool_expr(JsonPathGinContext *cxt, JsonPathGinPath path, if (!larg || !rarg) { - if (jsp->type == jpiOr) + /* + * For OR, we have to fail if either arm is unsupported. + * For AND, we can just ignore the unsupported arm and + * apply the supported one. + */ + if (!is_and) return NULL; return larg ? larg : rarg; } - type = not ^ (jsp->type == jpiAnd) ? JSP_GIN_AND : JSP_GIN_OR; + type = is_and ? JSP_GIN_AND : JSP_GIN_OR; return make_jsp_expr_node_binary(type, larg, rarg); }

view thread (2+ messages)

Message-ID: <2924325.1791577664@sss.pgh.pa.us>
Permalink:  ../2924325.1791577664@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/2924325.1791577664@sss.pgh.pa.us

 ·  · 

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-bugs@postgresql.org
  Cc: tgl@sss.pgh.pa.us, cgkanchi@gmail.com
  Subject: Re: GIN disagrees with seq scan when NOT and AND operators are combined
  In-Reply-To: <2924325.1791577664@sss.pgh.pa.us>
  References: <CAJqPDh9Cm=x8eYZ1Jf=_avd2WEqc=0t_f7_SO4igo-CKPi25_Q@mail.gmail.com> <2924325.1791577664@sss.pgh.pa.us>

* 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