Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id CC4729F9C33 for ; Thu, 13 Sep 2007 09:34:38 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 06113-03 for ; Thu, 13 Sep 2007 09:34:35 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.250]) by postgresql.org (Postfix) with ESMTP id 999D09F9B9F for ; Thu, 13 Sep 2007 09:34:35 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c36so67231ana for ; Thu, 13 Sep 2007 05:34:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; bh=RMNN90GoviGN/U9Uo2a9mZca5iBN3g4kxnJYPaUV2pE=; b=T+bD2dMZAwRpitlNlTZ7eyq5mW1lT7UMOxVM10g1ntF55cdqVDN83hmauvAD2wcc/TRFt1rdg2CDZDRTkIuOWJFYzlyTdaDMld9B6ZfQjCJ2LyqRoJCkiFRFcDIQUENA8oSFV7QtJIbd4uEHJ5NqZ1trnVSFReEgJ/vMjlpwLpI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=uss88itoGMaH48djbp9vwSAQDzi3oZmBYG7pypXrMn95WpJYz532NC1137HTtQQrh5G1FfHh6R43/Dv7fFhM9ZmqECgjqYeVbgXsDHNIoXoIQnsmObhhIRNezrwTQiB9amuRyt3/qyuLnbB6uxj12lHD1NgTvTdrI+xos4L3rpQ= Received: by 10.142.215.5 with SMTP id n5mr52216wfg.1189686872802; Thu, 13 Sep 2007 05:34:32 -0700 (PDT) Received: by 10.143.157.7 with HTTP; Thu, 13 Sep 2007 05:34:32 -0700 (PDT) Message-ID: Date: Thu, 13 Sep 2007 14:34:32 +0200 From: "W.Alphonse HAROUNY" To: pgsql-sql@postgresql.org Subject: Index usage in bitwise operation context MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_29447_33513718.1189686872798" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/101 X-Sequence-Number: 29314 ------=_Part_29447_33513718.1189686872798 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 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 ------=_Part_29447_33513718.1189686872798 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

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


------=_Part_29447_33513718.1189686872798--