Re: Btree index extension question - Mailing list pgsql-general

Hi,

Don't know enough about postgresql to be sure about this, but here goes:

If postgresql does bitwise operations, then you can use that instead of
defining new operators. Just construct a number for all the columns that
need to be true and do a bitwise 'and' with the stored value. (eg. (7 &
stored_val) = 7)

If postgresql uses an index to supply functions with their parameters,
then make a function that'll do the comparison for you and use it in your
query.  Or make the index (on all the columns) and make a function that
takes all the columns as the parameters to compare against (and ofcourse
the values that you want to check against). That way you always use the
columns of the index in the correct order.

Somebody please check this, as I may have been hit with a stupid-stick.

On Fri, 15 Mar 2002, Dmitry Tkach wrote:

> 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 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>