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 1vnuqQ-00DQt7-19 for pgsql-general@arkaria.postgresql.org; Thu, 05 Feb 2026 08:36:10 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vnuqP-00GBKz-16 for pgsql-general@arkaria.postgresql.org; Thu, 05 Feb 2026 08:36:09 +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 1vnuqO-00GBKG-32 for pgsql-general@lists.postgresql.org; Thu, 05 Feb 2026 08:36:08 +0000 Received: from mail-ed1-x52b.google.com ([2a00:1450:4864:20::52b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vnuqJ-00000001ACS-13Gr for pgsql-general@lists.postgresql.org; Thu, 05 Feb 2026 08:36:08 +0000 Received: by mail-ed1-x52b.google.com with SMTP id 4fb4d7f45d1cf-658b6757f7fso49003a12.1 for ; Thu, 05 Feb 2026 00:36:02 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1770280561; cv=none; d=google.com; s=arc-20240605; b=e/PoRBNFJoVioerKOZhTPnvC844kraJ7jFfEOrYp1mDZ73dAayJPPNzhOqPgTz7mQh lhtHKLPD1FLiHL1KIevyI6d93nuds4dFmaRAYTp9OyKSRy38agSnhERb/fUxVSE/B5j1 pFeYTZlzXgqBw7R8+rcx3tdN0RSoxIoOjWMK5bpaIPA5yW8tnVCpJevws0itwTCwXUCM LHVn+K6wo0eEnCPkanF4JseFbAyRUOh6yDU6sPFMKyLCCPru2twn9uAdMj52MzYEvy3v n39S/QlhgJ5rZPahMuVFOjrJvKioMpo89dpa9RDriofbYMy0hegb+IddhQgKlFKTsWJ8 HnRw== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=NRps4CTcN/yiOO4TLLC6dYYtgth92P5XBC7hdZn+3yU=; fh=pYlkZbsn/WOPctNgubLE5p+7czOJWMZ4WXLc7+RHM5U=; b=NCVxmj8vx+8leqRrYIsjyJKSBuvGuYrheA0kwz8zDO4b+Fl9YYN4x/EyaNceVPeR3f GGIWAXVujVDFiYNG5w1kKK5ZXgLq2ziMLzNhoPsL+7jLsbRiVp76vUmM1BFDstdvd+i+ gEYLVuUWpdpJPbY8ErRqqfeXg1Fleo/hKtyfoPTAd/XpYXeSudJfet/b5GUm25Y1eUSo Gh57CHCm6FFMvhvz8Diqt4fXKPMwVpoSXc73FxoLh9N57HfqnfTxkaEj3mWQAbv/qtkK 0R4s65FBrSUrNsHyPYT7XA8NM1UYo+ZcCwQhX1E0f5TABORx4dKj/E8+EIOH1JV9Z/QN 4xVQ==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1770280561; x=1770885361; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=NRps4CTcN/yiOO4TLLC6dYYtgth92P5XBC7hdZn+3yU=; b=NyuYmkY/E4ceJBJNstAimrQOdPi8k/crgIoXnUtFEfwDSBzG+Vvu0hpy7io+sWMkZv qkMleKEYSBsSc0vLBjxPn/whp74MrRhSYnWie6k8ih505t7hDUMPmmnrGEuDyLwdYQ7w f4wYfRN4k+hSlERormaEMWXOdmXVgpqvJ06zYufWyCCraK6jYlifmrwVpxe6GwOgGKY1 nv449gxY3fR9LHV5duu9pKCgO3sx0n+ImGdQ74/KdeNerGFQfj+Qa3VXKLkQ7vcBjrpJ DQwE3i/ud1r2P4/0cJ3avr5yS/JGoRAwd9PJNO9bqhmN7OF+EhhvCP+xYYnq6S3GFFSX yeww== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1770280561; x=1770885361; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=NRps4CTcN/yiOO4TLLC6dYYtgth92P5XBC7hdZn+3yU=; b=QsJb3U/r62/80EQbkC5T8FdmRO+MPYFkvsoaYlh1n5xSRHwh01pkKdDO6Y8IGkvf5B VZFu9Nune0pwf0xgOC5Vmo9cK+4lTrQCuPbtGPPV0u7ybo9+b2fIiNZuBREW2IR2NnAt gc4bzKhP2np7rMP4aGopxctFzLtVR8UCjdjQpiZ2XLMzv7i5i+QYNAUgeaNal0CvOoo6 d16/C9ost/nizIohHSSTrtwqZ1aazS1YpZs7SSoIVhysIRdeE96dvI6IcL6HGRlg/oAx 5ednq3LVz27iFovPjQankUK6ZGM7Wx8l0+kyalkxX2RAMqSxud0YN++kTwt//H+dAysy tRzg== X-Gm-Message-State: AOJu0YyaArtZ/2q3NP5/QWy55HIXIy2mZyblesm7d3Rgfe3l/6zDFLaQ MU3lfHaFXCyZnBvHPpxUQNupHiQUgP1Rw/53jdVezRiq0I/trLmANBEXv4T/+9vCa1Y85rKnDcT Qrv+0dMIMSaO0KdIghs2/IR9re19kVBE= X-Gm-Gg: AZuq6aKKxoM7q7ht8jzjicEtQ0lgI4N0GciS+IFNKzRarK3ZD72ePO9J1bTNTlkkARU EBT+eTCBFc+F+m7k1t5r+w9pNhHdWR/t38Bfc3K4b+h94MkoKiGez0WtbfDVKIfiG7BOpXq7kd9 yb2mQNfS5mkRsZEkFs1qhDBg4MC0DKN4nyfMlYiucU3seJX6FZUPxMT5M3ZQ5UcG0/u/y3oM/FW bB14pMjNW36ViWiXwCQB+U5qqnpOvhx/XNVUUFPOXhEdIQE2U76/vuf9I7UxCY0OwjVgVGTQ4eS UGE82gmU0yqt5Y+CePQKc9/j8qggwg== X-Received: by 2002:a05:6402:254f:b0:658:bf9f:75be with SMTP id 4fb4d7f45d1cf-6596304c6acmr1133712a12.16.1770280560756; Thu, 05 Feb 2026 00:36:00 -0800 (PST) MIME-Version: 1.0 References: <323794933.277637.1770220093639@mail.yahoo.com> In-Reply-To: <323794933.277637.1770220093639@mail.yahoo.com> From: yudhi s Date: Thu, 5 Feb 2026 14:05:49 +0530 X-Gm-Features: AZwV_QiKg7iOpv5cGu_yu8JUtox2ZJAZtxyUc-9UcCK3k5HJrYZyI20aXwLnrlg Message-ID: Subject: Re: Top -N Query performance issue and high CPU usage To: felix.quintgz@yahoo.com Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000fa0cd6064a0f8f06" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000fa0cd6064a0f8f06 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Feb 4, 2026 at 9:18=E2=80=AFPM wrote: > > Have you tried adding an index to txn_tbl.txn_type? > And a vacuum on all tables? It seems the visibility map is outdated. > > I'm using https://explain.dalibo.com to view the plan visually; it's more > convenient. > > You could use the option to periodically save the results of queries with > common filters to another table, and then retrieve the results from that > table when a user performs a query with their own filters. > You should also store the user's query results somewhere for a while to > prevent excessive database access. > > I imagine this is some kind of dashboard that each user is taken to after > authenticating. It looks nice in presentations, but after a while in > production, it can make the system unusable. I had to remove similar char= ts > from the homepage of a system because after a year of work, they were > taking a minute to load. > > > On Saturday, January 31, 2026 at 08:30:33 AM GMT-5, yudhi s < > learnerdatabase99@gmail.com> wrote: > Hello Experts, > We have a "Select" query which is using three to five main transaction > tables (txn_tbl, txn_status, txn_decision, txn_sale, ath) holding ~2milli= on > rows in each of them(which is going to increase to have ~50-100million in > future) and others(6-7) tables out of which some are master and some othe= r > small tables. > > When we are running this query , and it's taking ~2-3seconds , however > when we hit this query from 10-15 session at same time its causing CPU > spike up to ~50-60% for the DB instance and this is increasing and touchi= ng > 90% when we are increasing the hits further to 40-50 times concurrently. > > This query is going to be called in the first page of an UI screen and is > supposed to show the latest 1000 rows based on a certain transaction date= . > This query is supposed to allow thousands of users to hit this same query > at the first landing page at the same time. > > Its postgres version 17. The instance has 2-VCPU and 16GB RAM. > > I have the following questions. > > 1)Why is this query causing a high cpu spike ,if there is any way in > postgres to understand what part/line of the query is contributing to the > high cpu time? > 2)How can we tune this query to further reduce response time and mainly > CPU consumption ? Is any additional index or anything will make this plan > better further? > 3) Is there any guidance or best practices exists , to create/design top > N-queries for such UI scenarios where performance is an important factor? > 4)And based on the CPU core and memory , is there any calculation by usin= g > which , we can say that this machine can support a maximum N number of > concurrent queries of such type beyond which we need more cpu cores > machines? > Below is the query and its current plan:- > https://gist.github.com/databasetech0073/6688701431dc4bf4eaab8d345c1dc65f > RegardsYudhi > > > As folks suggested , adding an index on "tran_date" and combining the CTE to two, and making the data type equal for the "ent_id" has helped reduce the response to a large extent. Now I am trying to see if we can reduce any further. As most of the time(100-20=3D~80ms) is now on materialize loop whi= ch is happening 43K times. Also thinking if adding "txn_tbl_type_nm" column to the index i.e. composite index on (tran_date,txn_tbl_type_nm) will be advisable , in cases where , ~500K rows will be filtered by the *txn_tbl_type_nm *filter criteria (currently its just 17 rows getting filtered though for this case)= . https://gist.github.com/databasetech0073/558377c1939a9291e7b72b1cbac7c9f9 -> Nested Loop (cost=3D263.20..1680202.56 rows=3D483106 width=3D20) (actual time=3D6.421..111.220 rows=3D1000 loops=3D1) Buffers: shared hit=3D6168 -> Nested Loop (cost=3D262.77..1342550.91 rows=3D579149 width=3D20) (*actua= l time=3D6.406..107.946* rows=3D1049 loops=3D1) Join Filter: (df.ent_id =3D m.ent_id) Rows Removed by Join Filter: 514648 Buffers: shared hit=3D1972 -> Index Scan Backward using txn_tbl_due_dt_idx on txn_tbl df (cost=3D0.43..115879.87 rows=3D1419195 width=3D20) (*actual time=3D0.019..2= 0.377* rows=3D43727 loops=3D1) *Filter: ((txn_tbl_type_nm)::text =3D ANY ('{TYPE1,TYPE2,TYPE3}'::text[]))* *Rows Removed by Filter: 17* Buffers: shared hit=3D1839 -> Materialize (cost=3D262.35..364.01 rows=3D58 width=3D8) (actual time=3D0.000..0.001 rows=3D12 loops=3D43727) Buffers: shared hit=3D133 Regards Yudhi --000000000000fa0cd6064a0f8f06 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On Wed, Feb 4, 2026= at 9:18=E2=80=AFPM <felix.qu= intgz@yahoo.com> wrote:

Have you tried adding an index to txn_tbl.txn_type?
And a vacuum on all tables? It seems the visibility map is outdated.

I'm using https://explain.dalibo.com to view the plan visually; it= 's more convenient.

You could use the option to periodically save the results of queries with c= ommon filters to another table, and then retrieve the results from that tab= le when a user performs a query with their own filters.
You should also store the user's query results somewhere for a while to= prevent excessive database access.

I imagine this is some kind of dashboard that each user is taken to after a= uthenticating. It looks nice in presentations, but after a while in product= ion, it can make the system unusable. I had to remove similar charts from t= he homepage of a system because after a year of work, they were taking a mi= nute to load.


=C2=A0On Saturday, January 31, 2026 at 08:30:33 AM GMT-5, yudhi s <learnerdatabase= 99@gmail.com> wrote:
=C2=A0Hello Experts,
=C2=A0We have a "Select" query which is using three to five main = transaction tables (txn_tbl, txn_status, txn_decision, txn_sale, ath) holdi= ng ~2million rows in each of them(which is going to increase to have ~50-10= 0million in future) and others(6-7) tables out of which some are master and= some other small tables.

When we are running this query , and it's taking ~2-3seconds , however = when we hit this query from 10-15 session at same time its causing CPU spik= e up to ~50-60% for the DB instance and this is increasing and touching 90%= when we are increasing the hits further to 40-50 times concurrently.

This query is going to be called in the first page of an UI screen and is s= upposed to show the latest 1000 rows based on a certain transaction date. T= his query is supposed to allow thousands of users to hit this same query at= the first landing page at the same time.

Its postgres version 17.=C2=A0 The instance has 2-VCPU and 16GB RAM.

I have the following=C2=A0questions.

1)Why is this query causing a high cpu spike ,if there is any way in postgr= es to understand what part/line of the query is contributing to the high cp= u time?
2)How can we tune this query to further reduce response time and mainly CPU= consumption ? Is any additional index or anything will make this plan bett= er further?
3) Is there any guidance or best practices exists , to create/design top N-= queries for such UI scenarios where performance is an important factor?
4)And based on the CPU core and memory , is there any calculation by using = which , we can say that this machine can support a maximum N number of conc= urrent queries of such type beyond which we need more cpu cores machines? Below is the query and its current plan:-https://gist.github.com/databasetech0073/6688701431dc4bf4eaab= 8d345c1dc65f
RegardsYudhi



