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
	
	
	
best regards

michael

7 Apr 2004 06:01:03 -0700
geek_1981@yahoo.de (Michael Groth)
using  index on comparison with bit-operation?
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.

Thu, 8 Apr 2004 16:41:40 -0500
Bruno Wolff III <bruno@wolff.to>
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, Pennsylvania 19073

Thu, 8 Apr 2004 17:45:14 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: using  index on comparison with bit-operation?
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, Pennsylvania 19073

Thu, 8 Apr 2004 18:59:51 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: using  index on comparison with bit-operation?