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 1p260S-00088j-Le for pgsql-admin@arkaria.postgresql.org; Mon, 05 Dec 2022 07:35:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p260Q-0007Yl-Rx for pgsql-admin@arkaria.postgresql.org; Mon, 05 Dec 2022 07:35:14 +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 1p260Q-0007XM-BL for pgsql-admin@lists.postgresql.org; Mon, 05 Dec 2022 07:35:14 +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 1p260N-00078j-7T for pgsql-admin@lists.postgresql.org; Mon, 05 Dec 2022 07:35:13 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id 432F9380420; Mon, 5 Dec 2022 08:35:08 +0100 (CET) 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 aaxlsg6NwfIk; Mon, 5 Dec 2022 08:35:08 +0100 (CET) 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 EF513380102; Mon, 5 Dec 2022 08:35:07 +0100 (CET) Message-ID: Date: Mon, 5 Dec 2022 08:35:07 +0100 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.5.0 To: pgsql-admin@lists.postgresql.org References: From: Holger Jakobs Subject: Re: need suggestion on querying big tables In-Reply-To: Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="------------ncKjDLUYmlxEniO1HEPiQl6M" 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) --------------ncKjDLUYmlxEniO1HEPiQl6M Content-Type: multipart/mixed; boundary="------------0D4hrgrMca3h5BvbPC19RTi8"; protected-headers="v1" From: Holger Jakobs To: pgsql-admin@lists.postgresql.org Message-ID: Subject: Re: need suggestion on querying big tables References: In-Reply-To: --------------0D4hrgrMca3h5BvbPC19RTi8 Content-Type: multipart/alternative; boundary="------------HBjFBcriX40GVjM0U2JvFyEk" --------------HBjFBcriX40GVjM0U2JvFyEk Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: base64 QW0gMDUuMTIuMjIgdW0gMDc6Mjkgc2NocmllYiBFYmluIEpvemVyOg0KPiBIaSBUZWFtLA0K PiBpbiBwb3N0Z3Jlc3FsIDExIHZlcnNpb24gd2UgaGF2ZSB0d28gdGFibGVzIG9mIHNpemUg NDM1IEdCIGFuZCAzNDcgR0IuDQo+IGlmIHdlIHF1ZXJ5IG9uIHNpbmdsZSB0YWJsZSBvciBp ZiB3ZSBhcmUgZG9pbmcgaW5uZXIgam9pbiBvbiBib3RoIHRoZSANCj4gYmlnIHRhYmxlcywg aXQgaXMgbm90IGRpc3BsYWNpbmcgYW55IG91dHB1dCwgaXQga2VlcHMgcnVubmluZw0KPg0K PiBXZSBjYW4gc2VlIHRoZSB3YWl0IGV2ZW50IGlzIElPIGFuZCBkaXJlY3REYXRhZmlsZSAu DQo+DQo+IFNlcnZlciBTcGVjIDogOCBjb3JlcyBhbmTCoCA2NEdCIFJBTQ0KPiBQRyBjb25m aWcgOiA1MyBHQihlZmZlY3RpdmVfY2FjaGUpLCAxMiBHQihzaGFyZWQgYnVmZmVyKQ0KPg0K PiBjYW4geW91IHBsZWFzZSBzdWdnZXN0IHNvbWUgaWRlYXMgLCBob3cgd2UgY2FuIHF1ZXJ5 IG9uIGJpZyB0YWJsZXMgYW5kIA0KPiBmYXN0ZW4gdGhlbSB0byBnZXQgdGhlIG91dHB1dD8/ DQo+DQo+IFRoYW5rcyAmIFJlZ2FyZHMsDQo+IC9FYmluLw0KDQpUaGVyZSBhcmUgYmFzaWNh bGx5IHR3byAic2VjcmV0cyIgdG8gcGVyZm9ybWFuY2UgaW4gdGhpcyBjYXNlOg0KDQogMS4g bWFrZSBzdXJlIHRoYXQgdGhlIGpvaW4gb3BlcmF0aW9uIHVzZXMgaW5kZXhlcw0KIDIuIG1h a2Ugc3VyZSB0aGF0IHlvdSBoYXZlIGVub3VnaCB3b3JrX21lbSBhdmFpbGFibGUNCg0Kd29y a19tZW0gY2FuIGJlIHNldCBpbiBwb3N0Z3Jlc3FsLmNvbmYsIGJ1dCBhbHNvIGluIGV2ZXJ5 IHNlc3Npb24gDQppbmRpdmlkdWFsbHkuIFNvIGRvbid0IHNldCBpdCB0b28gaGlnaCBnZW5l cmFsbHksIGJlY2F1c2UgaXQgd2lsbCBiZSANCmFsbG9jYXRlZCBmb3IgZXZlcnkgc29ydGlu ZyBhbmQgaGFzaGluZyBvcGVyYXRpb24uDQoNCktpbmQgUmVnYXJkcywNCg0KSG9sZ2VyDQoN Ci0tIA0KSG9sZ2VyIEpha29icywgQmVyZ2lzY2ggR2xhZGJhY2gsIFRlbC4gKzQ5LTE3OC05 NzU5MDEyDQoNCg== --------------HBjFBcriX40GVjM0U2JvFyEk Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Am 05.12.22 um 07:29 schrieb Ebin Jozer:
Hi Team,
in postgresql 11 version we have two tables of size 435 GB and 347 GB.=C2=A0
if we query on single table or if we are doing inner join on both the big tables, it is not displacing any output, it keeps running=C2=A0

We can see the wait event is IO and directDatafile .

Server Spec : 8 cores and=C2=A0 64GB RAM
PG config : 53 GB(effective_cache), 12 GB(shared buffer)=C2=A0=

can you please suggest some ideas , how we can query on big tables and fasten them to get the output??

Tha= nks & Regards,
Ebin

There are basically two "secrets" to performance in this case:

  1. make sure that the join operation uses indexes=
  2. make sure that you have enough work_mem available

work_mem can be set in postgresql.conf, but also in every session individually. So don't set it too high generally, because it will be allocated for every sorting and hashing operation.

Kind Regards,

Holger

--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
--------------HBjFBcriX40GVjM0U2JvFyEk-- --------------0D4hrgrMca3h5BvbPC19RTi8-- --------------ncKjDLUYmlxEniO1HEPiQl6M Content-Type: application/pgp-signature; name="OpenPGP_signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="OpenPGP_signature" -----BEGIN PGP SIGNATURE----- wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCY42fKwUDAAAAAAAKCRCKV5yTsxlhuqBt AJ45kVh54g2LEfAYgGyEN4GEAwl/UACdGE53xjE/B3eGiNiuzl4ISdaK+V4= =D/DJ -----END PGP SIGNATURE----- --------------ncKjDLUYmlxEniO1HEPiQl6M--