From: | Dmitry Tkach <dmitry(at)openratings(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org(dot)pgsql-general(at)postgresql(dot)org(dot)pgsql-sql(at)postgresql(dot)org |
Subject: | Btree index extension question |
Date: | 2002-03-15 18:04:21 |
Message-ID: | 3C9237A5.6020104@openratings.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-general pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Hernan Nuñez | 2002-03-15 20:57:08 | Question .. |
Previous Message | Tom Lane | 2002-03-15 17:53:13 | Re: Errors on VACUUM |
From | Date | Subject | |
---|---|---|---|
Next Message | John Gray | 2002-03-15 18:07:32 | Re: Archives / News gateway |
Previous Message | Ron Snyder | 2002-03-15 18:03:17 | Re: Unexplainable slow down... SOLVED (we think) |
From | Date | Subject | |
---|---|---|---|
Next Message | Frederick Klauschen | 2002-03-15 18:17:39 | JDBC for J2ME |
Previous Message | Ken Corey | 2002-03-15 17:29:32 | How do I set the system time on production server? |