One of the possibilities would be to decompose your bitmap into an
array of base integers and then create a GIN (or GIST) index on that
array (intarray contrib package). This would make sense if your
articles are distributed relatively equally and if do not do big ORDER
BY and then LIMIT/OFFSET queries, that usually will need to sort the
results gotten from the GIN index.
As your are also probably doing some tsearch queries on the articles,
you can actually build combined (tverctor, intarray) GIN/GIST index to
optimize your searches.
A simple function, that can help you stripping your bitmap integer to
array of positions could look like:
-- DROP FUNCTION utils.bitmap_to_position_intarray(bitmap integer);
CREATE OR REPLACE FUNCTION utils.bitmap_to_position_intarray(bitmap
integer)
RETURNS integer[] AS
$BODY$
-- test
-- select utils.bitmap_to_position_intarray(5);
-- test performance
-- select utils.bitmap_to_position_intarray(s.i) from
generate_series(1, 10000) as s(i);
--
SELECT ARRAY(
SELECT s.i + 1 -- here we do +1 to make the position of the first
bit 1
FROM generate_series(0, 31) as s(i)
WHERE $1 & ( 1 << s.i ) > 0
);
$BODY$
LANGUAGE SQL IMMUTABLE STRICT;
You can create a GIN index directly using this function over your
bitmap field and then using array set operations will make the planner
to use the GIN index (more information about these indexes here:
http://www.postgresql.org/docs/8.4/interactive/textsearch-indexes.html):
CREATE INDEX idx_article_status_gin ON article USING
gin( (utils.bitmap_to_position_intarray(STATUS) ) );
and then you can do:
SELECT * FROM article WHERE utils.bitmap_to_position_intarray(STATUS)
&& ARRAY[1,5];
or
SELECT * FROM article WHERE utils.bitmap_to_position_intarray(STATUS)
&& utils.bitmap_to_position_intarray(5);
Have a look on the possible array set operations in
http://www.postgresql.org/docs/8.4/interactive/intarray.html.
Otherwise a solution from Jeo Conway to create separate indexes for
each bit also is worth to be looked up. This has actually drawbacks,
that you cannot look up combinations of bits efficiently. As an
advantage in the example from Jeo, you can efficiently do ORDER BY
article (or any other field, that you add into these limited
indexes).
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
