Re: Index usage when bitwise operator is used - Mailing list pgsql-performance

Hi,

I could not find and normal solution for that issue. But I am using
some workarounds for that issue.

The solution, that I am using now is to create an index for every bit
of your bitmap field.

So something like

CREATE INDEX idx_hobbybit_0_limited
  ON "versionA".user_fast_index
  USING btree
  (gender, dateofbirth) -- here the gender and dateofbirth fields are
the fields that we usually ORDER BY in the select statements, but you
can play with the needed fields
  WHERE (hobby_bitmap & 1) > 0;

by creating such an index for every used bit and combining WHERE
(hobby_bitmap & 1 ) > 0 like statements the planner will be choosing
the right index to use.

Another workaround, that will be more applicable in your case I think,
is to create a functional GIN index on your bitmap field using a
static function to create an array of bitmap keys from your bitmap
field.

CREATE OR REPLACE FUNCTION
"versionA".bitmap_to_bit_array(source_bitmap integer)
  RETURNS integer[] AS
'select ARRAY( select (1 << s.i) from generate_series(0, 32) as s(i)
where ( 1 << s.i ) & $1 > 0 )'
  LANGUAGE 'sql' IMMUTABLE STRICT;

And than create a GIN index on the needed field using this stored
procedure. After that, it would be possible to use intarray set
operators on the result of that function. This will also make it
possible to use that GIN index.

Actually it would be much much better if it were possible to build GIN
indexes directly on the bitmap fields. But this is to be implemented
by GIN and GiST index development team. Probably would be not a bad
idea to make a feature request on them.


With best regards,

Valentine Gogichashvili

On Sep 13, 2:30 pm, wharo...@gmail.com ("W.Alphonse HAROUNY") wrote:
> Hello,
>
> My question is about index usage when bitwise operations are invoked.
> Situation Context:
> --------------------------
>
> Lets suppose we have 2 tables TBL1 and TBL2 as the following:
> TBL1 {
>   ......... ;
>   integer categoryGroup; // categoryGroup is declared as an index on TABL1
>   ......... ;
>
> }
>
> TBL2 {
>   ......... ;
>   integer categoryGroup; // categoryGroup is declared as an index on TABL2
>   ......... ;
>
> }
>
> By conception, I suppose that:
> - [categoryGroup] may hold a limited number of values, less than 32 values.
> - [categoryGroup] is of type integer => it means 4 bytes => 32 bits
>   => 32 places available to hold binary '0' or binary '1' values.
> - [categoryGroup] is the result of an "OR bitwise operation" among a
> predefined set of variables [variableCategory].
>    We suppose that [variableCategory] is of type integer (=>32 bits)
>    and each binary value of [variableCategory] may only hold a single binary
> '1'.
>
> Ex: variableCategory1 = 00000000000000000000000000000010
>       variableCategory2 = 00000000000000000000000000100000
>       variableCategory3 = 00000000000000000000000000001000
>
>      If [categoryGroup] =  variableCategory1 | variableCategory2 |
> variableCategory3
>     =>[categoryGroup] = 00000000000000000000000000101010
>
> Question:
> --------------
> I have an SQL request similar to:
>
> SELECT ..... FROM TBL1, TBL2 WHERE
>  <inner join between TBL1 and TBL2 is True> AND
>  TBL1.CATEGORY & TBL2.CATEGORY <> 0  //-- where & is the AND bitwise
> operator
>
> Qst:
> 1/ IS the above SQL request will use the INDEX [categoryGroup] defined on
> TBL1 and TBL2 ?
> 2/ What should I do or How should I modify my SQL request in order
>    to force the query engine to use an index ? (the already defined index or
> another useful index)
>
> Thx a lot