Quantcast

Customizing psql console to show execution times

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Customizing psql console to show execution times

Phoenix Kiula
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!

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Customizing psql console to show execution times

Richard Broersma Jr

--- Phoenix Kiula <[hidden email]> 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.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Customizing psql console to show execution times

Leif Biberg Kristensen
In reply to this post by Phoenix Kiula
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/

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Customizing psql console to show execution times

Scott Marlowe-2
In reply to this post by Phoenix Kiula
On 8/15/07, Phoenix Kiula <[hidden email]> 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.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Customizing psql console to show execution times

Tom Lane-2
In reply to this post by Phoenix Kiula
"Phoenix Kiula" <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Customizing psql console to show execution times

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Customizing psql console to show execution times

Tom Lane-2
In reply to this post by Richard Broersma Jr
Richard Broersma Jr <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org/
Loading...