public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron Johnson <[email protected]>
To: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: Top -N Query performance issue and high CPU usage
Date: Tue, 3 Feb 2026 15:51:44 -0500
Message-ID: <CANzqJaB1CYyULF_nWtzQyK_=qhAQXj4PsOdc9ihHPsj5CxV_GQ@mail.gmail.com> (raw)
In-Reply-To: <CAEzWdqeSoEy=_67u1i2vvE8Jk7+hQdyeuTYxaRDWm3+ghLe2gw@mail.gmail.com>
References: <CAEzWdqd0SPkZMYNaAbERdgczkfQqLmNV5JBMmF-F9s7KjxJ0gw@mail.gmail.com>
<[email protected]>
<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>
<[email protected]>
<CAEzWdqeSoEy=_67u1i2vvE8Jk7+hQdyeuTYxaRDWm3+ghLe2gw@mail.gmail.com>
On Tue, Feb 3, 2026 at 1:50 PM yudhi s <[email protected]> wrote:
>
>
> On Tue, 3 Feb, 2026, 9:37 pm Adrian Klaver, <[email protected]>
> 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=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
>
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.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
view thread (5+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Top -N Query performance issue and high CPU usage
In-Reply-To: <CANzqJaB1CYyULF_nWtzQyK_=qhAQXj4PsOdc9ihHPsj5CxV_GQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox