Sunday, July 27, 2008

Re: [SQL] Re: Efficiently determining the number of bits set in the contents of, a VARBIT field

TJ O'Donnell wrote:
> I use a c function, nbits_set that will do what you need.
> I've posted the code in this email.
>
> TJ O'Donnell
> http://www.gnova.com
>
> #include "postgres.h"
> #include "utils/varbit.h"
>
> Datum nbits_set(PG_FUNCTION_ARGS);
> PG_FUNCTION_INFO_V1(nbits_set);
> Datum
> nbits_set(PG_FUNCTION_ARGS)
> {
> /* how many bits are set in a bitstring? */
>
> VarBit *a = PG_GETARG_VARBIT_P(0);
> int n=0;
> int i;
> unsigned char *ap = VARBITS(a);
> unsigned char aval;
> for (i=0; i < VARBITBYTES(a); ++i) {
> aval = *ap; ++ap;
> if (aval == 0) continue;
> if (aval & 1) ++n;
> if (aval & 2) ++n;
> if (aval & 4) ++n;
> if (aval & 8) ++n;
> if (aval & 16) ++n;
> if (aval & 32) ++n;
> if (aval & 64) ++n;
> if (aval & 128) ++n;
> }
> PG_RETURN_INT32(n);
> }
>
>
>
>> Hi all,
>> Am looking for a fast and efficient way to count the number of bits set
>> (to 1) in a VARBIT field. I am currently using
>> "LENGTH(REGEXP_REPLACE(CAST(a.somefield_bit_code AS TEXT),'0','','g'))".
>>
>> Allan.
>
>
When I had to do that, in days with smaller amounts of RAM, but very long
bit-vectors, I used a faster function sort-of like this:

static char table[256] = {
0,1,1,2,1,2,2,3,1,.....
};

Then like above, but instead of the loop,

n+= table[aval];


You get the idea.

--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ PGP-Key: 9A2FC99A Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 20:20:01 up 7 days, 1:08, 4 users, load average: 4.16, 4.15, 4.10

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

No comments: