From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Teodor Sigaev <teodor(at)sigaev(dot)ru> |
Cc: | Rob Imig <rimig88(at)gmail(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Performant queries on table with many boolean columns |
Date: | 2016-04-21 16:12:49 |
Message-ID: | CAKFQuwbij6yfxixc-LSq6dtt9a=6cTZyHNJwJnrG0-PGAmGHCA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Thu, Apr 21, 2016 at 3:04 AM, Teodor Sigaev <teodor(at)sigaev(dot)ru> wrote:
> 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!)
>
>
At that point you should just forget bool exists and define the columns as
int4.
I'll give you points for making it work but its not a solution I'd be proud
to offer up.
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Janes | 2016-04-21 16:36:37 | Re: Performant queries on table with many boolean columns |
Previous Message | Teodor Sigaev | 2016-04-21 10:04:13 | Re: Performant queries on table with many boolean columns |