Received: from localhost (unknown [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id 3AD369F9C23
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 13 Sep 2007 19:56:00 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 91383-08 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 13 Sep 2007 19:55:55 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from oxford.xeocode.com (unknown [87.127.95.196])
	by postgresql.org (Postfix) with ESMTP id 59EB69F9C15
	for <pgsql-sql@postgresql.org>; Thu, 13 Sep 2007 19:55:55 -0300 (ADT)
Received: from localhost ([127.0.0.1] helo=oxford.xeocode.com)
	by oxford.xeocode.com with esmtp (Exim 4.67)
	(envelope-from <stark@enterprisedb.com>)
	id 1IVxbE-0001Ig-2T; Thu, 13 Sep 2007 23:55:52 +0100
To: "W.Alphonse HAROUNY" <wharouny@gmail.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Index usage in bitwise operation context
In-Reply-To: <f97d4e240709130534h3464403esf08ab8be2d5bedbf@mail.gmail.com> (W.
	Alphonse HAROUNY's message of "Thu\,
	13 Sep 2007 14\:34\:32 +0200")
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux)
X-Draft-From: ("nnimap+mail01.enterprisedb.com:INBOX.sql" 1065)
References: <f97d4e240709130534h3464403esf08ab8be2d5bedbf@mail.gmail.com>
From: Gregory Stark <stark@enterprisedb.com>
Organization: EnterpriseDB
Date: Thu, 13 Sep 2007 23:55:51 +0100
Message-ID: <87myvqnpjc.fsf@oxford.xeocode.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200709/102
X-Sequence-Number: 29315

"W.Alphonse HAROUNY" <wharouny@gmail.com> writes:

> 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 ?

No, & isn't an indexable operator for any of the standard indexing methods.

You could create 32 partial indexes on some other key with clauses like
 WHERE tbl1.category & 0100... = 0100...
But I don't think that would be useful for a join clause in any case.

Second idea, you could create an expression index on 

 tbl1 (category & 0100... = 0100..., 
       category & 0010... = 0010..., 
       category & 0001... = 0001..., 
       ...)

Again I don't see that it's going to be used for a join condition.

Lastly, you could look for a GIST index method for varbit which would be
superior to both of the above tactics. I'm still not sure it would be able to
handle a join clause though, but maybe?

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com