Skip site navigation (1) Skip section navigation (2)

Question about index extensions.

From: Dmitry Tkach <dmitry(at)openratings(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Question about index extensions.
Date: 2002-03-14 20:00:51
Message-ID: 3C910173.8050100@openratings.com (view raw or flat)
Thread:
Lists: pgsql-novice
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







pgsql-novice by date

Next:From: Laurent NDate: 2002-03-15 15:41:52
Subject: few admin questions
Previous:From: Jules AlbertsDate: 2002-03-14 15:38:14
Subject: simple previous / next form in PHP

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group