pg_explain_lexer: `EXPLAIN ANALYZE` syntax highlighting 2018-11-30
For a document I'm in the process of redacting, I have to include lots of EXPLAIN ANALYZE outputs, since I'm discussing query plans.
However, for people who are not used to reading explains, making sense of all the info in there can be quite daunting.
To improve the readability of this document, I'm making a custom Pygments lexer, to add some syntaxic coloration to the output of EXPLAIN.
It's available on github.
Have a look at the example output file to see how it looks. You can also find an example of how to use it in LaTeX in my test document.
Note that it currently does not handle all possible nodes; I'll add support for the others in the future. Feel free to submit your query plans with unsupported keywords or messed up syntax highlighting as github issues, i'll make sure to fix them.
A cool trick is that you can also use it from psql. To try it in a single session, just input this in psql:
\set color '\\g |pygmentize -l ~/projects/pg_explain_lexer/pg_explain_lexer.py:PgExplainLexer -x'
You can now use :color at the end of an EXPLAIN ANALYZE query to get syntax highlighting in your psql:
EXPLAIN ANALYZE SELECT *
FROM customers c
JOIN orders o ON c.customerid = o.customerid
WHERE o.netamount > 100
ORDER BY c.customerid :color
And the output will look like this:
If you find yourself with the output printed twice, make sure to remove the semicolon at the end of your query, the \g already sends the current query buffer for execution (thanks to ioguix for the tip).
If you want to use this all the time, rather than having to type :color for every explain, psql's pager system can be abused.
The output of a query can be piped to a program to navigate easier than plain mouse scrolling.
This program is usually a pager, such as less, but it can be anything, including pygments.
Since coloring the output of normal SQL queries with this lexer would not be relevant, we'll need to write a wrapper to distinguish query plans from normal queries output, and to redirect the output either to pygments, or to your usual pager.
This ugly one-liner does the job, if you put it in your .psqlrc:
\pset pager always
\setenv PAGER '{ IFS= read -r line; if echo "$line" | grep -q "QUERY PLAN"; then pygmentize -l ~/projects/pg_explain_lexer/pg_explain_lexer.py:PgExplainLexer -x <<<$(printf "%s\n%s" "$line" "$(cat)"); else pspg -XFi -s 5 <<<$(printf "%s\n%s" "$line" "$(cat)"); fi }'
If the first line of output contains the string QUERY PLAN, it will pass the whole output to pygmentize; otherwise, in the else part, it will pass it to the usual pager, pspg in my case. No need to add :color anymore:
I also often need to EXPLAIN ANALYZE queries stored in files, so I added this shell function to my .zshrc:
This function can be passed a query as argument, or a file containing a query:
Note that this assumes that psql is enough to connect to the database, which is achievable using environment variables or a pgpass file.
It also assumes the file contains only one query.
Finally, a user wanted to colorize the output of auto_explain in postgresql's log file, so I made a quick script to do just that:
|
The script is easy to break as the parsing it does is very basic (it checks for a line ending with a semi-colon to determine whether i t has reached the end of a query, for example), but the worst that can happen is that you get weird colours where you shouldn't, so ¯\ (ツ)/¯
Feel free to contribute to the project, or to open an issue with a query plan that's not handled by the lexer; I'll add it to the tests and add support for the missing keywords.