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
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 |