bitmask index

From: Marcus Engene <mengpg2(at)engene(dot)se>
To: pgsql-performance(at)postgresql(dot)org
Subject: bitmask index
Date: 2011-06-22 21:27:48
Message-ID: 4E025E54.6020402@engene.se
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Greg Smith 2011-06-22 21:35:39 Re: seq scan in the case of max() on the primary key column
Previous Message Tripura 2011-06-22 20:57:41 Re: Improve the Postgres Query performance