Skip to main content
0 reactions

How to Time Queries in psql

Featured card titled Time Queries in psql, with a terminal window showing \timing on, Timing is on., Time: 145.428 ms and \timing off.
Turn on the psql timer to see how long each query takes.

Wondering how long a query takes? Turn on psql’s timer with \timing, and psql prints the time after every statement you run.

Every command and output on this page is real, from PostgreSQL 19 on macOS.

Syntax

SyntaxPlain text
\timing [ on | off ]
  • \timing: on its own, switches the timer on if it’s off, and off if it’s on.
  • on or off: sets it either way, whatever it was before.
  • psql shows the time in milliseconds. Anything longer than a second also appears in minutes and seconds.

\timing is described in the psql documentation (opens in a new tab).

Step 1: Turn On the Timer

Connect with psql, as Part 2 of the PostgreSQL Tutorial shows, and type:

postgres=#Plain text
\timing on
OutputPlain text
Timing is on.

Step 2: Time a Query

This query creates the numbers from 1 to 1,000,000 with generate_series and counts them, so it takes long enough to measure:

postgres=#SQL
SELECT count(*) FROM generate_series(1, 1000000);
OutputPlain text
  count
---------
 1000000
(1 row)

Time: 145.428 ms

The Time: line under the result is how long the statement took.

Step 3: Run It Again

postgres=#SQL
SELECT count(*) FROM generate_series(1, 1000000);
OutputPlain text
  count
---------
 1000000
(1 row)

Time: 124.992 ms

The time changes a little from run to run, so compare a few runs rather than trusting a single one.

Step 4: Turn the Timer Off

postgres=#Plain text
\timing off
OutputPlain text
Timing is off.

From now on, results come without the Time: line:

postgres=#SQL
SELECT count(*) FROM generate_series(1, 1000000);
OutputPlain text
  count
---------
 1000000
(1 row)

Common Mistakes

MistakeWhat happensDo this instead
Typing \timeinvalid command \time Try \? for help.The command is \timing.
Treating it as a server setting: SET timing = on;ERROR: unrecognized configuration parameter "timing"\timing is a psql command, so it starts with a backslash and needs no semicolon.

FAQ

What unit is the time in?

Milliseconds. When a statement takes longer than a second, psql also shows the time in minutes and seconds, as the documentation describes.

How do I keep the timer on every time I open psql?

Put \timing on in your psql settings file, .psqlrc, which psql reads every time it starts, as How to Customize psql with .psqlrc shows.

Conclusion

Type \timing on, and psql shows how long every statement takes, until you type \timing off. Run a query a few times to get a fair idea of its speed.

Additional Resources

Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada, and used with their permission.

0 comments