From: Daniel Ceregatti <daniel@omnis.com>
To: pgsql-performance@postgresql.org
Subject: Re: Comparing user attributes with bitwise operators
Date: Thu, 16 Sep 2004 09:46:43 -0700
Message-ID: <4149C373.6040808@omnis.com> (raw)
In-Reply-To: <41494A94.5080800@familyhealth.com.au>
References: <200409160141.37604.patrick@phpforhire.com>
<41494A94.5080800@familyhealth.com.au>
Christopher Kings-Lynne wrote:
> Sounds like you want a many-to-many table that maps user_ids to match_ids
>
> Then you can put an index over (user_id, match_id) and the search will
> be very fast.
>
> Chris
>
If I understand you correctly, I believe I've tried this approach. While
matching on a single attribute and a single value was indeed very fast
and used an index, as soon as I tried to match on more than one value
(where valueid in (1, 2, 3)) the index was no longer used. Since my
approach used ints, I used in(), which is effectively "or", which is
presumably why the index is no longer used. With the bit, one would do a
bitwise "or" (where value & search = value). This cannot be easily
indexed, afaik.
The other problem I had with a 1:many table, where there was a row for
every person's attributes (~20M rows) was that somehow time was lost in
either sorting or somewhere else. Individual queries against a single
attribute would be very fast, but as soon as I tried to join another
attribute, the query times got really bad. See http://sh.nu/w/email.txt
line 392 (Don't worry, there are line numbers in the page).
So far I've stuck with my original plan, which is to maintain a 1:1
table of people:attributes where each attribute is in its own column.
Still, no index is used, but it's been the best performer up to now.
I'm still looking for a better plan though.
Daniel
--
Daniel Ceregatti - Programmer
Omnis Network, LLC
You are fighting for survival in your own sweet and gentle way.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: daniel@omnis.com
Subject: Re: Comparing user attributes with bitwise operators
In-Reply-To: <4149C373.6040808@omnis.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox