Joe bot commands reference

help

Show help message.

explain

Analyze your query (SELECT, INSERT, DELETE, UPDATE, or WITH) and generate recommendations.

First, EXPLAIN is applied, immediately providing the plan without execution, to understand the plan structure and the planner costs and estimated row numbers for each node in the plan. Right after that, EXPLAIN (ANALYZE, BUFFERS) for the same query will be used to get the execution plan with all the details: the actual time of execution for each node in the plan, the actual number of rows, numbers of shared buffers hit and read, and so on. Please read the chapter "Using EXPLAIN" in the official PostgreSQL documentation.

The final result is provided in both JSON and textual forms. The data is never provided, only meta-data (the detailed plans, timing).

plan

Analyze the specified query (SELECT, INSERT, DELETE, UPDATE, or WITH) without execution.

First, EXPLAIN is applied, immediately providing the plan without execution, to understand the plan structure and the planner costs and estimated row numbers for each node in the plan.

If the plan uses a HypoPG index, an additional EXPLAIN for the same query will be executed to obtain the plan without execution, with the HypoPG extension turned off.

The final result is provided with all available plans without execution. The data is never provided, users get only meta-data.

exec

Execute any query (for example, CREATE INDEX).

The data output is never provided, users see only the time spent on the execution or errors, if any.

activity

Show currently running sessions in Postgres (states: active, idle in transaction, disabled).

terminate [PID]

Terminate Postgres backend that has the specified PID.

reset

Revert the database to the initial state. Usually takes less than a minute.

All database changes applied during the session are discarded and cannot be restored.

psql meta-commands

The following psql meta-commands are supported:

  • \d, \d+ โ€“ relations (tables, views, materialized views, indexes),
  • \dt, \dt+ โ€“ tables,
  • \di, \di+ โ€“ indexes,
  • \dv, \dv+ โ€“ views,
  • \dm, \dm+ โ€“ materialized views,
  • \l, \l+ - databases.

See the official PostgreSQL documentation for details.


Extensions

hypo

Create hypothetical indexes using the HypoPG extension. This allows verifying index ideas without actually building large indexes.

โš  Note that an extended Postgres image for Database Lab is required. For a quick start, you can use prepared images created by Postgres.ai, or prepare your own.

The following subcommands are supported:

  • hypo [CREATE_INDEX_QUERY] โ€“ create a hypothetical index. For example, hypo create index hypo_index_test on table1 using btree (id).
  • hypo desc โ€“ describe all hypothetical indexes.
  • hypo desc [OID] โ€“ describe the specified hypothetical index. For example, hypo desc 18284.
  • hypo drop [OID] โ€“ drop the specified hypothetical index. For example, hypo drop 18284.
  • hypo reset โ€“ drop all hypothetical indexes.