MIME-Version: 1.0
In-Reply-To: 
 <CAGrpgQ836rDb1ic4Ftcd04oCqX=yETdTaMiQvYqOdON9a=BomA@mail.gmail.com>
References: 
 <CANcrS5pR1P1Tj=e-RQQ=FF3WPAy_fyruS0YJer-+iJHxR1JAiA@mail.gmail.com>
	<5717D07E.3070802@sigaev.ru>
	<CAMkU=1xXXANTYp7YLQjjzvgYCFQsr+y6iTphx1Q4y8iP7QpRjw@mail.gmail.com>
	<CANcrS5oHanMwESO0jaqROV44wQRNO6y_gXj8C4=uY27t3u7TfQ@mail.gmail.com>
	<CANcrS5oP6+CRucJD9R59E2V9NoY4ZrAbW91pxj2eoivGfsz_ng@mail.gmail.com>
	<CAGrpgQ9PR8zh2Hdnoa3VnThsbAYo4GA1khbbo59+t9ddAwMqTg@mail.gmail.com>
	<CAGrpgQ836rDb1ic4Ftcd04oCqX=yETdTaMiQvYqOdON9a=BomA@mail.gmail.com>
Date: Mon, 25 Apr 2016 11:28:42 -0500
Message-ID: 
 <CAHyXU0xJcfd0BhV1Ln3WTNAHZz1qa0AupqRYaOLcO3BaTD3Nag@mail.gmail.com>
Subject: Re: Performant queries on table with many boolean columns
From: Merlin Moncure <mmoncure@gmail.com>
To: bricklen <bricklen@gmail.com>
Cc: Rob Imig <rimig88@gmail.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On Sun, Apr 24, 2016 at 3:14 PM, bricklen <bricklen@gmail.com> wrote:
> Query plan for the md5() index test:
>
>  Index Scan using lots_of_columns_md5_idx on lots_of_columns
> (cost=3D0.93..3.94 rows=3D1 width=3D208) (actual time=3D0.043..0.043 rows=
=3D1 loops=3D1)
>    Index Cond: ('1ba23a0668ec17e230d98c270d6664dc'::text =3D
> md5((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((=
(((((((((((((((((((((((((((((((c1)::text
> || (c2)::text) || (c3)::text) || (c4)::text) || (c5)::text) || (c6)::text)
> || (c7)::text) || (c8)::text) || (c9)::text) || (c10)::text) || (c11)::te=
xt)
> || (c12)::text) || (c13)::text) || (c14)::text) || (c15)::text) ||
> (c16)::text) || (c17)::text) || (c18)::text) || (c19)::text) || (c20)::te=
xt)
> || (c21)::text) || (c22)::text) || (c23)::text) || (c24)::text) ||
> (c25)::text) || (c26)::text) || (c27)::text) || (c28)::text) || (c29)::te=
xt)
> || (c30)::text) || (c31)::text) || (c32)::text) || (c33)::text) ||
> (c34)::text) || (c35)::text) || (c36)::text) || (c37)::text) || (c38)::te=
xt)
> || (c39)::text) || (c40)::text) || (c41)::text) || (c42)::text) ||
> (c43)::text) || (c44)::text) || (c45)::text) || (c46)::text) || (c47)::te=
xt)
> || (c48)::text) || (c49)::text) || (c50)::text) || (c51)::text) ||
> (c52)::text) || (c53)::text) || (c54)::text) || (c55)::text) || (c56)::te=
xt)
> || (c57)::text) || (c58)::text) || (c59)::text) || (c60)::text) ||
> (c61)::text) || (c62)::text) || (c63)::text) || (c64)::text) || (c65)::te=
xt)
> || (c66)::text) || (c67)::text) || (c68)::text) || (c69)::text) ||
> (c70)::text) || (c71)::text) || (c72)::text) || (c73)::text) || (c74)::te=
xt)
> || (c75)::text) || (c76)::text) || (c77)::text) || (c78)::text) ||
> (c79)::text) || (c80)::text) || (c81)::text) || (c82)::text) || (c83)::te=
xt)
> || (c84)::text) || (c85)::text) || (c86)::text) || (c87)::text) ||
> (c88)::text) || (c89)::text) || (c90)::text) || (c91)::text) || (c92)::te=
xt)
> || (c93)::text) || (c94)::text) || (c95)::text) || (c96)::text) ||
> (c97)::text) || (c98)::text) || (c99)::text) || (c100)::text)))
>    Buffers: shared hit=3D4
>  Planning time: 0.389 ms
>  Execution time: 0.129 ms
> (5 rows)

Hm.  Maybe use VARBIT? (assuming there are no null values or null can
be treated as false).

CREATE OR REPLACE FUNCTION MakeVarBit(VARIADIC BOOL[]) RETURNS VARBIT AS
$$
  SELECT string_agg(CASE WHEN v THEN '1' ELSE '0' END, '')::VARBIT
  FROM
  (
    SELECT UNNEST($1) v
  ) q;
$$ LANGUAGE SQL IMMUTABLE;

postgres=3D# select MakeVarBit(true, true, false);
 makevarbit
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80
 110

create index on lots_of_columns (MakeVarBit(c1, c2, c3, c4 ...));

merlin


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