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

Re: Using bitwise operator vs. mapping table

From: Brian Ghidinelli <brian(at)pukkasoft(dot)com>
To: sfpug(at)postgresql(dot)org
Subject: Re: Using bitwise operator vs. mapping table
Date: 2007-10-15 21:19:15
Message-ID: 4713D953.9@pukkasoft.com (view raw or flat)
Thread:
Lists: sfpug
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


In response to

Responses

sfpug by date

Next:From: David FetterDate: 2007-10-15 22:14:27
Subject: Re: Using bitwise operator vs. mapping table
Previous:From: Josh BerkusDate: 2007-10-14 21:07:19
Subject: Re: Using bitwise operator vs. mapping table

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