Shaul Dar <shauldar@gmail.com> writes:
> I have at column that is a bit array of 16, each bit specifying if a certain
> property, out of 16, is present or not. Our typical query select 300
> "random" rows (could be located in different blocks) from the table based on
> another column+index, and then filters them down to ~50 based on this the
> bit field. Currently we have 16 separate indexes built on each bit, and on
> our 25M rows table each index takes about 880MB for a total of 14GB!
Ouch. One possibility is to replace the bitarray with an integer array
(k is in the int[] array iff bit k was set in the bitarray) and then use
the GIST or GIN indexing capabilities of contrib/intarray. I also seem
to recall having seen a module that provides GIST indexing for bitops
on plain integers --- have you looked on pgfoundry?
This isn't necessarily better than what you're doing, as btree indexes
are a lot better optimized than GIST/GIN. But it would be worth looking
into.
regards, tom lane