Date: Tue, 3 Feb 2026 22:02:19 +0100
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Top -N Query performance issue and high CPU usage
Message-ID: <jasqhe5gccxmdmtog2qp64qylt64w6ownnqeppjh76vprks44j@cgdvwok4hdwz>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <CAEzWdqc-2O8mWGdeDhnzKrp7-kwC99sqJ+ArWUS38WuHUKP-UQ@mail.gmail.com>
 <CANzqJaBucLq65V9OH_Ruah7S=g+5s-L8yFkjELdAerZszzcOXA@mail.gmail.com>
 <CAEzWdqd3dcJoDDaG8t1nsgrdy7Tw-EvD1zMCXHy_uOgkLFAZdQ@mail.gmail.com>
 <CANzqJaDHtROOVSEB_i6Lo+wFki-vNuQ6wDTNGT-hCWRsMBpvZQ@mail.gmail.com>
 <CAEzWdqddHgqsZCachpQMgZmRRRuTQ9HxvdP6=Lhr+TYtPL=w-A@mail.gmail.com>
 <CANzqJaDxOsp=QNqa5bK4JXKOt7uHfxNpcTPZGWuVUMpGjNUB4Q@mail.gmail.com>
 <CAEzWdqc3hdnZbHTLd1kXxR3FUdxGpM7=Eea9c8Hp7QsF+HKMtg@mail.gmail.com>
 <CANzqJaAE6bevjj43W-94Bx1xGB9OPbUtOyXV2kaGpju1wt3k+A@mail.gmail.com>
 <55d333e9-5172-4ece-9723-a64d6137acee@aklaver.com>
 <CAEzWdqeSoEy=_67u1i2vvE8Jk7+hQdyeuTYxaRDWm3+ghLe2gw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="vavcntehgfhajpix"
Content-Disposition: inline
In-Reply-To: <CAEzWdqeSoEy=_67u1i2vvE8Jk7+hQdyeuTYxaRDWm3+ghLe2gw@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/jasqhe5gccxmdmtog2qp64qylt64w6ownnqeppjh76vprks44j%40cgdvwok4hdwz>
Precedence: bulk


--vavcntehgfhajpix
Content-Type: text/plain; protected-headers=v1; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
Subject: Re: Top -N Query performance issue and high CPU usage
MIME-Version: 1.0

On 2026-02-04 00:20:20 +0530, yudhi s wrote:
>=20
>=20
> On Tue, 3 Feb, 2026, 9:37 pm Adrian Klaver, <adrian.klaver@aklaver.com> w=
rote:
>=20
>     On 2/3/26 07:59, Ron Johnson wrote:
>=20
>     >
>     >
>     > There is no VARCHAR or CHAR; there is only TEXT.=A0 Thus, this is 1=
00%
>     > expected and normal.
>=20
>     What Ron is saying is that there are varchar and char types, but they
>     boil down to text per:
>=20
>     https://www.postgresql.org/docs/current/datatype-character.html
>=20
>     "text is PostgreSQL's native string data type, in that most built-in
>     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."
>=20
>     As to performance see:
>=20
>     "
>     Tip
>=20
>     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 advanta=
ges
>     in some other database systems, there is no such advantage in
>     PostgreSQL; in fact character(n) is usually the slowest of the three
>     because of its additional storage costs. In most situations text or
>     character varying should be used instead.
>     "
>=20
>=20
> Thank you. I was looking into those casting(::text) in the explain plan o=
utput
> in similar way (as it was happening for int8 to numeric join scenario) an=
d was
> thinking, may be it's spending some cpu cycles on doing these ::text cast=
ing
> behind the scenes for that column and if there is someway(data type chang=
e) 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. Thanks
> again.=A0
>=20
> In regards to the below, "nested loop" having response time of 100ms. I
> understand, here the casting function us now removed after changing the d=
ata
> type of columns to match in both side of the join.
>=20
> So, is this expected to do a nested loop on 500k rows to take 100ms?
>=20
> -> =A0Nested Loop =A0(cost=3D262.77..1342550.91 rows=3D579149 width=3D20)=
 (actual time=3D
> 6.406..107.946=A0rows=3D1049 loops=3D1)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 Join Filter: (df.ent_id =3D m.ent_id)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 Rows Removed by Join Filter: 514648
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 Buffers: shared hit=3D1972
>=20

Take a closer look at what that nested loop does:

        ->  Nested Loop  (cost=3D266.53..1548099.38 rows=3D411215 width=3D2=
0) (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 d=
f  (cost=3D0.43..115471.09 rows=3D1417983 width=3D20) (actual time=3D0.047.=
=2E20.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=3D16)=
 (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.)

        hjp

--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--vavcntehgfhajpix
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmmCYk8ACgkQ8g5IURL+
KF2ayA/+Kb6O/QLFG4xGhmbcOqVXf6DiebLwQGibvYvs0vwm/SPk3JMVCMheugPj
nOYM45g66PDo6W3TqNVYcWJvFOQfseDsgET0DIhg3ounSuR3gdNtLP2qMqly1fkK
87Ah9SJjlDsD2QvpD5TbLiDEelpUkVIE1MPA471/jSkrezDCx87nnBN0dhE0Ja6t
sYCZJXzVqlX1C1MrvOVAK6nAR+lbHEBzLftVg9YJGvwEJZ7gn9ab+vMwGlHtmfvY
/J0tuhPV35+Uud+opAVvgNmyXECHyUDadTKwqHfLliiabE7CB1aK8VSfPAmvRiC/
ncRUUwR3PmIQUNWNE5lP7mkxy4taWOmOxg1OnungUeKoUC5ekQGQeA4NxPm1P01H
H+KdX/RK9zW0MafqG617LPoUwAxG6u5Wel9sRruUyUzaBN0yhIfjj3y66aEJFHrA
8g+uUrvvZ76idUSzv95HH62kOrGKX5IH1JtXHu5lFqIHMpagU5f1cprfK7oOwVTK
GdtAhullfGFj113q8c5vmw4a7JexfJ8siunG6Dk5gy3MKfNfG2Ph9nQ5FS/ibTps
cP8zPBRntk5GvaYhzXTD8Kt01vbT8leD4V2CRkqAbjUrAT5Bw56V3tKkOOx3PmJE
omMVptUjFZHnVTJN6wGN86lpYTVruwBIeYHZ26sM/AfXZMcwiaE=
=5/c4
-----END PGP SIGNATURE-----

--vavcntehgfhajpix--