As folks suggested , adding an index o= n "tran_date" and combining the CTE to two, and making the data t= ype equal for the "ent_id" has helped reduce the response to a la= rge extent. Now I am trying to see if we can reduce any further. As most of= the time(100-20=3D~80ms) is now on materialize loop which is happening 43K= times.

Also thinking if adding "txn_tbl_type= _nm" column to the index i.e. composite index on (tran_date,txn_tbl_ty= pe_nm) will be advisable , in cases where , ~500K rows will be filtered=C2= =A0 by the=C2=A0txn_t= bl_type_nm fil= ter criteria (currently its just 17 rows getting filtered though for this c= ase).


<= td id=3D"gmail-file-gistfile1-txt-LC36" class=3D"gmail-blob-code gmail-blob= -code-inner gmail-js-file-line" style=3D"box-sizing:border-box;padding:0px = 10px;line-height:20px;vertical-align:top;overflow:visible;font-family:"= ;Monaspace Neon",ui-monospace,SFMono-Regular,"SF Mono",Menlo= ,Consolas,"Liberation Mono",monospace;font-size:12px;white-space:= pre"> Rows Removed by Filter: 17=
Buffers: shared hit=3D6168
= -> Nested Loop (cost=3D262.77..1342550.91 rows=3D579149 width=3D20= ) (actual time=3D6.406..107.946 rows=3D1049 loops=3D1)
Join Filter: (df.ent_id= =3D m.ent_id)
Rows Removed by Join Filter: 514648
Buffers: shar= ed hit=3D1972
-> Index Scan Backward using txn_tbl= _due_dt_idx on txn_tbl df (cost=3D0.43..115879.87 rows=3D1419195 width=3D2= 0) (actual time=3D0.019..20.377 rows=3D43727 loops=3D1)
Filter: ((txn= _tbl_type_nm)::text =3D ANY ('{TYPE1,TYPE2,TYPE3}'::text[]))
Buffers: shared hit= =3D1839
-> Materialize (cost=3D262.35..364.01 row= s=3D58 width=3D8) (actual time=3D0.000..0.001 rows=3D12 loops=3D43727)
Buffers:= shared hit=3D133



Regards
Yudhi
--000000000000fa0cd6064a0f8f06--