Re: Storing and querying boolean fields

On Wed, Jan 09, 2008 at 06:19:40PM -0500, Brown, Richard wrote:
>
> - We are using PostgreSQL 7.3.4, and am locked into this version. I would
> upgrade if I could, but the decision is not mine.

I mean this sincerely and not snidely: get another job. 7.3.20 was the last
release in the 7.3 series. It's binary compatible with the 7.3.4 version
you have. There is _no_ reason not to have the latest patch level. If some
bonehead manager thinks you have to stick with the broken old software,
you're in deep trouble. In addition, the project has stopped maintaining
7.3. If you're still in testing, _now_ is the time to upgrade.

Also,

> I need to add many boolean flags to my database that need to be retrieved by
> primary key, as well as be searched upon with decent speed. By "many", I
> mean about 30 booleans total.

I have pretty grave doubts that what you have here is a good application for
a relational database.

> 1. Postgres will only use 1 index per table, so putting these fields in the
> same table as several other searchable fields actually hurts us more than
> helps.

Well, ancient versions of Postgres will only use 1 index per table, yes.
This is actually fixed in later releases. So if you come asking for help,
you're going to get the answer, "Upgrade." That's the right answer.

> 2. We haven't been able to get any index scans in preliminary testing (all
> seq. scans).

I'd bet the selectivity is going to be lousy anyway, given what you
described.

> After all of that background, my actual question is: what is the best way
> to effectively store and query this type of data? It seems more and more
> that our initial design is just flat-out wrong, but we are at a loss as to
> what the other options are.

I suspect the design is indeed wrong. I think you need to tell us what
you're trying to do rather than tell us what kind of data you want to store.
My guess is that your data _representation_ is wrong in the first place, and
if you re-think that, you'll find that there's a natural way to store it.
It's possible, though, that what you have is a kind of data that doesn't
lend itself to a relational model. The RDBMS sometimes turns into the
hammer people have, so they start banging away; when what they need is a
different data system.

A