CLI - Query Analysis

The Workflow

In this tutorial, you'll learn how to analyze an SQL command using Metis CLI. First, you'll configure a connection to the DB. Then you'll run an analysis on the SQL Command. The analysis can be based on the Estimated or the Actual plan.
The Estimated plan means the SQL command does not run. Instead, the DB engine uses statistics to evaluate what tables and indexes to use and how many rows will be read and returned. The process is very fast but sometimes not as accurate as Actual plan. The Actual plan, as the name suggests, means the DB engine runs the query, which might take a long time. When the SQL command finishes running it provides detailed stats about its execution.
All of this information is sent to Metis' servers for deep analysis. The CLI returns a direct link to the insights (results) in the web app.

Run the CLI

If you haven't downloaded the CLI, please run
brew tap metis-data/homebrew-cli-brew
brew install metis-cli
Else, you can download the binary from here.
Run The CLI
metis-cli
The screenshots in this tutorial are of Warp (https://www.warp.dev/) terminal.

Main Commands

In this tutorial you'll use the following commands:
Command
Description
addConnection()
Add a new connection to a Postgres server using a prompt
addApiKey(api-key)
Add an API Key. It is required to view the results in the Metis Web App
connect(connection-name)
Connect to the Postgres Server
queryAnalysis(sql)
Analyze an SQL query

Add a New Connection to Postgres

Add a new connection to a Postgres DB using the command addConnection(). It starts a prompt so you can easily type the properties of the connection string
addConnection()
You can add a connection string in one line. The connection string can support advanced features the Wizard doesn't support, such as SSL.
addConnection("neondb","postgres", "postgres://itay:[email protected]:5432/main?ssl=true")​
Connect to the Postgres database.
connect("flights")
To view the existing connections use the command listConnections().

Configure an API Key

The CLI has a basic UI to show the main insights about the query, the web app provides a richer UI with many more details. An API Key can be found in the Web App, under the page "API Key". You can also generate new API Keys on that page.
The page "API Keys" in the web app
In the CLI, add the API Key using the command setApiKey. For ex.
setApiKey("2pqpXmJ5MKlIofNcUwAe9JECgfmKC9q3R9d5I4q0")

Analyze a query

Analyze a query using the command queryAnalysis. The command generates the estimated execution plan. The CLI shows a quick analysis of the query and also sends the data to Metis Web App.
queryAnalysis("SQL_QUERY")
for ex.
queryAnalysis("select * from postgres_air.boarding_pass as b where passenger_id = 4484037")
You can also analyze the query using the actual execution plan.
queryAnalysis("select * from postgres_air.boarding_pass as b where passenger_id = 4484037").execPlanType("Actual")va
The CLI prints a basic query analysis

View the Results in the Web App

At the end of the results, the CLI shows a URL. Click on the link to open the web app. Some terminals (such as iTerm2) don't support this functionality. In this case, you'll have to manually copy the URL to the browser.The Analysis of the query in the Web App:
The Analysis of the query in the Web App:

Tip - Multi-line SQL string

Sometimes the SQL command is a multi-line one. In this case, the CLI must work in editor mode to continue the command even when a new line starts. Type .editor Then enter the SQL command using the character ` . Notice, do NOT use " (double quote) or ' (single quote).
Multi-line SQL Command. Don't forget to run CTRL + D to finish