X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 19D67329E5F for ; Thu, 16 Sep 2004 09:45:06 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 04088-10 for ; Thu, 16 Sep 2004 08:44:55 +0000 (GMT) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by svr1.postgresql.org (Postfix) with ESMTP id CC008329F01 for ; Thu, 16 Sep 2004 09:44:32 +0100 (BST) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1C7rsT-0008Es-00; Thu, 16 Sep 2004 04:44:29 -0400 To: Patrick Clery Cc: pgsql-performance@postgresql.org Subject: Re: Comparing user attributes with bitwise operators References: <200409160141.37604.patrick@phpforhire.com> In-Reply-To: <200409160141.37604.patrick@phpforhire.com> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 16 Sep 2004 04:44:29 -0400 Message-ID: <87llfappqq.fsf@stark.xeocode.com> Lines: 32 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/159 X-Sequence-Number: 8287 Patrick Clery writes: > Method 3 is the only one that used the index, but the only really acceptable > method here is Method 1. > > My questions are... > - Is there any hope in getting this to use an efficient index? > - Any mathmaticians know if there is a way to reorder my bitwise comparison to > have the operator use = and not an != (perhaps to force an index)? (AFAIK, > the answer to the second question is no) The only kind of index that is capable of indexing this type of data structure for arbitrary searches would be a GiST index. I'm not aware of any implementation for bitfields, though it would be an appropriate use. What there is now is the contrib/intarray package. You would have to store more than just the bitfields, you would have to store an array of integer flags. That might be denser actually if you end up with many flags few of which are set. GiST indexes allow you to search arbitrary combinations of set and unset flags. using the "@@" operator int[] @@ query_int - returns TRUE if array satisfies query (like '1&(2|3)') You might be able to look at the code there and adapt it to apply to bit fields. If so I think it would be a useful tool. But GiST indexing is pretty esoteric stuff. -- greg