Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1atHFb-0008WI-IX for pgsql-performance@arkaria.postgresql.org; Thu, 21 Apr 2016 16:14:43 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1atHFa-0006WU-PY for pgsql-performance@arkaria.postgresql.org; Thu, 21 Apr 2016 16:14:42 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1atHDp-0003un-DD for pgsql-performance@postgresql.org; Thu, 21 Apr 2016 16:12:53 +0000 Received: from mail-oi0-x230.google.com ([2607:f8b0:4003:c06::230]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1atHDm-0006Ow-9H for pgsql-performance@postgresql.org; Thu, 21 Apr 2016 16:12:52 +0000 Received: by mail-oi0-x230.google.com with SMTP id k142so90966462oib.1 for ; Thu, 21 Apr 2016 09:12:50 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc; bh=UuzfBkP2Br11Dma+ZH7xc3tybgNR8O5NvyRSsc7W7Os=; b=GXz/inIbRZc9YN8UqrE9pGvrQn/vnnJJnEdjf8eQnmEZbtvCrgeZn0nQt7mTKNINYz k1vFyMP4ypXEffc6+bXfDY638miTiXDgYg5gkuyWx2Ay8X4Hq5Yuc2cW7kaIWoHGdunW 7Ub1aUNvIk+FPsWH9wfkedXToyUgTtHLz87XUmvGV4AfqJKeXFfobs6td88GtZmYAMct NtPMEUJxc9D8xyVRba3ZzfiOp3ATfiFQnFhpAIOn6IrWL3NIJqCRuim0qmxrLoKpIgXo LPA1/YHTbMBBuwGJWWSnwjQIgVsWlujtrvm+IY/NmRdzpCj8LLXrUC53HK5iFye8RLJC 4BuA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:date :message-id:subject:from:to:cc; bh=UuzfBkP2Br11Dma+ZH7xc3tybgNR8O5NvyRSsc7W7Os=; b=JxTT+Q984HbfZ+PS1+bUWd1A2rXMDjRTcw83/UbYDL9pEdPswdA8QDF9f+D99b1zyi jYOR3s1H+pKhBU8F+8Bkes2vk3RKKHwctWkld4NzY58bEskDzasDarvk/pIMEsmjBC3Y fygxCYIXjv8JBC2WE4RB6wYL8JItrFmmK5h52EBdh8L8HL1GreHogFB1ykQXfxUgn2sx onlGVhGLS+AYAvBWJx9T6h+A1J93KCSDc5bolQlBWZDrPJbNBAWIfua+EDh8tGDPpiJ+ e+I5Lj5DGW9NB16IdtUgPM7N1aQA6C3Z/klaSJwOUYVj5Gzr3geOs/b5LcH5yrzsSFaZ ytWg== X-Gm-Message-State: AOPr4FXYzePDr5WtQidfo47v7mDSJZFy0JvaF5e7hHuIEtiSOs96sg1ED34/nGtbrGfEm6yZWKWR6oi8wwUcuw== MIME-Version: 1.0 X-Received: by 10.182.21.196 with SMTP id x4mr6452195obe.84.1461255169915; Thu, 21 Apr 2016 09:12:49 -0700 (PDT) Received: by 10.157.47.245 with HTTP; Thu, 21 Apr 2016 09:12:49 -0700 (PDT) In-Reply-To: <5718A59D.4090706@sigaev.ru> References: <5717D07E.3070802@sigaev.ru> <5718A59D.4090706@sigaev.ru> Date: Thu, 21 Apr 2016 09:12:49 -0700 Message-ID: Subject: Re: Performant queries on table with many boolean columns From: "David G. Johnston" To: Teodor Sigaev Cc: Rob Imig , "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary=14dae93b5e1a5443a6053100fbdd X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --14dae93b5e1a5443a6053100fbdd Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable On Thu, Apr 21, 2016 at 3:04 AM, Teodor Sigaev wrote: > looks like a deal for contrib/bloom index in upcoming 9.6 release >> =E2=80=8BCurious, 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=3Doff; --because of empty table > # explain select * from x where v::int4 =3D 1; > QUERY PLAN > ------------------------------------------------------------------ > Bitmap Heap Scan on x (cost=3D25.08..35.67 rows=3D14 width=3D1) > Recheck Cond: ((v)::integer =3D 1) > -> Bitmap Index Scan on i (cost=3D0.00..25.07 rows=3D14 width=3D0) > Index Cond: ((v)::integer =3D 1) > > Or cast it to "char" type (with quoting!) > > =E2=80=8BAt that point you should just forget bool exists and define the co= lumns 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. =E2=80=8B --14dae93b5e1a5443a6053100fbdd Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
On Thu, Ap= r 21, 2016 at 3:04 AM, Teodor Sigaev <teodor@sigaev.ru> wrote:
=C2=A0 =C2=A0 looks like a deal for contrib/bloom index in upcoming 9.6 rel= ease
=E2=80=8BCurious, it doesn't look like it will work with booleans out o= f the box.
http://www.postgresql.org/docs/devel/static/bl= oom.html

 There is no rocket science here:
# create table x (v bool);
# create index i on x using bloom ((v::int4));
# set enable_seqscan=3Doff; --because of empty table
# explain select * from x where v::int4 =3D 1;
=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 QUERY PLAN
------------------------------------------------------------------
=C2=A0Bitmap Heap Scan on x=C2=A0 (cost=3D25.08..35.67 rows=3D14 width=3D1)=
=C2=A0 =C2=A0Recheck Cond: ((v)::integer =3D 1)
=C2=A0 =C2=A0->=C2=A0 Bitmap Index Scan on i=C2=A0 (cost=3D0.00..25.07 r= ows=3D14 width=3D0)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: ((v)::integer =3D 1)

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


=E2=80=8BAt that point you should just forget bool exists and defin= e 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.
=E2=80=8B
=C2=A0
--14dae93b5e1a5443a6053100fbdd--