1

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)

4
  • Is the machine where you run this otherwise idle?
    – jjanes
    Nov 7, 2021 at 9:06
  • @jjanes yes, it is. Nov 7, 2021 at 10:13
  • I can't reproduce that. I think I would worry more about why one interactive execution was twice as slow as the other interactive one.
    – jjanes
    Nov 7, 2021 at 16:32
  • 2
    I can reproduce that with PostgreSQL 14.0 on Fedora Linux. You can use perf to see where the time is spent. Nov 8, 2021 at 8:15

1 Answer 1

1

(Not enough space to write a comment, so posting as an answer instead -- I'll change it when I hear back from the author)

Can you provide some more details about the "massively slower times" when using psql? What version are you using? In my tests, they look similar (3x in each method):

edb=# SELECT count(*) FROM ( SELECT generate_series(1,100000) ) AS t;
 count  
--------
 100000
(1 row)

Time: 13.328 ms
edb=# SELECT count(*) FROM ( SELECT generate_series(1,100000) ) AS t;
 count  
--------
 100000
(1 row)

Time: 12.400 ms
edb=# SELECT count(*) FROM ( SELECT generate_series(1,100000) ) AS t;
 count  
--------
 100000
(1 row)

Time: 12.242 ms
edb=# \q
[root@mdn /]# psql --command '\timing' --command 'SELECT count(*) FROM ( SELECT generate_series(1,100000) ) AS t;'
Timing is on.
 count  
--------
 100000
(1 row)

Time: 13.369 ms
[root@mdn /]# psql --command '\timing' --command 'SELECT count(*) FROM ( SELECT generate_series(1,100000) ) AS t;'
Timing is on.
 count  
--------
 100000
(1 row)

Time: 13.325 ms
[root@mdn /]# psql --command '\timing' --command 'SELECT count(*) FROM ( SELECT generate_series(1,100000) ) AS t;'
Timing is on.
 count  
--------
 100000
(1 row)

Time: 13.257 ms
1
  • updated answer with output of benchmarks and version. Nov 7, 2021 at 8:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.