Adding plans to explain.depesz.com from your psql

So, lately I have been thinking about integrating explain.depesz.com with psql. Currently, you have to run explain, copy the output, switch to webbrowser, go to explain.depesz.coma> site, paste output, click submit. It's not that it's a lot of work, but it can be simplified.

To simplify it, we need just 2 minutes.

First, you need to create /usr/local/bin/explain.depesz.com script (path and name is irrelevant, but /usr/local/bin/ is usually in $PATH, and explain.depesz.com name will be pretty self-explanatory when using it.

Content of this file should be:

#!/usr/bin/env bash
service_url=https://explain.depesz.com
url="$( curl --silent --include --data-urlencode "plan@-" "${service_url}/" | perl -ne 'print if s/\ALocation:\s*(\S+)\s*\z/$1/i' )"
echo "${service_url}${url}"
firefox "${service_url}${url}"

Instead of “firefox" you can use any webbrowser you want, though you might need to play with options to it.

Anyway. When you'll do it, and the script will have 0755 privileges, then in your psql, you run something along the lines of:

$ \o |EXPLAIN.depesz.com
$ EXPLAIN analyze SELECT * FROM pg_class WHERE relname = 'YEAH';
$ \o
Created NEW window IN existing browser SESSION.

That is, you run: “\o |explain.depesz.com", which redirects output of all commands to this script (it should be started by now, by psql).

Then you run your explain command.

And afterwards – you let psql know that you're done with output redirection – with \o.

At this time (at the end), explain.depesz.com, will get all the data, post it to explain.depesz.com, and open explain page in your browser (but only after final \o). You can also put the final \o at the end of explain line (after semicolon).

If this is too much for you to remember (switching \o on and off), you can get the same results by doing:

$ EXPLAIN analyze SELECT * FROM pg_class WHERE relname = 'YEAH' \g | EXPLAIN.depesz.com

that is – replacing “;" with “\g | …".

Hope if will help you, a bit 🙂

5 thoughts on “Adding plans to explain.depesz.com from your psql”

  1. Nice. Unfortunately I’m mostly remotely logged in to a client’s site when I need this, so I created a version that just echoes the URL rather than trying to load it into a browser. My terminal program is smart enough to recognize a URL and make it clickable.

    It’s a pity we can’t easily include the query with the explain.

  2. @Andrew:
    well, re: query – I need help with yacc-style conflcts in grammar. once it will be done, there will be proper sql paste site, and it will be part of explain.depesz.com. The problem – I can’t seem to find anyone to look with me at the conflicts, and help me :/

  3. Thanks for this. I, like @Andrew, preferred a textual display of the URL. I combined this with my existing shortcuts for explain and explain analyze.

    Here’s my setup:
    ~/.psqlrc
    \set exp ‘explain (verbose on)’
    \set expan ‘explain (analyze on, buffers on, verbose on, timing on)’
    \set webexp ‘\\g | explain.depesz.com

    ~/bin/explain.depesz.com
    #!/bin/bash

    curl –silent –include –data-urlencode “plan=$(cat -)” https://explain.depesz.com/ | \
    perl -ne ‘print if s/\ALocation:\s*(\S+)\s*\z/$1\n/’

    Then when in a psql shell:
    foo=> :expan select random() from generate_series(1,1000) :webexp;
    https://explain.depesz.com/s/uMAG

  4. Thanks to MatheusOl from IRC for update for the script – it didn’t work when I migrated to https, but the new version (updated in post body) works great.

Comments are closed.