My preferred client for PostgreSQL is psql, and while it is very powerful I like a few features a most:
Both are internal commands in
\timing is there for a very long time, but got improved at some point.
\watch came later, but is also there for a couple years now.
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:
That is neat. If you us
\timing multiple times, it toggles on and off.
You can also use
off to activate or deactivate the timing:
psql feature I use often is
\watch. This repeats the last query every so often:
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.
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
\copy, or become familiar with all the
\d variants. But
\timing are the ones I use really often in daily work.