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 1vmuMf-0002eK-26 for pgsql-general@arkaria.postgresql.org; Mon, 02 Feb 2026 13:53:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vmuMd-00E0P2-2A for pgsql-general@arkaria.postgresql.org; Mon, 02 Feb 2026 13:53:16 +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 1vmuMd-00E0Ou-11 for pgsql-general@lists.postgresql.org; Mon, 02 Feb 2026 13:53:16 +0000 Received: from mail-ed1-x531.google.com ([2a00:1450:4864:20::531]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vmuMb-00000000gOp-2dN1 for pgsql-general@lists.postgresql.org; Mon, 02 Feb 2026 13:53:15 +0000 Received: by mail-ed1-x531.google.com with SMTP id 4fb4d7f45d1cf-658078d6655so8649985a12.3 for ; Mon, 02 Feb 2026 05:53:13 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1770040392; cv=none; d=google.com; s=arc-20240605; b=b0eDLPNaerPTM/zLqjpZ80472jIXM2E2Fqzw9NAy/PvSHXuo6DqLRPjRHbrnUt706a 4tlZQQ4anLZQIwDA15IUMZnTByM7gQOS6rumfpOH3RjyqHNVuqinpSIgtPlsjenn+pJs /0xXMnAWHbR9rddioRx2FjaeHUBrjFXxiOt/NHk0hry8V9+7dMBcgkhwRTEKbsLJ58sV C2RBGFL2ViskDmhyRei4LDJaGwYndku7zZuWVUbsXEoaiiKY/JYMna6O1Y0bL7GyZ6g2 pYCQQdK8bE8Xv3Y6nksA5JBZIu5jcgnD1YwoyayTdpT/vT8UlDkylN835QzqEe/hYnpt 2Dgg== 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=Z/zAF++Y/OVV+S56p8TBt9KIvDg+ygees+5V1Ne9fxs=; fh=M5d6ZpcaQVHTXkkOH6MmskvUc9TU9yzFINFTBAvu35o=; b=WUXpHxRqj8v95udqD9clS+wR/3/3zUsKSwJ6RmLtyeoceICZFe39qt8QHv1RYUFRyz UfIv9LdSctRFUwO3g4/guvfXtcB2j817G0DcRAq5YyzgdoNhaML1RXWBN9IkBVnp6RpH 7esGbb5bLpbRGm8N/uIYLLd+RGZXnUua0/91ECsbqiXpfBJRdQyPuw6s2JB22Hf5IJ+b Kofakji8WC0UqHAV6mzBT5jA0d0c5E5v5KBBwQrcl6+BVUMXwhckwTnNDwpCu4Mb2XPU 9r2zu0MTkRikiGBELK+4LSK0SjabSLJqTsk8MCW2lQRhaW6pcaptp3oXaXfTJtJU8/Um BaMA==; 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=1770040392; x=1770645192; 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=Z/zAF++Y/OVV+S56p8TBt9KIvDg+ygees+5V1Ne9fxs=; b=ANBQiXTN1m3nTvKnDLD5+I8UlfX8lgaIh9mn6j4tJiy5dSoX3ocOME3fofeq/nL3j7 6ctcfYi0/UHQx3tI6vLynM8JG29/RGXCL69tz+Gu2pm6EftLg/SLK9Lbk/7Q1XSC1kCJ AsbqiguC4ZCIW1cTJ5xw+JHwqTk/s42hBCmgbXCGdT+BcKJna6ApCnrdfSlkDHahu/WR GGj2xcrI6woCJBKD0Wq8I15d4vx1qw3+rVJcFGyqDLocb9ehZ6pZ8aDQTpYr8IWz4KrG ZA1Sf+6wSdTY/veAH95/I/UwDQ9HONiymRllFWbLrIDCXo4koSds3UfZ49VYuCEXLeoe 5JlA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1770040392; x=1770645192; 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=Z/zAF++Y/OVV+S56p8TBt9KIvDg+ygees+5V1Ne9fxs=; b=cT7xYETvEjBi3dSMv809hNfrfZEIjSHpkkc87aYXgox+inoedf9CO16e7zNlhlPRat NNGBixymUUd0Gu9Kfzw8WT9qbUGabozcPEBwFMXpSZflXaMiQ0SiVIa82KtbZtHmBfAF RGAFuzGtYWX9vOfXWFbGUTiPIa76bzhmP0VGr+c2XkTznyr4of1qMh7GdsmXkLxQgspL 8jnUkdJWp1otDgxyJ0JGJYdrqKm+a8nQgfl8rln7pOnykZ0qPe3JLsP4qRygZeCckiIy xys3kganqJx27uK01ragBoiCuBaVFvZE6162r7aZkMCuMFRJGvCL43ZAoLOGrxBx8LR0 iorw== X-Gm-Message-State: AOJu0YwIJ+QcvJaBGjiIYeR4w2HYmqFb/V1DCnl78cPbgRvF06G8fPPS k91wpjWhHFGpD511QgtdeXWA1twtQQ+7TZyF3UsCTDb2bNgmZ9/Aoa3KdzldKNN/ebMnzsmKWOw kXgbKMY0fYvLZspVU4PIiK2nZfWVpxAk= X-Gm-Gg: AZuq6aK5BVyjEm+l2ODizy/7YR0raS1YuhmJUhxRYQZFLCrXW3YKZFtB9vX1FwkXbpj 1RKR2b70HZ5mtOdcNZfzAZWSH699EjpvvdZVxFpsSeWIrl9DY0nMdxk9+9vVkuSLiIktAyCtb9f 132yFB/vOK5VwM8v2NrKf67GXs+doiz9pg1OfU8f3d9cmOnhuJSxs596uqzPNSXTV304mf9X8sT ZOQ1YIkrcFFiDZWLIpuYfXs9KtrZGg0OWvopPJOsxDrONEtHvcMKnfT2GcyTYzovCrNjqWrMC/h FpcyvyPEPV/vFWhjh7/OfpJVjd6FZw== X-Received: by 2002:a05:6402:5108:b0:658:b6de:fe57 with SMTP id 4fb4d7f45d1cf-658de54355amr7589442a12.6.1770040392066; Mon, 02 Feb 2026 05:53:12 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: yudhi s Date: Mon, 2 Feb 2026 19:23:00 +0530 X-Gm-Features: AZwV_Qj_Pq0xG3U6wFNoiwaipWyCftDuKCouZhODVGMOXpam02BRmilG_MrthGc Message-ID: Subject: Re: Top -N Query performance issue and high CPU usage To: Ron Johnson Cc: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000ceb8d20649d7a461" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ceb8d20649d7a461 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Feb 2, 2026 at 7:04=E2=80=AFPM Ron Johnson wrote: > On Mon, Feb 2, 2026 at 6:39=E2=80=AFAM yudhi s > wrote: > >> >> >> On Mon, Feb 2, 2026 at 3:17=E2=80=AFAM Peter J. Holzer wrote: >> >>> >>> If you do have that many simultaneous accesses to the landing page, and >>> you can't speed up the query significantly (I take it you've seen the >>> suggestion to check whether there's an index on >>> APP_schema.txn_tbl.tran_date), then maybe you don't need to perform it >>> for every user? I don't know what the query is supposed to do, but >>> unless the "ent_id" is really a user id, it doesn't seem to be specific >>> to the user. So maybe you can cache the result for a minute or an hour >>> and show the same result to everybody who logs in during that time. >>> >>> >>> >> >> There was no index on column tran_date , I created one and it's making >> the query finish in ~200ms, a lot faster than in the past. Below is the >> portion of the query and its plan which actually consumes most of the >> resource and time post the new index creation. >> >> https://gist.github.com/databasetech0073/344df46c328e02b98961fab0cd22149= 2 >> >> 1) Now the part which takes time is the "nested loop" join on the >> "ent_id" column. Can we do anything to make it much better/faster? >> >> 2) Also another question I had was, with this new index the table scan >> of txn_tbl is now fully eliminated by the "Index Scan Backward" even i h= ave >> other columns from that table projected in the query, so how its getting >> all those column values without visiting table but just that index scan >> backward operation? >> > > Reading through EXPLAIN output isn't always a mystery. > > Search for "actual time" and you'll find row 53, which is the "deepest" > (most nested) row with the highest actual time. > > That tells you where the time is now spent, and what it's doing. > > > My apologies if i misunderstand the plan, But If I see, it's spending ~140ms(140ms-6ms) i.e. almost all the time now, in performing the below nested loop join. So my question was , is there any possibility to reduce the resource consumption or response time further here? Hope my understanding is correct here. -> Nested Loop (cost=3D266.53..1548099.38 rows=3D411215 width=3D20) (actual= time=3D *6.009..147.695* rows=3D1049 loops=3D1) Join Filter: ((df.ent_id)::numeric =3D m.ent_id) Rows Removed by Join Filter: 513436 Buffers: shared hit=3D1939 --000000000000ceb8d20649d7a461 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Mon, Feb 2, = 2026 at 7:04=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Mon, = Feb 2, 2026 at 6:39=E2=80=AFAM yudhi s <learnerdatabase99@gmail.com> wrote:=


On Mon, Feb 2, 2026 at 3:17= =E2=80=AFAM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:

If you do have that many simultaneous accesses to the landing page, and
you can't speed up the query significantly (I take it you've seen t= he
suggestion to check whether there's an index on
APP_schema.txn_tbl.tran_date), then maybe you don't need to perform it<= br> for every user? I don't know what the query is supposed to do, but
unless the "ent_id" is really a user id, it doesn't seem to b= e specific
to the user. So maybe you can cache the result for a minute or an hour
and show the same result to everybody who logs in during that time.

