Received: from exchange.hq.openratings.com (adsl-141-154-210-2.ba-dsg.net [141.154.210.2]) by postgresql.org (Postfix) with ESMTP id 0589D47632A; Fri, 15 Mar 2002 16:39:56 -0500 (EST) Received: from openratings.com (cancun.hq.openratings.com [10.0.0.225]) by exchange.hq.openratings.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id GPKPL5VW; Fri, 15 Mar 2002 16:40:13 -0500 Message-ID: <3C927F13.3000007@openratings.com> Date: Fri, 15 Mar 2002 18:09:07 -0500 From: Dmitry Tkach User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:0.9.7+) Gecko/20020123 X-Accept-Language: en-us MIME-Version: 1.0 To: fcanedo@hotpop.com Cc: pgsql-admin@postgresql.org, pgsql-general@postgresql.org, pgsql-sql@postgresql.org Subject: Re: Btree index extension question References: Content-Type: text/plain; charset=KOI8-R; format=flowed Content-Transfer-Encoding: 7bit X-Archive-Number: 200203/847 X-Sequence-Number: 22219 fcanedo@hotpop.com wrote: > >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) > Yeah... The thing is that I want to be able to the index. And to use the index, I need BOOLEAN operators (this seems to be the LEAST of my problems,but anyway) - so, I have to define 'wrappers' around the standard bitwise operations - e.g. a <<= b ---> a & b = a; > > >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. > Well ... that's the point - can't do that :-( You can create functional indexes in postgres (and anywhere else AFAIK), but the function must take a SINGLE parameter. In other words, the only way to do what I need would be to create 15 functions, like: check_bit_1 (x) return x & 1 = 1; check_bit_2 (x) return x & 2 = 2; etc... And then create 15 different indexes (one for each func). But even that would not be of much help, because I need to search by a COMBINATION of parameters, and need a COMPOUND index to do that, not a separate index for each attr... > 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. > I am not sure I understand this suggestion... If I make the index on all the columns, I would need to specify all the (leftmost) values in the search criteria to be able to use it, right? For example, suppose, I have an index on (a,b,c) - then select * from foo where a=bar and b=bar will work, but select * from foo where b=bar and c=bar will not... That's exactly my problem - I need to be able to search by any combination of the values - (a),(b),(c),(ab),(ac),(bc),(abc) ... only I have 15 of them - too many combinations to consider buidling indexes for any of them :-(