Let's say I have a very simple command like
SELECT count(*) FROM ( SELECT generate_series(1,100000) ) AS t;
If I open up a terminal and I run \timing
and then the above query, I'll consitantly get massively slower times then if I run
$ psql --command '\timing' --command 'SELECT count(*) FROM ( SELECT generate_series(1,100000) ) AS t;'
It's not even close. Is there any explanation of this behavior and variance in \timing
Output of Benchmark
When I run it in one go, I never get more than 5 ms
$ psql --command '\timing' --command 'SELECT count(*) FROM ( SELECT generate_series(1,100000) ) AS t;'
Timing is on.
count
--------
100000
(1 row)
Time: 4.497 ms
$ psql --command '\timing' --command 'SELECT count(*) FROM ( SELECT generate_series(1,100000) ) AS t;'
Timing is on.
count
--------
100000
(1 row)
Time: 4.437 ms
With the copy-paste into terminal I'm showing sometimes close to 20ms.
=# \timing
Timing is on.
ecarroll=# SELECT count(*) FROM ( SELECT generate_series(1,100000) ) AS t;
count
--------
100000
(1 row)
Time: 9.500 ms
=# SELECT count(*) FROM ( SELECT generate_series(1,100000) ) AS t;
count
--------
100000
(1 row)
Time: 19.017 ms
It doesn't seem to matter if I use Unix Domains Sockets or TCP.
Psql version
psql (PostgreSQL) 13.4 (Debian 13.4-0+deb11u1)
perf
to see where the time is spent.