From: Gregory Stark <stark@enterprisedb.com>
To: W.Alphonse HAROUNY <wharouny@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Index usage in bitwise operation context
Date: Thu, 13 Sep 2007 23:55:51 +0100
Message-ID: <87myvqnpjc.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <f97d4e240709130534h3464403esf08ab8be2d5bedbf@mail.gmail.com>
References: <f97d4e240709130534h3464403esf08ab8be2d5bedbf@mail.gmail.com>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: stark@enterprisedb.com, wharouny@gmail.com
Subject: Re: Index usage in bitwise operation context
In-Reply-To: <87myvqnpjc.fsf@oxford.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox