Postgres Pro
Downloads
Home   >   mailing lists

Thread: using index on comparison with bit-operation?

using index on comparison with bit-operation?

From
geek_1981@yahoo.de (Michael Groth)
Date:
hi,

is it possible to use an index on the expression '(table_1.field &
table_2.field)::int > 0' ?

here's the whole query:

SELECT COUNT(*)
FROMusers AS users
JOINsearch_profile AS search_profile ON
(search_profile.bin_matching_field_0 &
users.bin_matching_field_0)::int > 0
WHEREusers.id = 190
best regards

michael

Re: using index on comparison with bit-operation?

From
Bruno Wolff III
Date:
On Wed, Apr 07, 2004 at 06:01:03 -0700, Michael Groth <geek_1981@yahoo.de> wrote:
> hi,
> 
> is it possible to use an index on the expression '(table_1.field &
> table_2.field)::int > 0' ?
> 
> here's the whole query:
> 
> SELECT 
>     COUNT(*)
> FROM
>     users AS users
> JOIN
>     search_profile AS search_profile ON
> (search_profile.bin_matching_field_0 &
> users.bin_matching_field_0)::int > 0
> WHERE
>     users.id = 190

In 7.4 you can create indexes on expressions.

Re: using index on comparison with bit-operation?

From
Bruce Momjian
Date:
Michael Groth wrote:
> hi,
> 
> is it possible to use an index on the expression '(table_1.field &
> table_2.field)::int > 0' ?
> 
> here's the whole query:
> 
> SELECT 
>     COUNT(*)
> FROM
>     users AS users
> JOIN
>     search_profile AS search_profile ON
> (search_profile.bin_matching_field_0 &
> users.bin_matching_field_0)::int > 0
> WHERE
>     users.id = 190

No, there is no way to use an index because the columns are in different
tables.  It is like saying:
tab1.col1 = tab2.col2

Now, you want them both to be true, so it is really:
tab1.col1 AND tab2.col2

and that can be indexed by separate indexes on col1 and col2.  Of
course, if many rows are true, the index will not be used because it is
faster to just look at all the rows with a sequential scan.


--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073

Re: using index on comparison with bit-operation?

From
Bruce Momjian
Date:
Bruno Wolff III wrote:
> On Wed, Apr 07, 2004 at 06:01:03 -0700,
>   Michael Groth <geek_1981@yahoo.de> wrote:
> > hi,
> > 
> > is it possible to use an index on the expression '(table_1.field &
> > table_2.field)::int > 0' ?
> > 
> > here's the whole query:
> > 
> > SELECT 
> >     COUNT(*)
> > FROM
> >     users AS users
> > JOIN
> >     search_profile AS search_profile ON
> > (search_profile.bin_matching_field_0 &
> > users.bin_matching_field_0)::int > 0
> > WHERE
> >     users.id = 190
> 
> In 7.4 you can create indexes on expressions.

But it uses two different tables.  You can't mix tables in an index, can
you?

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073