MIME-Version: 1.0
In-Reply-To: <5717D07E.3070802@sigaev.ru>
References: 
 <CANcrS5pR1P1Tj=e-RQQ=FF3WPAy_fyruS0YJer-+iJHxR1JAiA@mail.gmail.com>
	<5717D07E.3070802@sigaev.ru>
Date: Wed, 20 Apr 2016 17:07:27 -0400
Message-ID: 
 <CAMAYy4Krf1b3Lgz42-nAKYCSAfErhJospJp87yG5_HqkZCVO8A@mail.gmail.com>
Subject: Re: Performant queries on table with many boolean columns
From: Rick Otten <rottenwindfish@gmail.com>
To: Teodor Sigaev <teodor@sigaev.ru>
Cc: Rob Imig <rimig88@gmail.com>, pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary=001a1144f91229f23a0530f0fb5c
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--001a1144f91229f23a0530f0fb5c
Content-Type: text/plain; charset=UTF-8

Would a bit string column work? --
http://www.postgresql.org/docs/9.5/static/datatype-bit.html

You might need to use a lot of bitwise OR statements in the query though if
you are looking at very sparse sets of specific values...

Something like the get_bit() function might allow you to select a specific
bit, but then you might want a bunch of functional indexes on the column
for various get_bit() combinations.

Maybe you can group commonly queried sets of columns into bit strings.
 (rather than having one bit string column for all 100 booleans).


On Wed, Apr 20, 2016 at 2:54 PM, Teodor Sigaev <teodor@sigaev.ru> wrote:

>
>> The obvious thing seems to make a table with ~100 columns, with 1 column
>> for each boolean property. Though, what type of indexing strategy would
>> one use on that table? Doesn't make sense to do BTREE. Is there a better
>> way to structure it?
>>
>> looks like a deal for contrib/bloom index in upcoming 9.6 release
>
>
> --
> 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
>

--001a1144f91229f23a0530f0fb5c
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Would a bit string column work? --=C2=A0<a href=3D"http://=
www.postgresql.org/docs/9.5/static/datatype-bit.html">http://www.postgresql=
.org/docs/9.5/static/datatype-bit.html</a>=C2=A0<div><br></div><div>You mig=
ht need to use a lot of bitwise OR statements in the query though if you ar=
e looking at very sparse sets of specific values...</div><div><br></div><di=
v>Something like the get_bit() function might allow you to select a specifi=
c bit, but then you might want a bunch of functional indexes on the column =
for various get_bit() combinations.</div><div><br></div><div>Maybe you can =
group commonly queried sets of columns into bit strings. =C2=A0(rather than=
 having one bit string column for all 100 booleans).</div><div><br></div><d=
iv><br></div></div><div class=3D"gmail_extra"><br><div class=3D"gmail_quote=
">On Wed, Apr 20, 2016 at 2:54 PM, Teodor Sigaev <span dir=3D"ltr">&lt;<a h=
ref=3D"mailto:teodor@sigaev.ru" target=3D"_blank">teodor@sigaev.ru</a>&gt;<=
/span> wrote:<br><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8=
ex;border-left:1px #ccc solid;padding-left:1ex"><span class=3D""><blockquot=
e class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc sol=
id;padding-left:1ex">
<br>
The obvious thing seems to make a table with ~100 columns, with 1 column<br=
>
for each boolean property. Though, what type of indexing strategy would<br>
one use on that table? Doesn&#39;t make sense to do BTREE. Is there a bette=
r<br>
way to structure it?<br>
<br>
</blockquote></span>
looks like a deal for contrib/bloom index in upcoming 9.6 release<span clas=
s=3D"HOEnZb"><font color=3D"#888888"><br>
<br>
<br>
-- <br>
Teodor Sigaev=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 E-mail: <a href=3D"mailto:teodor@sigaev.ru" target=3D"_blank=
">teodor@sigaev.ru</a><br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 WWW: <a href=3D=
"http://www.sigaev.ru/" rel=3D"noreferrer" target=3D"_blank">http://www.sig=
aev.ru/</a><br>
<br>
<br>
-- <br>
Sent via pgsql-performance mailing list (<a href=3D"mailto:pgsql-performanc=
e@postgresql.org" target=3D"_blank">pgsql-performance@postgresql.org</a>)<b=
r>
To make changes to your subscription:<br>
<a href=3D"http://www.postgresql.org/mailpref/pgsql-performance" rel=3D"nor=
eferrer" target=3D"_blank">http://www.postgresql.org/mailpref/pgsql-perform=
ance</a><br>
</font></span></blockquote></div><br></div>

--001a1144f91229f23a0530f0fb5c--