Обсуждение: Customizing psql console to show execution times

In some examples posted to this forum, it seems to me that when people
execute queries in the psql window, they also see "90 ms taken"
(milliseconds), which denotes the time taken to execute the query.
Where can I set this option because I'm not seeing it in my psql
window on both Win XP and Linux. Thanks!

--- Phoenix Kiula <phoenix.kiula@gmail.com> wrote:

> In some examples posted to this forum, it seems to me that when people
> execute queries in the psql window, they also see "90 ms taken"
> (milliseconds), which denotes the time taken to execute the query.
> Where can I set this option because I'm not seeing it in my psql
> window on both Win XP and Linux. Thanks!

You can get the timing from psql two ways:

EXPLAIN ANALYZE your_query;

Or you can use of the psql internal commands to see the this of psql internal command type "\?"
from the psql command prompt.  The one you want is "\timing".  However, notice that "\timing" and
explain analyze do not exactly agree on the results they produce.

IIRC, "\time" also counts its own overhead.

Regards,
Richard Broersma Jr.

On Wednesday 15. August 2007, Phoenix Kiula wrote:
>In some examples posted to this forum, it seems to me that when people
>execute queries in the psql window, they also see "90 ms taken"
>(milliseconds), which denotes the time taken to execute the query.
>Where can I set this option because I'm not seeing it in my psql
>window on both Win XP and Linux. Thanks!

pgslekt=> select count(*) from persons;
 count
-------
 16704
(1 row)

pgslekt=> \timing
Timing is on.
pgslekt=> select count(*) from persons;
 count
-------
 16704
(1 row)

Time: 3,127 ms
pgslekt=> \timing
Timing is off.
pgslekt=>
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

On 8/15/07, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
> In some examples posted to this forum, it seems to me that when people
> execute queries in the psql window, they also see "90 ms taken"
> (milliseconds), which denotes the time taken to execute the query.
> Where can I set this option because I'm not seeing it in my psql
> window on both Win XP and Linux. Thanks!

If your on an OS with a time command (linux, windows with the gnu
utils ported to it) you can do:

time psql -c "select ..."

OR you can do

echo "\\\timing\nselect ...."|psql dbname

This is all from linux.  I don't know how this "windows" you speak of
works really.  Seriously, I stopped using it so long ago I'm lost when
I'm on it.

"Phoenix Kiula" <phoenix.kiula@gmail.com> writes:
> In some examples posted to this forum, it seems to me that when people
> execute queries in the psql window, they also see "90 ms taken"
> (milliseconds), which denotes the time taken to execute the query.
> Where can I set this option because I'm not seeing it in my psql
> window on both Win XP and Linux. Thanks!

I think you're looking for the \timing command?
http://www.postgresql.org/docs/8.2/static/app-psql.html
(under meta-commands, about halfway down the page)

            regards, tom lane

> I think you're looking for the \timing command?
> http://www.postgresql.org/docs/8.2/static/app-psql.html
> (under meta-commands, about halfway down the page)


Thanks everyone. "\timing" it is!

Happy camper.

Richard Broersma Jr <rabroersma@yahoo.com> writes:
> However, notice that "\timing" and
> explain analyze do not exactly agree on the results they produce.

\timing reports the total elapsed time as seen at the client.  EXPLAIN
ANALYZE tells you about the query execution path inside the server; so
it omits the costs of parsing, planning, and network data transmission.

EXPLAIN ANALYZE also has much higher measurement overhead (typically 2
gettimeofday() calls per row, rather than 2 per query as for \timing).
So it's not unheard of for E.A. to report a number *larger* than the
actual execution time, especially on cheap PC hardware which tends to
have dog-slow gettimeofday().

They're both useful, but you have to keep in mind what you're measuring
and for what purpose.

            regards, tom lane