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.

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

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