From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
cc: =?UTF-8?B?546L6LeD5p6X?= <violin0613@tju.edu.cn>,
        pgsql-bugs <pgsql-bugs@postgresql.org>, 3764353996 <3764353996@qq.com>
Subject: Re: Fw:Re: Fw: gbt_var_consistent in
 contrib/btree_gist/btree_utils_var.c has internal-node type confusion on the
 <> strategy, bypassing exclusion constraints
In-reply-to: <3854290.1783086630@sss.pgh.pa.us>
References: 
 <AH*AvQCYKhQGVvPWi1GiU4oY.8.1781609375063.Hmail.3020001251@tju.edu.cn>
 <CAJTYsWWSx+5-kVaivB_znaQCk3TmHrO4QNNM-CD3AN-oOSmG8w@mail.gmail.com>
 <AGoA0AD5KvwQ8G7e6ofMyap6.3.1781760245047.Hmail.3020001251@tju.edu.cn>
 <3748108.1783003096@sss.pgh.pa.us>
 <CAJTYsWX1C5JLj92QdqEcArWs4XtXPog+yvyb6=X=_cghjXnkYQ@mail.gmail.com>
 <3756776.1783011257@sss.pgh.pa.us>
 <CAJTYsWVus+7hn846A0gMJYk+9uUGZxizk5Herbxb4CmhF3vndQ@mail.gmail.com>
 <3793780.1783033337@sss.pgh.pa.us>
 <CAJTYsWUg+ZH=W5EduH1AEDfJU0waO4uU4hakaiOF+KKUKFbR9g@mail.gmail.com>
 <3854290.1783086630@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Fri, 03 Jul 2026 09:50:30 -0400"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <3879600.1783097914.0@sss.pgh.pa.us>
Date: Fri, 03 Jul 2026 13:01:19 -0400
Message-ID: <3879867.1783098079@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3879867.1783098079%40sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3879600.1783097914.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable

For the archives' sake: I tested the 0003 patch (sort with bitcmp
not byteacmp) with the attached script.  I get this with HEAD:

   ->  Index Only Scan using varbitidx on varbittmp  (cost=3D0.28..219.00 =
rows=3D3584 width=3D0) (actual time=3D0.146..1.895 rows=3D3200.00 loops=3D=
1)
         Index Cond: (a > '11111'::bit varying)
         Heap Fetches: 0
         Index Searches: 1
         Buffers: shared hit=3D1 read=3D110

and this with the bitcmp patch:

   ->  Index Only Scan using varbitidx on varbittmp  (cost=3D0.28..267.00 =
rows=3D3584 width=3D0) (actual time=3D0.081..0.955 rows=3D3200.00 loops=3D=
1)
         Index Cond: (a > '11111'::bit varying)
         Heap Fetches: 0
         Index Searches: 1
         Buffers: shared hit=3D1 read=3D47

You might get different hit-vs-read splits depending on your
shared_buffers setting, but the totals should be pretty stable.
So that's a clear win, and indeed running the same script on
v17 shows 50-some buffer accesses, so we did lose performance
from this mistake.

I don't see any win however for a bit(N) column where all the
entries are the same width.  This is unsurprising, since the
bit_len prefix is the same for all and so the bytea sort gives
the same ordering as bitcmp.  Possibly this explains our failure
to notice this problem earlier.

Anyway, 0003 seems quite solid so I'll go push that part.

			regards, tom lane


------- =_aaaaaaaaaa0
Content-Type: text/plain; name="btree-gist-bit-index-build.sql";
	charset="us-ascii"
Content-ID: <3879600.1783097914.2@sss.pgh.pa.us>
Content-Description: btree-gist-bit-index-build.sql

create extension if not exists btree_gist;

drop table if exists varbittmp;

CREATE TABLE varbittmp (a varbit);

insert into varbittmp
select repeat(i::bit(8)::text, j)::varbit from
  generate_series(0,255) i,
  generate_series(1,100) j;

insert into varbittmp select * from varbittmp;
insert into varbittmp select * from varbittmp;

vacuum analyze varbittmp;

select count(*) from varbittmp;

SELECT count(*) FROM varbittmp WHERE a > '11111';

CREATE INDEX varbitidx ON varbittmp USING GIST ( a );

select pg_size_pretty(pg_relation_size('varbittmp')) as table_size,
       pg_size_pretty(pg_indexes_size('varbittmp')) as index_size,
       pg_indexes_size('varbittmp') / 8192 as index_blocks;

explain (analyze, buffers)
SELECT count(*) FROM varbittmp WHERE a > '11111';

------- =_aaaaaaaaaa0--