Skip to main content
View rawEdit

Run Joe from the CLI (pgai joe)

Joe is the PostgresAI SQL optimization assistant: it runs your EXPLAIN / EXPLAIN ANALYZE requests on an ephemeral DBLab thin clone of your database, so you can analyze and optimize queries with production-identical plans without touching production. pgai joe brings Joe to the terminal (and to scripts and AI agents): plan a query, get the real execution plan, build real or hypothetical indexes, and iterate — every result also lands in the Joe history in the PostgresAI Console.

pgai joe and pgai projects require CLI 0.16 or later.

Reference​

Prerequisites​

  • Node.js 18+ (or Bun 1.0+) to run the CLI.
  • A project in your organization with a registered, active Joe instance (see Joe setup).
  • Your user must hold the AllFeaturesUser or Admin role in the organization — Joe CLI commands are rejected with 403 Forbidden otherwise.

The CLI is published as two equivalent npm packages: postgresai (canonical) and pgai (short wrapper). npm install -g postgresai installs both the postgresai and pgai binaries; npx pgai@latest … runs without installing.

Authenticate​

npx pgai@latest auth login

This opens your browser (OAuth with PKCE), asks you to pick an organization, and stores the resulting API key in ~/.config/postgresai/config.json. All joe commands authenticate with this key. If you choose All my organizations (a global token, CLI 0.17+), add --org <alias> or --org-id <id> to every joe and projects command, or set PGAI_ORG once — see organization selection. See the auth reference for storing a key directly (--set-key), useful in CI.

Find a project with Joe ready​

npx pgai@latest projects
PROJECT_ID  ALIAS      PROJECT        JOE    TUNNEL
12 main-db Main DB ready yes
15 analytics Analytics no no

A ready value in the JOE column means the project has an active Joe instance — those projects can be targeted with --project <id|alias> below. For projects without one, register a Joe instance first, or target a Joe instance directly with --instance-id <id>.

note

Examples below use the short pgai joe … form for brevity; without a global install, run them as npx pgai@latest joe ….

Get a query plan (no execution)​

plan returns the EXPLAIN plan without executing the query — the fast, safe default:

pgai joe plan "select * from users where email = 'alice@example.com'" \
--project main-db
command 3521 · ok
console: https://console.postgres.ai/acme/projects/main-db/sessions/812/commands/40417
plan:
Seq Scan on users (cost=0.00..1877.10 rows=1 width=142)
Filter: ((email)::text = 'alice@example.com'::text)
⚑ client-side: Seq Scan on users — no index serves this predicate; consider adding one.

The console: line is the result's permalink in the Console (see Console links). The ⚑ line is a lightweight client-side hint derived from the structured plan; the full result (plan, statistics, recommendations) is also saved to the Console's SQL optimization history (Query optimization → History).

Get the real execution plan​

explain runs EXPLAIN and EXPLAIN ANALYZE — the query actually executes, on the DBLab clone (never on your production database):

pgai joe explain "select * from users where email = 'alice@example.com'" \
--project main-db
command 3522 · ok
console: https://console.postgres.ai/acme/projects/main-db/sessions/812/commands/40418
plan:
Seq Scan on users (cost=0.00..1877.10 rows=1 width=142)
Filter: ((email)::text = 'alice@example.com'::text)

execution plan (EXPLAIN ANALYZE):
Seq Scan on users (cost=0.00..1877.10 rows=1 width=142) (actual time=8.912..8.914 rows=1 loops=1)
Filter: ((email)::text = 'alice@example.com'::text)
Rows Removed by Filter: 99999
Planning Time: 0.176 ms
Execution Time: 8.987 ms
…

(Sample output abridged — Joe also returns buffer/timing statistics and optimization recommendations when available.) Because the clone shares the production data and planner configuration, plan structure and buffer numbers match production; timing may differ due to cache state — see Joe bot for details.

Test an index idea​

Clones are writable: build a real index with exec, then re-check the plan.

pgai joe exec "create index i_users_email on users (email)" --project main-db
pgai joe explain "select * from users where email = 'alice@example.com'" \
--project main-db

Or use HypoPG hypothetical indexes — no actual index build, instant even on huge tables (affects plan cost estimates only, not real execution):

