pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.




view thread (12+ messages)  latest in thread

Message-ID: <4149C373.6040808@omnis.com>
Permalink:  ../4149C373.6040808@omnis.com/
Also on:    postgresql.org/message-id/4149C373.6040808@omnis.com

 · 

reply

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