Query tool v8
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:
- Issue ad-hoc SQL queries.
- Execute arbitrary SQL commands.
- Edit the result set of a SELECT query if it is updatable.
- Displays current connection and transaction status as configured by the user.
- Save the data displayed in the output panel to a CSV file.
- Review the execution plan of a SQL statement in either a text, a graphical format or a table format (similar to https://explain.depesz.com).
- View analytical information about a SQL statement.
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 upper panel displays the
SQL Editor
. You can use the panel to enter, edit, or execute a query. It also shows theHistory
tab which can be used to view the queries that have been executed in the session, and aScratch Pad
which can be used to hold text snippets during editing. If the Scratch Pad is closed, it can be re-opened (or additional ones opened) by right-clicking in the SQL Editor and other panels and adding a new panel. - The lower panel displays the
Data Output
panel. The tabbed panel displays the result set returned by a query, information about a query's execution plan, server messages related to the query's execution and any asynchronous notifications received from the server.
The Query tool Toolbar
The Query tool
toolbar uses context-sensitive icons that provide shortcuts to frequently performed tasks. If an icon is highlighted, the option is enabled; if the icon is grayed-out, the task is disabled. Please note that disabled icons may support functionality accessed via the data editor.
Hover over an icon to display a tooltip that describes the icon's functionality:
Icon | Behavior | Shortcut |
---|---|---|
Open File | Click the Open File icon to display a previously saved query in the SQL Editor. | Accesskey + O |
Save | Click the Save icon to perform a quick-save of a previously saved query, or to access the Save menu:- Select Save to save the selected content of the SQL Editor panel in a file. - Select Save As to open a new browser dialog and specify a new location to which to save the selected content of the SQL Editor panel. | Accesskey + S |
Find | Use the Find menu to search, replace, or navigate the code displayed in the SQL Editor:- Select Find to provide a search target, and search the SQL Editor contents. - Select Find next to locate the next occurrence of the search target. - Select Find previous to move to the last occurrence of the search target. - Select Pesistent find to identify all occurrences of the search target within the editor. - Select Replace to locate and replace (with prompting) individual occurrences of the target. - Select Replace all to locate and replace all occurrences of the target within the editor. - Select Jump to navigate to the next occurrence of the search target. | Cmd+F Cmd+G Cmd+Shift+G Cmd+Shift+F Alt+G |
Copy | Click the Copy icon to copy the content that is currently highlighted in the Data Output panel. when in View/Edit data mode. | Accesskey + C |
Paste | Click the Paste icon to paste a previously row into a new row when in View/Edit data mode. | Accesskey + P |
Delete | Click the Delete icon to mark the selected rows for delete when in View/Edit data mode. | Accesskey + D |
Edit | Use options on the Edit menu to access text editing tools; the options operate on the text displayed in the SQL Editor panel when in Query tool mode:- Select Indent Selection to indent the currently selected text. - Select Unindent Selection to remove indentation from the currently selected text. - Select Inline Comment Selection to enclose any lines that contain the selection in SQL style comment notation. - Select Inline Uncomment Selection to remove SQL style comment notation from the selected line. - Select Block Comment to enclose all lines that contain the selection in C style comment notation. This option acts as a toggle. | Tab Shift+Tab Cmd+/ Cmd+. Shift+Cmd+/ |
Filter | Click the Filter icon to set filtering and sorting criteria for the data when in View/Edit data mode. Click the down arrow to access other filtering and sorting options:- Click Sort/Filter to open the sorting and filtering dialogue. - Click Filter by Selection to show only the rows containing the values in the selected cells. - Click Exclude by Selection to show only the rows that do not contain the values in the selected cells. - Click Remove Sort/Filter to remove any previously selected sort or filtering options. | Accesskey + F |
Limit Selector | Select a value in the Limit Selector to limit the size of the dataset to a number of rows. | Accesskey + R |
Stop | Click the Stop icon to cancel the execution of the currently running query. | Accesskey + Q |
Execute/Refresh | Click the Execute/Refresh icon to either execute or refresh the query highlighted in the SQL editor panel. Click the down arrow to access other execution options:- Add a check next to Auto-Rollback to instruct the server to automatically roll back a transaction if an error occurs during the transaction. - Add a check next to Auto-Commit to instruct the server to automatically commit each transaction. Any changes made by the transaction will be visible to others, and durable in the event of a crash. | F5 |
Explain | - Click the Explain icon to view an explanation plan for the current query. The result of the EXPLAIN is displayed graphically on the Explain tab of the output panel, and in text form on the Data Output tab. | F7 |
Explain analyze | Click the Explain analyze icon to invoke an EXPLAIN ANALYZE command on the current query.Navigate through the Explain Options menu to select options for the EXPLAIN command:- Select Verbose to display additional information regarding the query plan. - Select Costs to include information on the estimated startup and total cost of each plan node, as well as the estimated number of rows and the estimated width of each row. - Select Buffers to include information on buffer usage. - Select Timing to include information about the startup time and the amount of time spent in each node of the query. | Shift+F7 |
Commit | Click the Commit icon to commit the transaction. | Shift+CTRL+M |
Rollback | Click the Rollback icon to rollback the transaction. | Shift+CTRL+R |
Clear | Use options on the Clear drop-down menu to erase display contents:- Select Clear Query Window to erase the content of the SQL Editor panel. - Select Clear History to erase the content of the History tab. | Accesskey + L |
Download as CSV | Click the Download as CSV icon to download the result set of the current query to a comma-separated list. You can specify the CSV settings through Preferences -> SQL Editor -> CSV output dialogue. | F8 |
Macros | Click the Macros icon to manage the macros. You can create, edit or clear the macros through Manage Macros option. |
The SQL Editor Panel
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 execution details.
Options on the Edit
menu offer functionality that helps with code formatting and commenting:
- The auto-indent feature will automatically indent text to the same depth as the previous line when you press the Return key.
- Block indent text by selecting two or more lines and pressing the Tab key.
- Implement or remove SQL style or toggle C style comment notation within your code.
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
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. You can:
- Select and copy from the displayed result set.
- Use the
Execute/Refresh
options to retrieve query execution information and set query execution options. - Use the
Save results to file
icon to save the content of theData Output
tab as a comma-delimited file. - Edit the data in the result set of a SELECT query if it is updatable.
A result set is updatable if:
- All columns are either selected directly from a single table, or are not table columns at all (e.g. concatenation of 2 columns). Only columns that are selected directly from the table are editable, other columns are read-only.
- All the primary key columns or OIDs of the table are selected in the result set.
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 browser tab to keep your previous results available.
Explain Panel
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 PEM generates the Explain [Analyze]
plan in JSON format.
On successful generation of Explain
plan, it will create three tabs/panels under the Explain panel.
- Graphical
Please note that EXPLAIN VERBOSE
cannot be displayed graphically. Click on a node icon on the Graphical
tab to review information about that item; a popup window will display on the right side with the information about the selected object. For information on JIT statistics, triggers and a summary, Click on the button 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
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 rows, loops.
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
Statistics
tab shows two tables: 1. Statistics per Plan Node Type 2. Statistics per Table
Messages Panel
Use the Messages
tab to view information about the most recently executed query:
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:
Query tool output information
Notifications Panel
Use the Notifications
tab to view the notifications using PostgreSQL Listen/ Notify
feature. For more details see [PostgreSQL documentation](https:// www.postgresql.org/docs/current/sql-listen.html).
Example:
- Execute
LISTEN "foo"
in firstQuery tool
session
- In the another
Query tool
session, executeNotify
command orpg_notify
function to send the notification of the event together with the payload.
- You can observe the
Notification
tab in the firstQuery tool
session where it shows the Recorded time, Event, Process ID, and the Payload of the particular channel.
Query History Panel
Use the Query History
tab to review activity for the current session:
The Query History tab displays information about recent commands:
- The date and time that a query was invoked.
- The text of the query.
- The number of rows returned by the query.
- The amount of time it took the server to process the query and return a result set.
- Messages returned by the server (not noted on the
Messages
tab). - The source of the query (indicated by icons corresponding to the toolbar).
You can show or hide the queries generated internally by PEM (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.
Connection Status
Use the Connection status
feature to view the current connection and transaction status by clicking on the status icon in query tool:
Change connection
User can connect to another server or database from existing open session of query tool.
- Click on the connection link next to connection status.
- Now click on the <New Connection> option from the dropdown.
- Now select server, database, user, and role to connect and click OK.
- A newly created connection will now get listed in the options.
- To connect, select the newly created connection from the dropdown list.
Macros
Query tool Macros enable you to execute pre-defined SQL queries with a single key press. Pre-defined queries can contain the placeholder $SELECTION$. Upon macro execution, the placeholder will be replaced with the currently selected text in the Query Editor pane of the Query tool.
To create a macro, select the Manage Macros option from the Macros menu on the Query tool. Select the key you wish to use, enter the name of the macro, and the query, optionally including the selection placeholder, and then click the Save button to store the macro.
To clear a macro, select the macro on the Manage Macros dialogue, and then click the Clear button.
The server will prompt you for confirmation to clear the macro.
To clear all macros, click on the Clear button on left side of the key. The server will prompt you for confirmation to clear all the rows.
To execute a macro, simply select the appropriate shortcut keys, or select it from the Macros menu.
Note
If ENABLE_DATA_ACCESS_TOOLS
configuration option is set to False then Query tool is disabled.