pgexplain

The pgexplain command is useful for extracting a query and its related parameters from PostgreSQL logs, running it with an appropriate explain, and collecting the results in the form of an SQL Query and a statistical JSON execution plan. Finally, it can submit the query and the plan to explain.dalibo.com and display the result as a consultation URL.

---

Installation 🛠️

To install locally, you can proceed with poetry or pipx:

poetry run pgexplain

or

pipx install -e ./

Usage Examples 💡

You can extract the query from the postgresql logs or from the postgresql container output:

docker compose logs postgresql | pgexplain -lps /tmp/query.sql -f '2025-11-27 08:07:17.338 UTC [69286]'

With this, it's possible to generate the execution plan by running the correct explain:

pgexplain -i /tmp/query.sql -j /tmp/explain.json -d postgresql://myuser@localhost/mydb

Or do everything in a single command: extract the query with parameters by filtering the logs, execute the explain, and finally submit the result online for consultation:

docker compose logs postgresql | pgexplain -lpe -s /tmp/explain.sql -j /tmp/explain.json -f '2025-11-27 08:07:17.338 UTC [69286]' -t 'my use case' -d postgresql://myuser@localhost/mydb

output:

Run explain... done.
https://explain.dalibo.com/plan/xxxxxxxxxxxxxx

---

Notes on Options

• -i specifies an optional file from which to take the SQL query
• -l specifies to read the SQL query and parameters from STDIN instead of a file
• -s specifies an optional file on which to save the "cleaned up" query (with -p)
• -j specifies an optional file on which to save the execution plan
• -e specifies to send the result online for consultation
• -d specifies the database on which to execute the query
• -f specifies the optional filter to apply to locate the query in the logs (useful with -l)
• -t specifies the optional title of the web page