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 - Mailing list pgsql-bugs

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.00loops=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.00loops=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';