From: Mladen Gogala <gogala.mladen@gmail.com>
To: Kenny Bachman <kenny.bachman17@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Postgresql 14 performance
Date: Sun, 21 Aug 2022 16:02:02 -0400
Message-ID: <1821f1b5-aabc-6cc6-1d5b-26381c6270d9@gmail.com> (raw)
In-Reply-To: <CAC0w7L+ezaB8tw-kh6rZC0RhG8ZwhLG_EkUErGEiUQ=f8uAC6g@mail.gmail.com>
References: <CAC0w7LL+uFgPvctiHMxrVZV-DnNVg2K_wFaeZwM96XQFkjk1uA@mail.gmail.com>
<62c9a8d8-8c0f-3c11-5cca-e05e126c25d8@gmail.com>
<CAC0w7L+ezaB8tw-kh6rZC0RhG8ZwhLG_EkUErGEiUQ=f8uAC6g@mail.gmail.com>
On 8/21/22 14:55, Kenny Bachman wrote:
> Hi Mladen,
>
> Thank you for your help.
>
> Yes, the plan is huge, because there is a view object. The system has
> a very low load today and there are 2-3 active sessions normally, no
> long-running query.
> I checked the activity view, and there is no wait event for my query,
> which has a NULL value for the wait event column.
>
> Here is my memory status:
>
> total used free shared buff/cache
> available
> Mem: 251 6 1 24 243 220
>
> How can we explain the cache doesn't work as expected? I mean, I
> expect more performant the second or third time running because of the
> cache. However, every time I run it I get the same exec time value.
Hi Kenny,
Please follow the execution with "perf top". If you get something like this:
then your problem may be with the JIT component. Jit has large influence
on the execution speed:
scott=# \timing on
Timing is on.
scott=# set jit=on;
SET
Time: 0.359 ms
scott=# select count(*) from emp e1,emp e2,emp e3,emp e4,emp e5,emp
e6,emp e7;
count
-----------
105413504
(1 row)
Time: 3785.608 ms (00:03.786)
scott=# set jit=off;
SET
Time: 0.278 ms
scott=# select count(*) from emp e1,emp e2,emp e3,emp e4,emp e5,emp
e6,emp e7;
count
-----------
105413504
(1 row)
Time: 4660.464 ms (00:04.660)
In my case, JIT speeds things up. However, I am aware of the situations
when JIT slows things down. BTW, what you've just described demonstrates
the need for a reliable event tracing mechanism, which was once
suggested by Jeff Holt.
Regards
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.comAttachments:z0Bz8CMokl57DLsH.png (image/png, 92.4K) downloadview image
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: pgsql-admin@postgresql.org
Cc: gogala.mladen@gmail.com, kenny.bachman17@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Postgresql 14 performance
In-Reply-To: <1821f1b5-aabc-6cc6-1d5b-26381c6270d9@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 DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox