Обсуждение: using index on comparison with bit-operation?
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
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, Pennsylvania19073
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