public inbox for [email protected]
help / color / mirror / Atom feedFrom: Kirk Wolak <[email protected]>
To: Gurjeet Singh <[email protected]>
Cc: Maciek Sakrejda <[email protected]>
Cc: Nikolay Samokhvalov <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Heikki Linnakangas <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Pavel Stehule <[email protected]>
Cc: Andrey Borodin <[email protected]>
Cc: Laurenz Albe <[email protected]>
Subject: Re: Proposal: :SQL_EXEC_TIME (like :ROW_COUNT) Variable (psql)
Date: Sun, 26 Feb 2023 23:07:45 -0500
Message-ID: <CACLU5mQ2hQpLbaQgPBM1ZBat-Q_Rk0wkmquQtrx6iO+Pc_s_vA@mail.gmail.com> (raw)
In-Reply-To: <CACLU5mRZMS0=bLn80AcoZ9Ad4MstLHWEr0nFWMDig3QX_BmKDw@mail.gmail.com>
References: <CACLU5mSRwHr_8z=enMj-nXF1tmC7+Jn5heZQNiKuLyxYUtL2fg@mail.gmail.com>
<[email protected]>
<CANNMO+KqqM0YpVUmVDec+9nku9ai9T=Y5Tp=pZ0rp1jR3d8kqA@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CAOtHd0D28EgtwVKw+AUeEiH9Ai+-vUZH=PtCePBgpDp0WBY93Q@mail.gmail.com>
<CANNMO++GPeo3v7DELX02stRqWcJGdAbkh3i04m5Vc5nC3kkHng@mail.gmail.com>
<CAOtHd0B8+FHsNKVGy8Bg39MX31eg2UpTUd1NxexZgVvOs-WUaA@mail.gmail.com>
<CACLU5mQKvCHUaCx_yooZJZ=Ud1Bddux48j7C2xF0cNQC8AqVQg@mail.gmail.com>
<CABwTF4U+N5oi2TmWzCEAVpWb5kM3V4jqcLFczDhcPypTrkqejQ@mail.gmail.com>
<CACLU5mRZMS0=bLn80AcoZ9Ad4MstLHWEr0nFWMDig3QX_BmKDw@mail.gmail.com>
On Fri, Feb 24, 2023 at 10:56 PM Kirk Wolak <[email protected]> wrote:
> On Fri, Feb 24, 2023 at 2:11 AM Gurjeet Singh <[email protected]> wrote:
>
>> On Thu, Feb 23, 2023 at 8:42 PM Kirk Wolak <[email protected]> wrote:
>>
> ...
> > I think like ROW_COUNT, it should not change because of internal
>> commands.
>> ...
>
> By using \timing, the user is explicitly opting into any overhead
>> caused by time-keeping. With this feature, the timing info will be
>> collected all the time. So do consider evaluating the performance
>> impact this can cause on people's workloads. They may not care for the
>> impact in interactive mode, but in automated scripts, even a moderate
>> performance overhead would be a deal-breaker.
>>
> Excellent point. I run lots of long scripts, but I usually set \timing
> on, just because I turn off everything else.
> I tested 2,000+ lines of select 1; (Fast sql shouldn't matter, it's the
> most impacted)
> Honestly, it was imperceptible, Maybe approximating 0.01 seconds
> With timing on: ~ seconds 0.28
> With timing of: ~ seconds 0.27
>
> The \timing incurs no realistic penalty at this point. The ONLY penalty
> we could face is the time to
> write it to the variable, and that cannot be tested until implemented.
> But I will do that. And I will
> report the results of the impact. But I do not expect a big impact. We
> update SQL_COUNT without an issue.
> And that might be much more expensive to get.
>
Okay, I've written and tested this using SQL_EXEC_ELAPSED (suggested name
improvement).
First, the instant you have ANY output, it swamps the impact. (I settled
on: SELECT 1 as v \gset xxx) for no output
Second, the variability of running even a constant script is mind-blowing.
Third, I've limited the output... I built this in layers (init.sql
initializes the psql variables I use), run_100.sql runs
another file (\i tst_2000.sql) 100 times. Resulting in 200k selects.
Executive Summary: 1,000,000 statements executed, consumes ~2 - 2.5
seconds of extra time (Total)
So, the per statement cost is: 2.5s / 1,000,000 = 0.000,0025 s per statement
Roughly: 2.5us
Unfortunately, my test lines look like this:
Without Timing
done 0.198215 (500) *total *98.862548 *min* 0.167614 *avg*
0.19772509600000000000 *max *0.290659
With Timing
done 0.191583 (500) *total* 100.729868 *min *0.163280 *avg
*0.20145973600000000000
*max *0.275787
Notice that the With Timing had a lower min, and a lower max. But a higher
average.
The distance between min - avg AND min - max, is big (those are for 1,000
selects each)
Are these numbers at the "So What" Level?
While testing, I got the distinct impression that I am measuring something
that changes, or that the
variance in the system itself really swamps this on a per statement basis.
It's only impact is felt
on millions of PSQL queries, and that's a couple of seconds...
Curious what others think before I take this any further.
regards, Kirk
>
> Thanks!
>
>>
>> [1]:
>> https://github.com/gurjeet/home/blob/08f1051fb854f4fc8fbc4f1326f393ed507a55ce/.bashrc#L278
>> [2]:
>> https://github.com/gurjeet/home/blob/08f1051fb854f4fc8fbc4f1326f393ed507a55ce/.bashrc#L262
>>
>> Best regards,
>> Gurjeet
>> http://Gurje.et
>>
>
view thread (18+ 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], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Proposal: :SQL_EXEC_TIME (like :ROW_COUNT) Variable (psql)
In-Reply-To: <CACLU5mQ2hQpLbaQgPBM1ZBat-Q_Rk0wkmquQtrx6iO+Pc_s_vA@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