ads' corner

\timing

With timing you get a client-side timer how long it takes to run a SQL command. This time includes the planning time, but also the time it takes to transfer back the result - so it might be slightly skewed if you run a very complicated query, or retrieve a very large result set. But in general it gives a very good idea for how long it takes to run a certain query:

postgres=# \c pgsqlphriday 
You are now connected to database "pgsqlphriday" as user "ads".
pgsqlphriday=# CREATE TABLE pgsqlphriday (id SERIAL, data FLOAT8);
CREATE TABLE
pgsqlphriday=# \timing 
Timing is on.
pgsqlphriday=# INSERT INTO pgsqlphriday (data) SELECT random() FROM generate_series(1, 10000000);
INSERT 0 10000000
Time: 9626,326 ms (00:09,626)

That is neat. If you us \timing multiple times, it toggles on and off.

pgsqlphriday=# \timing 
Timing is off.
pgsqlphriday=# \timing 
Timing is on.
pgsqlphriday=# \timing 
Timing is off.

You can also use on and off to activate or deactivate the timing:

pgsqlphriday=# \timing on
Timing is on.
pgsqlphriday=# \timing on
Timing is on.
pgsqlphriday=# \timing off
Timing is off.

\watch

The other psql feature I use often is \watch. This repeats the last query every so often:

pgsqlphriday=# SELECT * FROM pg_locks WHERE NOT granted; \watch 5
 locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath 
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-----+------+---------+----------
(0 rows)

When operating on live databases - as we all sometimes have to do - I usually have another terminal open and run above query. The terminal sits on another screen, and it tells me almost instantly if there are blocked queries (the granted flag being false). If by accident I run a query which blocks a production query, those queries show up in the terminal. The query is repeated by psql every 5 seconds, I just have to keep an eye on it.

Summary

psql has many other useful features and you should explore them. Check the manpage and scroll to "Metacommands", it's a long list. Maybe start with \gexec or \copy, or become familiar with all the \d variants. But \watch and \timing are the ones I use really often in daily work.