=C2=A0 =C2=A0 =C2=A0 =C2=A0

There w= as no index on column=C2=A0 tran_date =C2=A0, I created one and it's making the query finish in=C2=A0 ~20= 0ms, a lot faster than in the past. Below is the portion of the query and i= ts plan which actually consumes most of the resource and time post the new = index creation.

https= ://gist.github.com/databasetech0073/344df46c328e02b98961fab0cd221492

1) Now the part=C2=A0 which takes time is the "= nested loop" join on the "ent_id"=C2=A0 column. Can we do an= ything to make it much better/faster?

2) Also anot= her question I had was,=C2=A0=C2=A0with this new index the table scan of tx= n_tbl is now fully eliminated by the "Index Scan Backward" even i= have other columns from that table projected in the query, so how its gett= ing all those column values without visiting table but just that index scan= backward operation?

Reading through EXPLAI= N output isn't always a mystery.

Search for &q= uot;actual time" and you'= ;ll find row 53, which is the "deepest" (most nested) row with th= e highest=C2=A0actual time.
=

That tells you where the time is now spent, and what it= 's doing.



My apologies if i misunderstand=C2=A0the plan, But If I see,=C2=A0 =C2=A0= it's spending ~140ms(140ms-6ms) i.e. almost all the time now, in perfor= ming the below nested loop join. So my question was , is there any possibil= ity to reduce the resource consumption or response time further=C2=A0here?= =C2=A0 Hope my understanding is correct here.

-&= gt; Nested Loop (cost=3D266.53..1548099.38 rows=3D411215 width=3D20) (act= ual time=3D6.009..147.695 rows=3D1049 loops=3D1)
Join Filter: ((df.ent_id):= :numeric =3D m.ent_id)
Rows Removed by Join Filter: 51= 3436
Buffe= rs: shared hit=3D1939

=C2=A0
--000000000000ceb8d20649d7a461--