Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 801C0B5DC00 for ; Wed, 22 Jun 2011 18:42:39 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 84619-04 for ; Wed, 22 Jun 2011 21:42:29 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.171]) by mail.postgresql.org (Postfix) with ESMTP id CEE58B5DC03 for ; Wed, 22 Jun 2011 18:42:28 -0300 (ADT) Received: from [192.168.1.7] (mail.highperformancepostgresql.com [71.179.240.8]) by mrelayeu.kundenserver.de (node=mrbap0) with ESMTP (Nemesis) id 0MELXc-1QotPX3O3s-00G5tF; Wed, 22 Jun 2011 23:42:28 +0200 Message-ID: <4E0261C1.8000807@2ndQuadrant.com> Date: Wed, 22 Jun 2011 17:42:25 -0400 From: Greg Smith User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.16) Gecko/20110505 Icedove/3.0.11 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: bitmask index References: <4E025E54.6020402@engene.se> In-Reply-To: <4E025E54.6020402@engene.se> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Provags-ID: V02:K0:5mS/VK3u3+UuXDwSbBqkTgYB975v4vPhg6iFMjWW5OU gsvejVPk/90wvcgDRoD47GCbY0aJ8RBwiVApG7kJ9CqnvEn0aT 0LIJNJ/AjaNAJ6+zdOfpzOSlAqbxTyFz/v/CNTkLkULmAPEJBM JLT6UibfEfjFQ5rt1ur6+5TmtLFER+e2VCSDp+biJYH93JZTTu Uk7IxeMwcKFlVHXUQ+e4BXVmPw8Ign4FO6kQA2dkHI= X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201106/216 X-Sequence-Number: 44149 On 06/22/2011 05:27 PM, Marcus Engene wrote: > I have some tables with bitmask integers. Set bits are the interesting > ones. Usually they are sparse. If it's sparse, create a partial index that just includes rows where the bit is set: http://www.postgresql.org/docs/current/static/indexes-partial.html You need to be careful the query uses the exact syntax as the one that created the index for it to be used. But if you do that, it should be able to pull the rows that match out quickly. -- Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books