Skip site navigation (1) Skip section navigation (2)

Re: Using bitwise operator vs. mapping table

From: David Fetter <david(at)fetter(dot)org>
To: SF Postgres <sfpug(at)postgresql(dot)org>
Subject: Re: Using bitwise operator vs. mapping table
Date: 2007-10-15 22:14:27
Message-ID: 20071015221427.GN27311@fetter.org (view raw or flat)
Thread:
Lists: sfpug
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

In response to

Responses

sfpug by date

Next:From: eleinDate: 2007-10-15 23:46:49
Subject: [ameli@CS.Berkeley.EDU: DB Seminar: Friday 10/19, 1-2:30pm, 606 Soda Hall]
Previous:From: Brian GhidinelliDate: 2007-10-15 21:19:15
Subject: Re: Using bitwise operator vs. mapping table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group