Re: making a pg store of 'multiple checkboxes' efficient

On Apr 18, 2007, at 4:28 AM, Alban Hertroys wrote:

> I got some good results using bitwise operations on an integer column.
> An index on such a column helps a great deal of course. What type of
> integer you need depends on how many booleans you (expect to) have.
>
> My operations were like "WHERE (value & 80) = 80" to match against the
> 7th and 5th bits, with value an int4 column. Query times are in the
> tens
> of milliseconds range.
>
> Admittedly I only got about that many records through joins with other
> tables I needed (meaning that my result set may at some point in time
> have been around as big as your data set), so the planner may have
> reduced the number of bitwise operations significantly for me.
>
> The actual number of bitwise values to compare was around 40,000
> integers * 25 mask values, but that got joined with over 1M records
> from
> another result set.
>
> A bitwise operator on a single column should (theoretically) have less
> overhead than integer/boolean operators on multiple columns. Computers
> are good at bitwise operations, after all.

Computers are good at bitwise operations, but software often has
scary implementations :)

thanks for the input. I'll definitely go this route. It was my
first thought, but there is almost no documentation out there for
this type of storage.

// Jonathan Vanasco

| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
| SyndiClick.com
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
| FindMeOn.com - The cure for Multiple Web Personality Disorder
| Web Identity Management and 3D Social Networking
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
| RoadSound.com - Tools For Bands, Stuff For Fans
| Collaborative Online Management And Syndication Tools
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -