Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kPLfQ-0001ZB-Ti for pgsql-docs@arkaria.postgresql.org; Mon, 05 Oct 2020 08:16:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kPLfP-0007lf-AF for pgsql-docs@arkaria.postgresql.org; Mon, 05 Oct 2020 08:16:19 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kPLfO-0007kX-Ln for pgsql-docs@lists.postgresql.org; Mon, 05 Oct 2020 08:16:19 +0000 Received: from mail-zr0che01on2093.outbound.protection.outlook.com ([40.107.24.93] helo=CHE01-ZR0-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kPLfH-0001XT-7s for pgsql-docs@lists.postgresql.org; Mon, 05 Oct 2020 08:16:17 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=AdSCoqxqJZYWu1Xb7JZ1l1OKELPsT3J7+gS+iOHhjwKXz/vinNA1oKjrGa0kIz8MN23DS/3vlM8qlQ+oPaslOa3w4mhnfmrmqIIwt5jn3nc/oENxSHyi8gvWoW7u8SY2S6x19I1HTlBKsBWIi8SpLr/L2nsgq2VJ5cg8KzsEnZO0MnemA/tcbVUYvSbRzsMe5sy4qyhbpd9sZPNXqsti3a/MBkdUhTiivuxOVvtU2IH302VFF4d7CNXbbDshioB8+pO6Bxq46BVP1ZDztqLa5hH1+P1ULfAI3HGnjmHcF0PnfYbc50jrA8vc8xAWmRGJDfPIwhurooXTkySGbY384g== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=65wRtx2kK+0ayye62/WkI+nR40/Vkvvccn2wM/j0UxE=; b=NxNhLSuobmHhPxBs5m/1jiblkacN/+5Yak/zIKsiDftB50kDEZ29fYQD99m1ZZuCe77mACY4Dne/ePPqDQ77MjmxNa2JHnhCE9DtEmHLqD7hr03uiMMWMswjXObqQ12h1pZdfc3bo1FyMGkuaAirBs55xI8i/sJNWc8M9NsmlboVA6mpj/w2qWe6pLT8qXsyMl5BXVvWZNnm5hMunT4IF8bN3L23oy+TI5Kzf+tteYF9cxe0qdRsVZYO9WlucHp285N9/IfZrAz1Zgr7OTOR3fv1TujMynJ6QbuuQIfny1QxXcW64x0ArqG7ksPfdB5svOuXLM83TttASX5bauG10Q== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=dbi-services.com; dmarc=pass action=none header.from=dbi-services.com; dkim=pass header.d=dbi-services.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=dbi-services.com; s=selector2; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=65wRtx2kK+0ayye62/WkI+nR40/Vkvvccn2wM/j0UxE=; b=Bmq19IIjhPQwZ18VgFmV0zpsWAurXmVghBZyFf1jlSyKlLkZIJuAyPUS4hGfsem0Uh54p4CZ8Ejgj7VNVFtekyRkuzFeESrumSwr6zA+CLHBlZgnxWxGp5oX21arYUWAIdNpVarPFFYsQwD6sw8vbGv8tg6s/eoBgNtL3gZexaY= Received: from GVAP278MB0102.CHEP278.PROD.OUTLOOK.COM (2603:10a6:710:23::15) by GVAP278MB0085.CHEP278.PROD.OUTLOOK.COM (2603:10a6:710:21::12) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.3433.35; Mon, 5 Oct 2020 08:16:02 +0000 Received: from GVAP278MB0102.CHEP278.PROD.OUTLOOK.COM ([fe80::a8dc:cda4:c121:cbd8]) by GVAP278MB0102.CHEP278.PROD.OUTLOOK.COM ([fe80::a8dc:cda4:c121:cbd8%7]) with mapi id 15.20.3433.044; Mon, 5 Oct 2020 08:16:02 +0000 From: "Daniel Westermann (DWE)" To: Pg Docs Subject: Re: Wrong example in the bloom documentation Thread-Topic: Wrong example in the bloom documentation Thread-Index: AQHWlMRYK9iztnw7OkKvSv5AhnzKKKmItcTa Date: Mon, 5 Oct 2020 08:16:01 +0000 Message-ID: References: In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: lists.postgresql.org; dkim=none (message not signed) header.d=none;lists.postgresql.org; dmarc=none action=none header.from=dbi-services.com; x-originating-ip: [37.49.40.101] x-ms-publictraffictype: Email x-ms-office365-filtering-correlation-id: 01e8a8ab-6321-4212-dd01-08d86906e5e0 x-ms-traffictypediagnostic: GVAP278MB0085: x-microsoft-antispam-prvs: x-ms-oob-tlc-oobclassifiers: OLM:10000; x-ms-exchange-senderadcheck: 1 x-microsoft-antispam: BCL:0; x-microsoft-antispam-message-info: gsZYPLhyl1td6KgUiwaEe997SHnhNCpxfPhKzFRqkES/zdH3+/s+jqedfB2sXdqjoqW4FGFh9phBU2fh6zAk2Bc9IUvqgXjo4SaZlirtGEI09aKLNHyemraK77zZkBVbWG0itEHNJDVTnPrVlWd2YXa4ytTan58+L/gB19C/w5qw1flopVQnQiOGPYU+yttY4bBzOUUrkJVxBYCtSerctsIImybmIDOuusB/1ihr+XCbPpnMaIG5Ov7OgKzL48NvGmp+sMYWcA91hAGgU5FY3vZMjp2zBwiGUImOc09Ai2P6Te9LKkmlLIlcFQLc3D2v+BNsagEMlzF4pBYTw+/PMosj4ukoKXNvjQdR+5cGFxTnD6OjeCHKl3I0w/gEYglcvi57kSeoBlT0hEpCP2KsIg== x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:GVAP278MB0102.CHEP278.PROD.OUTLOOK.COM;PTR:;CAT:NONE;SFS:(366004)(136003)(376002)(396003)(39830400003)(346002)(19627405001)(86362001)(2906002)(186003)(55016002)(478600001)(91956017)(76116006)(71200400001)(9686003)(8936002)(166002)(83380400001)(53546011)(966005)(83080400001)(8676002)(6506007)(66476007)(64756008)(66446008)(66556008)(66946007)(5660300002)(52536014)(33656002)(26005)(316002)(7696005)(6916009);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata: jSaHwO81IYr6hoSNZwuVt45yqdTHA1odtio6b4eAtJaJiTy40Qq4mMSKhBWfO6jbDcL45WswvBFd4s9KFz+UNtH/NvPjHwRfr+KKWJprTlTpQ81RdKLen5vm4pw65WcFkkcRtyQ7K33D7baHt68K791eatD1YUZOu53W86whkp3OYQ/X1RSaQILJKOdZUMXtLM+JWnFtY7ryZjewr4rlOKRMMr51WNQLIJC4S2lMrd/XKsImCndQv6y8tizlctuuOjH12k4tIQhPHer+m0P9paeQO1Zn50fKYpiF1LFSm2Yrqkt5YqDlb34oOrjHSg7U4YfR/tp5hGVBvn7I6CTOhGI5Qg+/2RwdYWwjoj/3XUayVv7L7+Zo52bI8KOXiqs4PeWZzGtAKLD3ksTJmkmPl+zjzTHF9odbV/qzZQxorhKjbJKuqsy+MM2CxPUiDJ1D31r0HdDk6iCQwKhWaiKyxMK5cWRYJYXqT0ZBjERhPODmz6kL7nrzx0CMq3cxMCo6p5vvkLoMYA0nOLW7xSZeTWt0kRs5ury20WLE9btjEsECMKNvAzsWraitB1kpK/RwHsl+gbvBmz+/ipZxlhiljKnSWa9La/ZrxnYWCC872Yf8TCik68MBATXWXqj2Zw8CJQ7N0mHY1HrJVmeVWcm9yw== x-ms-exchange-transport-forked: True Content-Type: multipart/alternative; boundary="_000_GVAP278MB01027D2186E612EEE3176B47D20C0GVAP278MB0102CHEP_" MIME-Version: 1.0 X-OriginatorOrg: dbi-services.com X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: GVAP278MB0102.CHEP278.PROD.OUTLOOK.COM X-MS-Exchange-CrossTenant-Network-Message-Id: 01e8a8ab-6321-4212-dd01-08d86906e5e0 X-MS-Exchange-CrossTenant-originalarrivaltime: 05 Oct 2020 08:16:01.8881 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: b9c70613-b1ea-47b7-9fdd-e6f3a67ce75c X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: tfRzx27CElK8LsE4wkAngX5ZsqzxvamqEjWpeQdpADtmhKFmfQ4XYQQRFCzfM0UFuVLHhNa+5zzC/URxM2wO8jCoNWlWUEBGat/H9AuJliESz/iOdMQ4pc9bjuF+NuXN X-MS-Exchange-Transport-CrossTenantHeadersStamped: GVAP278MB0085 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_GVAP278MB01027D2186E612EEE3176B47D20C0GVAP278MB0102CHEP_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, any thoughts on this? Regards Daniel ________________________________ From: Daniel Westermann (DWE) Sent: Sunday, September 27, 2020 17:58 To: Pg Docs Subject: Wrong example in the bloom documentation Hi, I've briefly discussed this with Bruce some time ago in [1]. Replaying the example referenced in the documentation does not give a Bitma= p Heap Scan on tbloom but a parallel seq scan with the default configuratio= n: -- tested on head postgres=3D# CREATE TABLE tbloom AS postgres-# SELECT postgres-# (random() * 1000000)::int as i1, postgres-# (random() * 1000000)::int as i2, postgres-# (random() * 1000000)::int as i3, postgres-# (random() * 1000000)::int as i4, postgres-# (random() * 1000000)::int as i5, postgres-# (random() * 1000000)::int as i6 postgres-# FROM postgres-# generate_series(1,10000000); SELECT 10000000 postgres=3D# CREATE INDEX bloomidx ON tbloom USING bloom (i1, i2, i3, i4, i= 5, i6); CREATE INDEX postgres=3D# CREATE index btreeidx ON tbloom (i1, i2, i3, i4, i5, i6); CREATE INDEX postgres=3D# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 =3D 898732 AND i= 5 =3D 123451; QUERY PLAN ---------------------------------------------------------------------------= -------------------------------------------------- Gather (cost=3D1000.00..127220.00 rows=3D250 width=3D24) (actual time=3D2= 134.851..2221.836 rows=3D0 loops=3D1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on tbloom (cost=3D0.00..126195.00 rows=3D104 widt= h=3D24) (actual time=3D1770.691..1770.692 rows=3D0 loops=3D3) Filter: ((i2 =3D 898732) AND (i5 =3D 123451)) Rows Removed by Filter: 3333333 Planning Time: 0.895 ms JIT: Functions: 6 Options: Inlining false, Optimization false, Expressions true, Deforming= true Timing: Generation 65.512 ms, Inlining 0.000 ms, Optimization 46.328 ms,= Emission 40.658 ms, Total 152.499 ms Execution Time: 2288.056 ms (12 rows) As bloom was introduced in 9.6 I quickly tried with 9.6.17 and indeed for t= his version the example is correct: postgres=3D# select version(); version ---------------------------------------------------------------------------= ------------------------------- PostgreSQL 9.6.17 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 2019= 0507 (Red Hat 8.3.1-4), 64-bit (1 row) postgres=3D# CREATE TABLE tbloom AS postgres-# SELECT postgres-# (random() * 1000000)::int as i1, postgres-# (random() * 1000000)::int as i2, postgres-# (random() * 1000000)::int as i3, postgres-# (random() * 1000000)::int as i4, postgres-# (random() * 1000000)::int as i5, postgres-# (random() * 1000000)::int as i6 postgres-# FROM postgres-# generate_series(1,10000000); SELECT 10000000 postgres=3D# CREATE INDEX bloomidx ON tbloom USING bloom (i1, i2, i3, i4, i= 5, i6); CREATE INDEX postgres=3D# CREATE index btreeidx ON tbloom (i1, i2, i3, i4, i5, i6); CREATE INDEX postgres=3D# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 =3D 898732 AND i= 5 =3D 123451; QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------- Bitmap Heap Scan on tbloom (cost=3D178436.06..179392.83 rows=3D250 width= =3D24) (actual time=3D2279.363..2279.363 rows=3D0 loops=3D1) Recheck Cond: ((i2 =3D 898732) AND (i5 =3D 123451)) Rows Removed by Index Recheck: 2329 Heap Blocks: exact=3D2288 -> Bitmap Index Scan on bloomidx (cost=3D0.00..178436.00 rows=3D250 wi= dth=3D0) (actual time=3D994.406..994.406 rows=3D2329 loops=3D1) Index Cond: ((i2 =3D 898732) AND (i5 =3D 123451)) Planning time: 282.059 ms Execution time: 2286.138 ms (8 rows) The reason is that parallel execution is disabled by default in 9.6, and if= that is turned on the plan changes there as well: postgres=3D# set max_parallel_workers_per_gather =3D 2; SET postgres=3D# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 =3D 898732 AND i= 5 =3D 123451; QUERY PLAN ---------------------------------------------------------------------------= ------------------------------------------------ Gather (cost=3D1000.00..127194.29 rows=3D1 width=3D24) (actual time=3D114= 8.047..1148.206 rows=3D0 loops=3D1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on tbloom (cost=3D0.00..126194.19 rows=3D1 width= =3D24) (actual time=3D1039.501..1039.501 rows=3D0 loops=3D3) Filter: ((i2 =3D 898732) AND (i5 =3D 123451)) Rows Removed by Filter: 3333333 Planning time: 0.580 ms Execution time: 1148.247 ms (8 rows) Starting with PostgreSQL 10 the example in the documentation is therefore w= rong. Attached a proposal to fix this. The new example starts with 100x red= uced rows (as suggested by Bruce in [1] and adds a note that the behavior c= hanges as soon as parallel execution is cheaper than the index access. Thoughts? Regards Daniel [1] https://www.postgresql.org/message-id/flat/20191105231854.GA26542%40mom= jian.us#7859b106ce614dd9530941196dad5bbc --_000_GVAP278MB01027D2186E612EEE3176B47D20C0GVAP278MB0102CHEP_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi,

any thoughts on this?

Regards
Daniel



From: Daniel Westermann (= DWE)
Sent: Sunday, September 27, 2020 17:58
To: Pg Docs <pgsql-docs@lists.postgresql.org>
Subject: Wrong example in the bloom documentation
 
Hi,

I've briefly discussed this with Bruce some time ago in [1].
Replaying the example referenced in the documentation does not give a Bitma= p Heap Scan on tbloom but a parallel seq scan with the default configuratio= n:

-- tested on head
postgres=3D# CREATE TABLE tbloom AS
postgres-#    SELECT
postgres-#      (random() * 1000000)::int as i1, postgres-#      (random() * 1000000)::int as i2, postgres-#      (random() * 1000000)::int as i3, postgres-#      (random() * 1000000)::int as i4, postgres-#      (random() * 1000000)::int as i5, postgres-#      (random() * 1000000)::int as i6 postgres-#    FROM
postgres-#   generate_series(1,10000000);
SELECT 10000000
postgres=3D# CREATE INDEX bloomidx ON tbloom USING bloom (i1, i2, i3, i4, i= 5, i6);
CREATE INDEX
postgres=3D# CREATE index btreeidx ON tbloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX
postgres=3D# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 =3D 898732 AND i= 5 =3D 123451;
            &nb= sp;            =             &nb= sp;            =        QUERY PLAN    &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;  
---------------------------------------------------------------------------= --------------------------------------------------
 Gather  (cost=3D1000.00..127220.00 rows=3D250 width=3D24) (actua= l time=3D2134.851..2221.836 rows=3D0 loops=3D1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on tbloom  (cost=3D0.00..12= 6195.00 rows=3D104 width=3D24) (actual time=3D1770.691..1770.692 rows=3D0 l= oops=3D3)
         Filter: ((i2 =3D 898732) A= ND (i5 =3D 123451))
         Rows Removed by Filter: 33= 33333
 Planning Time: 0.895 ms
 JIT:
   Functions: 6
   Options: Inlining false, Optimization false, Expressions true,= Deforming true
   Timing: Generation 65.512 ms, Inlining 0.000 ms, Optimization = 46.328 ms, Emission 40.658 ms, Total 152.499 ms
 Execution Time: 2288.056 ms
(12 rows)


As bloom was introduced in 9.6 I quickly tried with 9.6.17 and indeed for t= his version the example is correct:
postgres=3D# select version();
            &nb= sp;            =             &nb= sp;           version&nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;          
---------------------------------------------------------------------------= -------------------------------
 PostgreSQL 9.6.17 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1= 20190507 (Red Hat 8.3.1-4), 64-bit
(1 row)
postgres=3D# CREATE TABLE tbloom AS
postgres-#    SELECT
postgres-#      (random() * 1000000)::int as i1, postgres-#      (random() * 1000000)::int as i2, postgres-#      (random() * 1000000)::int as i3, postgres-#      (random() * 1000000)::int as i4, postgres-#      (random() * 1000000)::int as i5, postgres-#      (random() * 1000000)::int as i6 postgres-#    FROM
postgres-#   generate_series(1,10000000);
SELECT 10000000
postgres=3D# CREATE INDEX bloomidx ON tbloom USING bloom (i1, i2, i3, i4, i= 5, i6);
CREATE INDEX
postgres=3D# CREATE index btreeidx ON tbloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX
postgres=3D# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 =3D 898732 AND i= 5 =3D 123451;
            &nb= sp;            =             &nb= sp;            =         QUERY PLAN   &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;    
---------------------------------------------------------------------------= ----------------------------------------------------
 Bitmap Heap Scan on tbloom  (cost=3D178436.06..179392.83 rows=3D= 250 width=3D24) (actual time=3D2279.363..2279.363 rows=3D0 loops=3D1)
   Recheck Cond: ((i2 =3D 898732) AND (i5 =3D 123451))
   Rows Removed by Index Recheck: 2329
   Heap Blocks: exact=3D2288
   ->  Bitmap Index Scan on bloomidx  (cost=3D0.00..= 178436.00 rows=3D250 width=3D0) (actual time=3D994.406..994.406 rows=3D2329= loops=3D1)
         Index Cond: ((i2 =3D 89873= 2) AND (i5 =3D 123451))
 Planning time: 282.059 ms
 Execution time: 2286.138 ms
(8 rows)

The reason is that parallel execution is disabled by default in 9.6, and if= that is turned on the plan changes there as well:

postgres=3D# set max_parallel_workers_per_gather =3D 2;
SET
postgres=3D# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 =3D 898732 AND i= 5 =3D 123451;
            &nb= sp;            =             &nb= sp;            =       QUERY PLAN     &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;
---------------------------------------------------------------------------= ------------------------------------------------
 Gather  (cost=3D1000.00..127194.29 rows=3D1 width=3D24) (actual = time=3D1148.047..1148.206 rows=3D0 loops=3D1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on tbloom  (cost=3D0.00..12= 6194.19 rows=3D1 width=3D24) (actual time=3D1039.501..1039.501 rows=3D0 loo= ps=3D3)
         Filter: ((i2 =3D 898732) A= ND (i5 =3D 123451))
         Rows Removed by Filter: 33= 33333
 Planning time: 0.580 ms
 Execution time: 1148.247 ms
(8 rows)

Starting with PostgreSQL 10 the example in the documentation is therefore w= rong. Attached a proposal to fix this. The new example starts with 100x red= uced rows (as suggested by Bruce in [1] and adds a note that the behavior c= hanges as soon as parallel execution is cheaper than the index access.

Thoughts?

Regards
Daniel

[1] https://www.postgresql.org/message-id/flat/20191105231854.GA26542%40momjian= .us#7859b106ce614dd9530941196dad5bbc
--_000_GVAP278MB01027D2186E612EEE3176B47D20C0GVAP278MB0102CHEP_--