Cockroach: ui: add query plan inspector

[knz]

So @RaduBerinde, @andreimatei and myself have recently found ourselves in want of this feature, so much that we have started to implement part of it (see below), but this was done outside of our product because we don't know to extend the UI code ourselves.

The desired feature is a new page in the UI application called "Query Inspector" which should more or less look like this:

• in the admin RPC code, a new endpoint "AnalyzeQuery" which:
  
  
  
  • takes as input a single string with SQL code, and
  
  
  • returns two JSON objects,
  
  
  • one for the logical plan (code remaining to write)
  
  
  • and one for the physical plan (code partially written already; distSQL part already implemented)
  
  

• in the UI 3 sections organized vertically:

  1. a text input widget where the user can enter SQL code, titled "Query"
  2. an initially empty canvas titled "Logical plan", updated using the RPC results when the user updates the text widget.
  3. an initially empty canvas titled "Physical plan", updated using the RPC results when the user updates the text widget.

  Both canvases would run the graph visualisation interface already prototyped by Radu on https://raduberinde.github.io/

Note: this feature is not about running the query and inspecting its results. This would be quite more complicated to achieve! and not as useful I think, because we already have plenty of tools / apps to run queries.

To give you an idea of how much we're suffering, consider that we currently achieve this by the following steps across at least 3 screens:

1. start the server with some environment variables set (COCKROACH_DISTSQL_LOG_PLAN=1) and verbose logging enabled
2. run some query using the CLI shell
3. select the JSON code produced by the server in its log messages to the clipboard
4. open a browser to Radu's viz on https://raduberinde.github.io/
5. click "start again"
6. paste the clipboard contents
7. click "generate graph"
8. start at step 2 for every other query we want to inspect.

[knz]

Remark by @petermattis: this could be a "hidden" page like we already hide "raft" and "ranges".

[petermattis]

/debug pages are meant for quickly adding developer targeted debugging aids, which is exactly what you're after here.

[kuanluo]

Happy to contribute to the design of this project, @knz, @RaduBerinde, @andreimatei! Let me know if you guys want to set up a time to quickly chat about this, and see what we can do to make this less painful.

[benesch]

@RaduBerinde @andreimatei @knz I was thinking about picking this up—at least a draft implementation that tackles most of the UI work—in the next week or so, in between work on my starter project. Is this still needed/is that timeline okay?

[knz]

Y E S — Ⓨ Ⓔ Ⓢ — Ꙅ Ǝ Y — ⒴ ⒠ ⒮

[RaduBerinde]

@knz Note that as of #13521, COCKROACH_DISTSQL_LOG_PLAN=1 also prints out a URL that encodes the plan. The URL is not pretty, but it's convenient.

https://raduberinde.github.io/decode.html?eJzEkk9L9DAQxu_vp3h5rptDm3jqKSehHlxZvWkOtRkk0CZlksLK0u8uTXHdyuKloMeZef78WnKCD5bum54iqmeUEJAQUDACA4eWYgw8nxZhbY-oCgHnhzHNayPQBiZUJySXOkKFp-a1owM1lhgCllLjuhz-GHqqvaWjzhII7MdU_del0BJmEghj-oydxEVh-duF8i8Lv3oCW2Ky6w4tdzDTFaq74Pw1qIFd3_D7GenWdYl4ptpp-TIWhWq1OqOqLT_mQHEIPtIK4HueESD7RsunxjBySw8c2vyulnGf1XlhKablqpah9vlUzliX5nKLWW4xqx_NNytzMZnp30cAAAD__-sRIow=

[RaduBerinde]

BTW I was thinking of adding an EXPLAIN (DISTSQL) that prints out the plan JSON and this link.

[knz]

It's neat but 1) I don't want to rely on a separate public web site for this (what about privacy / confidentiality) 2) it's a single point of failure 3) it doesn't work if you're not connected to the internet.

Ideally we'd use your html/json and bake it in our UI generator.

[RaduBerinde]

Sure, I was just mentioning it because it's easier in the interim.

[andreimatei]

If this Query Inspector screen would also display tracing information, I could die a happy man :)
For context, @benesch, we have some work in progress tracing work that makes it pretty easy to get (distributed) tracing information for a query. We can talk about it if you're interested.

[benesch]

@knz, are you still planning on/hoping for a logical query plan? The physical half of this is done!

[knz]

Yes working on it now :-)

Sent from my Android device with K-9 Mail. Please excuse my brevity.

[vilterp]

Sadly @jrbotros's work rotted a bit (it had stopped working, and wasn't linked to from anywhere) and was deleted. It'd be nice to have it back, but It seems EXPLAIN (DISTSQL) has somewhat relieved the pain for internal devs which motivated this.

Also, @dianasaur323, myself and others have started talking about it again as a user-facing feature (probably 2.2 timeframe), that you get to from a new "workload" screen which has a table of query fingerprints. Click a fingerprint, get a representative plan. (The notion of "representative plan" needs some refinement).

[gigatexal]

looking forward to this being implemented in the DistSQL or something else

[jordanlewis]

This is done now, in the statements page.