Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1auQS2-0005Rm-Nd for pgsql-performance@arkaria.postgresql.org; Sun, 24 Apr 2016 20:16:18 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1auQS1-0006WR-Ne for pgsql-performance@arkaria.postgresql.org; Sun, 24 Apr 2016 20:16:17 +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 1auQQL-0004cn-Vq for pgsql-performance@postgresql.org; Sun, 24 Apr 2016 20:14:34 +0000 Received: from mail-yw0-x236.google.com ([2607:f8b0:4002:c05::236]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1auQQE-000431-Nv for pgsql-performance@postgresql.org; Sun, 24 Apr 2016 20:14:32 +0000 Received: by mail-yw0-x236.google.com with SMTP id g133so151834388ywb.2 for ; Sun, 24 Apr 2016 13:14:26 -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=b+CPP8EQsetUaXWs+mq1f4sxhx9s1zVB9hWyj/MdAEY=; b=J44m5N793pMePEpnteSfiOfwf0MDiZA+igCQ8RMQnR/fpZltDRuwwDOC25Hzn93p9+ Ld3tmjv/d28FwswTdHSqtvF8poQl1vOqLh98wbweE62O3efHML2l5EZ9u1b05m4t+jSv 7Ym95/7hiveWJfC/3FgWFw/sb48MPjs4xTuDAZEeV6GzVPmh8TGXvIy0kP7ombLn6ozT pCKQ9ex4Fq/7pzsZqE2XNy/N1BRBbBzVLpWymkjhyA509aHJLOJTLtj1AMzYTOgnQSn2 0+htcICPuMuvbBXBx2oVb/guDUbEFUeujKdJtVYoz1mPr5eV9tgw3Pvss+Q1/PSRDSuq xjYQ== 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=b+CPP8EQsetUaXWs+mq1f4sxhx9s1zVB9hWyj/MdAEY=; b=SipMTKHJyKvv2G4mt4WVHA0o3JSAwg9NTa74Rr/OKQFcjylDXburCCNIm8d9NdicsM ZAXUZl8G9rPWB5YRuxJieiqhH4whOitPjBKK7RF6T0P/s5J8KS5LPJC8PMn+IRHU3jID u7pafcqvBk21tOZXZRyJfMJyrDQpe0QFAKMxRmAXcaqWxsPEnAr40s6O5ZA6TdulK0YI i5GfuWn7m8m+nHKqVihoVZu1Ye65JoSuq1JW/P8T6xSJ1J0jrhv8d3IOpL4tePejoKTh TRvbg66RSCMHDuyPRzbGBsjAbIH7+rfXowhd3gfSzdMKj3SQvlRMv2ihi+zm8I/aYfNs 49Jw== X-Gm-Message-State: AOPr4FWjMT6YHfYF5AKBcBskv25CR4sIVC7I2agRWFSPqbvukkf4uLOo+Xa9bT+5E47fITXH3vU/1wwqwcACIQ== MIME-Version: 1.0 X-Received: by 10.129.159.144 with SMTP id w138mr19264956ywg.296.1461528865806; Sun, 24 Apr 2016 13:14:25 -0700 (PDT) Received: by 10.37.209.212 with HTTP; Sun, 24 Apr 2016 13:14:25 -0700 (PDT) In-Reply-To: References: <5717D07E.3070802@sigaev.ru> Date: Sun, 24 Apr 2016 13:14:25 -0700 Message-ID: Subject: Re: Performant queries on table with many boolean columns From: bricklen To: Rob Imig Cc: "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary=94eb2c0b6f70e02234053140b40c 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 --94eb2c0b6f70e02234053140b40c Content-Type: text/plain; charset=UTF-8 Query plan for the md5() index test: Index Scan using lots_of_columns_md5_idx on lots_of_columns (cost=0.93..3.94 rows=1 width=208) (actual time=0.043..0.043 rows=1 loops=1) Index Cond: ('1ba23a0668ec17e230d98c270d6664dc'::text = md5(((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((c1)::text || (c2)::text) || (c3)::text) || (c4)::text) || (c5)::text) || (c6)::text) || (c7)::text) || (c8)::text) || (c9)::text) || (c10)::text) || (c11)::text) || (c12)::text) || (c13)::text) || (c14)::text) || (c15)::text) || (c16)::text) || (c17)::text) || (c18)::text) || (c19)::text) || (c20)::text) || (c21)::text) || (c22)::text) || (c23)::text) || (c24)::text) || (c25)::text) || (c26)::text) || (c27)::text) || (c28)::text) || (c29)::text) || (c30)::text) || (c31)::text) || (c32)::text) || (c33)::text) || (c34)::text) || (c35)::text) || (c36)::text) || (c37)::text) || (c38)::text) || (c39)::text) || (c40)::text) || (c41)::text) || (c42)::text) || (c43)::text) || (c44)::text) || (c45)::text) || (c46)::text) || (c47)::text) || (c48)::text) || (c49)::text) || (c50)::text) || (c51)::text) || (c52)::text) || (c53)::text) || (c54)::text) || (c55)::text) || (c56)::text) || (c57)::text) || (c58)::text) || (c59)::text) || (c60)::text) || (c61)::text) || (c62)::text) || (c63)::text) || (c64)::text) || (c65)::text) || (c66)::text) || (c67)::text) || (c68)::text) || (c69)::text) || (c70)::text) || (c71)::text) || (c72)::text) || (c73)::text) || (c74)::text) || (c75)::text) || (c76)::text) || (c77)::text) || (c78)::text) || (c79)::text) || (c80)::text) || (c81)::text) || (c82)::text) || (c83)::text) || (c84)::text) || (c85)::text) || (c86)::text) || (c87)::text) || (c88)::text) || (c89)::text) || (c90)::text) || (c91)::text) || (c92)::text) || (c93)::text) || (c94)::text) || (c95)::text) || (c96)::text) || (c97)::text) || (c98)::text) || (c99)::text) || (c100)::text))) Buffers: shared hit=4 Planning time: 0.389 ms Execution time: 0.129 ms (5 rows) --94eb2c0b6f70e02234053140b40c Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Query plan for the md5() index test:

=C2=A0Index Sc= an using lots_of_columns_md5_idx on lots_of_columns=C2=A0 (cost=3D0.93..3.9= 4 rows=3D1 width=3D208) (actual time=3D0.043..0.043 rows=3D1 loops=3D1)
= =C2=A0=C2=A0 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)::text) || (c12)::text) || (c13)::text= ) || (c14)::text) || (c15)::text) || (c16)::text) || (c17)::text) || (c18):= :text) || (c19)::text) || (c20)::text) || (c21)::text) || (c22)::text) || (= c23)::text) || (c24)::text) || (c25)::text) || (c26)::text) || (c27)::text)= || (c28)::text) || (c29)::text) || (c30)::text) || (c31)::text) || (c32)::= text) || (c33)::text) || (c34)::text) || (c35)::text) || (c36)::text) || (c= 37)::text) || (c38)::text) || (c39)::text) || (c40)::text) || (c41)::text) = || (c42)::text) || (c43)::text) || (c44)::text) || (c45)::text) || (c46)::t= ext) || (c47)::text) || (c48)::text) || (c49)::text) || (c50)::text) || (c5= 1)::text) || (c52)::text) || (c53)::text) || (c54)::text) || (c55)::text) |= | (c56)::text) || (c57)::text) || (c58)::text) || (c59)::text) || (c60)::te= xt) || (c61)::text) || (c62)::text) || (c63)::text) || (c64)::text) || (c65= )::text) || (c66)::text) || (c67)::text) || (c68)::text) || (c69)::text) ||= (c70)::text) || (c71)::text) || (c72)::text) || (c73)::text) || (c74)::tex= t) || (c75)::text) || (c76)::text) || (c77)::text) || (c78)::text) || (c79)= ::text) || (c80)::text) || (c81)::text) || (c82)::text) || (c83)::text) || = (c84)::text) || (c85)::text) || (c86)::text) || (c87)::text) || (c88)::text= ) || (c89)::text) || (c90)::text) || (c91)::text) || (c92)::text) || (c93):= :text) || (c94)::text) || (c95)::text) || (c96)::text) || (c97)::text) || (= c98)::text) || (c99)::text) || (c100)::text)))
=C2=A0=C2=A0 Buffers: sha= red hit=3D4
=C2=A0Planning time: 0.389 ms
=C2=A0Execution time: 0.129= ms
(5 rows)
--94eb2c0b6f70e02234053140b40c--