Re: Using bitwise operator vs. mapping table

On Mon, Oct 15, 2007 at 02:19:15PM -0700, Brian Ghidinelli wrote:
> 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.

The word "optimize," is usually a synonym for, "give up on getting the
whole thing right." Don't do it unless forced to.

> However, we're growing and our users run quite a lot of reports
> which already do 5 or 6-way joins.

Is this actually causing a problem?

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

No, and there are a lot of maintenance and data integrity problems
inherent in it.

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

Premature optimization is the root of all evil.
Donald E. Knuth,
ACM Journal Computing Surveys
Vol 6, No. 4, Dec. 1974. p.268

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate