pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
To: Dmitry Tkach <dmitry@openratings.com>
To: pgsql-sql@postgresql.org
Subject: Re: Btree index extension question
Date: Sun, 17 Mar 2002 11:51:22 +0800
Message-ID: <00c901c1cd67$01cf1a40$0200a8c0@SOL> (raw)
References: <3C923B8B.7040705@openratings.com>

Why don't you use the 'varbit' or 'bit' type?

Chris

----- Original Message -----
From: "Dmitry Tkach" <dmitry@openratings.com>
To: <pgsql-sql@postgresql.org>
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
>




view thread (12+ messages)  latest in thread

Message-ID: <00c901c1cd67$01cf1a40$0200a8c0@SOL>
Permalink:  ../00c901c1cd67$01cf1a40$0200a8c0@SOL/
Also on:    postgresql.org/message-id/00c901c1cd67$01cf1a40$0200a8c0@SOL

 · 

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-sql@postgresql.org
  Cc: chriskl@familyhealth.com.au, dmitry@openratings.com
  Subject: Re: Btree index extension question
  In-Reply-To: <00c901c1cd67$01cf1a40$0200a8c0@SOL>

* 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