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 1p2Cnl-0002Yb-35 for pgsql-admin@arkaria.postgresql.org; Mon, 05 Dec 2022 14:50:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p2Cnj-00038v-SD for pgsql-admin@arkaria.postgresql.org; Mon, 05 Dec 2022 14:50:35 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p2Cni-00038l-SP for pgsql-admin@lists.postgresql.org; Mon, 05 Dec 2022 14:50:35 +0000 Received: from resdmta-c1p-023854.sys.comcast.net ([2001:558:fd00:56::d]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p2Cna-00047w-Fz for pgsql-admin@lists.postgresql.org; Mon, 05 Dec 2022 14:50:33 +0000 Received: from resomta-c1p-022589.sys.comcast.net ([96.102.18.236]) by resdmta-c1p-023854.sys.comcast.net with ESMTP id 2B5GpbExBLUpM2CnSp06ku; Mon, 05 Dec 2022 14:50:18 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=comcast.net; s=20190202a; t=1670251818; bh=yJLGXaNkj6j/zc8OoAScLzidGGmtSeklL98/s4Zo+tw=; h=Received:Received:From:Message-Id:Content-Type:Mime-Version: Subject:Date:To:Xfinity-Spam-Result; b=P3V/5EcRtzK0mAxYkSMsR3gvTSIHoFXZZAQlO//RlYVw0Eqo4uFyR0K6m1X1GOzK5 f50SJ/Q96tRnu/pUkE9vWtyjpIv8vhJ/oM6kwNf4MqU64iPvUZUfiIw6MOIXt7gtAP JfOx3uff3aidOv9pFgjzhU6rbCk9t6v4C+S43pTEAVavYsuvAv3g/1kVm12nB1I9f+ 6MbnpqZ/Wi1weq+nYgMz0xuk6ILO128edv/rso65QB6SFImqVCwoeeStTs7F+6/76r Q2vrhfD4ugJR4ZOtvQMmwr2zZsH0xQj1c8qM304jT9QpJpmaedHAkFv1HI2CUEwZKH X/sV5MmsL3nAw== Received: from smtpclient.apple ([IPv6:2601:240:d000:c160:886b:495b:dfc2:d2a9]) by resomta-c1p-022589.sys.comcast.net with ESMTPA id 2CnRpe2Juh8JX2CnSpT9kU; Mon, 05 Dec 2022 14:50:18 +0000 X-Xfinity-VMeta: sc=-100.00;st=legit From: John Wiencek Message-Id: <74322C20-BFDD-484A-9566-1644480F8253@comcast.net> Content-Type: multipart/alternative; boundary="Apple-Mail=_BFB70A05-2A6F-416D-B1D2-446640ECB021" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3696.120.41.1.1\)) Subject: Re: need suggestion on querying big tables Date: Mon, 5 Dec 2022 08:50:17 -0600 In-Reply-To: Cc: pgsql-admin To: Ebin Jozer References: X-Mailer: Apple Mail (2.3696.120.41.1.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_BFB70A05-2A6F-416D-B1D2-446640ECB021 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii Verify that your statistics are current. John > On Dec 5, 2022, at 12:29 AM, Ebin Jozer wrote: >=20 > Hi Team, > in postgresql 11 version we have two tables of size 435 GB and 347 GB.=20= > 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=20 >=20 > We can see the wait event is IO and directDatafile . >=20 > Server Spec : 8 cores and 64GB RAM > PG config : 53 GB(effective_cache), 12 GB(shared buffer)=20 >=20 > can you please suggest some ideas , how we can query on big tables and = fasten them to get the output?? >=20 > Thanks & Regards, > Ebin --Apple-Mail=_BFB70A05-2A6F-416D-B1D2-446640ECB021 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii Verify that your statistics are current.


John

On Dec 5, 2022, at 12:29 AM, = Ebin Jozer <ebinjozer@gmail.com> wrote:

Hi Team,
in postgresql 11 version we have two = tables of size 435 GB and 347 GB. 
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 

We can see the wait = event is IO and directDatafile .

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

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

Thanks & Regards,
Ebin

= --Apple-Mail=_BFB70A05-2A6F-416D-B1D2-446640ECB021--