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