Re: using index on comparison with bit-operation?

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