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
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
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
sfpug by date
|Next:||From: elein||Date: 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 Ghidinelli||Date: 2007-10-15 21:19:15|
|Subject: Re: Using bitwise operator vs. mapping table|