Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vmiOY-00FYkq-0e for pgsql-general@arkaria.postgresql.org; Mon, 02 Feb 2026 01:06:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vmiOW-00Axt2-0y for pgsql-general@arkaria.postgresql.org; Mon, 02 Feb 2026 01:06:25 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vmiOV-00Axsu-0X for pgsql-general@lists.postgresql.org; Mon, 02 Feb 2026 01:06:24 +0000 Received: from fout-a7-smtp.messagingengine.com ([103.168.172.150]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1vmiOS-00000000aZz-2Exr for pgsql-general@lists.postgresql.org; Mon, 02 Feb 2026 01:06:23 +0000 Received: from phl-compute-07.internal (phl-compute-07.internal [10.202.2.47]) by mailfout.phl.internal (Postfix) with ESMTP id 48692EC0120; Sun, 1 Feb 2026 20:06:18 -0500 (EST) Received: from phl-frontend-03 ([10.202.2.162]) by phl-compute-07.internal (MEProxy); Sun, 01 Feb 2026 20:06:18 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:cc:content-transfer-encoding:content-type:content-type:date :date:from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to; s=fm2; t=1769994378; x=1770080778; bh=ir+G/EPW/FpWxQlPktDREsIx+678cu9NdBR2jW/r3h0=; b= KqijiKGUh7YcYMtTXYC1GQ6q+8SigLhJdR2aISyzlWe1zSunPtaq6M5H5YXipOtZ ezhoXxM250KIt5pWe5TO301z6/jM1Qfx8el9oB5/k1+93xYVQD0ZWR6TO+vdwlgX GgYTI30RBIYQQLPJRBXzWABwJUSgIAlQY52jvOOz1dwy0Xg1/p3uQTcjUu/CXvW+ ysKe31qp+cXjhegkQCnqQifO6/wj2JI0YxVW/EX9xq/XqA/+WuSqgA0tv79mP0ij 03tW6OW1bPR9rChJXFo3AFVHmT52zP4p7ZOUpfrwUqhjdfSCxzuxVljq3UTmR8aj AnMW92nIiKZvsqMNroEV0Q== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:content-type:date:date:feedback-id:feedback-id :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm3; t=1769994378; x= 1770080778; bh=ir+G/EPW/FpWxQlPktDREsIx+678cu9NdBR2jW/r3h0=; b=l Hcmw+w1iJ2rNpIVocnWU/HHJo6ahNFVqh1WpXNeW9z8SN0W9hWI50FJ3Hn7fKIA6 b11xNfmPzVgRLImuAEy2mZDJ5zsaWt/S9J0cV77a5j7bJYUYW+jlItDx3TWF29hH Z/S+DyE6R2J6dFadqLJvSzle0SyZ9MYGlHkNDpg2vtP4AbOgn/gDo1m0mGHdCrBu BLDeealet2zjcfBW5WKxNABIjh9+Vx06jsAXelZyKPckvXuJPGcyHGkZs4un5Cg+ izLzYODusvA8eH6uA+8ndXI7JYFHG1pvXEHXM6evy1la4oy3pMWDgt2Z4ZlCtNEP WiGKkDmNiWcaeWyKY78rg== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeefgedrtddtgddujeeifeduucetufdoteggodetrf dotffvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfurfetoffkrfgpnffqhgenuceu rghilhhouhhtmecufedttdenucenucfjughrpefkffggfgfuvfevfhfhjggtgfesthekre dttddvjeenucfhrhhomheptegurhhirghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhl rghvvghrsegrkhhlrghvvghrrdgtohhmqeenucggtffrrghtthgvrhhnpeefgeefieeutd fggfetgefgheekjeehteeileeigfetieekjedvieeviefgheevtdenucevlhhushhtvghr ufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrughrihgrnhdrkhhlrghvvg hrsegrkhhlrghvvghrrdgtohhmpdhnsggprhgtphhtthhopedvpdhmohguvgepshhmthhp ohhuthdprhgtphhtthhopehlvggrrhhnvghruggrthgrsggrshgvleelsehgmhgrihhlrd gtohhmpdhrtghpthhtohepphhgshhqlhdqghgvnhgvrhgrlheslhhishhtshdrphhoshht ghhrvghsqhhlrdhorhhg X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Sun, 1 Feb 2026 20:06:17 -0500 (EST) Message-ID: Date: Sun, 1 Feb 2026 17:06:17 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Top -N Query performance issue and high CPU usage To: yudhi s Cc: pgsql-general References: Content-Language: en-US From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 1/31/26 11:46, yudhi s wrote: > Thank you. > > > 1) Without even looking at the plan I'm going to say 2-VCPU and 16GB > RAM > and is insufficient resources for what you want to do. > > > Can you please explain a bit in detail, how much minimum VCPU and RAM > will be enough resources to suffice this requirement? and you normally > do that calculation? Don't know what the minimum requirements are. It would depend on many variables 1) The plan being chosen, which in turn depends on the schema information as well as the data turnover. 2) What the VCPU is actually emulating. 3) The efficiency of of the virtual machines/containers with regard to accessing memory and storage. 4) The service limits of the virtualization. 5) What the storage system and how performant it is. In other words this is something you will need to test and derive your own formula for. > > 2) You will need to provide the schema definitions for the tables > involved. > > Do you mean table DDL or just the index definitions on the tables should > help? Basically what you get in psql when you do \d some_table. > > Also i was trying to understand , by just looking into the "explain > analyze" output, is there any way we can tie the specific step in the > plan , which is the major contributor of the cpu resources? Such that we > can then try to fix that part rather than looking throughout the query > as its big query? > > And if any suggestion to improve the TOP-N queries where the base table > may have many rows in it. -- Adrian Klaver adrian.klaver@aklaver.com Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vmiOY-00FYkq-0e for pgsql-general@arkaria.postgresql.org; Mon, 02 Feb 2026 01:06:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vmiOW-00Axt2-0y for pgsql-general@arkaria.postgresql.org; Mon, 02 Feb 2026 01:06:25 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vmiOV-00Axsu-0X for pgsql-general@lists.postgresql.org; Mon, 02 Feb 2026 01:06:24 +0000 Received: from fout-a7-smtp.messagingengine.com ([103.168.172.150]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1vmiOS-00000000aZz-2Exr for pgsql-general@lists.postgresql.org; Mon, 02 Feb 2026 01:06:23 +0000 Received: from phl-compute-07.internal (phl-compute-07.internal [10.202.2.47]) by mailfout.phl.internal (Postfix) with ESMTP id 48692EC0120; Sun, 1 Feb 2026 20:06:18 -0500 (EST) Received: from phl-frontend-03 ([10.202.2.162]) by phl-compute-07.internal (MEProxy); Sun, 01 Feb 2026 20:06:18 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:cc:content-transfer-encoding:content-type:content-type:date :date:from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to; s=fm2; t=1769994378; x=1770080778; bh=ir+G/EPW/FpWxQlPktDREsIx+678cu9NdBR2jW/r3h0=; b= KqijiKGUh7YcYMtTXYC1GQ6q+8SigLhJdR2aISyzlWe1zSunPtaq6M5H5YXipOtZ ezhoXxM250KIt5pWe5TO301z6/jM1Qfx8el9oB5/k1+93xYVQD0ZWR6TO+vdwlgX GgYTI30RBIYQQLPJRBXzWABwJUSgIAlQY52jvOOz1dwy0Xg1/p3uQTcjUu/CXvW+ ysKe31qp+cXjhegkQCnqQifO6/wj2JI0YxVW/EX9xq/XqA/+WuSqgA0tv79mP0ij 03tW6OW1bPR9rChJXFo3AFVHmT52zP4p7ZOUpfrwUqhjdfSCxzuxVljq3UTmR8aj AnMW92nIiKZvsqMNroEV0Q== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:content-type:date:date:feedback-id:feedback-id :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm3; t=1769994378; x= 1770080778; bh=ir+G/EPW/FpWxQlPktDREsIx+678cu9NdBR2jW/r3h0=; b=l Hcmw+w1iJ2rNpIVocnWU/HHJo6ahNFVqh1WpXNeW9z8SN0W9hWI50FJ3Hn7fKIA6 b11xNfmPzVgRLImuAEy2mZDJ5zsaWt/S9J0cV77a5j7bJYUYW+jlItDx3TWF29hH Z/S+DyE6R2J6dFadqLJvSzle0SyZ9MYGlHkNDpg2vtP4AbOgn/gDo1m0mGHdCrBu BLDeealet2zjcfBW5WKxNABIjh9+Vx06jsAXelZyKPckvXuJPGcyHGkZs4un5Cg+ izLzYODusvA8eH6uA+8ndXI7JYFHG1pvXEHXM6evy1la4oy3pMWDgt2Z4ZlCtNEP WiGKkDmNiWcaeWyKY78rg== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeefgedrtddtgddujeeifeduucetufdoteggodetrf dotffvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfurfetoffkrfgpnffqhgenuceu rghilhhouhhtmecufedttdenucenucfjughrpefkffggfgfuvfevfhfhjggtgfesthekre dttddvjeenucfhrhhomheptegurhhirghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhl rghvvghrsegrkhhlrghvvghrrdgtohhmqeenucggtffrrghtthgvrhhnpeefgeefieeutd fggfetgefgheekjeehteeileeigfetieekjedvieeviefgheevtdenucevlhhushhtvghr ufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrughrihgrnhdrkhhlrghvvg hrsegrkhhlrghvvghrrdgtohhmpdhnsggprhgtphhtthhopedvpdhmohguvgepshhmthhp ohhuthdprhgtphhtthhopehlvggrrhhnvghruggrthgrsggrshgvleelsehgmhgrihhlrd gtohhmpdhrtghpthhtohepphhgshhqlhdqghgvnhgvrhgrlheslhhishhtshdrphhoshht ghhrvghsqhhlrdhorhhg X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Sun, 1 Feb 2026 20:06:17 -0500 (EST) Message-ID: Date: Sun, 1 Feb 2026 17:06:17 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Top -N Query performance issue and high CPU usage To: yudhi s Cc: pgsql-general References: Content-Language: en-US From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 1/31/26 11:46, yudhi s wrote: > Thank you. > > > 1) Without even looking at the plan I'm going to say 2-VCPU and 16GB > RAM > and is insufficient resources for what you want to do. > > > Can you please explain a bit in detail, how much minimum VCPU and RAM > will be enough resources to suffice this requirement? and you normally > do that calculation? Don't know what the minimum requirements are. It would depend on many variables 1) The plan being chosen, which in turn depends on the schema information as well as the data turnover. 2) What the VCPU is actually emulating. 3) The efficiency of of the virtual machines/containers with regard to accessing memory and storage. 4) The service limits of the virtualization. 5) What the storage system and how performant it is. In other words this is something you will need to test and derive your own formula for. > > 2) You will need to provide the schema definitions for the tables > involved. > > Do you mean table DDL or just the index definitions on the tables should > help? Basically what you get in psql when you do \d some_table. > > Also i was trying to understand , by just looking into the "explain > analyze" output, is there any way we can tie the specific step in the > plan , which is the major contributor of the cpu resources? Such that we > can then try to fix that part rather than looking throughout the query > as its big query? > > And if any suggestion to improve the TOP-N queries where the base table > may have many rows in it. -- Adrian Klaver adrian.klaver@aklaver.com