Re: Using bitwise operator vs. mapping table

Josh Berkus wrote:
> Actually, for this approach you want to use an INTARRAY and not a bitmap,
> becuase we have special indexes (based on GIST or GIN) for INTARRAY. In
> theory, one could create a GIST index for bitmaps, but nobody's done it yet.

I've never used the array types before; I'll read up on it.

> So the first question to ask before even considering denormalization is, are
> your search queries, in fact, slow?

They aren't terrible today and I suspect there is some low hanging
optimization that could be collected by a real DBA. However, we're
growing and our users run quite a lot of reports which already do 5 or
6-way joins. I'm considering bit/intarray to avoid making it an 8-way
join (there are two fields we're going many-to-many with on this central
"registration" table).

> easier. Using the denormailzed approach (intarrays) will make specific
> queries faster.

Are there any benefits other than speed in the denormalized approach?
One potential I see is that we can determine the various member types
from a single record which I can see being convenient for reporting and
decision making.

Once I get through this PCI DSS effort I may hire someone to get that
low-hanging fruit but I am trying to keep this other dev work moving
forward in the mean time.

Brian