Re: Top -N Query performance issue and high CPU usage

On Tue, Feb 3, 2026 at 1:50 PM yudhi s <learnerdatabase99(at)gmail(dot)com> wrote:

>
>
> On Tue, 3 Feb, 2026, 9:37 pm Adrian Klaver, <adrian(dot)klaver(at)aklaver(dot)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?
>

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!