Received: from exchange.hq.openratings.com (adsl-141-154-210-2.ba-dsg.net [141.154.210.2]) by postgresql.org (Postfix) with ESMTP id 05F1D475AB1 for ; Fri, 15 Mar 2002 11:51:43 -0500 (EST) Received: from openratings.com (cancun.hq.openratings.com [10.0.0.225]) by exchange.hq.openratings.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id GPKPL5A1; Fri, 15 Mar 2002 11:52:04 -0500 Message-ID: <3C923B8B.7040705@openratings.com> Date: Fri, 15 Mar 2002 13:20:59 -0500 From: Dmitry Tkach User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:0.9.7+) Gecko/20020123 X-Accept-Language: en-us MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Btree index extension question Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Archive-Number: 200203/198 X-Sequence-Number: 7078 Hi, everybody! I was wonderring if there is somebody out there who could help me with understand how index extensions work... Let me state the problem first. I have many (15) boolean attributes and I need to be able to search the database for entries with any combination of those attributes for being true. For example - find all the entries, where a1=a2=a3=true or find all the entries where a1=a2=a4=true etc... Because there are so many of them (and the database is HUGE), putting every attribute into a separate column and creating a separate index on every possible combination, is really out of the question. So, I was thinking about creating a single int2 column, with each bit representing an attribute - so that, the first query I quoted above would look like "select * from table where attributes & 7 = 7", and the other query would be "select * from table where attributes & 11 = 11' etc... This looked so beautiful to me, but now I am stuck trying to index that table [:-(] I started off, hoping to get away with btrees. I defined an operator >>=(int2,int2) as 'select $1&$2=$2;' It looks nice so far, but then the question is - what do I do with the other operations? By analogy with 'normal' comparison operators, I would do: >> (I know the name is taken [:-)] as 'select not $2 >>= $1' =<< as 'select $2 >>= $1' << as 'select not $1 >>= $2' ... and leave '=' intact. But then I realized, that these set of operators, does not really define a complete order - for example, if I compare, say, 5 and 3: 5 & 3 = 1, 3 & 5 = 1, so I get BOTH 5 << 3 and 5 >> 3 being true at the same time [:-(] So my question is, first of all, is that a problem? Does btree require a complete order defined? Will it work with partial order? Secondly, if it is a problem, perhaps, I am missing something here, assuming that there is no way to define a set of operations to do what I want and provide a completely ordered set (or do I need it to define a perfect complete order - what exactly is required for btree to work? Any ideas?) And finally, if there is just no way I could get away with btrees, can I make an rtree to work for me? Could somebody explain to me (or point me to a doc somewhere) the meaning of the strategies (and requirements - like transitivity etc...) I need for an rtree, and also what support functions (like comparison func in case of a btree) do I need? Thank you very much for your attention. Any input will be greatly appreciated. Dima