Received: from jupiter.hub.org (jupiter.hub.org [64.49.215.5]) by postgresql.org (Postfix) with ESMTP id 53D7A475D3A; Fri, 15 Mar 2002 11:36:00 -0500 (EST) Received: (from news@localhost) by jupiter.hub.org (8.11.6/8.11.6) id g2FGZ7o26878; Fri, 15 Mar 2002 10:35:07 -0600 (CST) (envelope-from news) From: Dmitry Tkach X-Newsgroups: comp.databases.postgresql.general, comp.databases.postgresql.novice, comp.databases.postgresql.questions, comp.databases.postgresql.sql, comp.databases.postgresql.admin Subject: Btree index extension question Date: Fri, 15 Mar 2002 13:04:21 -0500 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 57 Message-ID: <3C9237A5.6020104@openratings.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: news@hub.org User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:0.9.7+) Gecko/20020123 X-Accept-Language: en-us To: pgsql-admin@postgresql.org.pgsql-general@postgresql.org.pgsql-sql@postgresql.org X-Archive-Number: 200203/264 X-Sequence-Number: 3798 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