16

I want to know the time that it takes to execute a query in Postgres. I see a lot of answers that suggest to use \timing, but I'm newbie in Postgres and I don't know how to use it.

Timur Shtatland
  • 12,024
  • 2
  • 30
  • 47
aName
  • 2,751
  • 3
  • 32
  • 61

1 Answers1

23

You can use \timing only with the command line client psql, since this is a psql command.

It is a switch that turns execution time reporting on and off:

test=> \timing
Timing is on.
test=> SELECT 42;
┌──────────┐
│ ?column? │
├──────────┤
│       42 │
└──────────┘
(1 row)

Time: 0.745 ms
test=> \timing
Timing is off.
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • ok then if I want to test a select query how i can do that I tried to do what you have done, but instead of `select 42`I do `select * from my_db` and I get `Commande \select invalide.` – aName Nov 14 '16 at 16:50
  • Try `SELECT` instead of `\select`, without the backslash. – Laurenz Albe Nov 14 '16 at 17:00
  • I'm getting a weird error, when a do \dt *.* I get a list that contains `my_schema` and `my_relation` but when I do a `select * from my_schema.my_relation` I get this error: `relation my_schema.my_relation doesn't exist` – aName Nov 14 '16 at 17:06
  • ok I solve it, I had just to use "my_schema"."my_relation", but still a question I want to test the time that takes to response to a query that returns more that 1000000, and I don't think that this option is the best – aName Nov 14 '16 at 17:17
  • 2
    If you want to measure the time it takes to output the result to a file, you can use `time` on UNIX like this: `time psql -P pager=off -c 'SELECT ...' >outfile` – Laurenz Albe Nov 15 '16 at 07:27
  • I get this error `ERROR: syntax error at or near "time" LIGNE 1 : time psql -P pager=off -c 'select * from "my_schema"."my_relation "` – aName Nov 15 '16 at 08:39
  • Which operating system are you on? Maybe you just have to install `time`. – Laurenz Albe Nov 15 '16 at 08:41
  • I'm using windows7 – aName Nov 15 '16 at 08:48
  • 1
    That makes things harder. You can execute a batch file with the `psql` command line sandwiched between two `echo %TIME%`. Then a simple subtraction will tell how long it took. – Laurenz Albe Nov 15 '16 at 09:13
  • @aName if you just want to figure out the time a query would take to run, you use "EXPLAIN ANLYZE ... query." which (kind of) really runs the query. – radiospiel Jun 25 '20 at 19:07
  • How to persist this across sessions? I want this to be enabled even after quitting the psql session. – Krishna Aug 23 '21 at 17:06
  • @Krishna To enable timing for all your `psql` sessions, run this in your shell (note the double backslash): `echo '\\timing on' >> ~/.psqlrc` – Timur Shtatland Apr 25 '23 at 20:04
  • 1
    @TimurShtatland thanks a lot. But I switched to `pgcli` long back, which already provides timing along with many other features. – Krishna Apr 26 '23 at 12:06