Message-ID: <4C3D0DE6.8070003@joeconway.com>
Date: Tue, 13 Jul 2010 18:07:50 -0700
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.8.0.12) Gecko/20071019 Fedora/1.5.0.12-3.fc6 pango-text
	Thunderbird/1.5.0.12 Mnenhy/0.7.5.666
MIME-Version: 1.0
To: Elias Ghanem <e.ghanem@acteos.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Queries with conditions using bitand operator
References: <4C3C5279.5090108@acteos.com>
In-Reply-To: <4C3C5279.5090108@acteos.com>
OpenPGP: id=B699434A
Content-Type: multipart/signed; micalg=pgp-sha256;
	protocol="application/pgp-signature";
	boundary="------------enig40BEB8AA3E934F390C648BEF"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enig40BEB8AA3E934F390C648BEF
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On 07/13/2010 04:48 AM, Elias Ghanem wrote:
> Hi,
> I have table "ARTICLE" containing a String a field "STATUS" that
> represents a number in binary format (for ex: 10011101).
> My application issues queries with where conditions that uses BITAND
> operator on this field (for ex: select * from article where status & 4 =
=3D
> 4).
> Thus i'm facing performance problemes with these select queries: the
> queries are too slow.
> Since i'm using the BITAND operator in my conditions, creating an index=

> on the status filed is useless
>  and since the second operator variable (status & 4 =3D 4; status & 8 =3D=
 8;
> status & 16 =3D 16...) a functional index is also usless (because a
> functional index require the use of a function that accept only table
> column as input parameter: constants are not accepted).
> So is there a way to enhance the performance of these queries?

You haven't given a lot of info to help us help you, but would something
along these lines be useful to you?

drop table if exists testbit;
create table testbit(
 id serial primary key,
 article text,
 status int
);

insert into testbit (article, status) select 'article ' ||
generate_series::text, generate_series % 256 from
generate_series(1,1000000);

create index idx1 on testbit(article) where status & 1 =3D 1;
create index idx2 on testbit(article) where status & 2 =3D 2;
create index idx4 on testbit(article) where status & 4 =3D 4;
create index idx8 on testbit(article) where status & 8 =3D 8;
create index idx16 on testbit(article) where status & 16 =3D 16;
create index idx32 on testbit(article) where status & 512 =3D 512;

update testbit set status =3D status + 512 where id in (42, 4242, 424242)=
;
explain analyze select * from testbit where status & 512 =3D 512;
                          QUERY PLAN
------------------------------------------------------------------
 Index Scan using idx32 on testbit  (cost=3D0.00..4712.62 rows=3D5000
                width=3D22) (actual time=3D0.080..0.085 rows=3D3 loops=3D=
1)
 Total runtime: 0.170 ms


HTH,

Joe

--=20
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & Support


--------------enig40BEB8AA3E934F390C648BEF
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.14 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iQIcBAEBCAAGBQJMPQ3mAAoJEDfy90M199hl3JAP/jmDDAH41tz65OJb646uhufn
7X2J4C+dAKXpcar0AphRKxUQpAHuzE7NQAnmyYsoOsEt4s38EETSEBamhjD2Ep/Y
Nxy+cXPEyLo8zizqUvHWFdv07NFN7Jr1/E9/Azr5piopkphnVOf4i1UTQee2IGXm
V5ZUKc932EySv92jKBvF/uGojO/VDNlyPKEBenSPDaaNsifhXqHoTja9IejfjRvj
0k5T5oNavfE4x4iRz7L325nbn8IvNdoPnWjJuIIJzgb/e0WvrXf5MT+csIzl1iLb
QEtDtYljP5CQ/FM+Zggcfl5rL/QiJV4mOjme/Rws4mnxv3guDiPTgJESFMu4Yfxd
A94TEZoX1ZF8ypkK+JN2jR5hAQpBPANMtbNFhzXaI8OFp5pn4zgxb3pu2TXKfuwq
ZOknD2wJfi9Nr9giEVDS/3cgW1/j8iEQJrD9X5I/FG4a9ao7xVk15aGyEbD2nWIg
5K8nS+FIrMJFsJBDoSKlLwiWsd6NjgJJbH20WxdyQoqhIhAxsMmzJlS+MmO73iTc
CIfY7iegQoEWlqhaXR7hHudO/xVQTe8gM6HlpcoofVclwUN1nGtLDjvJJI5ZfPZr
9NMm5deRRCE46DojlwjQ+kyFE1xZaBsoWxY0fuPLlJkr/0vttjvyapjLNOjEvCDp
oGjBdpeRoNP8nhFpRGe9
=1DIR
-----END PGP SIGNATURE-----

--------------enig40BEB8AA3E934F390C648BEF--