public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Ayush Tiwari <[email protected]>
Cc: 王跃林 <[email protected]>
Cc: pgsql-bugs <[email protected]>
Cc: 3764353996 <[email protected]>
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
Date: Fri, 03 Jul 2026 13:01:19 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
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>
	<[email protected]>
	<CAJTYsWX1C5JLj92QdqEcArWs4XtXPog+yvyb6=X=_cghjXnkYQ@mail.gmail.com>
	<[email protected]>
	<CAJTYsWVus+7hn846A0gMJYk+9uUGZxizk5Herbxb4CmhF3vndQ@mail.gmail.com>
	<[email protected]>
	<CAJTYsWUg+ZH=W5EduH1AEDfJU0waO4uU4hakaiOF+KKUKFbR9g@mail.gmail.com>
	<[email protected]>

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=0.28..219.00 rows=3584 width=0) (actual time=0.146..1.895 rows=3200.00 loops=1)
         Index Cond: (a > '11111'::bit varying)
         Heap Fetches: 0
         Index Searches: 1
         Buffers: shared hit=1 read=110

and this with the bitcmp patch:

   ->  Index Only Scan using varbitidx on varbittmp  (cost=0.28..267.00 rows=3584 width=0) (actual time=0.081..0.955 rows=3200.00 loops=1)
         Index Cond: (a > '11111'::bit varying)
         Heap Fetches: 0
         Index Searches: 1
         Buffers: shared hit=1 read=47

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


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


Attachments:

  [text/plain] btree-gist-bit-index-build.sql (773B, ../[email protected]/2-btree-gist-bit-index-build.sql)
  download | inline:
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';

view thread (13+ messages)  latest in thread

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: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  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: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox