Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iS5Rs-00065R-78 for pgsql-docs@arkaria.postgresql.org; Tue, 05 Nov 2019 20:29:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iS5Rq-0003Fo-DB for pgsql-docs@arkaria.postgresql.org; Tue, 05 Nov 2019 20:29:06 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iS5Rp-0003Fh-Me for pgsql-docs@lists.postgresql.org; Tue, 05 Nov 2019 20:29:06 +0000 Received: from mail-eopbgr60105.outbound.protection.outlook.com ([40.107.6.105] helo=EUR04-DB3-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 1iS5Rl-0005xG-Ec for pgsql-docs@lists.postgresql.org; Tue, 05 Nov 2019 20:29:03 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=X471qbIXr01aqkaRUoemYEF/3sVEBKf6u83mtpSKPHgMCb1FmENAHzEywpjAoohfFUDmnwS6/YLVsezU+U0PsbwegmcGPU34FI+JxjzC2gN7CeQFAMZ5MvJM+if/ihZPfmtPZptlG05Q1r/uMrgK7Ved+dyxcc1Vi1MMY6rzuAvqrABtEy755EBNJIFxU0llZWqSrXZPB83Zy786E+wBoSCoMvywT/vNLD64pZoDceQMpNK4fKd9JErCei+VwBjM3YfzMT2UFgyP3ThjFYscYCnQOEXRNY/aCTmoh1ypijEvIGu2Z6TCbxGpJeEr+aPow1kOuaJFxsLCA2xOVjArPA== 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=i6W7n1eXnf/HKJspfVWX5tgs/tfFY7SMU8TGWwN/CLE=; b=bHe5/BiE1f5Ipe+8k1brYukVZkBpSAK/2E+TGY8QhsN5uMCEP74f+n0egFAFs+lrJlXB9pxoceQJOyR5OE1W5+VqGE/vw5KVGAUo6KM8xmd0tktan2FW/1fPPEYslsvWkPI/AwfkXnwBzTxsn2+zV0D+XymEMAowuEuD3uECOaUFgpLiQ7VpsGVWSDLV5KIfro9n4Z7Be505Zzv0rZl8LvCIFdWuJP7BXAfP9Ya4Tb7w2jWYey0mrCkcN8arQ1to2k9UJFuIgWatg3aVcgqDETzTty7RUKHVRP2G4aNbSsfBZkCDi3jmEQh4tTXgSCbphXgUlYlOk5dwEhGKLnTXHA== 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=i6W7n1eXnf/HKJspfVWX5tgs/tfFY7SMU8TGWwN/CLE=; b=XdD3FWviDYH/asu0ZFZXw8/XJ6axn0ssyPppECgbKQu7SP6OkqxWGimliaH2j3OLeVv6Fo/BdTeTALVdYmu4Uqj2Mt+2DHbKgdTOja7+WgWPdfcU2Alud0/yt+i4S75EkfKkUlMbVw2JNkWwJ9MKhw2GaEtHwlRuYJQ6lpiYsNE= Received: from VI1PR09MB4142.eurprd09.prod.outlook.com (10.186.156.15) by VI1PR09MB2496.eurprd09.prod.outlook.com (20.177.58.139) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.2408.24; Tue, 5 Nov 2019 20:28:57 +0000 Received: from VI1PR09MB4142.eurprd09.prod.outlook.com ([fe80::b571:fbf3:18d1:959a]) by VI1PR09MB4142.eurprd09.prod.outlook.com ([fe80::b571:fbf3:18d1:959a%6]) with mapi id 15.20.2408.024; Tue, 5 Nov 2019 20:28:57 +0000 From: "Daniel Westermann (DWE)" To: Bruce Momjian , "pgsql-docs@lists.postgresql.org" Subject: Re: Instead of using the bloom index, a parallel sequencial scan is used with this example Thread-Topic: Instead of using the bloom index, a parallel sequencial scan is used with this example Thread-Index: AQHVlBQCFQlGMh3ci0CMGiXtT87JNKd9BfQK Date: Tue, 5 Nov 2019 20:28:57 +0000 Message-ID: References: <157193022667.1049.11617112818811329354@wrigleys.postgresql.org>,<20191105200251.GA32473@momjian.us> In-Reply-To: <20191105200251.GA32473@momjian.us> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: yes X-MS-TNEF-Correlator: authentication-results: spf=none (sender IP is ) smtp.mailfrom=daniel.westermann@dbi-services.com; x-originating-ip: [37.201.6.8] x-ms-publictraffictype: Email x-ms-office365-filtering-correlation-id: af3ac310-a9cf-4e47-8291-08d7622ec920 x-ms-traffictypediagnostic: VI1PR09MB2496: x-ms-exchange-purlcount: 1 x-microsoft-antispam-prvs: x-ms-oob-tlc-oobclassifiers: OLM:10000; x-forefront-prvs: 0212BDE3BE x-forefront-antispam-report: SFV:NSPM;SFS:(10019020)(136003)(376002)(346002)(396003)(39840400004)(366004)(189003)(199004)(8676002)(5660300002)(52536014)(66066001)(21615005)(81166006)(76176011)(6306002)(2906002)(2501003)(26005)(7696005)(19627405001)(316002)(229853002)(102836004)(6436002)(55016002)(6246003)(110136005)(71200400001)(14454004)(71190400001)(8936002)(256004)(54896002)(81156014)(486006)(99286004)(9686003)(508600001)(6506007)(236005)(7736002)(3846002)(186003)(99936001)(25786009)(86362001)(446003)(11346002)(105004)(606006)(66476007)(66556008)(76116006)(66946007)(66616009)(33656002)(476003)(66446008)(74316002)(64756008)(5024004)(6116002)(966005)(14444005);DIR:OUT;SFP:1102;SCL:1;SRVR:VI1PR09MB2496;H:VI1PR09MB4142.eurprd09.prod.outlook.com;FPR:;SPF:None;LANG:en;PTR:InfoNoRecords;MX:1;A:1; received-spf: None (protection.outlook.com: dbi-services.com does not designate permitted sender hosts) x-ms-exchange-senderadcheck: 1 x-microsoft-antispam: BCL:0; x-microsoft-antispam-message-info: YJR71elnRH/lvfgXJHFFcCx5cYr1xJjEhlw3Gd+w6zZTUsyAKTxHvqPET4NgY7+GVJCkkytjKl6ARh7iCfSZMl0/0CxFjP7S3Z+s3w5L10YiOLgBFXcr9RY7BdEbqA8MtOVYw9CfU4qaQcrKlb1e2nrvcTcxG0vwYqMAgyTOg7ML+NnZFPKxLwN2UPcdtzbepLVt/RrYvCljCrN0WwgQY8t1ipl0wGWT2n77zJNrm66mhLbdE9zKAfip4Hjoa72XECjX7yUF19plZprIw7Avy2+vUuW8F9TwwKPHO5XvTN8opoxc3/6oNk0t+RVRebwiLY6nootYzepV+LXU1JLsa5n3Lc8zX2rb5DCyhFSovP9RQLGIIomtJCin8DYJ4HnXwGfKTgbVbNEFd+vDP5DN7y47T1R957Xtbh8Y+jE+ePMquVWwFHUnArAto8VyB5N/4as96R8tFGC6P6q9KM+aqrHFdKFXVdX7QSbWW+O4sLA= x-ms-exchange-transport-forked: True Content-Type: multipart/mixed; boundary="_004_VI1PR09MB4142FC41A11AE3FFAA52B1F9D27E0VI1PR09MB4142eurp_" MIME-Version: 1.0 X-OriginatorOrg: dbi-services.com X-MS-Exchange-CrossTenant-Network-Message-Id: af3ac310-a9cf-4e47-8291-08d7622ec920 X-MS-Exchange-CrossTenant-originalarrivaltime: 05 Nov 2019 20:28:57.8015 (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: uo8Bt8iD/4pmj3dy+GXmKZa/J/cKzG74DEFVxh1/d0cxGDKQBU4tBrpUjhrNU0OfBoaD20N1RwOCpMBoHiiTrWhpASsrrnkBnkpcXENqdAIeMdZXRislCzsWswcr5g9H X-MS-Exchange-Transport-CrossTenantHeadersStamped: VI1PR09MB2496 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_004_VI1PR09MB4142FC41A11AE3FFAA52B1F9D27E0VI1PR09MB4142eurp_ Content-Type: multipart/alternative; boundary="_000_VI1PR09MB4142FC41A11AE3FFAA52B1F9D27E0VI1PR09MB4142eurp_" --_000_VI1PR09MB4142FC41A11AE3FFAA52B1F9D27E0VI1PR09MB4142eurp_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable >>On Thu, Oct 24, 2019 at 03:17:06PM +0000, PG Doc comments form wrote: >> The following documentation comment has been logged on the website: >> >>Page: https://www.postgresql.org/docs/12/bloom.html >> Description: >I cleaned up your script and created an SQL file that can be piped into >psql, attached. I see the bloomidx index being used without and with >the ANALYZE, output attached. I tested this on git master, and back >through PG 10. Would you please run these queries and post the output: Thanks for having a look, Bruce. You test case is not exactly the same as i= n the documentation. For you "temporary table" test case I indeed see the b= loom index getting used. Doing the same with a normal table results in a pa= rallel seq scan. postgres=3D# SELECT version(); version ---------------------------------------------------------------------------= ------------------------------------------------- PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150= 623 (Red Hat 4.8.5-39), 64-bit (1 row) postgres=3D# SELECT name, current_setting(name), source postgres-# FROM pg_settings postgres-# WHERE source NOT IN ('default', 'override'); name | current_setting | ----------------------------+--------------------+---------------------- application_name | psql | client client_encoding | UTF8 | client DateStyle | ISO, MDY | configuration file default_text_search_config | pg_catalog.english | configuration file dynamic_shared_memory_type | posix | configuration file lc_messages | en_US.utf8 | configuration file lc_monetary | de_CH.UTF-8 | configuration file lc_numeric | de_CH.UTF-8 | configuration file lc_time | en_US.UTF-8 | configuration file listen_addresses | * | configuration file log_timezone | Europe/Zurich | configuration file max_connections | 100 | configuration file max_stack_depth | 2MB | environment variable max_wal_size | 1GB | configuration file min_wal_size | 80MB | configuration file port | 5432 | environment variable shared_buffers | 128MB | configuration file TimeZone | Europe/Zurich | configuration file Regards Daniel --_000_VI1PR09MB4142FC41A11AE3FFAA52B1F9D27E0VI1PR09MB4142eurp_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
>>On Thu, Oct 24, 2019 at 03:17:06PM +0000, PG Doc= comments form wrote:
>> The following documentation comment has b= een logged on the website:
>>
>>Page: https://www.postgresql.org/docs/12/bloom.html
>> Description:

>I cleaned up your script and created an SQL file that can be piped into=
>psql, attached.  I see the bloomidx index being used without and w= ith
>the ANALYZE, output attached.  I tested this on git master, and ba= ck
>through PG 10.  Would you please run these queries and post the ou= tput:

Thanks for having a look, Bruce. You test case is = not exactly the same as in the documentation. For you "temporary table= " test case I indeed see the bloom index getting used. Doing the same = with a normal table results in a parallel seq scan.

postgres=3D# SELECT version();
                    =                      = ;                 version   &n= bsp;                     =                      = ;            
----------------------------------------------------------------------= ------------------------------------------------------
 PostgreSQL 12.0  on x86_64-pc-linux-gnu, compiled by gcc (G= CC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)

postgres=3D# SELECT name, current_setting(name), source
postgres-#         FROM pg_settings
postgres-#         WHERE source NOT IN ('default',= 'override');
            name       &n= bsp;    |  current_setting   |       &nb= sp;              
----------------------------+--------------------+------------= ----------
 application_name           | psql  = ;             | client
 client_encoding            | UTF8 =               | client
 DateStyle                = ;  | ISO, MDY           | configuration file<= br>
 default_text_search_config | pg_catalog.english | configuration = file
 dynamic_shared_memory_type | posix         &= nbsp;    | configuration file
 lc_messages               &nb= sp;| en_US.utf8         | configuration file
 lc_monetary               &nb= sp;| de_CH.UTF-8        | configuration file
 lc_numeric               &nbs= p; | de_CH.UTF-8        | configuration file
 lc_time                 =    | en_US.UTF-8        | configuration file<= br>
 listen_addresses           | *   &= nbsp;              | configuration file<= br>
 log_timezone               | = Europe/Zurich      | configuration file
 max_connections            | 100 &= nbsp;              | configuration file<= br>
 max_stack_depth            | 2MB &= nbsp;              | environment variabl= e
 max_wal_size               | = 1GB                | configuration = file
 min_wal_size               | = 80MB               | configuration file<= br>
 port                 &nb= sp;     | 5432               |= environment variable
 shared_buffers             | 128MB=              | configuration file
 TimeZone                =   | Europe/Zurich      | configuration file
Regards
Daniel
--_000_VI1PR09MB4142FC41A11AE3FFAA52B1F9D27E0VI1PR09MB4142eurp_-- --_004_VI1PR09MB4142FC41A11AE3FFAA52B1F9D27E0VI1PR09MB4142eurp_ Content-Type: application/sql; name="bloom2.sql" Content-Description: bloom2.sql Content-Disposition: attachment; filename="bloom2.sql"; size=625; creation-date="Tue, 05 Nov 2019 20:28:14 GMT"; modification-date="Tue, 05 Nov 2019 20:28:57 GMT" Content-Transfer-Encoding: base64 ZHJvcCB0YWJsZSB0Ymxvb207CkNSRUFURSBUQUJMRSB0Ymxvb20gQVMKU0VMRUNUCiAgICAgKHJh bmRvbSgpICogMTAwMDAwMCk6OmludCBhcyBpMSwKICAgICAocmFuZG9tKCkgKiAxMDAwMDAwKTo6 aW50IGFzIGkyLAogICAgIChyYW5kb20oKSAqIDEwMDAwMDApOjppbnQgYXMgaTMsCiAgICAgKHJh bmRvbSgpICogMTAwMDAwMCk6OmludCBhcyBpNCwKICAgICAocmFuZG9tKCkgKiAxMDAwMDAwKTo6 aW50IGFzIGk1LAogICAgIChyYW5kb20oKSAqIDEwMDAwMDApOjppbnQgYXMgaTYKRlJPTSBnZW5l cmF0ZV9zZXJpZXMoMSwxMDAwMDAwMCk7CgpDUkVBVEUgRVhURU5TSU9OIGJsb29tOwoKQ1JFQVRF IElOREVYIGJsb29taWR4IE9OIHRibG9vbSBVU0lORyBibG9vbSAoaTEsIGkyLCBpMywgaTQsIGk1 LCBpNik7CkNSRUFURSBpbmRleCBidHJlZWlkeCBPTiB0Ymxvb20gKGkxLCBpMiwgaTMsIGk0LCBp NSwgaTYpOwoKRVhQTEFJTiBBTkFMWVpFIFNFTEVDVCAqIEZST00gdGJsb29tIFdIRVJFIGkyID0g ODk4NzMyIEFORCBpNSA9IDEyMzQ1MTsKQU5BTFlaRSB0Ymxvb207CkVYUExBSU4gQU5BTFlaRSBT RUxFQ1QgKiBGUk9NIHRibG9vbSBXSEVSRSBpMiA9IDg5ODczMiBBTkQgaTUgPSAxMjM0NTE7Cg== --_004_VI1PR09MB4142FC41A11AE3FFAA52B1F9D27E0VI1PR09MB4142eurp_--