MIME-Version: 1.0
References: <CAEzWdqd0SPkZMYNaAbERdgczkfQqLmNV5JBMmF-F9s7KjxJ0gw@mail.gmail.com>
 <fde67103-e00e-460e-b2ec-eb539809e998@aklaver.com> <CAEzWdqd6LAHs+FiFeJLqDTS-QBLq6+foE1-mgBC9AXVpFmVnZg@mail.gmail.com>
 <vecavrvgzoxkks66nw2gvt3vot5lwbcm7f65iopgjbw72v2lc6@qd5leh3coj7g>
 <CAEzWdqcAbi0GYp_K64oZTeUeN3YN7-eFQ2m2fZDRvmnJx5Lb5w@mail.gmail.com>
 <CANzqJaDf4kKc89e_9YGZ+BorPbViYgPZomo1ssQO9utOHeStCg@mail.gmail.com>
 <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>
In-Reply-To: <55d333e9-5172-4ece-9723-a64d6137acee@aklaver.com>
From: yudhi s <learnerdatabase99@gmail.com>
Date: Wed, 4 Feb 2026 00:20:20 +0530
Message-ID: <CAEzWdqeSoEy=_67u1i2vvE8Jk7+hQdyeuTYxaRDWm3+ghLe2gw@mail.gmail.com>
Subject: Re: Top -N Query performance issue and high CPU usage
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Ron Johnson <ronljohnsonjr@gmail.com>, 
	"pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000000feb250649efea68"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqeSoEy%3D_67u1i2vvE8Jk7%2BhQdyeuTYxaRDWm3%2BghLe2gw%40mail.gmail.com>
Precedence: bulk

--0000000000000feb250649efea68
Content-Type: text/plain; charset="UTF-8"

On Tue, 3 Feb, 2026, 9:37 pm Adrian Klaver, <adrian.klaver@aklaver.com>
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 they
> 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-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."
>
> 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 three
> 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. Thanks 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=262.77..1342550.91 rows=579149 width=20) (*actual
time=6.406..107.946* rows=1049 loops=1)
              Join Filter: (*df.ent_id = m.ent_id*)
              Rows Removed by Join Filter: 514648
              Buffers: shared hit=1972

Regards
Yudhi

--0000000000000feb250649efea68
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"auto"><div><br><br><div class=3D"gmail_quote"><div dir=3D"ltr" =
class=3D"gmail_attr">On Tue, 3 Feb, 2026, 9:37 pm Adrian Klaver, &lt;<a hre=
f=3D"mailto:adrian.klaver@aklaver.com" target=3D"_blank" rel=3D"noreferrer"=
>adrian.klaver@aklaver.com</a>&gt; wrote:<br></div><blockquote class=3D"gma=
il_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-lef=
t:1ex">On 2/3/26 07:59, Ron Johnson wrote:<br>
<br>
&gt; <br>
&gt; <br>
&gt; There is no VARCHAR or CHAR; there is only TEXT.=C2=A0 Thus, this is 1=
00% <br>
&gt; expected and normal.<br>
<br>
What Ron is saying is that there are varchar and char types, but they <br>
boil down to text per:<br>
<br>
<a href=3D"https://www.postgresql.org/docs/current/datatype-character.html"=
 rel=3D"noreferrer noreferrer noreferrer" target=3D"_blank">https://www.pos=
tgresql.org/docs/current/datatype-character.html</a><br>
<br>
&quot;text is PostgreSQL&#39;s native string data type, in that most built-=
in <br>
functions operating on strings are declared to take or return text not <br>
character varying. For many purposes, character varying acts as though <br>
it were a domain over text.&quot;<br>
<br>
As to performance see:<br>
<br>
&quot;<br>
Tip<br>
<br>
There is no performance difference among these three types, apart from <br>
increased storage space when using the blank-padded type, and a few <br>
extra CPU cycles to check the length when storing into a <br>
length-constrained column. While character(n) has performance advantages <b=
r>
in some other database systems, there is no such advantage in <br>
PostgreSQL; in fact character(n) is usually the slowest of the three <br>
because of its additional storage costs. In most situations text or <br>
character varying should be used instead.<br>
&quot;<br></blockquote></div></div><div dir=3D"auto"><br></div><div dir=3D"=
auto">Thank you. I was looking into those casting(::text) in the explain pl=
an output in similar way (as it was happening for int8 to numeric join scen=
ario) and was thinking, may be it&#39;s spending some cpu cycles on doing t=
hese ::text casting behind the scenes for that column and if there is somew=
ay(data type change) to stop those. But from your explanation, it looks lik=
e those representation in the query plan is normal and have no performance =
overhead as such. Thanks again.=C2=A0</div><div dir=3D"auto"><br></div><div=
 dir=3D"auto">In regards to the below, &quot;nested loop&quot; having respo=
nse time of 100ms. I understand, here the casting function us now removed a=
fter changing the data type of columns to match in both side of the join.</=
div><div dir=3D"auto"><br></div><div dir=3D"auto">So, is this expected to d=
o a nested loop on 500k rows to take 100ms?</div><div dir=3D"auto"><br></di=
v><div dir=3D"auto"><span style=3D"font-size:12.8px;background-color:rgb(25=
0,248,255)">-&gt; =C2=A0Nested Loop =C2=A0(cost=3D262.77..1342550.91 rows=
=3D579149 width=3D20) (</span><b style=3D"font-size:12.8px;background-color=
:rgb(250,248,255)">actual time=3D6.406..107.946</b><span style=3D"font-size=
:12.8px;background-color:rgb(250,248,255)">=C2=A0rows=3D1049 loops=3D1)</sp=
an><br style=3D"font-size:12.8px;background-color:rgb(250,248,255)"><span s=
tyle=3D"font-size:12.8px;background-color:rgb(250,248,255)">=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Join Filter: (</span><b style=3D"font-si=
ze:12.8px;background-color:rgb(250,248,255)">df.ent_id =3D m.ent_id</b><spa=
n style=3D"font-size:12.8px;background-color:rgb(250,248,255)">)</span><br =
style=3D"font-size:12.8px;background-color:rgb(250,248,255)"><span style=3D=
"font-size:12.8px;background-color:rgb(250,248,255)">=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 Rows Removed by Join Filter: 514648</span><br s=
tyle=3D"font-size:12.8px;background-color:rgb(250,248,255)"><span style=3D"=
font-size:12.8px;background-color:rgb(250,248,255)">=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 Buffers: shared hit=3D1972</span></div><div dir=
=3D"auto"><br></div><div dir=3D"auto">Regards</div><div dir=3D"auto">Yudhi<=
/div><div dir=3D"auto"><br></div><div dir=3D"auto"><br></div><div dir=3D"au=
to"><br></div><div dir=3D"auto"><div class=3D"gmail_quote"><blockquote clas=
s=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;pad=
ding-left:1ex"></blockquote></div></div></div>

--0000000000000feb250649efea68--