Message-ID: <4E025E54.6020402@engene.se>
Date: Wed, 22 Jun 2011 23:27:48 +0200
From: Marcus Engene <mengpg2@engene.se>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US;
	rv:1.9.1.16) Gecko/20101125 Thunderbird/3.0.11
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: bitmask index
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi list,

I use Postgres 9.0.4.

I have some tables with bitmask integers. Set bits are the interesting 
ones. Usually they are sparse.

-- Many rows & columns
CREATE TABLE a_table
(
  objectid                   INTEGER PRIMARY KEY NOT NULL
,misc_bits                  INTEGER DEFAULT 0 NOT NULL
...
)
WITHOUT OIDS;

...and when I use it I...

select
     ...
from
     a_table
where
     0 <> (misc_bits & (1 << 13))

Now the dear tables have swollen and these scans aren't as nice anymore.

What indexing strategies would you use here?

External table?:

create table a_table_feature_x
(
  objectid                   INTEGER PRIMARY KEY NOT NULL -- fk to 
a_table.objectid
)
WITHOUT OIDS;


Internal in the big mama table?:

CREATE TABLE a_table
(
  objectid                   INTEGER PRIMARY KEY NOT NULL
,misc_bits                  INTEGER DEFAULT 0 NOT NULL
,feature_x                  VARCHAR(1) -- 'y' or null
...
)
WITHOUT OIDS;

CREATE INDEX a_table_x1 ON a_table(feature_x); -- I assume nulls are not 
here


Some other trick?


Thanks,
Marcus