public inbox for [email protected]
help / color / mirror / Atom feedFrom: Adrian Klaver <[email protected]>
To: yudhi s <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Top -N Query performance issue and high CPU usage
Date: Sun, 1 Feb 2026 17:06:17 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAEzWdqd6LAHs+FiFeJLqDTS-QBLq6+foE1-mgBC9AXVpFmVnZg@mail.gmail.com>
References: <CAEzWdqd0SPkZMYNaAbERdgczkfQqLmNV5JBMmF-F9s7KjxJ0gw@mail.gmail.com>
<[email protected]>
<CAEzWdqd6LAHs+FiFeJLqDTS-QBLq6+foE1-mgBC9AXVpFmVnZg@mail.gmail.com>
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
[email protected]
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 <[email protected]>)
id 1vmiOY-00FYkq-0e
for [email protected];
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 <[email protected]>)
id 1vmiOW-00Axt2-0y
for [email protected];
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 <[email protected]>)
id 1vmiOV-00Axsu-0X
for [email protected];
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 <[email protected]>)
id 1vmiOS-00000000aZz-2Exr
for [email protected];
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: <xms:ivh_aTMeR6Xfv0rx4LHA_1w5Rv3A9EB2LhkJuvpxwtWvpXbuffdhJw>
<xme:ivh_aT9NAS70NqUV-vgUu29iSmePiXa21sPTzZxnMrBuDgS7dr5FBt1GrcDi3-6C4
wRnyND1ppZ-RJf_vP15rOSkXWKPXa0nKQUjW1DEwjaSr7jdws8>
X-ME-Received: <xmr:ivh_af5zUc5YHuLLCE_IBpDuwEMgObgdiU77r_U4NnuNZ7yakatn8CMQ-eA1FD-DxUilGAkwGnkSEf0CWoYDfwA74PkU8tK8>
X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeefgedrtddtgddujeeifeduucetufdoteggodetrf
dotffvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfurfetoffkrfgpnffqhgenuceu
rghilhhouhhtmecufedttdenucenucfjughrpefkffggfgfuvfevfhfhjggtgfesthekre
dttddvjeenucfhrhhomheptegurhhirghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhl
rghvvghrsegrkhhlrghvvghrrdgtohhmqeenucggtffrrghtthgvrhhnpeefgeefieeutd
fggfetgefgheekjeehteeileeigfetieekjedvieeviefgheevtdenucevlhhushhtvghr
ufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrughrihgrnhdrkhhlrghvvg
hrsegrkhhlrghvvghrrdgtohhmpdhnsggprhgtphhtthhopedvpdhmohguvgepshhmthhp
ohhuthdprhgtphhtthhopehlvggrrhhnvghruggrthgrsggrshgvleelsehgmhgrihhlrd
gtohhmpdhrtghpthhtohepphhgshhqlhdqghgvnhgvrhgrlheslhhishhtshdrphhoshht
ghhrvghsqhhlrdhorhhg
X-ME-Proxy: <xmx:ivh_aS3g5mA15cEtEfvFjK6ZYaC9Uq47X6cmypDhv14MK4uGvk06CQ>
<xmx:ivh_aeBe-EjyOEIY66JzJOD4m9DxJzN0DxeTdZNg0eRAxcdCVgUr5A>
<xmx:ivh_ac2QFYtWy8fSBsi6t9ko6PClbfS8oVVf0wNCCBgYGogo0DegmQ>
<xmx:ivh_aQuI17b_2vBM5CoCIR-OqlpByxq-o-RdjTZKXvDnVqqAATFFXw>
<xmx:ivh_aQfuG44DZAOwCThKZJKsayyHFKPMZiQxgxHqwLQVOK7a0jsxm8k4>
Feedback-ID: i76984098:Fastmail
Received: by mail.messagingengine.com (Postfix) with ESMTPA; Sun,
1 Feb 2026 20:06:17 -0500 (EST)
Message-ID: <[email protected]>
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 <[email protected]>
Cc: pgsql-general <[email protected]>
References: <CAEzWdqd0SPkZMYNaAbERdgczkfQqLmNV5JBMmF-F9s7KjxJ0gw@mail.gmail.com>
<[email protected]>
<CAEzWdqd6LAHs+FiFeJLqDTS-QBLq6+foE1-mgBC9AXVpFmVnZg@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <[email protected]>
In-Reply-To: <CAEzWdqd6LAHs+FiFeJLqDTS-QBLq6+foE1-mgBC9AXVpFmVnZg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/;
List-Subscribe: <https://lists.postgresql.org/manage/;
List-Post: <mailto:[email protected]>
List-Owner: <mailto:[email protected]>
List-Archive: <https://www.postgresql.org/list/pgsql-general;
Archived-At: <https://www.postgresql.org/message-id/b87f6128-5df8-4fca-ae25-da7c7fa870eb%40aklaver.com;
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
[email protected]
view thread (24+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Top -N Query performance issue and high CPU usage
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox