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 1vnubZ-00DODj-0y for pgsql-general@arkaria.postgresql.org; Thu, 05 Feb 2026 08:20:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vnubY-00G3cU-1D for pgsql-general@arkaria.postgresql.org; Thu, 05 Feb 2026 08:20:48 +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 1vnubX-00G3cL-2u for pgsql-general@lists.postgresql.org; Thu, 05 Feb 2026 08:20:47 +0000 Received: from mail-ej1-x634.google.com ([2a00:1450:4864:20::634]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vnubU-00000001A5d-2VQV for pgsql-general@lists.postgresql.org; Thu, 05 Feb 2026 08:20:47 +0000 Received: by mail-ej1-x634.google.com with SMTP id a640c23a62f3a-b8845cb5862so96706766b.3 for ; Thu, 05 Feb 2026 00:20:44 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1770279638; cv=none; d=google.com; s=arc-20240605; b=OGAX0ujvJdcpPbMEr8aTnsw4AkD/PL/uLg0Q3tpzx3chFV2TzgbSOaWJxrT6gDej6Y 0BzPn0VpQjhNZsMKRMwzlJ4hzjCJQGhMXlLFd0OjGnIDkengWlaqVgZ7+I0oDwfWfJaa T1odQtcEJbh0I7TuA6JkTHcWJAnkV3J6isSSre8XPdHKS8lR2x4dwUOF5eR5wsNUvz18 d5Xds0jxtVPmR4Rne58+nSQ+CH/9IDZWsVvI77L2Fmhdl2Cxnpz3owf8mXxfTboS/owm j79WCI3XDzFs1PLsqD5cjN2EH72uUkSDbCfBUqM+mNYJztWRyebHHeIGhSsmZnbgMVFq 4KPg== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :dkim-signature; bh=rVsSwBiTCA1xXWz7kDJAh8taWyWDJe+obEJD1waJUjg=; fh=4rCG0PM8n0FOokGy8sSWDJpgPdCgp6yIXcpABJ7tUh0=; b=DJ0huI4duH4MrNhc+2CKjc9e62+v4MZgPLjvlAP7mBD4yNCLKZTe9OQ99ax/DUh3uQ 7WFcBbQtqPQch2lLZtY4H9S6mct5ENDYfGaVGYpdY2Ru6i1rSOEZv5EPzeCdA7GDjvo7 OgL+QfBZn8xl693cIYBHUN5oDsFbqCo5vCG51IAgmMGerypujHGxVag2zdvYEZGt1uHa Qyn2+4+ZGsrEPgHA49NX8mchhP1z8y+TKq+FVKCzn19zaJq6yXtFL8i8WcxT2t/v1b5z ihP7dXLZiCKgfBpXdkzqQ5eBo9/RtoxoukbM6tCfcpIlvjE6241HiHeYCLMnDW7+ljbu RVjg==; 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=1770279638; x=1770884438; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=rVsSwBiTCA1xXWz7kDJAh8taWyWDJe+obEJD1waJUjg=; b=fCF1jBg3BtvO4lFTFStHnbGz+8m6fDzXMe6SKtqQCRENo9B3E2vtYUp1NtPsds0mm/ 85o9nMK7XaDs8rTMrv6nDHnQ16Cvo3Nm9Sj0NCbZvujmTXgjCcD2Aq9dbUuneyxCAcxk Z3/QKs1ilZHRAGfcJy/Fywbwpc32rT89CHcI3BLm6eQlg+6Cyo/kBNbOuRKyoivnzlU5 9yaL5OMKLlE+1OhfETWC1OBOGQQ+NGXPKQuL2xUFLRpxMuTQ4PNrKAz3qhknGl3X03Hw EeUcY/hXR+cxxqhUcs6pgGKeQqoKotq4r/FieDGUViSTpFe2Syw6PmX5yA3XzqtjgrQy K0bw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1770279638; x=1770884438; h=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=rVsSwBiTCA1xXWz7kDJAh8taWyWDJe+obEJD1waJUjg=; b=hNEldAQr5P3yjsa2KLUwhorWnPed7vUPboPXz3A+pQ0qG2AQAfq8Z0gC+j1od540+x xUVhV0/th5xjfSmoXjWQ/OFYdP8i/m8XSv+37XvRANYQ/XjyPRsf1LzBK1niIRf+73up XAHcEClX/1D7N9ouFjraSCLI0kjm7mHpmXnLjVG+NwCStjkSnyYD1LG5eERWwkzdKwOC S1mPJcmeRLTR7fvS6FJaslck9J5YodVesyhEPXyE2ef0Xm0H0i+N4mQ7tG1SsutmMihx 61gl478OS8rfL9z0CdrEgUG0MMmB6Iv6rr96tm64rRntiELKhU/5exH8pUE9zzm6z9ms ahXg== X-Gm-Message-State: AOJu0YwPiP9CFtkISbeP+YWSxSmllt/SsNQFQ5lAZIJG6fuWazNvcA+E CesxzZh0PwdR2O3pSx1hKeSEH6fYPm2IOx2bje4CEnotVxgCI0deYCWEFIlSms3FLvBWG7Si5na EWXpeb8n9MePnDLR+WaIeMbmGjd0DA1BU2Ao8 X-Gm-Gg: AZuq6aIWQCX5P++m+mNcX+HqabL5Q1RNG3SgTXUtlKQOAHHk2aRpCeTsstC86nypEWO Cjzlh0gXUQO8IMOJJZlV/xN1G3ZEXUbO80LI9AoQrkQkg+mwFNKXjOmNAanv7KqVwxozEui4AB7 +zts1VIBHqfbSyA7SbYlrzn/2/QZx/16XOfPdZqzn2nILSpZA4s8+x3T9v3itSKVHEfMbwr+qoo AoOn6MopHUKB4tWqW0A/CYWqG/nzw6HsASfaNu9D38Gga5w2B523hlQVZd1xhE9JgYLrt9tLx0+ KYmH0M4Ake0zaRJdyZAfnLYgfOI3vkJryENMX3iW X-Received: by 2002:a17:907:7f8d:b0:b87:6b9c:6386 with SMTP id a640c23a62f3a-b8e9f6443dfmr406225966b.56.1770279637972; Thu, 05 Feb 2026 00:20:37 -0800 (PST) MIME-Version: 1.0 References: <55d333e9-5172-4ece-9723-a64d6137acee@aklaver.com> In-Reply-To: From: yudhi s Date: Thu, 5 Feb 2026 13:50:26 +0530 X-Gm-Features: AZwV_QjYJ5yUcX3Qf3k1PonYoKU9Hqf56xtHVqh4W0xYF7GhFtGvyX7Rb080nVw Message-ID: Subject: Re: Top -N Query performance issue and high CPU usage To: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000f97a1e064a0f587c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000f97a1e064a0f587c Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Feb 4, 2026 at 2:32=E2=80=AFAM Peter J. Holzer w= rote: > On 2026-02-04 00:20:20 +0530, yudhi s wrote: > > > > > > On Tue, 3 Feb, 2026, 9:37 pm Adrian Klaver, > wrote: > > > > On 2/3/26 07:59, Ron Johnson wrote: > > > > > > > > > > > There is no VARCHAR or CHAR; there is only TEXT. Thus, this is > 100% > > > expected and normal. > > > > What Ron is saying is that there are varchar and char types, but th= ey > > boil down to text per: > > > > https://www.postgresql.org/docs/current/datatype-character.html > > > > "text is PostgreSQL's native string data type, in that most built-i= n > > functions operating on strings are declared to take or return text > not > > character varying. For many purposes, character varying acts as > though > > it were a domain over text." > > > > As to performance see: > > > > " > > Tip > > > > There is no performance difference among these three types, apart > from > > increased storage space when using the blank-padded type, and a few > > extra CPU cycles to check the length when storing into a > > length-constrained column. While character(n) has performance > advantages > > in some other database systems, there is no such advantage in > > PostgreSQL; in fact character(n) is usually the slowest of the thre= e > > because of its additional storage costs. In most situations text or > > character varying should be used instead. > > " > > > > > > Thank you. I was looking into those casting(::text) in the explain plan > output > > in similar way (as it was happening for int8 to numeric join scenario) > and was > > thinking, may be it's spending some cpu cycles on doing these ::text > casting > > behind the scenes for that column and if there is someway(data type > change) to > > stop those. But from your explanation, it looks like those > representation in > > the query plan is normal and have no performance overhead as such. Than= ks > > again. > > > > In regards to the below, "nested loop" having response time of 100ms. I > > understand, here the casting function us now removed after changing the > data > > type of columns to match in both side of the join. > > > > So, is this expected to do a nested loop on 500k rows to take 100ms? > > > > -> Nested Loop (cost=3D262.77..1342550.91 rows=3D579149 width=3D20) (= actual > time=3D > > 6.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 > > > > Take a closer look at what that nested loop does: > > -> Nested Loop (cost=3D266.53..1548099.38 rows=3D411215 width= =3D20) > (actual time=3D6.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 > -> Index Scan Backward using txn_tbl_due_dt_idx on txn_tbl > df (cost=3D0.43..115471.09 rows=3D1417983 width=3D20) (actual time=3D0.0= 47..20.155 > rows=3D43626 loops=3D1) > Filter: ((txn_tbl_type_nm)::text =3D ANY > ('{.......}'::text[])) > Rows Removed by Filter: 17 > Buffers: shared hit=3D1816 > -> Materialize (cost=3D266.10..328.09 rows=3D58 width=3D1= 6) > (actual time=3D0.000..0.001 rows=3D12 loops=3D43626) > [lots of stuff] > > It scans backwards through txn_tbl_due_dt_idx which returns 43626 rows > and takes 20 milliseconds. > > For each of these rows it performs the "Materialize" node, which in turn > does lots of stuff, but whatever it is, it's fast and probably not worth > optimizing. The problem is that it's done 43626 times, which takes > another 120ms. > > So the most promising way to proceed it to try to reduce those 43626 > rows. Since the query is already scanning txn_tbl_due_dt_idx from newest > to oldest, is there a cutoff date where it is safe to ignore everything > older? If you can get it to scan only 2000 rows that would be 20 times > faster ... > > (I'm a bit confused by your naming. I'm guessing that the "Index Scan > Backward using txn_tbl_due_dt_idx" is there because of the "order by > df.tran_date desc", but the name of the index and the column don't > match.) > > Got it. Thank you. Yes , As folks here suggested, I created the new index on "tran_date" which is used as "order by desc" to only show the newest 1000 rows with a "limit" operator. And this index backward scan is getting used and helping to a large extent to drop the response time as opposed to early "table sequential scan'. Now , in this query as you said we need to see if we can further put a filter on the tran_date so as to minimize the records from table txn_tbl which would minimize the number of loops the materialize operation is happening. Need to check if that is possible without impacting business functionality. However, Is there any way this materialized operation will happen once i.e kind of a "HASH" Join fashion (where only once it will be scanned) rather in a nested loop fashion which is currently happening ~43K times? Another question i had in mind as there is the filter " Filter: ((txn_tbl_type_nm)::text =3D ANY ('{.......}'::text[]))" , will including this column in the index i.e. making it composite (TRAN_DATE, txn_tbl_type_nm) will be a good idea. Mainly in scenarios where this txn_tbl_type_nm will filter out more rows i.e. ~100-500K + rows? Regards Yudhi --000000000000f97a1e064a0f587c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Wed, Feb 4, = 2026 at 2:32=E2=80=AFAM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
On 2026-02-04 00:20:20 +0530, yudhi s wrote:
>
>
> On Tue, 3 Feb, 2026, 9:37 pm Adrian Klaver, <adrian.klaver@aklaver.com> = wrote:
>
>=C2=A0 =C2=A0 =C2=A0On 2/3/26 07:59, Ron Johnson wrote:
>
>=C2=A0 =C2=A0 =C2=A0>
>=C2=A0 =C2=A0 =C2=A0>
>=C2=A0 =C2=A0 =C2=A0> There is no VARCHAR or CHAR; there is only TEX= T.=C2=A0 Thus, this is 100%
>=C2=A0 =C2=A0 =C2=A0> expected and normal.
>
>=C2=A0 =C2=A0 =C2=A0What Ron is saying is that there are varchar and ch= ar types, but they
>=C2=A0 =C2=A0 =C2=A0boil down to text per:
>
>=C2=A0 =C2=A0 =C2=A0https://www.p= ostgresql.org/docs/current/datatype-character.html
>
>=C2=A0 =C2=A0 =C2=A0"text is PostgreSQL's native string data t= ype, in that most built-in
>=C2=A0 =C2=A0 =C2=A0functions operating on strings are declared to take= or return text not
>=C2=A0 =C2=A0 =C2=A0character varying. For many purposes, character var= ying acts as though
>=C2=A0 =C2=A0 =C2=A0it were a domain over text."
>
>=C2=A0 =C2=A0 =C2=A0As to performance see:
>
>=C2=A0 =C2=A0 =C2=A0"
>=C2=A0 =C2=A0 =C2=A0Tip
>
>=C2=A0 =C2=A0 =C2=A0There is no performance difference among these thre= e types, apart from
>=C2=A0 =C2=A0 =C2=A0increased storage space when using the blank-padded= type, and a few
>=C2=A0 =C2=A0 =C2=A0extra CPU cycles to check the length when storing i= nto a
>=C2=A0 =C2=A0 =C2=A0length-constrained column. While character(n) has p= erformance advantages
>=C2=A0 =C2=A0 =C2=A0in some other database systems, there is no such ad= vantage in
>=C2=A0 =C2=A0 =C2=A0PostgreSQL; in fact character(n) is usually the slo= west of the three
>=C2=A0 =C2=A0 =C2=A0because of its additional storage costs. In most si= tuations text or
>=C2=A0 =C2=A0 =C2=A0character varying should be used instead.
>=C2=A0 =C2=A0 =C2=A0"
>
>
> Thank you. I was looking into those casting(::text) in the explain pla= n output
> in similar way (as it was happening for int8 to numeric join scenario)= and was
> thinking, may be it's spending some cpu cycles on doing these ::te= xt casting
> behind the scenes for that column and if there is someway(data type ch= ange) to
> stop those. But from your explanation, it looks like those representat= ion in
> the query plan is normal and have no performance overhead as such. Tha= nks
> again.=C2=A0
>
> In regards to the below, "nested loop" having response time = of 100ms. I
> understand, here the casting function us now removed after changing th= e data
> type of columns to match in both side of the join.
>
> So, is this expected to do a nested loop on 500k rows to take 100ms? >
> -> =C2=A0Nested Loop =C2=A0(cost=3D262.77..1342550.91 rows=3D579149= width=3D20) (actual time=3D
> 6.406..107.946=C2=A0rows=3D1049 loops=3D1)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Join Filter: (df.ent_= id =3D m.ent_id)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Rows Removed by Join = Filter: 514648
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Buffers: shared hit= =3D1972
>

Take a closer look at what that nested loop does:

=C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Nested Loop=C2=A0 (cost=3D266.53..1= 548099.38 rows=3D411215 width=3D20) (actual time=3D6.009..147.695 rows=3D10= 49 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Join Filter: ((df.ent_id):= :numeric =3D m.ent_id)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Rows Removed by Join Filte= r: 513436
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Buffers: shared hit=3D1939=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Index Scan Bac= kward using txn_tbl_due_dt_idx on txn_tbl df=C2=A0 (cost=3D0.43..115471.09 = rows=3D1417983 width=3D20) (actual time=3D0.047..20.155 rows=3D43626 loops= =3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Filte= r: ((txn_tbl_type_nm)::text =3D ANY ('{.......}'::text[]))
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Rows = Removed by Filter: 17
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Buffe= rs: shared hit=3D1816
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Materialize=C2= =A0 (cost=3D266.10..328.09 rows=3D58 width=3D16) (actual time=3D0.000..0.00= 1 rows=3D12 loops=3D43626)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 [lots of stu= ff]

It scans backwards through txn_tbl_due_dt_idx which returns 43626 rows
and takes 20 milliseconds.

For each of these rows it performs the "Materialize" node, which = in turn
does lots of stuff, but whatever it is, it's fast and probably not wort= h
optimizing. The problem is that it's done 43626 times, which takes
another 120ms.

So the most promising way to proceed it to try to reduce those 43626
rows. Since the query is already scanning txn_tbl_due_dt_idx from newest to oldest, is there a cutoff date where it is safe to ignore everything
older? If you can get it to scan only 2000 rows that would be 20 times
faster ...

(I'm a bit confused by your naming. I'm guessing that the "Ind= ex Scan
Backward using txn_tbl_due_dt_idx" is there because of the "order= by
df.tran_date desc", but the name of the index and the column don't=
match.)


Got it. Thank you.

Yes , As folks here suggested, I created the new index on= =C2=A0 "tran_date" which is used as "order by desc" to = only show the newest 1000 rows with a "limit" operator. And this = index backward scan is getting used and helping to a=C2=A0large extent to d= rop the response time as opposed to=C2=A0early "table sequential scan&= #39;.

Now , in this query as you said we need to s= ee if we can further put a filter on the tran_date so as to minimize the re= cords from table txn_tbl which would minimize the number of loops the mater= ialize operation is happening. Need to check if that is possible without im= pacting business functionality. However,=C2=A0 Is there any way this materi= alized operation will happen once i.e kind of a "HASH" Join fashi= on (where only once it will be scanned) rather=C2=A0in a nested loop fashio= n which is currently happening ~43K times?=C2=A0

<= span style=3D"color:rgb(31,35,40);font-family:"Monaspace Neon",ui= -monospace,SFMono-Regular,"SF Mono",Menlo,Consolas,"Liberati= on Mono",monospace;font-size:12px;white-space:pre">Another question i = had in mind as there is the filter "=C2=A0Filter: ((txn_tbl_typ= e_nm)::text =3D ANY ('{.......}'::text[]))" , will including t= his column in the index i.e. making it composite (TRAN_DATE, txn_tbl_type_n= m)=C2=A0will be a good idea. Mainly in scenarios where this txn_tbl_type_nm= will filter out more rows i.e. ~100-500K=C2=A0+ rows?

=
Regards
Yudhi


--000000000000f97a1e064a0f587c--