Ask a Question
Report a Bug
This page in other versions:
Warning: This documentation is for a pre-release version of pgAdmin 4
The Query Tool is a powerful, feature-rich environment that allows you to
execute arbitrary SQL commands and review the result set. You can access the
Query Tool via the Query Tool menu option on the Tools menu, or through the
context menu of select nodes of the Browser tree control. The Query Tool
allows you to:
You can open multiple copies of the Query tool in individual tabs
simultaneously. To close a copy of the Query tool, click the X in the
upper-right hand corner of the tab bar.
The Query Tool features two panels:
The toolbar is described in the following subsections.
The SQL editor panel is a workspace where you can manually provide a query,
copy a query from another source, or read a query from a file. The SQL editor
features syntax coloring and autocompletion.
To use autocomplete, begin typing your query; when you would like the Query
editor to suggest object names or commands that might be next in your query,
press the Control+Space key combination. For example, type “*SELECT * FROM* ”
(without quotes, but with a trailing space), and then press the Control+Space
key combination to select from a popup menu of autocomplete options.
After entering a query, select the Execute/Refresh icon from the toolbar. The
complete contents of the SQL editor panel will be sent to the database server
for execution. To execute only a section of the code that is displayed in the
SQL editor, highlight the text that you want the server to execute, and click
the Execute/Refresh icon.
The message returned by the server when a command executes is displayed on the
Messages tab. If the command is successful, the Messages tab displays
Options on the Edit menu offer functionality that helps with code formatting
You can also drag and drop certain objects from the treeview which
can save time in typing long object names. Text containing the object name will be
fully qualified with schema. Double quotes will be added if required.
For functions and procedures, the function name along with parameter names will
be pasted in the Query Tool.
The Data Output panel displays data and statistics generated by the most
recently executed query.
The Data Output tab displays the result set of the query in a table format.
A result set is updatable if:
Any columns that are renamed or selected more than once are also read-only.
Editable and read-only columns are identified using pencil and lock icons
(respectively) in the column headers.
The psycopg2 driver version should be equal to or above 2.8 for updatable
query result sets to work.
An updatable result set is identical to the Data Grid in
View/Edit Data mode, and can be modified in the same way.
If Auto-commit is off, the data changes are made as part of the ongoing
transaction, if no transaction is ongoing a new one is initiated. The data
changes are not committed to the database unless the transaction is committed.
If any errors occur during saving (for example, trying to save NULL into a
column with NOT NULL constraint) the data changes are rolled back to an
automatically created SAVEPOINT to ensure any previously executed queries in
the ongoing transaction are not rolled back.
All rowsets from previous queries or commands that are displayed in the Data
Output panel will be discarded when you invoke another query; open another
Query Tool tab to keep your previous results available.
To generate the Explain or Explain Analyze plan of a query, click on
Explain or Explain Analyze button in the toolbar.
More options related to Explain and Explain Analyze can be selected from
the drop down on the right side of Explain Analyze button in the toolbar.
Please note that pgAdmin generates the Explain [Analyze] plan in JSON format.
On successful generation of Explain plan, it will create three tabs/panels
under the Explain panel.
Please note that EXPLAIN VERBOSE cannot be displayed graphically. Hover over
an icon on the Graphical tab to review information about that item; a popup
window will display information about the selected object. For information on
JIT statistics, triggers and a summary, hover over the icon on top-right
corner; a similar popup window will be displayed when appropriate.
Use the download button on top left corner of the Explain canvas to download
the plan as an SVG file.
Note: Download as SVG is not supported on Internet Explorer.
Note that the query plan that accompanies the Explain analyze is available on
the Data Output tab.
Table tab shows the plan details in table format, it generates table format
similar to explain.depsez.com. Each row of the table represent the data for a
Explain Plan Node. It may contain the node information, exclusive timing,
inclusive timing, actual vs planned rows differences, actual rows, planned
background color of the exclusive, inclusive, and Rows X columns may vary based on the
difference between actual vs planned.
If percentage of the exclusive/inclusive timings of the total query time is:
> 90 - Red color
> 50 - Orange (between red and yellow) color
> 10 - Yellow color
If planner mis-estimated number of rows (actual vs planned) by
10 times - Yellow color
100 times - Orange (between Red and Yellow) color
1000 times - Red color
Statistics tab shows two tables:
1. Statistics per Plan Node Type
2. Statistics per Table
Use the Messages tab to view information about the most recently executed
If the server returns an error, the error message will be displayed on the
Messages tab, and the syntax that caused the error will be underlined in the
SQL editor. If a query succeeds, the Messages tab displays how long the
query took to complete and how many rows were retrieved:
Use the Query History tab to review activity for the current session:
The Query History tab displays information about recent commands:
You can show or hide the queries generated internally by pgAdmin (during
‘View/Edit Data’ or ‘Save Data’ operations).
To erase the content of the Query History tab, select Clear history from
the Clear drop-down menu.
Query History is maintained across sessions for each database on a per-user
basis when running in Query Tool mode. In View/Edit Data mode, history is not
retained. By default, the last 20 queries are stored for each database. This
can be adjusted in config_local.py by overriding the MAX_QUERY_HIST_STORED
value. See the Deployment section for more information.
Use the Connection status feature to view the current connection and
transaction status by clicking on the status icon in the Query Tool: