Re: bitwise storage and operations

On Sep 27, 2016, at 10:54 AM, Brian Dunavant wrote:

> db=# select 'foo' where (9 & 1) > 0;

A HA

Thank you Brian and David -- I didn't realize that you needed to do the comparison to the result.

(or convert the result as these work):

select 'foo' where (9 & 1)::bool;
select 'foo' where bool(9 & 1);

I kept trying to figure out how to run operators on "9" and "1" independently to create a boolean result. I either needed more coffee or less yesterday.

As a followup question...

Some searches suggested that Postgres can't use indexes of INTs for these comparisons, but could on bitwise string columns.

One of these tables has over 30MM rows, so I'm trying to avoid a seq scan as much as possible.

I thought of creating a function index that casts my column to a bitstring, and then tailors searches onto that. For example:

CREATE TEMPORARY TABLE example_toggle(
id int primary key,
toggle int default null
);
INSERT INTO example_toggle (id, toggle) VALUES (1, 1), (2, 2), (3, 3), (4, 5), (5, 8);
CREATE INDEX idx_example_toggle_toggle_bit ON example_toggle(cast(toggle as bit(4)));

While these selects work...

select * from example_toggle where (toggle & 1)::bool AND (toggle & 4)::bool;
select * from example_toggle where (toggle::bit(4) & 1::bit(4) <> 0::bit(4)) AND (toggle::bit(4) & 4::bit(4) <> 0::bit(4));

Only about 200k items have a flag right now (out of 30MM) so I thought of using a partial index on the set flags.

The only way I've been able to get an index on the not null/0 used is to do the following:

CREATE INDEX idx_example_toggle_toggle_bit ON example_toggle(cast(toggle as bit(4))) WHERE toggle <> 0;

then tweak the query with

select * from example_toggle where (toggle & 1)::bool AND (toggle & 4)::bool AND (toggle > 0);
select * from example_toggle where (toggle::bit(4) & 1::bit(4) <> 0::bit(4)) AND (toggle::bit(4) & 4::bit(4) <> 0::bit(4)) AND (toggle > 0);

obviously, the sample above is far too small for an index to be considered... but in general... is a partial index of "toggle <> 0" and then hinting with "toggle > 0" the best way to only index the values that are not null or 0?