pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dmitry Tkach <dmitry@openratings.com>
To: fcanedo@hotpop.com
Cc: pgsql-admin@postgresql.org
Cc: pgsql-general@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: Btree index extension question
Date: Fri, 15 Mar 2002 18:09:07 -0500
Message-ID: <3C927F13.3000007@openratings.com> (raw)
References: <Pine.LNX.4.33.0203152152070.845-100000@icarus.hades>

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 :-(





view thread (12+ messages)  latest in thread

Message-ID: <3C927F13.3000007@openratings.com>
Permalink:  ../3C927F13.3000007@openratings.com/
Also on:    postgresql.org/message-id/3C927F13.3000007@openratings.com

 · 

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: dmitry@openratings.com, fcanedo@hotpop.com, pgsql-admin@postgresql.org, pgsql-general@postgresql.org
  Subject: Re: Btree index extension question
  In-Reply-To: <3C927F13.3000007@openratings.com>

* 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