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

• PostgresAI CLI reference — joe command
• PostgresAI CLI reference — projects command

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 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 = '[email protected]'" \
  --project main-db

command 3521 · ok
plan:
Seq Scan on users  (cost=0.00..1877.10 rows=1 width=142)
  Filter: ((email)::text = '[email protected]'::text)
⚑ client-side: Seq Scan on users — no index serves this predicate; consider adding one.

The ⚑ line is a lightweight client-side hint derived from the structured plan; the full result (plan, statistics, recommendations) is also saved to the Joe history in the Console.

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 = '[email protected]'" \
  --project main-db

command 3522 · ok
plan:
Seq Scan on users  (cost=0.00..1877.10 rows=1 width=142)
  Filter: ((email)::text = '[email protected]'::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 = '[email protected]'::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 = '[email protected]'" \
  --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 = '[email protected]'" --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:

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.

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 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).