Re: Proposal: :SQL_EXEC_TIME (like :ROW_COUNT) Variable (psql)

On Fri, Feb 24, 2023 at 2:11 AM Gurjeet Singh <gurjeet(at)singh(dot)im> wrote:

> On Thu, Feb 23, 2023 at 8:42 PM Kirk Wolak <wolakk(at)gmail(dot)com> wrote:
> > I love that my proposal for %T in the prompt, triggered some great
> conversations.
> >
> > This is not instead of that. That lets me run a query and come back
> HOURS later, and know it finished before 7PM like it was supposed to!
>
> Neat! I have this info embedded in my Bash prompt [1], but many a
> times this is not sufficient to reconstruct the time it took to run
> the shell command.
> ...
> > I think like ROW_COUNT, it should not change because of internal
> commands.
>
> +1
>
> > So, you guys +1 this thing, give additional comments. When the feedback
> settles, I commit to making it happen.
>
> This is definitely a useful feature. I agree with everything in the
> proposed UI (reporting in milliseconds, don't track internal commands'
> timing).
>
> I think 'duration' or 'elapsed' would be a better words in this
> context. So perhaps the name could be one of :sql_exec_duration (sql
> prefix feels superfluous), :exec_duration, :command_duration, or
> :elapsed_time.
>

I chose that prefix because it sorts near ROW_COUNT (LOL) when you do \SET

I agree that the name wasn't perfect...
I like SQL_EXEC_ELAPSED
keeping the result closer to ROW_COUNT, and it literally ONLY applies to SQL

> 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.

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
>