From: Marcus Engene <mengpg2@engene.se>
To: pgsql-performance@postgresql.org
Subject: bitmask index
Date: Wed, 22 Jun 2011 23:27:48 +0200
Message-ID: <4E025E54.6020402@engene.se> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mengpg2@engene.se
Subject: Re: bitmask index
In-Reply-To: <4E025E54.6020402@engene.se>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox