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 1vnNNS-0063Xs-0p for pgsql-general@arkaria.postgresql.org; Tue, 03 Feb 2026 20:52:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vnNNR-007DaN-15 for pgsql-general@arkaria.postgresql.org; Tue, 03 Feb 2026 20:52:01 +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 1vnNNQ-007DaF-37 for pgsql-general@lists.postgresql.org; Tue, 03 Feb 2026 20:52:00 +0000 Received: from mail-oa1-x35.google.com ([2001:4860:4864:20::35]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vnNNO-00000000ufC-1Jfc for pgsql-general@lists.postgresql.org; Tue, 03 Feb 2026 20:52:00 +0000 Received: by mail-oa1-x35.google.com with SMTP id 586e51a60fabf-4094b31a037so65551fac.1 for ; Tue, 03 Feb 2026 12:51:57 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1770151915; cv=none; d=google.com; s=arc-20240605; b=LvMoXeJHQ/GLknUGe7BUgpgRBzEKtuq3Ldl7/thT4Hv3qrb7owDtth0qQSZbhiszL6 fDTcUrR237xsgdMCefVjX6gYL3aDLNe9F0SOiY1Gep6oMMmQEcH01tNyiI3czpkCQxlp R4XhTBL36nnWu5AVptF4G7NxI4Y94UyrXVKH1CY+QLbJQ5FA6TDmc6gV4jPNRqB3eKIN WS50LeJ3Y2Wc5bQPcJVVtvMDAbjB3zgkAdzbrAq1fUmOg8Ih0Gf8CvqRybbLyPVjZj/3 1hIAXxOSNWfLph2xrSRXYTXlyWkEFnMqwYlGC98wRNXzmazH1qIXjpLOQmMAlx1bkmAb NkMQ== 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=zpEOMJeRDc1OnEqx8doJK02o7HvH4Fyc0V9MF44sVu4=; fh=2DoGC78b0mi8uZ4UY+9ocPF86GSVzeaJWqou/x907js=; b=jdosg2LmDitaKJkErtkzpU25JS2Wt7TkjUDDHPLAgXHVkWRwzITyObKyH/xuhVtfj3 Lc0ppAqf19ofjPmyi8g6gsa7pJWs4RrnN1URRlUtUCVVrGRcML7FxL3yv6qFlL8tAVtr tX2GfPsmHKQet31Sb9IvgeXL9hHSuKBgxQ0vD0W/c2xLzIvbnqdR7Uf47gmAy6h4Brm7 fbzn4Qa+fgrEvMsop7RSPIruPQJ4+HQd8i/YqXCB/LMsarFpABSsJ2v7bR1AJmc74/HY 9Mo74h5/Q8kK0rymR1C1Bj85Cb2UkWszmeoBiwJigO2E018I+2n48HRl8zgcBZI2bQz6 5XvA==; 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=1770151915; x=1770756715; 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=zpEOMJeRDc1OnEqx8doJK02o7HvH4Fyc0V9MF44sVu4=; b=dzIxW1GP/uCinzZgRFhqaQOqRpZnJQqUJODPNE5yHiydJWtnmToztxnEtwEmA17p0n vEM74jUbeYd/j0a0rnuwhZV2d0VmAd7x3tCFpKLt5PiQHFK5c9vzJbYQaM0SaikurD8F GrX/Uaabfhkrl0Cz5JhDxwARITZhrps1SRBSVF9hwFvuetz/Ek/oDkBjPtC1f9vmlEOJ cC4kzvHc20ls/h/Ay4AwSYm4LnHIgcKW8h5nhkIUwu7m0mbY9J4+seFpLWLrglf6ZtOb Akk8Jc1tAfAq1fngUWukhHEYfeQyEDRWazImiQpoCVVXW3mVDigeCd4kiVmstV/WOPDl JQjA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1770151915; x=1770756715; 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=zpEOMJeRDc1OnEqx8doJK02o7HvH4Fyc0V9MF44sVu4=; b=IUyCG2YoTPM6jpDG6UpaBvPY+9q+uEDqOoF+bJQD4uxa1fi31/b0O66lgFAk8qimRk Qd7ZEQyOBxmyX5vlYxZlz2nyxQ6+w7hBhp9udFlqktoMnsFC5Y+uCxQOXGm9QR0JgqTL R1M/cp/YP+6/MIb/G/43q1VBxM/sGJLI4AtqBcdlXgdqGE3oQhFu8zuTTqmaCpvA5pz1 54floaFIJZLFaulcVbqjvDzM/ZSBw/5pQ5cCiPOxfkR9noSvAAx0pKLY9o4JvtwkTnAd cNxfJR0ufwQO0ddXftig3NguWDUcmUz4pTcMC9QbPoJFLd+DQjPklNU2JOgxQrQJktnx fEGw== X-Gm-Message-State: AOJu0Yx3jqTWkuaY2KySjTzywhGbvKE6R0Qr7iJ5fWorOirznXZOSRYD FKgTC8Ge6cNEieVIyuUs88StN7UoHPUku0puNOPqoCqSgXV4nUEDqID7kENvpNb54umevtLBzrv +ccfa8QQ3IgQVM8TgEh8/rqjDKm7hIGNJsit/ X-Gm-Gg: AZuq6aK99HPGUkaL0L+11EKLkjubULdC5aYyx4Tx1Z3C+0EqxjDaj3W9Y1G17yAbpLl Y5OsKvLPWryMupxFX/fBlqXZEhOuz4lCu+GovQtSyp8IJbimRCVZV9T2zcm3v+VLVxOeP6+dDlA ORVDmr6H+hxrSEzki/T9dRXPp37TRXF1IIOxcB9RIHIAaAZlPVUyO6GKE+PQ9u7m2EwUCkypYav QSmGQ/uQ/CfWWH9a4IEz1i4V56ZuiQghrVL2qpaA+0FJcJoxUgy58YsKyDfb9FG7FhMQQfX X-Received: by 2002:a05:6808:d4a:b0:44d:c08a:e039 with SMTP id 5614622812f47-462d586ce3cmr446234b6e.10.1770151915480; Tue, 03 Feb 2026 12:51:55 -0800 (PST) MIME-Version: 1.0 References: <55d333e9-5172-4ece-9723-a64d6137acee@aklaver.com> In-Reply-To: From: Ron Johnson Date: Tue, 3 Feb 2026 15:51:44 -0500 X-Gm-Features: AZwV_QgPX-GWwytw251yqZ35_j7Vo2wpOBlp6zhdrm5cWeFR9kx7oWyifxZ9Qp8 Message-ID: Subject: Re: Top -N Query performance issue and high CPU usage To: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000001eea340649f19cdc" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000001eea340649f19cdc Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Feb 3, 2026 at 1:50=E2=80=AFPM 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 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? > HAVE YOU ANALYZED THE TABLES? > > -> Nested Loop (cost=3D262.77..1342550.91 rows=3D579149 width=3D20) (*a= ctual > 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 > Decompose complex problems into a small problem, then start adding stuff. https://gist.github.com/databasetech0073/6688701431dc4bf4eaab8d345c1dc65f In this case, I would run SELECT * FROM limited_txns, to get a base EXPLAIN, then strip out all WHERE clauses, the ORDER BY and the LIMIT then run it again for another EXPLAIN. Then add back lines 33-34 and EXPLAIN. Then line 7, etc, etc saving each EXPLAIN. See what makes it break. --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000001eea340649f19cdc Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Feb 3, 2026 at 1:50=E2=80=AFPM yu= dhi s <learnerdatabase99@= gmail.com> wrote:


= On Tue, 3 Feb, 2026, 9:37 pm Adrian Klaver, <adrian.klaver@aklave= r.com> wrote:
On 2/3/26 07:59, Ron Johnson wrote:

>
>
> There is no VARCHAR or CHAR; there is only TEXT.=C2=A0 Thus, this is 1= 00%
> expected and normal.

What Ron is saying is that there are varchar and char types, but they
boil down to text per:

https://www.pos= tgresql.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 pl= an output in similar way (as it was happening for int8 to numeric join scen= ario) and was thinking, may be it'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

In regards to the below, "nested loop" 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.

So, is this expected to d= o a nested loop on 500k rows to take 100ms?
HAVE YOU ANALYZED THE TABLES?
=C2=A0

-> =C2=A0Nested Loop =C2=A0(cost=3D262.77..1342550.= 91 rows=3D579149 width=3D20) (actual time=3D6.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
<= /div>

Decompose complex problems into a sma= ll problem, then start adding stuff.


In this case, I would run SELECT * FROM=C2=A0limited_txns, to get a base EXPLAIN, then = strip out all WHERE clauses, the ORDER BY and the LIMIT then run it again f= or another EXPLAIN.

Then add back lines 33-34 and = EXPLAIN.=C2=A0 Then line 7, etc, etc saving each EXPLAIN.=C2=A0 See what ma= kes it break.
=C2=A0
--
Death to <Redacted>, and butter sauce.
Don't boil me, I&= #39;m still alive.
<Redacted> lobster!
=
--0000000000001eea340649f19cdc--