Subject: Re: Performant queries on table with many boolean columns
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: Rob Imig <rimig88@gmail.com>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
References: 
 <CANcrS5pR1P1Tj=e-RQQ=FF3WPAy_fyruS0YJer-+iJHxR1JAiA@mail.gmail.com>
 <5717D07E.3070802@sigaev.ru>
 <CAKFQuwY3z7oGwriDie4guGPudy_k8S4wWJKrTRy7zPKjTUm=wQ@mail.gmail.com>
From: Teodor Sigaev <teodor@sigaev.ru>
Message-ID: <5718A59D.4090706@sigaev.ru>
Date: Thu, 21 Apr 2016 13:04:13 +0300
User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:42.0) Gecko/20100101
 Firefox/42.0 SeaMonkey/2.39
MIME-Version: 1.0
In-Reply-To: 
 <CAKFQuwY3z7oGwriDie4guGPudy_k8S4wWJKrTRy7zPKjTUm=wQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

>     looks like a deal for contrib/bloom index in upcoming 9.6 release
> ​Curious, it doesn't look like it will work with booleans out of the box.
> http://www.postgresql.org/docs/devel/static/bloom.html

There is no rocket science here:
# create table x (v bool);
# create index i on x using bloom ((v::int4));
# set enable_seqscan=off; --because of empty table
# explain select * from x where v::int4 = 1;
                             QUERY PLAN
------------------------------------------------------------------
  Bitmap Heap Scan on x  (cost=25.08..35.67 rows=14 width=1)
    Recheck Cond: ((v)::integer = 1)
    ->  Bitmap Index Scan on i  (cost=0.00..25.07 rows=14 width=0)
          Index Cond: ((v)::integer = 1)

Or cast it to "char" type (with quoting!)

-- 
Teodor Sigaev                                   E-mail: teodor@sigaev.ru
                                                    WWW: http://www.sigaev.ru/


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance