X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 70A2E329CE3 for ; Thu, 16 Sep 2004 17:46:54 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 93447-04 for ; Thu, 16 Sep 2004 16:46:44 +0000 (GMT) Received: from omnis-mail.omnis.com (omnis-mail.omnis.com [216.239.128.28]) by svr1.postgresql.org (Postfix) with ESMTP id 68010329D69 for ; Thu, 16 Sep 2004 17:46:44 +0100 (BST) Received: from [216.239.128.141] (daniel.omnis.com [216.239.128.141]) by omnis-mail.omnis.com (Postfix) with ESMTP id A1A791BFD7 for ; Thu, 16 Sep 2004 09:46:43 -0700 (PDT) Message-ID: <4149C373.6040808@omnis.com> Date: Thu, 16 Sep 2004 09:46:43 -0700 From: Daniel Ceregatti Organization: Omnis Network User-Agent: Mozilla Thunderbird 0.7.1 (X11/20040626) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Comparing user attributes with bitwise operators References: <200409160141.37604.patrick@phpforhire.com> <41494A94.5080800@familyhealth.com.au> In-Reply-To: <41494A94.5080800@familyhealth.com.au> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/166 X-Sequence-Number: 8294 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.