Received: from mail.iinet.net.au (symphony-01.iinet.net.au [203.59.3.33]) by postgresql.org (Postfix) with SMTP id 9A094477420 for ; Sat, 16 Mar 2002 22:57:43 -0500 (EST) Received: (qmail 30375 invoked by uid 666); 17 Mar 2002 03:57:45 -0000 Received: from unknown (HELO SOL) (203.59.159.105) by mail.iinet.net.au with SMTP; 17 Mar 2002 03:57:45 -0000 Message-ID: <00c901c1cd67$01cf1a40$0200a8c0@SOL> From: "Christopher Kings-Lynne" To: "Dmitry Tkach" , References: <3C923B8B.7040705@openratings.com> Subject: Re: Btree index extension question Date: Sun, 17 Mar 2002 11:51:22 +0800 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200203/220 X-Sequence-Number: 7099 Why don't you use the 'varbit' or 'bit' type? Chris ----- Original Message ----- From: "Dmitry Tkach" To: Sent: Saturday, March 16, 2002 2:20 AM Subject: [SQL] Btree index extension question > 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 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >