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?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
