Using PostgreSQL Explain Graph
Need help? Have a suggestion? Navigate to Issues.
Read about the latest updates.
Dive into Advanced Features of PostgreSQL Explain Graph.
Click images below to see them full-size.
Tested for all versions of PostgreSQL from 9.3 - 12.1. Free... for now.
| Step 1 | Copy / paste your JSON explain plan | OR | Enter your server credentials and query. |
|---|
Your query MUST include explain and format json. PostgreSQL Explain Graph will not be able to show the plan without these parameters.
For default installations of PostgreSQL, explain (analyze, buffers, format json, verbose) select ... will execute the query and retrieve all possible information.
You may include any other explain options you wish (##analyze##, ##buffers##, ##costs##, ##settings##, ##summary##, ##timing##, ##verbose##). If you run the actual query (by specifying ##analyze##), it may take a little longer, but you will get far more useful information. Specifying ##explain (format json) select ...## will retrieve the plan soonest because it won't execute the query.
Step 2: Wait for your PostgreSQL database to retrieve the plan details.
Once the JSON plan is retrieved, it will be stored in the JSON plan field. This way, you can study it in the future or send it to others for analysis without running the query again.
If you paste only a JSON explain plan, then click ##Show JSON Plan Graph##, it should appear quickly.
Step 3: Browse your plan graph!
Click operators to see their properties.
Use the toolbar to view actual or estimated rows or data mass, and show the ratios between actual and estimated values. Red and yellow backgrounds indicate largest and second largest percentages. On each arrow, boldface text indicates actual values, and normal text indicates estimates. If there are no numbers, it means there are no values, or you are viewing a ratio. I'll probably be getting rid of the Disk and RAM buttons.
Use the time slider and cost slider to get a sense of how long each operation takes in relation to other operations, as well as the entire query.
If an operator has a little box in the upper-right corner, that means the operation is using workers. Click the little box to toggle worker information.
After you generate two or more explain plan graphs, you can compare their costs and duration. Highlight all plans you want to compare, then click the ##Compare Plans## tab.
= How to Install PostgreSQL Explain Graph =
Prerequisites for Installing on All Operating Systems
This is a Java application, so you'll need a Java Runtime Environment installed, version 8 or later. Sometimes this is referred to as 1.8.
To see what version of Java you have installed, open a command prompt or terminal, and enter this command: ##java --version##
I have version 11 (a.k.a. 1.11) installed as shown: image:java-version.png
= How to Install PostgreSQL Explain Graph on Windows =Make sure you have a Java Runtime Environment installed, at least version 8. To see what version of Java you have installed, open a command prompt, and enter this command: ##java --version## Create a new directory on your machine. Suggestion #1: ##cd c:\Users%USERNAME%\Desktop## ##mkdir "PostgreSQL Explain Graph BETA"## Suggestion #2: ##cd %USERPROFILE%\Desktop## ##mkdir "PostgreSQL Explain Graph BETA"## Don't create the file in a directory you don't have write access to. The Java application has to create a sqlite file for it to work. Download the .ZIP file. [sdm_download id="1999" fancy="0"] Unzip the file in the directory you created. Double-click the file to launch it. If it doesn't launch, create a batch file: {{{ lang=batch rem BEGIN peg.bat c: cd \Users%USERNAME%\Desktop"PostgreSQL Explain Graph BETA" rem -OR- cd %USERPROFILE%\Desktop"PostgreSQL Explain Graph BETA" java -jar peg.jar rem END peg.bat }}} Double-click ##peg.bat## to launch the application.
= How to Install PostgreSQL Explain Graph on a Mac =Make sure you have a Java Runtime Environment installed, at least version 8. To see what version of Java you have installed, open Terminal, and enter this command: ##java --version## Create a new directory on your machine. Don't create the file in a directory you don't have write access to. The Java application has to create a sqlite file for it to work. Suggestion: ##cd ~/Desktop## ##mkdir "PostgreSQL Explain Graph BETA"## Download the .ZIP file. [sdm_download id="1999" fancy="0"] Copy the file to the directory you created. Decompress the file into the directory you created. Open a terminal, and change to the directory you created. ##cd ~/Desktop/"PostgreSQL Explain Graph BETA"## Create a shell file to execute the .JAR file. {{{ lang=batch
cd ~/Desktop/"PostgreSQL Explain Graph BETA" java -jar peg.jar
}}} After creating ##peg.sh##, enable permissions to execute it: ##cd ~/Desktop/"PostgreSQL Explain Graph BETA"## ##chmod +x peg.sh## Double-click ##peg.sh## or enter the command ##./peg.sh## to launch the application.
= How to Install PostgreSQL Explain Graph on Linux =Make sure you have a Java Runtime Environment installed, at least version 8. To see what version of Java you have installed, open a terminal, and enter this command: ##java --version## Create a new directory on your machine. Suggestion: ##cd ~/Desktop## ##mkdir "PostgreSQL Explain Graph BETA"## Don't create the file in a directory you don't have write access to. The Java application has to create a sqlite file for it to work. Download the .ZIP file. [sdm_download id="1999" fancy="0"] Copy the file to the directory you created. Decompress the file into the directory you created. Open a terminal, and change to the directory you created. ##cd ~/Desktop/"PostgreSQL Explain Graph BETA"## Create a shell file to execute the .JAR file. {{{ lang=batch
cd ~/Desktop/"PostgreSQL Explain Graph BETA" java -jar peg.jar
}}} After creating ##peg.sh##, enable permissions to execute it: ##cd ~/Desktop/"PostgreSQL Explain Graph BETA"## ##chmod +x peg.sh## Enter the command ##./peg.sh## to launch the application.
In the Plan Source tab:
After you show an explain graph, the ##Show Plan Graph## text box will show the json explain plan and your PostgreSQL environment info. You can use this to share your plan with your fellow PostgreSQL peeps.
== Not So Great Features and Bugs ==
- I do store passwords in the local sqlite database as plaintext. However, I do not write them (or any server, database name, or usernames) to the JSON text field.
- If there's a connection or query error, it appears in the JSON plan field. It's better than nothing, but I haven't come up with a better solution yet.
- The ##Compare Plans## feature has a minor bug. Sometimes, you have to click a different plan, then select the plan you want to compare.
- The 2000's Java look: I'm a DBA, not a Java developer.
- There are no installers. It's only one file, so I'm not sure it's worthwhile. Your input is welcome on this!
- I have noticed that some large plans (~100kb of JSON text and larger, 60 or more operators) take a moment to render. The largest plan I've tested (the json text was 247k with 111 operators) takes about 9 seconds on my Mac.
- I use a sqlite database to store and process explain plans. I am still working on the application, so occasionally I update the schema. If it's a minor schema update, your plans will remain. If it's a major schema update, your plans will be deleted. To see the sqlite database schema version, navigate to Help / About. At the time of this writing, it's version 3.0. The major version is 3, and the minor version is 0.
== A Few Technical Notes ==
If you see differences between PEG and other tools: actual mass, estimated mass, actual rows, estimated rows
This will be the case if your query uses workers.
Currently, PEG is the only tool that includes worker with parallel leaders. Total rows includes rows fetched by all workers. By extension, total data mass includes workers.
The explain clause ##verbose## controls whether PostgreSQL will return worker information. This has an impact on how closely the graphical plan matches the actual process PostgreSQL performed while running the query. See Advanced Features of PostgreSQL Explain Graph for more information.
When you click the ##Execute and Show Plan Graph## button, at least two queries will execute.
Query: the application will retrieve some PostgreSQL system information from ##pg_settings## and make it part of your json plan. The query will be something like this: ##select name, setting, unit from pg_settings where category like 'Query%' or category like 'Resource%'## Queries: All queries in the ##Execute before running EXPLAIN plan##, if any. Each query must be separated by a semicolon. Query: Your ##explain (analyze, format json, verbose) select ...## query. Queries: All queries in the ##Execute after running EXPLAIN plan##, if any. Each query must be separated by a semicolon.
JSON plan contents: The JSON plan will contain info about your PostgreSQL environment, like version number, actual query, OS (Windows, Ubuntu, etc.), and OS version. People that tune queries often ask for this information.
== Future Plans ==
- Encrypt Passwords. Right now, passwords are stored as plaintext in the sqlite database. While this is not ideal, the sqlite database is stored on the local user's workstation, so it is as secure as the user's own workstation.
- Let Users Rename Plans. Self-explanatory.
- Improve the Layout. For example, I don't like where the ##Compare Plans## tab is. I just can't think of anywhere else to put it. I'm not great at graphical design.
- Add an Installer. I'm working on creating installers using IzPack. I've made the most progress on the Windows installer, but the Linux and Mac installers aren't creating shortcuts on the Desktop. I also don't yet know a best practice of where to place files on the latter two OSs. The actual Java application is one .JAR file, so creating an installer seems like overkill, but having an installer represents a lower barrier-to-entry.
- Add Tear-Away Panels. The ##Plan List##, ##Plan Graph##, ##Properties##, and ##Slider## panels are welded together. It's not possible to rearrange them yet.
- Integrate with a Query Execution tool. My preference would be executequery.org. I've put in a request, but I haven't heard back from Takis Diakoumis, the developer.
Need help? Have a suggestion? PostgreSQL Explain Graph Forums (opens a new window)
[sdm_download id="1999" fancy="0"] [sdm_download_counter id="1999"]
= Gratitude =
Many thanks from Akila Muthuveeraganapathy, depesz (Hubert Lubaczewski), Thomas Munro, Stanislav Silin from Upwork, people on IRC channel #postgresql, people on the Postgres slack channel, and others!