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

Re: Using bitwise operator vs. mapping table

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: sfpug(at)postgresql(dot)org
Subject: Re: Using bitwise operator vs. mapping table
Date: 2007-10-14 21:07:19
Message-ID: 200710141407.19905.josh@agliodbs.com (view raw or flat)
Thread:
Lists: sfpug
Brian,

> I am more familiar with the second method but I like the bits approach
> because it lets me do things like search for "any of the following event
> types" very easily and the number of types of events is limited to what
> will fit in a 32-bit integer.
>
> Any thoughts?  How does that type of bitwise operation perform in
> comparison to the mapping table (where the key may be either an integer
> or a UUID)?

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.

Whether or not to use such an approach is the usual question of 
denormalization.  That is, using the normalized approach (an attributes child 
table) will make data maintenance, validation, and future schema extensions 
easier.  Using the denormailzed approach (intarrays) will make specific 
queries faster.  

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

-- 
Josh Berkus
PostgreSQL @ Sun
San Francisco

In response to

Responses

sfpug by date

Next:From: Brian GhidinelliDate: 2007-10-15 21:19:15
Subject: Re: Using bitwise operator vs. mapping table
Previous:From: Brian GhidinelliDate: 2007-10-14 00:37:05
Subject: Using bitwise operator vs. mapping table

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