PostgreSQL’s interactive terminal,
psql, is an incredibly powerful tool for managing databases and executing queries. In this blog, we’ll discuss how to measure query execution time using“timing” and fetch all rows of a query result without paging using“\pset pager off”`. Let’s dive in step-by-step.
Why Enable Timing in PSQL?
When optimizing your SQL queries, it’s crucial to measure their execution time to understand their performance. Enabling timing in psql lets you see how long a query takes to execute.
Why Turn Off the Pager?
By default, psql uses a pager (like less) to display query results when the output spans multiple rows or exceeds the screen size. This requires user interaction to scroll through the data. If you want to avoid paging and fetch all rows instantly, you can turn off the pager using \pset pager off.
Steps to Enable Timing and Fetch Rows Without Paging
Follow these steps to configure psql for an optimal query experience:
1. Launch PSQL
First, connect to your PostgreSQL database using the psql command. Open your terminal and run:
psql -U your_username -d your_database
Replace your_username and your_database with your actual PostgreSQL username and database name.
2. Enable Timing
To enable query timing, run the following command inside the psql session:
\timing
This will toggle the timing feature ON, and you will see the message:
Timing is on.
Now, every SQL query you execute will display its execution time.
3. Turn Off the Pager
To fetch all rows without paging, disable the pager by executing:
\pset pager off
This ensures that query results are displayed directly in the terminal without requiring scrolling or user interaction.
You will see a confirmation message like:
Pager usage is off.
4. Execute a Query
Now, run your SQL queries to see the results along with their execution time. For example:
select * from employees;
If the table contains many rows, all rows will be displayed without paging, and the timing will show the query execution time at the end of the result.
If pager will not be off, it will show result in pages and need your intervention to move next page.
a
Conclusion
Using \timing and \pset pager off in psql allows you to measure query performance and view results seamlessly. These simple yet powerful commands can significantly enhance your workflow when working with PostgreSQL. Give them a try, and let us know how they work for you!