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 1oAorX-0008Ii-Ac for pgsql-admin@arkaria.postgresql.org; Mon, 11 Jul 2022 08:33:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oAorV-0002ue-5R for pgsql-admin@arkaria.postgresql.org; Mon, 11 Jul 2022 08:33:49 +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 1oAorU-0002uV-RG for pgsql-admin@lists.postgresql.org; Mon, 11 Jul 2022 08:33:48 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oAorQ-0004rh-CD for pgsql-admin@lists.postgresql.org; Mon, 11 Jul 2022 08:33:47 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id 21EAF380D45; Mon, 11 Jul 2022 10:33:42 +0200 (CEST) Received: from rs.plausibolo.de ([127.0.0.1]) by localhost (h2367442.stratoserver.net [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id Z2zm2Id6bFuh; Mon, 11 Jul 2022 10:33:42 +0200 (CEST) Received: from [192.168.178.28] (ip-037-201-152-231.um10.pools.vodafone-ip.de [37.201.152.231]) by rs.plausibolo.de (Postfix) with ESMTPSA id C5028380D3A; Mon, 11 Jul 2022 10:33:41 +0200 (CEST) Message-ID: <7ffca868-0728-6263-eac5-33cf56a46a54@jakobs.com> Date: Mon, 11 Jul 2022 10:33:41 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Thunderbird/91.10.0 Content-Language: de-DE To: pgsql-admin@lists.postgresql.org References: From: Holger Jakobs Subject: Re: Improve "select count(*)" query - takes more than 30 mins for some large tables In-Reply-To: Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="------------X7zDrotbq0jTJl0gkDKXD000" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --------------X7zDrotbq0jTJl0gkDKXD000 Content-Type: multipart/mixed; boundary="------------5lSs72cI8D0uJG61vDAcKn92"; protected-headers="v1" From: Holger Jakobs To: pgsql-admin@lists.postgresql.org Message-ID: <7ffca868-0728-6263-eac5-33cf56a46a54@jakobs.com> Subject: Re: Improve "select count(*)" query - takes more than 30 mins for some large tables References: In-Reply-To: --------------5lSs72cI8D0uJG61vDAcKn92 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: base64 SGkgUmFqLA0KDQpTaW5jZSBQb3N0Z3JlU1FMIHVzZXMgTVZDQyB0aGVyZSBpcyBubyBjdXJy ZW50IG51bWJlciBvZiByb3dzIGFzIGFuIA0KYWJzb2x1dGUgdHJ1dGguIEV2ZXJ5IENPVU5U KCopIGhhcyB0byBjaGVjayB0aGUgdmlzaWJpbGl0eSBvZiBlYWNoIHJvdyANCmZvciB0aGUg Y3VycmVudCB0cmFuc2FjdGlvbi4gVGhpcyBtYWtlcyBpdCBzbG93IGZvciBsYXJnZSB0YWJs ZXMuDQoNCklmIHlvdSBkb24ndCBuZWVkIHRoZSBleGFjdCBmaWd1cmUgYnV0IGp1c3QgYW4g ZXN0aW1hdGlvbiwgeW91IGNhbiBnZXQgDQp0aGlzIGZyb20gdGhlIGludGVybmFsIHN0YXRp c3RpY3MgdGFibGVzLiBBZnRlciBhbiBBTkFMWVpFIHRoZXkgYXJlIA0KY2xvc2UgdG8gdGhl ICJyZWFsIHZhbHVlIi4NCg0KVGhpcyBzaG93IHRoZSBudW1iZXIgb2Ygcm93cyBmb3IgYWxs IG5vbi1lbXB0eSB0YWJsZXMgaW4gYWxsIHZpc2libGUgDQpzY2hlbWFzLg0KDQpTRUxFQ1Qg bnNwbmFtZSBBUyBzY2hlbWFuYW1lLHJlbG5hbWUscmVsdHVwbGVzOjpudW1lcmljDQogwqAg RlJPTSBwZ19jbGFzcyBDIExFRlQgSk9JTiBwZ19uYW1lc3BhY2UgTiBPTiAoTi5vaWQgPSBD LnJlbG5hbWVzcGFjZSkNCiDCoCBXSEVSRQ0KIMKgIMKgIG5zcG5hbWUgSU4gKFNFTEVDVCB1 bm5lc3QoY3VycmVudF9zY2hlbWFzKGZhbHNlKSkpIEFORA0KIMKgwqDCoCByZWxraW5kPSdy JyBBTkQgcmVsdHVwbGVzID4gMA0KIMKgIE9SREVSIEJZIHJlbHR1cGxlcyBERVNDOw0KDQpS ZWdhcmRzLA0KDQpIb2xnZXINCg0KQW0gMTEuMDcuMjIgdW0gMDk6MTYgc2NocmllYiBSYWog a3VtYXI6DQo+IEhpLA0KPg0KPiBIb3cgY2FuIEkgaW1wcm92ZSAic2VsZWN0IGNvdW50KCop IiBmb3IgbGFyZ2VyIHRhYmxlcz8gSSdtIGRvaW5nIGEgZGIgDQo+IG1pZ3JhdGlvbiBhbmQg bmVlZCB0byB2YWxpZGF0ZSB0aGUgZGF0YSBjb3VudC4NCj4gInNlbGVjdCBjb3VudCgqKSAi IHF1ZXJpZXMgYXJlIHRha2luZyBtb3JlIHRoYW4gMzAgbWludXRlcyBmb3Igc29tZSANCj4g dGFibGVzIHdoaWNoIGlzIG1vcmUgdGhhbiB0aGUgZG93bnRpbWUgd2UgaGF2ZS4NCj4gV2ls bCB3b3JrX21lbSBpbmNyZWFzZSBoZWxwPyBvciBob3cgY2FuIGkgc3BlZWQgdXAgdGhpcyBy b3cgY291bnQ/DQo+DQo+IFRoYW5rcywNCj4gUmFqDQoNCi0tIA0KSG9sZ2VyIEpha29icywg QmVyZ2lzY2ggR2xhZGJhY2gsIFRlbC4gKzQ5LTE3OC05NzU5MDEyDQoNCg== --------------5lSs72cI8D0uJG61vDAcKn92-- --------------X7zDrotbq0jTJl0gkDKXD000 Content-Type: application/pgp-signature; name="OpenPGP_signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="OpenPGP_signature" -----BEGIN PGP SIGNATURE----- wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCYsvgZQUDAAAAAAAKCRCKV5yTsxlhurL3 AJ4oNBbflq4GhrXmrI35OPLpOQ2NfwCgknOFCJBySPUmMWrJpkSUrds2o08= =/cBj -----END PGP SIGNATURE----- --------------X7zDrotbq0jTJl0gkDKXD000--