Обсуждение: how do i find out how long a query took?

Is there a psql function I can call after making a query to find out how
long the last query took? (e.g. after running a create index on a table with
a lot of records, I would like to know how long it took). I know I could do
this using JDBC and wrapping the query in getTimeMillis() but I'm curious if
psql has a built-in function or if the client has a utility cmd.

thx
andy

Andy Kriger wrote:
> Is there a psql function I can call after making a query to find out how
> long the last query took? (e.g. after running a create index on a table with
> a lot of records, I would like to know how long it took). I know I could do
> this using JDBC and wrapping the query in getTimeMillis() but I'm curious if
> psql has a built-in function or if the client has a utility cmd.

Yes, you can use \timing in psql:

    test=> \timing
    Timing is on.
    test=> select * from pg_language;
     lanname  | lanispl | lanpltrusted | lanplcallfoid | lanvalidator |
    lanacl
    ----------+---------+--------------+---------------+--------------+--------

     sql      | f       | t            |             0 |         2248 | {=U}
     internal | f       | f            |             0 |         2246 | {=}
     c        | f       | f            |             0 |         2247 | {=}
    (3 rows)

    Time: 76.83 ms

and in 7.3 we will have log_duration which will send all query
durations to the server logs.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

select version();
                           version
-------------------------------------------------------------
 PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)


\timing does not work gives following

oss=> \timing
Showing only tuples.
\t: extra argument 'iming' ignored

Any ideas ??? or was it just 7.2.2??


On Mon, 30 Sep 2002, Bruce Momjian wrote:

> Andy Kriger wrote:
> > Is there a psql function I can call after making a query to find out how
> > long the last query took? (e.g. after running a create index on a table with
> > a lot of records, I would like to know how long it took). I know I could do
> > this using JDBC and wrapping the query in getTimeMillis() but I'm curious if
> > psql has a built-in function or if the client has a utility cmd.
>
> Yes, you can use \timing in psql:
>
>     test=> \timing
>     Timing is on.
>     test=> select * from pg_language;
>      lanname  | lanispl | lanpltrusted | lanplcallfoid | lanvalidator |
>     lanacl
>     ----------+---------+--------------+---------------+--------------+--------
>
>      sql      | f       | t            |             0 |         2248 | {=U}
>      internal | f       | f            |             0 |         2246 | {=}
>      c        | f       | f            |             0 |         2247 | {=}
>     (3 rows)
>
>     Time: 76.83 ms
>
> and in 7.3 we will have log_duration which will send all query
> durations to the server logs.
>
>

--
Darren Ferguson

Darren Ferguson wrote:
> select version();
>                            version
> -------------------------------------------------------------
>  PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96
> (1 row)
>
>
> \timing does not work gives following
>
> oss=> \timing
> Showing only tuples.
> \t: extra argument 'iming' ignored
>
> Any ideas ??? or was it just 7.2.2??

Oops, that is a 7.3 new feature.  There isn't a way in 7.2.X.  Perhaps
you can set your psql prompt to the current time but I can't figure out
how to do that either.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Mon, 30 Sep 2002, Bruce Momjian wrote:

> Darren Ferguson wrote:
> > select version();
> >                            version
> > -------------------------------------------------------------
> >  PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96
> > (1 row)
> >
> >
> > \timing does not work gives following
> >
> > oss=> \timing
> > Showing only tuples.
> > \t: extra argument 'iming' ignored
> >
> > Any ideas ??? or was it just 7.2.2??
>
> Oops, that is a 7.3 new feature.  There isn't a way in 7.2.X.  Perhaps
> you can set your psql prompt to the current time but I can't figure out
> how to do that either.

explain analyze in 7.2 should tell you how long the query took to run, but
won't give you the result set.