pgai joe hypo "create index on users (email)" --project main-db
pgai joe plan "select * from users where email = 'alice@example.com'" --project main-db
pgai joe hypo reset --project main-db

(Every joe command except result needs a target: pass --project <id|alias> or --instance-id <id> — or set a default project once with pgai set-default-project <project> and omit both.)

To start over from a pristine clone:

pgai joe reset --project main-db

Inspect and manage clone activity​

# pg_stat_activity snapshot on the clone
pgai joe activity --project main-db

# terminate a runaway backend on the clone
pgai joe terminate 12345 --project main-db

# \d-family metadata: tables, indexes, sizes
pgai joe describe users --project main-db
pgai joe describe users --variant '\d+' --project main-db

Long-running commands​

Commands are synchronous: the CLI submits the command and polls for the result for up to 25 seconds (configurable with --budget <seconds>). If the result isn't ready in time — e.g. a long EXPLAIN ANALYZE, or a cold clone being provisioned — the CLI exits successfully with a resume handle:

started 3523 · pending · budget 25s reached — resume:  pgai joe result 3523

Fetch the result later by command id. Once the result exists, this also prints its console: link:

pgai joe result 3523

Scripting and agents​

Every joe subcommand (and projects) accepts --json for machine-readable output, and --debug to trace API calls:

pgai joe plan "select 1" --project main-db --json | jq '.plan_json'

Exit codes are script-friendly: 0 for a successful result (and for a budget-expired one-shot — resume by id), 1 for a failed command or any error.

In scripts, parse --json and not the human output, because the human format can get new lines over time, such as console:.

Each result that Joe stores gets a permanent link to its page in the Console, which shows the plan, statistics and recommendations; only members of the organization can open it.

The CLI prints the link under the command N · ok line, and with --json the result has the three fields below. The fields come from the platform, so every CLI version that has --json returns them.

FieldMeaning
console_urlThe link: <console>/<org alias>/projects/<project alias>/sessions/<session id>/commands/<console command id>. The session part is sessions/manual for a command run outside a session.
console_command_idThe command's id in the Console (a string). It is not the CLI's command_id, which is the id you pass to pgai joe result.
session_idThe Console session of the result (a string), or null.
pgai joe explain "select * from users where id = 1" --project main-db --json \
| jq -r '.console_url // empty'

Use console_url as given and do not build the link yourself, because it needs the Console's session and command ids, which differ from the CLI's command_id.

Some replies have no link:

  • No stored result. A pending or budget-expired command, help, and a command that Joe rejected have no link, and the three fields are absent. When the command was pending or budget-expired, pgai joe result <id> returns the link after the result is ready.
  • Failed command. There is a link only if Joe stored a result, and the CLI prints it on stderr after the error. A plain SQL error such as relation "x" does not exist usually has none.
  • console_url is null. Either the platform has no Console address configured, which can occur on a self-hosted platform, or the organization or project alias has characters outside A-Z a-z 0-9 . _ ~ -.

The link contains the project alias, so old links stop working after you change the alias; pgai joe result <id> returns a new link.

On postgres.ai, links start with https://console.postgres.ai/, and an older link that starts with https://postgres.ai/console/ redirects to the same page.

In the Console, the query text and the time in each Query optimization → History row link to the same page.

Troubleshooting​

  • Project not found for id/alias/name '…' — run pgai projects to see available projects; the match is case-insensitive on id, alias, and name.
  • Project '…' has no Joe instance — --project requires the project to have a registered, active Joe instance. Register one, or target a Joe instance directly with --instance-id <id>.
  • 403 Forbidden / "Joe API v2 requires the All Features role" — ask an org admin to grant your user the AllFeaturesUser (or Admin) role.
  • 401 — your stored API key is missing or expired; re-run pgai auth login.
  • "This command needs an organization, and you are signed in with a global token" — pass --org <alias> or --org-id <id> (or set PGAI_ORG); run pgai orgs to list the organizations your token can reach.
  • Environments behind Cloudflare Access (previews, some staging setups) — the CLI's plain HTTPS calls may be blocked by the access layer; you may need extra setup (e.g. a service token) or to run from an allowed network. For non-production API endpoints, see environment variables (PGAI_API_BASE_URL).